# Elastic Search Aggregations Slow **URL:** \ **Category:** Elasticsearch\ **Created:** [October 14, 2021, 9:24am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701 "2021-10-14T09:24:02Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 14, 2021, 9:24am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/1 "2021-10-14T09:24:02Z")
Good day I have an Elasticsearch index with 50 million records in it. This is working as expected. If I add aggregations to my query it is taking quite a long time to get the results. Any advise ? Thanks in advance. Kind regards. ---
**Author:** ![alfianaf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alfianaf/32/93192_2.png) [@alfianaf](https://discuss.elastic.co/u/alfianaf)\ **Post date:** [October 14, 2021, 9:28am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/2 "2021-10-14T09:28:41Z")
Greetings, try lower the date range so you don't need to aggregate that much records, ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 14, 2021, 10:34am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/3 "2021-10-14T10:34:52Z")
> [@Srini12](#): > > If I add aggregations to my query it is taking quite a long time to get the results. Any advise ? - How slow it is? - What does look like the request? - What is the output? - Is it still the same after some runs? Or is it only the first run? - Which version are you using? - What kind of hardware? ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 14, 2021, 10:58am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/4 "2021-10-14T10:58:58Z")
Hi David, Thanks. It is slow **with aggregations** No of records in the result set: 7982 time taken : 35 seconds **without aggregations** same data is returned in 10 seconds it is not only for the first request. I am using 7.13.3 version. Hardware is top notch (if I don't use aggregation it is quite quick) **my request is poco built on below parameters:** productform:"", keyword: "", contributor: "", cop: "", language: "", publicationStatus: "", imprint: "", publisher: "", wholesalers: "", salesRights: "", subject: "", identifier: "", populateAggregations: true, pubDateFrom: "2010-09-10", targetAudienceCode: "", pubDateTo: "2022-09-10", isAutoCompleteSearch: false ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 14, 2021, 11:46am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/5 "2021-10-14T11:46:18Z")
10 seconds is super slow. Please share the request sent to Elasticsearch. ---
**Author:** ![BenB196](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benb196/32/83401_2.png) [@BenB196](https://discuss.elastic.co/u/BenB196)\ **Post date:** [October 14, 2021, 12:05pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/6 "2021-10-14T12:05:50Z")
You might be running into this bug [Slow StringTermsAggregatorFromFilters · Issue #76104 · elastic/elasticsearch (github.com)](https://github.com/elastic/elasticsearch/issues/76104) in 7.13.x, which was fixed in 7.14.0. You can see if this is the case, by using the issues provided workaround, setting the following cluster setting: ```json "search.aggs.rewrite_to_filter_by_filter": false ``` I ran into a fairly similar issue not too long ago with a somewhat simple agg, and this turned out to be the issue. But as mentioned previously, being able to see the query and agg you're actually running would be helpful here. ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 14, 2021, 1:10pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/7 "2021-10-14T13:10:33Z")
Hi David, Below are the data i am sending it to the ES from my GQL. do you want some thing else? {gqlbooks(title:"", isbn:"", productform:"", keyword:"", contributor:"", cop:"", language:"", publicationStatus:"", imprint:"", publisher:"", wholesalers:"", salesRights:"", subject:"", identifier:"0123b1e0-b723-470b-9143-8a2a74edcfb2", populateAggregations:true, pubDateFrom:"1996-10-14", targetAudienceCode:"", pubDateTo:"2022-10-14",isAutoCompleteSearch:false) { resultCount publicationdate publisher author isbn13 title productform languagetext audiences author noofpages publicationstatus productclassifiers productclassifiercodes subtitle wholesalers markets publicationstatus cop bookName imprint imageurl rpgList bucketDTO distributors}} ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 14, 2021, 1:51pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/8 "2021-10-14T13:51:55Z")
I'd like to see the HTTP Request which is sent to Elasticsearch. I can not guess from that how this is then translated to the queryDSL. ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 16, 2021, 8:00am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/9 "2021-10-16T08:00:09Z")
Hi David, appologies for delay, please find http request below: ```auto GET /idx-myelasticindex/_search { "size": 0, "query": { "match": { "bookName": "Prooi" } }, "aggs": { "Terms_Aggregation" : { "terms": { "field": "cop.keyword" } }, "Author_Aggregation" : { "terms": { "field": "author.keyword"} }, "Format_Aggregation" : { "terms": { "field": "productform.keyword"} }, "Status_Aggregation" : { "terms": { "field": "publicationstatus.keyword"} }, "Readership_Aggregation" : { "terms": { "field": "audiences.keyword"} } } } ``` ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 16, 2021, 9:36am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/10 "2021-10-16T09:36:27Z")
And what is the full response from Elasticsearch? Please share it in both cases. One with the aggs and one without any aggregation, but same query. ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 19, 2021, 12:29pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/11 "2021-10-19T12:29:48Z")
Hi David The response object is quite a huge object, it is over the allowed size here, I have uploaded at below location, can you please try get it from below, first one is with and other one without aggregations: [https://www.kwiksnoop.com/documents/elastic.json](https://www.kwiksnoop.com/documents/elastic.json) thanks in advance. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 19, 2021, 1:17pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/12 "2021-10-19T13:17:22Z")
So. A first look at this gives me: With aggregations: ```auto { "took" : 15311, "hits" : { "total" : { "value" : 6, "relation" : "eq" } ... } ``` Without aggregations: ```auto "took" : 21, ``` First of all, we can see that without aggs, the time is only 21ms. Not 10s. Then, the time spent on the aggregation is 15s for only 6 documents. Which does not make sense at all. Could you run the same agg again and again, and give the output (only the first lines until `hits` is enough) after some runs? It it still slow? > Hardware is top notch What kind of hardware do you have? ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 19, 2021, 1:40pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/13 "2021-10-19T13:40:58Z")
Hi David, Thank you > First of all, we can see that without aggs, the time is only 21ms. Not 10s. > Then, the time spent on the aggregation is 15s for only 6 documents. Which does not make sense at all. I am not too worried about response time for without aggregations 10s was for almost 8000 record, for aggregations response time huge for large data sets. > Could you run the same agg again and again, and give the output (only the first lines until `hits` is enough) after some runs? > It it still slow? > there is very little improvement, if I run 3 times the response time is 13.5 seconds. ```auto "took" : 13597, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 6, "relation" : "eq" }, ``` Below is the server configuration: RAM : 64 GB 64 Bit, OS 16 Core Processor 2.80 GHz Windows 10 ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 19, 2021, 3:06pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/14 "2021-10-19T15:06:15Z")
Could you add `"profile": true` when you run with the aggs? What is the output of: ```auto GET / GET /_cat/nodes?v GET /_cat/health?v GET /_cat/indices?v ``` If some outputs are too big, please share them on [gist.github.com](http://gist.github.com) and link them here. ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 25, 2021, 9:13am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/15 "2021-10-25T09:13:35Z")
Hi David, Please see below picture for the quereis. ![david](https://us1.discourse-cdn.com/elastic/original/3X/6/e/6e7d01deb917821e3261a574cebd3b9d8ce3672c.png) ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 25, 2021, 11:15am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/16 "2021-10-25T11:15:06Z")
Please don't post images of text as they are hard to read, may not display correctly for everyone, and are not searchable. Instead, paste the text and format it with `` icon or pairs of triple backticks (```), and check the preview window to make sure it's properly formatted before posting it. This makes it more likely that your question will receive a useful answer. It would be great if you could update your post to solve this. Also add the first query I asked for: ``` GET / ``` Thanks ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 28, 2021, 8:22am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/17 "2021-10-28T08:22:05Z")
Hi David Please see below now, may be copy and paste the stats in any text editor to read properly. I will send the GET / in next communication result for : GET /\_cat/nodes?v ```auto ip heap.percent ram.percent cpu load_1m load_5m load_15m node.role master name 127.0.0.1 44 85 65 cdfhilmrstw * <> ``` GET /\_cat/health?v ```auto epoch timestamp cluster status node.total node.data shards pri relo init unassign pending_tasks max_task_wait_time active_shards_percent 1635152186 08:56:26 elasticsearch yellow 1 1 18 18 0 0 2 0 - 90.0% ``` GET /\_cat/indices?v ```auto health status index uuid pri rep docs.count docs.deleted store.size pri.store.size green open .kibana_7.13.3_001 zTV0ZBDYR7OHohvEaxo_ng 1 0 345 39 4.9mb 4.9mb green open .apm-agent-configuration kQqZGYSQHaQN16hLlHQsQ 1 0 0 0 208b 208b green open .tasks 2eEdiJmdTgmOEtKudXhHAQ 1 0 136 0 81kb 81kb green open .kibana_task_manager_7.13.3_001 4UjZ7VU7RQ2qP9YeFXHKGw 1 0 11 1005 4.2mb 4.2mb green open .security-7 TBOvtIRWRCipq97T1Oew3Q 1 0 55 0 268.4kb 268.4kb green open .apm-custom-link 6IkqyS-sTICtQt44VtI1Iw 1 0 0 0 208b 208b green open .kibana-event-log-7.13.3-000001 MoXe_5IARTqYnLcanYYgrg 1 0 85 0 31kb 31kb green open .kibana-event-log-7.13.3-000003 JJD5JlrPQfiMnqP_s9Sf2Q 1 0 5 0 27.1kb 27.1kb green open .kibana-event-log-7.13.3-000002 N5M6KzlHQkOQO1zJRJRd5g 1 0 25 0 36.6kb 36.6kb green open .kibana-event-log-7.13.3-000004 jeYUrCRNQLW4_wgzk5npkg 1 0 13 0 18.6kb 18.6kb yellow open idx-autherindex FK7YBB16SIOzMz_TxrvP_w 1 1 3 0 70.6kb 70.6kb green open .async-search Ev7YbBA1SFWlwCAvM1Ij9w 1 0 0 0 6.7kb 6.7kb yellow open idx-discoveryproduct Gw5EN2OSSkCw0kEhiTeKMg 1 1 55990270 24907862 96.2gb 96.2gb ``` ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 29, 2021, 8:55am UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/18 "2021-10-29T08:55:06Z")
Hi David, Please find below as requested. ```auto { "name" : "my-server-name", "cluster_name" : "elasticsearch", "cluster_uuid" : "uTFhdzzdSDuSo7RB9NUg1g", "version" : { "number" : "7.13.3", "build_flavor" : "default", "build_type" : "zip", "build_hash" : "5d21bea28db1e89ecc1f66311ebdec9dc3aa7d64", "build_date" : "2021-07-02T12:06:10.804015202Z", "build_snapshot" : false, "lucene_version" : "8.8.2", "minimum_wire_compatibility_version" : "6.8.0", "minimum_index_compatibility_version" : "6.0.0-beta1" }, "tagline" : "You Know, for Search" } ``` ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [October 29, 2021, 1:22pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/19 "2021-10-29T13:22:45Z")
I'm guessing that you are searching in `idx-discoveryproduct`, right? ```auto health status index pri rep docs.count docs.deleted store.size pri.store.size yellow open idx-discoveryproduct 1 1 55990270 24907862 96.2gb 96.2gb ``` So you have one single shard of 96gb. That's too much IMO. Should have at least 2 shards, and may be more. I'd try to split this index into 5 shards (20gb each more or less) to see if this is getting better. You can try the Split API. Also I can see that you have a lot of deletes. Are you doing a lot of updates? ---
**Author:** ![Srini12](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@Srini12](https://discuss.elastic.co/u/Srini12)\ **Post date:** [October 29, 2021, 1:37pm UTC](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701/20 "2021-10-29T13:37:37Z")
Yes, I am `idx-discoveryproduct` is the index i am working on. And we have lots of updates. It is already in product index, what is the impact if I split it now into multiple shards? Thanks [Next page](https://discuss.elastic.co/t/elastic-search-aggregations-slow/286701.md?page=2)