# Slow aggregation no matter the size of the result set

**URL:** <https://discuss.elastic.co/t/slow-aggregation-no-matter-the-size-of-the-result-set/150331>\
**Category:** Elasticsearch\
**Created:** [September 28, 2018, 11:42am UTC](https://discuss.elastic.co/t/slow-aggregation-no-matter-the-size-of-the-result-set/150331 "2018-09-28T11:42:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Iliyan\_Tanev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iliyan_tanev/32/35980_2.png) [@Iliyan\_Tanev](https://discuss.elastic.co/u/Iliyan_Tanev)\
**Post date:** [September 28, 2018, 11:42am UTC](https://discuss.elastic.co/t/slow-aggregation-no-matter-the-size-of-the-result-set/150331/1 "2018-09-28T11:42:13Z")

</div>

Hi,

We're experiencing a strange behavior with an aggregation.

The below query is taking 1 - 1.5 seconds to execute.

```
GET index/type/_search
{
  "size": 0, 
  "query": {
     "bool": {
       "must": [
		   {
			  "term": {
				 "query": {
					"value": "some search query"
				 }
			  }
		   }
       ]
	}
  },
  "aggs": {
     "queries": {
         "terms": {
           "field": "query.keyword",
           "size": 10
         }
     }
  }
}

```

After a thorough investigation, we've found that the slowness comes from the aggregation on this specific field - **query.keyword**. The interesting thing here is that the aggregation is slow no matter if the size of the result-set is 40,000 or 1 document - the runtime is exactly the same.

However, you can get fast results with the aggregation if you are aggregating on a different field, for example **title** (also keyword, see mapping below) . So it seems to be something related to the **query** field and/or it's subfields.

Here are the mappings:

```
...
         "query": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
         "title": {
            "type": "keyword"
          }
...

```

The index shard setup is 2p+1r, with a total of 9,992,927 documents.  
Elasticsearch 5.3.2

## **Some statistics:**

**Query 1 - without aggregation**  
Returned docs: 1  
Execution time: 34ms

**Query 2 - without aggregation**  
Returned docs 40,000  
Execution time: 35ms

* * *

**Query 1 - with aggregation on query.keyword**  
Returned docs: 1  
Execution time: 1427ms

**Query 2 - with aggregation on query.keyword**  
Returned docs: 40,000  
Execution time: 1350ms

* * *

**Query 1 - with aggregation on title**  
Returned docs: 1  
Execution time: 56ms

**Query 2 - with aggregation title**  
Returned docs: 40,000  
Execution time: 63ms

* * *

## Question:

So the question is why aggregating on query.keyword is slowing down the query no matter what size the result set is filtered down to. The aggregation should only run on the result-set, right?!?

Does anyone have any thoughts on this or how I debug it further?

---

<div class="post-metadata">

**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 28, 2018, 11:54am UTC](https://discuss.elastic.co/t/slow-aggregation-no-matter-the-size-of-the-result-set/150331/2 "2018-09-28T11:54:35Z")

</div>

Try [execution hint](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-execution-hint) of `map` on the terms agg.

---

<div class="post-metadata">

**Author:** ![Iliyan\_Tanev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iliyan_tanev/32/35980_2.png) [@Iliyan\_Tanev](https://discuss.elastic.co/u/Iliyan_Tanev)\
**Post date:** [September 28, 2018, 12:21pm UTC](https://discuss.elastic.co/t/slow-aggregation-no-matter-the-size-of-the-result-set/150331/3 "2018-09-28T12:21:12Z")

</div>

Thank you very much @Mark_Harwood! This solved the problem. 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 26, 2018, 12:21pm UTC](https://discuss.elastic.co/t/slow-aggregation-no-matter-the-size-of-the-result-set/150331/4 "2018-10-26T12:21:17Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.