# Nested aggregation got too slow result **URL:** \ **Category:** Elasticsearch\ **Created:** [April 24, 2018, 5:36am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217 "2018-04-24T05:36:10Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 5:36am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/1 "2018-04-24T05:36:10Z")
I have an index with a billions document in the future, for now it's around 20mil documents. It took over 10s to get the result while I need a query around 3-4s for billion documents. Is my structure was wrong or need to improve the query or server configuration? Im using amazon elasticsearch service. This query will return amount/transactions/items of every station in every area Query: ``` { "size" : 0, "query": { "bool": { "must": [ { "range": { "date_sec": { "gte": "1483228800", "lte": "1525046400" } } }, { "range": { "time_sec": { "gte": "32400", "lte": "75600" } } } ] } }, "aggs": { "numstoreamountclient" : { "filter" : { "range" : { "amount" : { "gt" : 0 } } }, "aggs": { "numstore_amountclient": { "cardinality" : { "field" : "id_station" } } } }, "id_station": { "terms": { "field": "id_station" }, "aggs": { "area_type": { "terms": { "field": "area_type" }, "aggs": { "max_time" : { "max" : { "field" : "time_sec" } }, "min_time" : { "min" : { "field" : "time_sec" } }, "amountclient": { "sum": { "field": "amount" } }, "itemclient": { "sum": { "field": "items" } }, "transactionclient" : { "value_count" : { "field" : "id" } }, "inwatchinghour": { "filter" : { "term" : { "in_watchinghour" : 1 } }, "aggs" : { "amountclientwatch": { "sum": { "field": "amount" } }, "itemclient": { "sum": { "field": "items" } }, "transactionclientwatch" : { "value_count" : { "field" : "id" } } } }, "saleclient": { "filter" : { "bool": { "must": [ { "term" : { "in_watchinghour" : 1 } }, { "range": { "items": { "gt": "0" } } }, { "range": { "amount": { "gt": "0" } } } ] } }, "aggs" : { "sale_client" : { "value_count" : { "field" : "id" } } } } } } } } } } ``` Execute time is: 11s ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 24, 2018, 7:11am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/2 "2018-04-24T07:11:00Z")
What instance types is your cluster deployed on? How many nodes do you have in the cluster? What does CPU usage look like on the nodes while you are running this query? How many indices and shards is your data spread across? ---
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 8:38am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/3 "2018-04-24T08:38:05Z")
Hi Christian, Im using: - m4.large.elasticsearch - single node - CPU less than 1% - only 3 indices but 2 is default and small data - Number of nodes 1 - Number of data nodes 1 - Active primary shards 11 - Active shards 11 Im in Thailand and Vietnam, ES server in Singapore ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 24, 2018, 8:49am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/4 "2018-04-24T08:49:57Z")
> [@sontd985](#): > > CPU less than 1% That sounds far too low. Are you sure this captures the CPU usage while the query is running? How often is monitoring polling data? ---
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 8:53am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/5 "2018-04-24T08:53:37Z")
![2018-04-24_1552](https://us1.discourse-cdn.com/elastic/original/3X/3/d/3dc5490876b79cb1eab2dd7b68de66d506132234.png) You can see the last hour I checked with this query, it's very low ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 24, 2018, 8:57am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/6 "2018-04-24T08:57:36Z")
Can you zoom in around the time the query ran? If the sample interval is not granular enough, it may be that a spike in CPU usage that only lasts 10 seconds is not registered. ---
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 9:02am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/7 "2018-04-24T09:02:57Z")
My mistake, this is the last query I just checked. ![2018-04-24_1601](https://us1.discourse-cdn.com/elastic/original/3X/b/5/b591ceac0779cdd870f83c392fe8cc18dff099dd.png) ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 24, 2018, 10:21am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/8 "2018-04-24T10:21:50Z")
Can you zoom in further and reduce the period? is this showing the max value? ---
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 10:27am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/9 "2018-04-24T10:27:51Z")
Hi Christian, Max value of CPU is 19% at the time I was executing the query, this is max zoom in amazon tool. I can not zoom in anymore, which information that you need more? 19% I think is very high, 5 query like this will kill the server ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 24, 2018, 10:36am UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/10 "2018-04-24T10:36:56Z")
I am not familiar with Amazon monitoring, so do not know if what you are seeing is accurate or not. This makes it is hard to tell what is limiting performance. ---
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 4:11pm UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/11 "2018-04-24T16:11:56Z")
This is another monitoring part(from 23:00) ![2018-04-24_2307](https://us1.discourse-cdn.com/elastic/original/3X/b/9/b9b6053fd76888371de1950ad63e3e3151dab8b5.png) ![2018-04-24_2308_001](https://us1.discourse-cdn.com/elastic/original/3X/8/c/8cfdc24d898ddf7f805062b550dc9440abc61b64.png) ![2018-04-24_2308](https://us1.discourse-cdn.com/elastic/original/3X/5/9/596493d0efa77bd66023d61b58ba0d6040e81908.png) Did you understand the aggregation above? Is it problem when im using many nested aggs? ---
**Author:** ![sontd985](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@sontd985](https://discuss.elastic.co/u/sontd985)\ **Post date:** [April 24, 2018, 4:29pm UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/12 "2018-04-24T16:29:56Z")
JSON document: ``` { "_index": "c980_datacashier", "_type": "doc", "_id": "35512229", "_version": 2, "_score": 2, "_source": { "receiptid": "104-1245104147-42-3", "ticket": 3, "id_employee": 104147, "roomtype": null, "id_station": 2209, "total_time": null, "time_begin": 45900, "date_sec": 1509753600, "promotion": null, "items": 4, "in_watchinghour": 0, "time_end": 46200, "status_check": 0, "numberofclients": null, "shift": null, "@timestamp": "2018-04-23T21:25:55.397Z", "area_type": 1, "id_clientstation": 104, "time_sec": 45900, "employee": null, "status_2": null, "cashier": null, "room": null, "amount": 157678, "id_cashier": 42, "status_1": 0, "update_time": "2018-04-22T11:47:25.383Z", "@version": "1", "deleted": 0, "id": 35512229 }, "fields": { "time_begin": [ "1970-01-01T00:00:45.900Z" ], "update_time": [ "2018-04-22T11:47:25.383Z" ], "@timestamp": [ "2018-04-23T21:25:55.397Z" ], "time_end": [ "1970-01-01T00:00:46.200Z" ] } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 22, 2018, 4:34pm UTC](https://discuss.elastic.co/t/nested-aggregation-got-too-slow-result/129217/13 "2018-05-22T16:34:30Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.