# Query/filter ordering performance issue

**URL:** <https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804>\
**Category:** Elasticsearch\
**Created:** [June 7, 2019, 6:55pm UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804 "2019-06-07T18:55:35Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![nwood888](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@nwood888](https://discuss.elastic.co/u/nwood888)\
**Post date:** [June 7, 2019, 6:55pm UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804/1 "2019-06-07T18:55:35Z")

</div>

Elastic version: 6.3.2  
Documents: ~120,000,000

We're noticing very odd behavior with how Elastic appears to be ordering filter/query operations. Given the following query:

```
POST /index/_search
{
  "from": 0,
  "size": 5,
  "query": {
"bool": {
  "must": [
    {
      "term": {
        "customer": "customer-id"
      }
    },
    {
      "query_string": {
        "fields": [
          "filename"
        ],
        "query": "*term*"
      }
    }
  ]
}
  }
}

```

Running this query takes ~3.5 seconds.  
Removing the customer terms query/filter takes ~3.5 seconds  
Removing the query\_string query takes 35ms and consists of ~1,500 results (down from 120M total docs in the index)

It seems like the query\_string operation is being run before the term filter since it takes 3.5 seconds with or without the term filter.

My question is, why doesn't Elastic apply the term filter first so that the query\_string operation (more expensive) only runs on 1,500 documents instead of the full 120M?

Ideas I've had but haven't been able to make work yet:

- Rearranging the query to get the term filter to run before the query\_string. I've tried different variations of nesting must/should/bool.
- Passing some type of hint to the query processor so that it knows to run the term filter first. I haven't found any way to do this.

Also, I realize wildcard prefixes cause performance issues, but I don't think that's what's happening here.

Any insight would be appreciated.

Nick

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [June 10, 2019, 1:30pm UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804/2 "2019-06-10T13:30:17Z")

</div>

try moving the `term` query into the `filter` part of the `bool` query.

Also you should rethink your use of wildcards in the query string query. Maybe using shingles, ngrams or edge-ngrams at index time would yield better results at query time.

---

<div class="post-metadata">

**Author:** ![nwood888](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@nwood888](https://discuss.elastic.co/u/nwood888)\
**Post date:** [June 10, 2019, 4:53pm UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804/3 "2019-06-10T16:53:18Z")

</div>

Thank you @spinscale. Though I tried this and it still doesn't seem to address the performance issue. It does cause the end result-set to be filtered correctly, but it does not limit the initial search data set so performance is still poor.

```
POST /index/_search
{
  "from": 0,
  "size": 5,
  "query": {
    "bool": {
      "filter": {
          "term": {
            "customer": "customer-id"
          }
      }, 
      "must": [
        {
          "query_string": {
            "fields": [
              "filename"
            ],
            "query": "*term*"
          }
        }
      ]
    }
  }
} 

```

We'll try the other analyzers, but even if we get that working, it seems like this filter issue would provide a massive performance improvement.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [June 11, 2019, 7:13am UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804/4 "2019-06-11T07:13:43Z")

</div>

I think that the query string query is so expensive, that the filter does not play a role.

Have you tried using the profile API? See [https://www.elastic.co/guide/en/elasticsearch/reference/7.1/search-profile.html](https://www.elastic.co/guide/en/elasticsearch/reference/7.1/search-profile.html) and [https://www.elastic.co/guide/en/kibana/7.1/xpack-profiler.html](https://www.elastic.co/guide/en/kibana/7.1/xpack-profiler.html)

---

<div class="post-metadata">

**Author:** ![nwood888](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@nwood888](https://discuss.elastic.co/u/nwood888)\
**Post date:** [June 11, 2019, 5:17pm UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804/5 "2019-06-11T17:17:21Z")

</div>

Thank you @spinscale. I'll check out the profiler.

For the time being, we've made use of the standard analyzer, ngrams and removed the wildcard prefix and are seeing significant improvements.

---

<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:** [July 9, 2019, 5:20pm UTC](https://discuss.elastic.co/t/query-filter-ordering-performance-issue/184804/6 "2019-07-09T17:20:51Z")

</div>

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