# Query super slow whenever first time on an index

**URL:** <https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643>\
**Category:** Elasticsearch\
**Created:** [December 13, 2017, 8:37pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643 "2017-12-13T20:37:24Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![akirafei](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@akirafei](https://discuss.elastic.co/u/akirafei)\
**Post date:** [December 13, 2017, 8:37pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/1 "2017-12-13T20:37:24Z")

</div>

Similar query

first time: 19.301 s

second time: 0.314 s

index status after the first query

> <https://gist.github.com/xubo-fei/78f97dc3948f63bffcf10a47c467839d>

---

<div class="post-metadata">

**Author:** ![akirafei](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@akirafei](https://discuss.elastic.co/u/akirafei)\
**Post date:** [December 13, 2017, 8:38pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/2 "2017-12-13T20:38:48Z")

</div>

cluster status

> <https://gist.github.com/xubo-fei/7f83c9be573ce2e31a6f1c7e7ab870b2>

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [December 13, 2017, 8:52pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/3 "2017-12-13T20:52:47Z")

</div>

You have 973.385.595 docs with 1.123.353.812.871 (1.1 Tb if I'm not mistaken) with only 2 data nodes?

That might explain that it takes a lot of time to read the data from disk the first time. On the second run, I guess that the FS Cache is playing its role.

What is the query you are running BTW?

Ho! I just saw that BTW:

```auto
"spins": "true"

```

Use SSD Drives!

---

<div class="post-metadata">

**Author:** ![akirafei](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@akirafei](https://discuss.elastic.co/u/akirafei)\
**Post date:** [December 13, 2017, 8:53pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/4 "2017-12-13T20:53:33Z")

</div>

```auto
{

  "size":500,

  "sort":[{"@timestamp":{"order":"desc","unmapped_type":"boolean"}}],

  "query":

    {"bool":

      {"must":[

        {"query_string":

          {"analyze_wildcard":true,

            "default_field":"message",

            "query":"event_code:5156 AND _type:logs AND exa_parser_name:q-5156 AND host:QRADAR_5156_HOST_NAME AND data_type:process-network AND dest_port:88"

          }

        }],

        "must_not":[]

      }

    }

}

```

---

<div class="post-metadata">

**Author:** ![akirafei](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@akirafei](https://discuss.elastic.co/u/akirafei)\
**Post date:** [December 13, 2017, 8:54pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/5 "2017-12-13T20:54:23Z")

</div>

Yes 1T data.

It works well on similar physical machines.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [December 13, 2017, 8:55pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/6 "2017-12-13T20:55:06Z")

</div>

If you did not notice I updated my answer in the meantime.

---

<div class="post-metadata">

**Author:** ![akirafei](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@akirafei](https://discuss.elastic.co/u/akirafei)\
**Post date:** [December 13, 2017, 8:57pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/7 "2017-12-13T20:57:12Z")

</div>

Thanks,

1. is there anyway to get the time spend on fetching from disk?
2. I tried close the index and reopen the index, seems query is still fast. FS cache not clear after close the index?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [December 13, 2017, 8:59pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/8 "2017-12-13T20:59:09Z")

</div>

1. I don't know
2. Why would it? The OS does not know that we are not going to access the files anymore.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 10, 2018, 8:59pm UTC](https://discuss.elastic.co/t/query-super-slow-whenever-first-time-on-an-index/111643/9 "2018-01-10T20:59:09Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.