# Nested aggregation slows query from ~400ms to 10s **URL:** \ **Category:** Elasticsearch\ **Created:** [September 6, 2018, 9:41am UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539 "2018-09-06T09:41:39Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![newnewb](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@newnewb](https://discuss.elastic.co/u/newnewb)\ **Post date:** [September 6, 2018, 9:41am UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/1 "2018-09-06T09:41:40Z")
Hello everyone, I created a simple query which looks for specific 5 car ID's and aggregates upon them. It's extremely fast, takes about 300-500ms. But if I add a nested aggregation which looks only for a specific colour in those 5 ID's, it suddenly takes 10 seconds or longer. This doesn't make sense to me, anyone got an explanation? Sample query down below: ``` { "stored_fields": [ ... ], "query": { "constant_score": { "filter": { "bool": { "must": [ { "term": { "typeId": 8 } }, { "terms": { "carId": [ 2796963847, 2550869, 152794103, 7678419, 338 ] } } ] } } } }, "aggs": { "cars": { "terms": { "field": "carId" }, "size": 25, "aggs": { "colors": { "terms": { "field": "colourId", "include": [ 534278 ] } } } } }, "size": 0 } ``` ---
**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\ **Post date:** [September 6, 2018, 1:49pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/2 "2018-09-06T13:49:55Z")
300-500ms for a simple unscored filter is an order of magnitude slower than I would expect. When you do the query without the aggs, how many hits does it report? If it's hundreds of millions across many shards, well, that could take some time to aggregate over. I haven't used the [Profile API](https://www.elastic.co/guide/en/elasticsearch/reference/master/search-profile.html) much, but maybe it can shed some light on it. ---
**Author:** ![newnewb](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@newnewb](https://discuss.elastic.co/u/newnewb)\ **Post date:** [September 6, 2018, 2:10pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/3 "2018-09-06T14:10:42Z")
Mostly varies, can be lower at times. Without the aggs, it has 78 hits, but the database is huge. Used Search Profiler in Kibana already, it looks something like this: ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/e/7e14ed390d41b3a1b56854909ae2efa6f8c0ba62.png) PS: The response is pre-cached already, so it's down to 550ms. carId=keywordId ---
**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\ **Post date:** [September 6, 2018, 4:10pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/4 "2018-09-06T16:10:30Z")
I can't fathom how aggregating over 78 hits would take 10 seconds. I'd be curious to see what the underlying OS's are doing while this query is laboring for 10 seconds. Specifically, is there I/O wait with low CPU? Just guessing here as I don't know what version you are running, what the mappings look like, how many data nodes there are, how many shards are in the index, how big the largest shards are, how big your JVM heap is, what kinds of storage you are using, if you have changed the default configuration in any way, etc. ---
**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 7, 2018, 1:54am UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/5 "2018-09-07T01:54:01Z")
Try the terms agg with an ‘execution\_hint’ of ‘map’ ---
**Author:** ![newnewb](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@newnewb](https://discuss.elastic.co/u/newnewb)\ **Post date:** [September 7, 2018, 7:49am UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/6 "2018-09-07T07:49:21Z")
Yeah, me neither, extremely odd. Any other query so far, even nested aggregations, has been supremely fast, even when aggregating on all the data. Yet, on only 78 hits all hell breaks lose lol. No I/O wait with low CPU, Running v6.3.2. All fields are just keyword type, except typeId which is long. The cluster is huge, not sure how many data nodes or shards there are, but I'm pretty confident they aren't the problem 😁 ---
**Author:** ![newnewb](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@newnewb](https://discuss.elastic.co/u/newnewb)\ **Post date:** [September 7, 2018, 7:51am UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/7 "2018-09-07T07:51:16Z")
Thanks for the hint! Just tried combinations on both term aggregations, unfortunately speed stays the same (or even slows down in some cases a little bit). ---
**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 7, 2018, 11:47am UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/8 "2018-09-07T11:47:45Z")
> [@newnewb](#): > > Yet, on only 78 hits all hell breaks lose Crazy docs with millions of colour terms? ---
**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\ **Post date:** [September 8, 2018, 1:43pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/9 "2018-09-08T13:43:16Z")
If you run the 10s agg query several times in a row, does it always take 10s? Also, how confident are you that none of your nodes are swapping? ---
**Author:** ![newnewb](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@newnewb](https://discuss.elastic.co/u/newnewb)\ **Post date:** [September 10, 2018, 2:12pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/10 "2018-09-10T14:12:13Z")
One colour per doc, but milions of docs 😃 ---
**Author:** ![newnewb](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@newnewb](https://discuss.elastic.co/u/newnewb)\ **Post date:** [September 10, 2018, 2:18pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/11 "2018-09-10T14:18:15Z")
Nope, it reduces the speed down to a few hundred ms. Pretty confident there isn't much problems with the architecture. Noticed that the slowdown is caused by 'typeId: 8' field because it's an integer. ES somehow does two operations: compares a range of numbers \>8 and \<8 instead of just =8. Guess I should convert the field to a keyword type.. or are there any other ideas without the need for reindexing? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 8, 2018, 2:18pm UTC](https://discuss.elastic.co/t/nested-aggregation-slows-query-from-400ms-to-10s/147539/12 "2018-10-08T14:18:18Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.