# Very slow aggregate query with 15 M of records **URL:** \ **Category:** Elasticsearch\ **Tags:** painless\ **Created:** [August 11, 2024, 12:27pm UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742 "2024-08-11T12:27:30Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![DevYSM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devysm/32/136701_2.png) [@DevYSM](https://discuss.elastic.co/u/DevYSM)\ **Post date:** [August 11, 2024, 12:27pm UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/1 "2024-08-11T12:27:30Z")
Hello, I have an index that has more than 15M records of website logs but while I'm trying to do an aggregation to sum the total of some fields the request takes a long time more than 10 seconds and after that gives me an error ```auto { "statusCode": 502, "error": "Bad Gateway", "message": "socket hang up" ``` And the following query is: ```auto { "size": 0, "query": { "bool": { "must": [ { "range": { "day": { "gte": "2024-04-03", "lte": "2024-04-04", "format": "yyyy-MM-dd" } } }, { "term": { "country_alias": "kw" } } ] } }, "aggs": { "entities": { "terms": { "field": "entity_title", "size": 100000 }, "aggs": { "records": { "top_hits": { "size": 1, "_source": { "includes": [ "entity_id", "entity_type", "entity_title", "country_alias", "main_taxonomy", "main_taxonomy_title", "owner_phone", "created_at", "updated_at" ] } } }, "total_visits": { "sum": { "script": { "lang": "painless", "source": "doc['visit_ios_count'].value + doc['visit_android_count'].value + doc['visit_huawei_count'].value + doc['visit_web_count'].value" } } }, "total_calls": { "sum": { "script": { "lang": "painless", "source": "doc['call_ios_count'].value + doc['call_android_count'].value + doc['call_huawei_count'].value + doc['call_web_count'].value" } } }, "total_whatsapp": { "sum": { "script": { "lang": "painless", "source": "doc['whatsapp_ios_count'].value + doc['whatsapp_android_count'].value + doc['whatsapp_huawei_count'].value + doc['whatsapp_web_count'].value" } } }, "total_chat": { "sum": { "script": { "lang": "painless", "source": "doc['chat_ios_count'].value + doc['chat_android_count'].value + doc['chat_huawei_count'].value + doc['chat_web_count'].value" } } } } } } ``` Can any one help me to fix this issue? ---
**Author:** ![DevYSM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devysm/32/136701_2.png) [@DevYSM](https://discuss.elastic.co/u/DevYSM)\ **Post date:** [August 11, 2024, 12:40pm UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/2 "2024-08-11T12:40:10Z")
@dadoonet Can you help me? ---
**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:** [August 11, 2024, 5:07pm UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/3 "2024-08-11T17:07:00Z")
This a community forum where everyone volunteers so it is considered rude to ping people not already involved in the convesation. Please also be patient as it can take time to get questions answered. The more specific they are the more time it may take as not everyone may know the anser or that area. If you have not received any response after 2 or 3 business days it is usually fine to bump the thread. When asking a question it is always useful to specify which version of Elasticsearch you are using and some details about the size and configuration of your cluster. I have never seen the error you posted. Do you have a proxy in between the client and Elasticsearch that could cause the error? How many shards is the data spread across? What is the size of these? When your query runs and is slow, do you notice high CPU usage? Given that you use scripting to sum up different fields within the document, have you tested instead adding these to the document so you do not have to use scripts? ---
**Author:** ![DevYSM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devysm/32/136701_2.png) [@DevYSM](https://discuss.elastic.co/u/DevYSM)\ **Post date:** [August 12, 2024, 6:59am UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/4 "2024-08-12T06:59:08Z")
I'm so sorry for the mistake and all respect for our community. I have never seen the error you posted. Do you have a proxy in between the client and Elasticsearch that could cause the error? _I have asked the DevOps and he's said it's added Cloudflare_ I'm using the latest version of Elasticsearch 8.15. This is the shards query result. ```auto classified-log-report-per-day 0 p STARTED 54000 28.4mb 28.4mb 172.18.0.2 Elasticsearch classified-log-report-per-day 0 r UNASSIGNED ``` Finally, I'm using the sum script because this data is synced from MongoDB. ========================= I'll share the final result example, In this example, I'm trying to group the totals for each entity\_title to get the totals in range date for example in 1 month. ````auto { "took": 42, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 10000, "relation": "gte" }, "max_score": null, "hits": [] }, "aggregations": { "entities": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 27344, "buckets": [ { "key": "ابحث عن عمل ", "doc_count": 21, "records": { "hits": { "total": { "value": 21, "relation": "eq" }, "max_score": 1.6797194, "hits": [ { "_index": "classified-log-report-per-day", "_id": "66a9ad4f72912a8f4a00cf71", "_score": 1.6797194, "_source": { "entity_title": "ابحث عن عمل ", "created_at": "2024-07-31T03:19:43.072000", "updated_at": "2024-07-31T03:19:43.072000", "country_alias": "kw", "main_taxonomy": 195, "owner_phone": "+96599615720", "entity_id": 10464740, "entity_type": "post", "main_taxonomy_title": { "ar": "وظائف / باحثون عن عمل", "en": "Jobs" } } } ] } }, "total_visits": { "value": 53 }, "total_calls": { "value": 0 }, "total_whatsapp": { "value": 3 }, "total_chat": { "value": 0 } } ] } } }``` ```` ---
**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:** [August 12, 2024, 7:11am UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/5 "2024-08-12T07:11:04Z")
> [@DevYSM](#): > > I have asked the DevOps and he's said it's added Cloudflare That then probably explains the error seen. > [@DevYSM](#): > > `classified-log-report-per-day 0 p STARTED 54000 28.4mb 28.4mb 172.18.0.2 Elasticsearch` It looks like you have a single node and as the index is very small it is odd that the query even if it is using scripts is taking that long. It would help if you could provide details about the amount of resources available to this node, e.g. CPU cores, RAM, heap size and the type of storage used. It would also help if you could monitor CPU usage while the query is running. ---
**Author:** ![DevYSM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devysm/32/136701_2.png) [@DevYSM](https://discuss.elastic.co/u/DevYSM)\ **Post date:** [August 12, 2024, 7:25am UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/6 "2024-08-12T07:25:28Z")
![image](https://us1.discourse-cdn.com/elastic/original/3X/2/a/2a8f13b033fa024f90e4b96371533d6f9f9cebc3.png) Can you check the image above? ---
**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:** [August 12, 2024, 8:21am UTC](https://discuss.elastic.co/t/very-slow-aggregate-query-with-15-m-of-records/364742/7 "2024-08-12T08:21:35Z")
That does not really answer my question about provisioned resources. Was it taken when the query was running?