We had a really long query (it could be up to 30k lines), and in v8 it took around 50 ms. After upgrading to v9, the query took around 400–500 ms.
When we profiled it, the results were essentially identical in profile.shards[0].searches. (profiled v8 and v9)
We reindexed everything, checked the mappings, templates, and cluster configuration, but we didn’t find any differences.
Then we refactored the query, which reduced the query size by about 75% (from 30k to 7k lines). The query performance improved by roughly 15%, but it’s still far from the performance we had in v8.
The original query, but simplified:
GET some-index/_search
{ "collapse": { "field": "relationships.master.data.id.raw" }, "from": 0, "profile": true, "size": 100, "query": { "bool": { "must": [ { "bool": { "filter": { "bool": { "must": [ { "term": { "id": "some-id" } } ] } } } }, { "bool": { "should": [ { "bool": { "filter": [ { "term": { "meta.locale": "ar-INT" } } ] } }, { "bool": { "filter": [ { "term": { "meta.locale": "az-AZ" } } ], "must_not": [ { "term": { "translations": "ar-INT" } } ] } }, { "bool": { "filter": [ { "term": { "meta.locale": "az-INT" } } ], "must_not": [ { "term": { "translations": "ar-INT" } }, { "term": { "translations": "az-AZ" } } ] } }, { "bool": { "filter": [ { "term": { "meta.locale": "bs-BA" } } ], "must_not": [ { "term": { "translations": "ar-INT" } }, { "term": { "translations": "az-AZ" } }, { "term": { "translations": "az-INT" } } ] } } ] } } ] } }, "_source": false }
The optimized query, but simplified:
GET some-index/_search
{ "collapse": { "field": "relationships.master.data.id.raw" }, "from": 0, "profile": true, "size": 100, "query": { "bool": { "must": [ { "bool": { "filter": { "bool": { "must": [ { "term": { "id": "some-id" } } ] } } } }, { "bool": { "should": [ { "bool": { "filter": [ { "term": { "meta.locale": "ar-INT" } } ] } }, { "bool": { "filter": [ { "term": { "meta.locale": "az-AZ" } } ], "must_not": [ { "terms": { "translations": [ "ar-INT" ] } } ] } }, { "bool": { "filter": [ { "term": { "meta.locale": "az-INT" } } ], "must_not": [ { "terms": { "translations": [ "ar-INT", "az-AZ" ] } } ] } }, { "bool": { "filter": [ { "term": { "meta.locale": "bs-BA" } } ], "must_not": [ { "terms": { "translations": [ "ar-INT", "az-AZ", "az-INT" ] } } ] } } ] } } ] } }, "_source": false }
However, after the optimization, we’re seeing different profile metrics. The profile.shards[0].searches[0].query still has identically matching entries, but a lot of duplication appears.
In the optimized query, I’ve observed the following duplication behavior:
In v8, the profile.shards[0].searches[0].query array contains 2 elements. The first element is a BooleanQuery, and the second is a TermQuery with the description translations:az-AZ.
In v9, the profile.shards[0].searches[0].query array contains 26 elements. The first two queries are exactly the same (BooleanQuery and TermQuery), but the next 24 elements are duplicates of the TermQuery with the description translations:az-AZ.
As the query grows, these duplicates appear more and more often in profile.shards[0].searches[0].query.
Is this a bug?
Could this be causing the performance difference between v8 and v9?
Where else could the performance bottleneck originate?
And most importantly: how can we fix it?