# Slow query with aggregations **URL:** \ **Category:** Elasticsearch\ **Created:** [September 2, 2021, 8:35am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143 "2021-09-02T08:35:02Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Drept](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@Drept](https://discuss.elastic.co/u/Drept)\ **Post date:** [September 2, 2021, 8:35am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/1 "2021-09-02T08:35:02Z")
I have implemented a system of aggregations for search filters and am enduring speed problems with a bit longer queries: there are no problems when a query contains up to 4 words but extremely slow queries onward. I have used the profiling API, and it turns out that the aggregations are slowing down everything, taking roughly 24 s out of the 25 s required for the search. Here is a [link to the gist with the full profiling](https://gist.github.com/Dterb/d813e2cf6605cb7bf63f663a25c253af). As can be seen from the gist, I am using sampler aggregations, otherwise there are constant timeout errors as searches take way more than 60 seconds. The mapping for my index is the following: ```auto { "movies" : { "mappings" : { "properties" : { "description" : { "type" : "text" }, "all_actors" : { "type" : "text" }, "episode_title" : { "type" : "text" }, "actors_keyword" : { "type" : "keyword", "ignore_above" : 1000 }, "series_title" : { "type" : "keyword", "ignore_above" : 1000 }, "language" : { "type" : "keyword", "ignore_above" : 1000 }, "number_of_actors" : { "type" : "short" }, "translated_title" : { "type" : "text" }, "subject_areas" : { "type" : "text" }, "subject_areas_keyword" : { "type" : "keyword", "ignore_above" : 1000 }, "url" : { "type" : "text" }, "year" : { "type" : "short", "null_value" : 0 } } } } } ``` Other data that might be helpful: - number of shards: 1 - number of replicas: 0 - number of documents in the index: approx. 80 million documents - size of the index: approx. 30 GB - the fields such as 'actors\_keyword' and 'subject\_areas' are high-cardinality ones, and I am currently not using the 'eager\_global\_ordinals' setting If I turn off the aggregations at all, the search queries work great up until longer queries with over 20 words or so. The question is: how can I improve the current situation? I am a novice, so I might be missing even some apparently evident details. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [September 2, 2021, 8:55am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/2 "2021-09-02T08:55:27Z")
If you look at the profile output searches for common terms like `of` and `and` take most of the time (as you would expect) because they match many many docs. You could choose to add these to a "stop word" list to remove them from the index when adding docs or make the query stricter - requiring more of the search terms to match rather than accepting any doc with the word `and` as a match. ---
**Author:** ![Drept](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@Drept](https://discuss.elastic.co/u/Drept)\ **Post date:** [September 2, 2021, 10:24am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/3 "2021-09-02T10:24:18Z")
Thanks. As adding stop words would require rebuilding the whoe indices, I've just tried running the same query by eliminating the stop words from search in python. I've also added a _minimum\_should\_match_ of _65%_. The queries with many words indeed became much quicker, they are almost instantaneous. On the contrary, the searches for smaller queries are rather slow and take around 4 s. Could you please take a look at another [gist with the updated profiling for a shorter query](https://gist.github.com/Dterb/fd7dd41fb50910f88daf973049979e2e) to help me understand what could be improved yet? P.S. Don't you mind if I prepare an answer to my own question on StackOverflow crediting your post? I guess I'm not the only one having such questions. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [September 2, 2021, 10:42am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/4 "2021-09-02T10:42:13Z")
> The queries with many words indeed became much quicker, they are almost instantaneous. On the contrary, the searches for smaller queries are rather slow and take around 4 s. This is likely just because long queries with 65% min\_should\_match are quite selective (matching few docs) but a search for one or two words is going to match many more docs. More matching docs = more disk seeks to look up doc values used in aggregations = more time. Do you have total match counts to compare? Are search response times linear with number of matching docs? > P.S. Don't you mind if I prepare an answer to my own question on StackOverflow crediting your post? I guess I'm not the only one having such questions. Sure. Thanks for sharing. ---
**Author:** ![Drept](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@Drept](https://discuss.elastic.co/u/Drept)\ **Post date:** [September 2, 2021, 10:54am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/5 "2021-09-02T10:54:01Z")
I don't know how to measure the total match count. I've tried removing the limits for the shard size, but am running out of memory and still can't get the match count. From what I see, 'French language' and 'mammal ferrets' run roughly at the same speed (while we would expect the 'mammal ferrets' query to be quite quicker I suppose). Maybe this is where the aggregations are indeed to heave due to the high cardinality and I should set _eager\_global\_ordinals_ to improve performance? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [September 2, 2021, 11:02am UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/6 "2021-09-02T11:02:13Z")
> [@Drept](#): > > I don't know how to measure the total match count It should be returned in the JSON of results under hits/total/value. This count may be capped at 10k unless: a) You disable [track\_total\_hits](https://www.elastic.co/guide/en/elasticsearch/reference/master/search-your-data.html#track-total-hits) or b) your request has aggregations, in which case all matches must be visited and the count is full > Maybe this is where the aggregations are indeed to heave due to the high cardinality and I should set _eager\_global\_ordinals_ to improve performance? You may also want to experiment with the [map execution hint](https://www.elastic.co/guide/en/elasticsearch/reference/master/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-execution-hint) ---
**Author:** ![Drept](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@Drept](https://discuss.elastic.co/u/Drept)\ **Post date:** [September 2, 2021, 3:39pm UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/7 "2021-09-02T15:39:22Z")
The total hit count for the [last query](https://gist.github.com/Dterb/fd7dd41fb50910f88daf973049979e2e) is around 516,000 documents. Strangely enough, the execution time is now around 0.5 s, with all the aggregations and without any additional modification on my part. I was also able to reveal that the problem is indeed with the high-cardinality fields. If I raise the shard\_size parameters from 300 to 3,000 for these fields, the search becomes extremely slow, while a shard\_size of 100,000 for low-cardinality fields doesn't seem to affect the speed. Thank you very much for your help. UPD. Maybe (I'm not sure) the quicker speed for the last query is due to the fact that I increased the JVM heap size twice. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [September 2, 2021, 3:47pm UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/8 "2021-09-02T15:47:17Z")
> [@Drept](#): > > . Strangely enough, the execution time is now around 0.5 s, with all the aggregations and without any additional modification on my part. Elasticsearch and file-system caches are likely behind this. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 30, 2021, 3:47pm UTC](https://discuss.elastic.co/t/slow-query-with-aggregations/283143/9 "2021-09-30T15:47:30Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.