Regression after upgrade to v9: query execution drastically increased, and query profile behaves different #137488
Description
Elasticsearch Version
9.0.4
Installed Plugins
No response
Java Version
bundled
OS Version
elastic cloud
Problem Description
We have upgraded our elastic cloud version from v8 to v9, and we see a massive execution regression in some our queries.
v8 where it worked: 8.3.3, 8.18.4, 8.18, 8.19.6
v9 where regression experienced: 9.0.4, 9.1.5, 9.2.0
I can provide a minimal reproduction where the profile starts to diverge:
GET some-index/_search
{ "from": 0, "profile": true, "size": 100, "query": { "bool": { "must": [ { "bool": { "filter": { "bool": { "must": [ { "term": { "id": "f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38" } } ] } } } }, { "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" ] } } ] } } ] } } ] } }, "_source": false }in v8 we got the following profile metrics:
{ "took": 0, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 0, "relation": "eq" }, "max_score": null, "hits": [] }, "profile": { "shards": [ { "id": "[nodeId][some-index][0]", "node_id": "nodeId", "shard_id": 0, "index": "some-index", "cluster": "(local)", "searches": [ { "query": [ { "type": "BooleanQuery", "description": "+(ConstantScore(id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38))^0.0 +((ConstantScore(meta.locale:ar-INT))^0.0 (-translations:(ar-INT) #meta.locale:az-AZ) (-translations:(ar-INT az-AZ) #meta.locale:az-INT))", "time_in_nanos": 291882, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 9077, "match": 0, "score_count": 0, "next_doc_count": 1, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 30, "create_weight": 9347, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 273458 }, "children": [ { "type": "BoostQuery", "description": "(ConstantScore(id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38))^0.0", "time_in_nanos": 208786, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 1, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 678, "advance_count": 2, "score": 0, "count_weight_count": 0, "build_scorer_count": 31, "create_weight": 2578, "shallow_advance": 133, "count_weight": 0, "create_weight_count": 1, "build_scorer": 205397 }, "children": [ { "type": "TermQuery", "description": "id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38", "time_in_nanos": 197010, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 491, "advance_count": 2, "score": 0, "count_weight_count": 0, "build_scorer_count": 31, "create_weight": 690, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 195829 } } ] }, { "type": "BooleanQuery", "description": "(ConstantScore(meta.locale:ar-INT))^0.0 (-translations:(ar-INT) #meta.locale:az-AZ) (-translations:(ar-INT az-AZ) #meta.locale:az-INT)", "time_in_nanos": 38993, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 1, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 2567, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 3, "create_weight": 3989, "shallow_advance": 1486, "count_weight": 0, "create_weight_count": 1, "build_scorer": 30951 }, "children": [ { "type": "BoostQuery", "description": "(ConstantScore(meta.locale:ar-INT))^0.0", "time_in_nanos": 4158, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 1, "create_weight": 450, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3708 }, "children": [ { "type": "TermQuery", "description": "meta.locale:ar-INT", "time_in_nanos": 3641, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 1, "create_weight": 73, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3568 } } ] }, { "type": "BooleanQuery", "description": "-translations:(ar-INT) #meta.locale:az-AZ", "time_in_nanos": 19609, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 1, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 2406, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 3, "create_weight": 1111, "shallow_advance": 1104, "count_weight": 0, "create_weight_count": 1, "build_scorer": 14988 }, "children": [ { "type": "MultiTermQueryConstantScoreBlendedWrapper", "description": "translations:(ar-INT)", "time_in_nanos": 8872, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 1, "create_weight": 264, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 8608 } }, { "type": "TermQuery", "description": "meta.locale:az-AZ", "time_in_nanos": 4398, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 1182, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 3, "create_weight": 151, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3065 } } ] }, { "type": "BooleanQuery", "description": "-translations:(ar-INT az-AZ) #meta.locale:az-INT", "time_in_nanos": 6511, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 1, "create_weight": 1386, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 5125 }, "children": [ { "type": "MultiTermQueryConstantScoreBlendedWrapper", "description": "translations:(ar-INT az-AZ)", "time_in_nanos": 3796, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 1, "create_weight": 40, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3756 } }, { "type": "TermQuery", "description": "meta.locale:az-INT", "time_in_nanos": 1066, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 1, "create_weight": 76, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 990 } } ] } ] } ] } ], "rewrite_time": 41759, "collector": [ { "name": "QueryPhaseCollector", "reason": "search_query_phase", "time_in_nanos": 9978, "children": [ { "name": "SimpleTopScoreDocCollector", "reason": "search_top_hits", "time_in_nanos": 2740 } ] } ] } ], "aggregations": [] } ] } }In v9 we got the following profile metrics:
{ "took": 3, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 0, "relation": "eq" }, "max_score": null, "hits": [] }, "profile": { "shards": [ { "id": "[nodeId][some-index][0]", "node_id": "nodeId", "shard_id": 0, "index": "some-index", "cluster": "(local)", "searches": [ { "query": [ { "type": "BooleanQuery", "description": "+(ConstantScore(id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38))^0.0 +((ConstantScore(meta.locale:ar-INT))^0.0 (-translations:(ar-INT) #meta.locale:az-AZ) (-translations:(ar-INT az-AZ) #meta.locale:az-INT))", "time_in_nanos": 2018156, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 63124, "match": 0, "next_doc_count": 13, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 35, "create_weight": 15278, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 1939754 }, "children": [ { "type": "BoostQuery", "description": "(ConstantScore(id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38))^0.0", "time_in_nanos": 499161, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 13, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 6145, "advance_count": 16, "score": 0, "count_weight_count": 0, "build_scorer_count": 48, "create_weight": 2429, "shallow_advance": 2426, "count_weight": 0, "create_weight_count": 1, "build_scorer": 488161 }, "children": [ { "type": "TermQuery", "description": "id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38", "time_in_nanos": 464606, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 3660, "advance_count": 16, "score": 0, "count_weight_count": 0, "build_scorer_count": 48, "create_weight": 1116, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 459830 } } ] }, { "type": "BooleanQuery", "description": "(ConstantScore(meta.locale:ar-INT))^0.0 (-translations:(ar-INT) #meta.locale:az-AZ) (-translations:(ar-INT az-AZ) #meta.locale:az-INT)", "time_in_nanos": 1297550, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 13, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 15548, "advance_count": 5, "score": 0, "count_weight_count": 0, "build_scorer_count": 43, "create_weight": 9534, "shallow_advance": 98687, "count_weight": 0, "create_weight_count": 1, "build_scorer": 1173781 }, "children": [ { "type": "BoostQuery", "description": "(ConstantScore(meta.locale:ar-INT))^0.0", "time_in_nanos": 123521, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 6, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 870, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 29, "create_weight": 1165, "shallow_advance": 460, "count_weight": 0, "create_weight_count": 1, "build_scorer": 121026 }, "children": [ { "type": "TermQuery", "description": "meta.locale:ar-INT", "time_in_nanos": 112238, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 477, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 29, "create_weight": 115, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 111646 } } ] }, { "type": "BooleanQuery", "description": "-translations:(ar-INT) #meta.locale:az-AZ", "time_in_nanos": 421371, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 13, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 11824, "advance_count": 5, "score": 0, "count_weight_count": 0, "build_scorer_count": 43, "create_weight": 2765, "shallow_advance": 737, "count_weight": 0, "create_weight_count": 1, "build_scorer": 406045 }, "children": [ { "type": "MultiTermQueryConstantScoreBlendedWrapper", "description": "translations:(ar-INT)", "time_in_nanos": 154707, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 17, "create_weight": 263, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 154444 } }, { "type": "TermQuery", "description": "meta.locale:az-AZ", "time_in_nanos": 179387, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 11273, "advance_count": 5, "score": 0, "count_weight_count": 0, "build_scorer_count": 43, "create_weight": 99, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 168015 } } ] }, { "type": "BooleanQuery", "description": "-translations:(ar-INT az-AZ) #meta.locale:az-INT", "time_in_nanos": 493088, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 13, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 452, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 43, "create_weight": 3610, "shallow_advance": 589, "count_weight": 0, "create_weight_count": 1, "build_scorer": 488437 }, "children": [ { "type": "MultiTermQueryConstantScoreBlendedWrapper", "description": "translations:(ar-INT az-AZ)", "time_in_nanos": 248167, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 29, "create_weight": 66, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 248101 } }, { "type": "TermQuery", "description": "meta.locale:az-INT", "time_in_nanos": 118308, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 172, "advance_count": 3, "score": 0, "count_weight_count": 0, "build_scorer_count": 43, "create_weight": 106, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 118030 } } ] } ] } ] }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 5071, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 271, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4800 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4857, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 279, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4578 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4152, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 271, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3881 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4923, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 221, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4702 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 5006, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 213, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4793 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4587, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 230, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4357 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4653, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 214, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4439 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 3881, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 197, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3684 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4012, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 189, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3823 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 3716, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 213, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 3503 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4719, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 197, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4522 } }, { "type": "TermQuery", "description": "translations:az-AZ", "time_in_nanos": 4923, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 2, "create_weight": 172, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4751 } } ], "rewrite_time": 75140, "collector": [ { "name": "QueryPhaseCollector", "reason": "search_query_phase", "time_in_nanos": 23331, "children": [ { "name": "TopScoreDocCollector", "reason": "search_top_hits", "time_in_nanos": 10376 } ] } ] } ], "aggregations": [] } ] } }We have experienced the following differences:
- You will see a whole bunch more BooleanQuery and TermQuery in v9 than in v8.
- For the same query executed multiple times in v9, we received different profiling results (different number of BooleanQuery and TermQuery).
Forum discussion for more detailed description
Steps to Reproduce
As Mayya from the forum has requested, I have tested where the regression manifested:
The following query has quite similar execution time, and didn't experience any regression.
GET some-index/_search
{ "from": 0, "profile": true, "size": 100, "query": { "bool": { "filter": [ { "term": { "meta.locale": "az-AZ" } } ], "must_not": [ { "terms": { "translations": [ "ar-LB", "ar-MA", "ar-MEA", "ar-OM", "ar-QA", "ar-SA", "ar-TN", "az-AZ", "bg-BG", "bs-BA", "cs-CZ", "da-DK", "de-AT", "de-CH", "de-DE", "de-INT", "el-CY", "el-GR", "en-AU", "en-CA", "en-CAR", "en-EG", "en-EU", "en-GB", "en-HK", "en-IE", "en-IN", "en-INT", "en-JM", "en-KE", "en-KW", "en-LK", "en-MEA", "en-MK", "en-MT", "en-MV", "en-MY", "en-NA", "en-NG", "en-NZ", "en-OM", "en-PH", "en-PK", "en-SE", "en-SG", "en-US", "en-ZA", "es-AR", "es-BO", "es-CAR", "es-CL", "es-CO", "es-CR", "es-DO", "es-EC", "es-ES", "es-INT", "es-LAT", "es-MX", "es-PA", "es-PE", "et-EE", "fi-FI", "fr-BE", "fr-CA", "fr-CH", "fr-DZ", "fr-FR", "fr-INT", "fr-MU", "fr-SN", "he-IL", "hi-IN", "hr-HR", "hu-HU", "id-ID", "is-IS", "it-IT", "ja-JP", "ka-GE", "kk-KZ", "ko-KR", "lt-LT", "lv-LV", "mk-MK", "nl-BE", "nl-NL", "no-NO", "pl-PL", "pt-AO", "pt-BR", "pt-PT", "ro-RO", "ru-BY", "ru-KZ", "ru-RU", "ru-UZ", "sk-SK", "sl-SI", "sq-AL", "sq-KV", "sr-ME", "sr-RS", "sv-SE", "th-TH", "tr-TR", "uk-UA", "zh-CN", "zh-HK" ] } } ] } }, "_source": false }In the next step we add the id to the filter, and thats the point when we experienced the performance bottleneck:
GET some-index/_search
{ "from": 0, "profile": true, "size": 100, "query": { "bool": { "filter": [ { "term": { "id": "f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38" } }, { "term": { "meta.locale": "az-AZ" } } ], "must_not": [ { "terms": { "translations": [ "ar-LB", "ar-MA", "ar-MEA", "ar-OM", "ar-QA", "ar-SA", "ar-TN", "az-AZ", "bg-BG", "bs-BA", "cs-CZ", "da-DK", "de-AT", "de-CH", "de-DE", "de-INT", "el-CY", "el-GR", "en-AU", "en-CA", "en-CAR", "en-EG", "en-EU", "en-GB", "en-HK", "en-IE", "en-IN", "en-INT", "en-JM", "en-KE", "en-KW", "en-LK", "en-MEA", "en-MK", "en-MT", "en-MV", "en-MY", "en-NA", "en-NG", "en-NZ", "en-OM", "en-PH", "en-PK", "en-SE", "en-SG", "en-US", "en-ZA", "es-AR", "es-BO", "es-CAR", "es-CL", "es-CO", "es-CR", "es-DO", "es-EC", "es-ES", "es-INT", "es-LAT", "es-MX", "es-PA", "es-PE", "et-EE", "fi-FI", "fr-BE", "fr-CA", "fr-CH", "fr-DZ", "fr-FR", "fr-INT", "fr-MU", "fr-SN", "he-IL", "hi-IN", "hr-HR", "hu-HU", "id-ID", "is-IS", "it-IT", "ja-JP", "ka-GE", "kk-KZ", "ko-KR", "lt-LT", "lv-LV", "mk-MK", "nl-BE", "nl-NL", "no-NO", "pl-PL", "pt-AO", "pt-BR", "pt-PT", "ro-RO", "ru-BY", "ru-KZ", "ru-RU", "ru-UZ", "sk-SK", "sl-SI", "sq-AL", "sq-KV", "sr-ME", "sr-RS", "sv-SE", "th-TH", "tr-TR", "uk-UA", "zh-CN", "zh-HK" ] } } ] } }, "_source": false }Profile output in v8:
{ "took": 1, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 0, "relation": "eq" }, "max_score": null, "hits": [] }, "profile": { "shards": [ { "id": "[nodeId][some-index][0]", "node_id": "nodeId", "shard_id": 0, "index": "some-index", "cluster": "(local)", "searches": [ { "query": [ { "type": "BooleanQuery", "description": "-translations:(ar-LB ar-MA ar-MEA ar-OM ar-QA ar-SA ar-TN az-AZ bg-BG bs-BA cs-CZ da-DK de-AT de-CH de-DE de-INT el-CY el-GR en-AU en-CA en-CAR en-EG en-EU en-GB en-HK en-IE en-IN en-INT en-JM en-KE en-KW en-LK en-MEA en-MK en-MT en-MV en-MY en-NA en-NG en-NZ en-OM en-PH en-PK en-SE en-SG en-US en-ZA es-AR es-BO es-CAR es-CL es-CO es-CR es-DO es-EC es-ES es-INT es-LAT es-MX es-PA es-PE et-EE fi-FI fr-BE fr-CA fr-CH fr-DZ fr-FR fr-INT fr-MU fr-SN he-IL hi-IN hr-HR hu-HU id-ID is-IS it-IT ja-JP ka-GE kk-KZ ko-KR lt-LT lv-LV mk-MK nl-BE nl-NL no-NO pl-PL pt-AO pt-BR pt-PT ro-RO ru-BY ru-KZ ru-RU ru-UZ sk-SK sl-SI sq-AL sq-KV sr-ME sr-RS sv-SE th-TH tr-TR uk-UA zh-CN zh-HK) #id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38 #meta.locale:az-AZ", "time_in_nanos": 580437, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 2942, "match": 0, "next_doc_count": 1, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "count_weight_count": 0, "score": 0, "build_scorer_count": 25, "create_weight": 4929, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 572566 }, "children": [ { "type": "MultiTermQueryConstantScoreBlendedWrapper", "description": "translations:(ar-LB ar-MA ar-MEA ar-OM ar-QA ar-SA ar-TN az-AZ bg-BG bs-BA cs-CZ da-DK de-AT de-CH de-DE de-INT el-CY el-GR en-AU en-CA en-CAR en-EG en-EU en-GB en-HK en-IE en-IN en-INT en-JM en-KE en-KW en-LK en-MEA en-MK en-MT en-MV en-MY en-NA en-NG en-NZ en-OM en-PH en-PK en-SE en-SG en-US en-ZA es-AR es-BO es-CAR es-CL es-CO es-CR es-DO es-EC es-ES es-INT es-LAT es-MX es-PA es-PE et-EE fi-FI fr-BE fr-CA fr-CH fr-DZ fr-FR fr-INT fr-MU fr-SN he-IL hi-IN hr-HR hu-HU id-ID is-IS it-IT ja-JP ka-GE kk-KZ ko-KR lt-LT lv-LV mk-MK nl-BE nl-NL no-NO pl-PL pt-AO pt-BR pt-PT ro-RO ru-BY ru-KZ ru-RU ru-UZ sk-SK sl-SI sq-AL sq-KV sr-ME sr-RS sv-SE th-TH tr-TR uk-UA zh-CN zh-HK)", "time_in_nanos": 364073, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "count_weight_count": 0, "score": 0, "build_scorer_count": 25, "create_weight": 329, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 363744 } }, { "type": "TermQuery", "description": "id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38", "time_in_nanos": 123895, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "next_doc_count": 0, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 648, "advance_count": 2, "count_weight_count": 0, "score": 0, "build_scorer_count": 26, "create_weight": 418, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 122829 } }, { "type": "TermQuery", "description": "meta.locale:az-AZ", "time_in_nanos": 5689, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 1036, "match": 0, "next_doc_count": 1, "score_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 116, "advance_count": 2, "count_weight_count": 0, "score": 0, "build_scorer_count": 3, "create_weight": 47, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 4490 } } ] } ], "rewrite_time": 21903, "collector": [ { "name": "QueryPhaseCollector", "reason": "search_query_phase", "time_in_nanos": 28390, "children": [ { "name": "SimpleTopScoreDocCollector", "reason": "search_top_hits", "time_in_nanos": 2779 } ] } ] } ], "aggregations": [] } ] } }Profile output in v9:
{ "took": 22, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 0, "relation": "eq" }, "max_score": null, "hits": [] }, "profile": { "shards": [ { "id": "[nodeId][some-index][0]", "node_id": "nodeId", "shard_id": 0, "index": "some-index", "cluster": "(local)", "searches": [ { "query": [ { "type": "BooleanQuery", "description": "-translations:(ar-LB ar-MA ar-MEA ar-OM ar-QA ar-SA ar-TN az-AZ bg-BG bs-BA cs-CZ da-DK de-AT de-CH de-DE de-INT el-CY el-GR en-AU en-CA en-CAR en-EG en-EU en-GB en-HK en-IE en-IN en-INT en-JM en-KE en-KW en-LK en-MEA en-MK en-MT en-MV en-MY en-NA en-NG en-NZ en-OM en-PH en-PK en-SE en-SG en-US en-ZA es-AR es-BO es-CAR es-CL es-CO es-CR es-DO es-EC es-ES es-INT es-LAT es-MX es-PA es-PE et-EE fi-FI fr-BE fr-CA fr-CH fr-DZ fr-FR fr-INT fr-MU fr-SN he-IL hi-IN hr-HR hu-HU id-ID is-IS it-IT ja-JP ka-GE kk-KZ ko-KR lt-LT lv-LV mk-MK nl-BE nl-NL no-NO pl-PL pt-AO pt-BR pt-PT ro-RO ru-BY ru-KZ ru-RU ru-UZ sk-SK sl-SI sq-AL sq-KV sr-ME sr-RS sv-SE th-TH tr-TR uk-UA zh-CN zh-HK) #id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38 #meta.locale:az-AZ", "time_in_nanos": 20823063, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 29185, "match": 0, "score_count": 0, "next_doc_count": 11, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 41, "create_weight": 4143, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 20789735 }, "children": [ { "type": "MultiTermQueryConstantScoreBlendedWrapper", "description": "translations:(ar-LB ar-MA ar-MEA ar-OM ar-QA ar-SA ar-TN az-AZ bg-BG bs-BA cs-CZ da-DK de-AT de-CH de-DE de-INT el-CY el-GR en-AU en-CA en-CAR en-EG en-EU en-GB en-HK en-IE en-IN en-INT en-JM en-KE en-KW en-LK en-MEA en-MK en-MT en-MV en-MY en-NA en-NG en-NZ en-OM en-PH en-PK en-SE en-SG en-US en-ZA es-AR es-BO es-CAR es-CL es-CO es-CR es-DO es-EC es-ES es-INT es-LAT es-MX es-PA es-PE et-EE fi-FI fr-BE fr-CA fr-CH fr-DZ fr-FR fr-INT fr-MU fr-SN he-IL hi-IN hr-HR hu-HU id-ID is-IS it-IT ja-JP ka-GE kk-KZ ko-KR lt-LT lv-LV mk-MK nl-BE nl-NL no-NO pl-PL pt-AO pt-BR pt-PT ro-RO ru-BY ru-KZ ru-RU ru-UZ sk-SK sl-SI sq-AL sq-KV sr-ME sr-RS sv-SE th-TH tr-TR uk-UA zh-CN zh-HK)", "time_in_nanos": 20163565, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 0, "match": 0, "score_count": 0, "next_doc_count": 0, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 0, "advance_count": 0, "score": 0, "count_weight_count": 0, "build_scorer_count": 41, "create_weight": 279, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 20163286 } }, { "type": "TermQuery", "description": "id:f4c9c13b-9d9a-4ec1-8074-bb26cacbcc38", "time_in_nanos": 344952, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 2839, "match": 0, "score_count": 0, "next_doc_count": 10, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 730, "advance_count": 4, "score": 0, "count_weight_count": 0, "build_scorer_count": 52, "create_weight": 714, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 340669 } }, { "type": "TermQuery", "description": "meta.locale:az-AZ", "time_in_nanos": 165008, "breakdown": { "set_min_competitive_score_count": 0, "match_count": 0, "shallow_advance_count": 0, "set_min_competitive_score": 0, "next_doc": 492, "match": 0, "score_count": 0, "next_doc_count": 1, "compute_max_score_count": 0, "compute_max_score": 0, "advance": 19035, "advance_count": 14, "score": 0, "count_weight_count": 0, "build_scorer_count": 37, "create_weight": 107, "shallow_advance": 0, "count_weight": 0, "create_weight_count": 1, "build_scorer": 145374 } } ] } ], "rewrite_time": 23622, "collector": [ { "name": "QueryPhaseCollector", "reason": "search_query_phase", "time_in_nanos": 23875, "children": [ { "name": "TopScoreDocCollector", "reason": "search_top_hits", "time_in_nanos": 11937 } ] } ] } ], "aggregations": [] } ] } }Logs (if relevant)
No response