# Aggregation Search Query is slow

**URL:** <https://discuss.elastic.co/t/aggregation-search-query-is-slow/161313>\
**Category:** Elasticsearch\
**Created:** [December 18, 2018, 11:35am UTC](https://discuss.elastic.co/t/aggregation-search-query-is-slow/161313 "2018-12-18T11:35:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bronzik](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bronzik/32/32950_2.png) [@bronzik](https://discuss.elastic.co/u/bronzik)\
**Post date:** [December 18, 2018, 11:35am UTC](https://discuss.elastic.co/t/aggregation-search-query-is-slow/161313/1 "2018-12-18T11:35:42Z")

</div>

Hi guys

**Cluster Configuration:**

There are 3 data Nodes hosted on Elastic Cloud.

The state below is given per Node:

- ES version: 6.5.1

- Max Heap size configured per Node: 7.9 GB  
Heap used: 57-61 %

- Max RAM size configured per Node: 240 GB  
RAM used: 99 %

- CPU (average): 5-10%

- Disk available: 386 GB

Our index uses Custom Routing for Multi-tenancy. So all of the _queried_ data is allocated on single Primary Shard + 1 replica.

A Shard to be queried has 27 649 038 docs. The only tenant is indexed on a Shard to be queried.

The following aggregation-query is implemented [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html#\_field\_collapse\_example](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html#_field_collapse_example)

**Aggregation-search-query:**

```
{
	"size": 0,
	"aggs": {
		"top_sites": {
			"terms": {
				"field": "taskId",
				"size": 10,
				"order": [{
						"top_hit": "desc"
					}
				]
			},
			"aggs": {
				"top_hit": {
					"max": {
						"script": {
							"source": "_score"
						}
					}
				},
				"top_tags_hits": {
					"top_hits": {
						"size": 1
					}
				}
			}
		}
	},
	"query": {
		"bool": {
			"should": [{
					"match": {
						"all_copy_to_field": {
							"boost": 15.0,
							"query": "some sentence with several words",
							"fuzziness": "AUTO",
							"prefix_length": 3,
							"max_expansions": 10
						}
					}
				}, {
					"bool": {
						"should": [{
								"match": {
									"field_1": {
										"query": "some sentence with several words",
										"fuzziness": "AUTO",
										"prefix_length": 3,
										"max_expansions": 10
									}
								}
							}, {
								"match": {
									"field_2": {
										"query": "some sentence with several words",
										"fuzziness": "AUTO",
										"prefix_length": 3,
										"max_expansions": 10
									}
								}
							}, {
								"match": {
									"field_3": {
										"query": "some sentence with several words",
										"fuzziness": "AUTO",
										"prefix_length": 3,
										"max_expansions": 10
									}
								}
							}, {
								"match": {
									"field_4": {
										"query": "some sentence with several words",
										"fuzziness": "AUTO",
										"prefix_length": 3,
										"max_expansions": 10
									}
								}
							}, {
								"match": {
									"field_5.shingle": {
										"query": "some sentence with several words"
									}
								}
							}, {
								"match": {
									"field_5": {
										"query": "some sentence with several words",
										"fuzziness": "AUTO",
										"prefix_length": 3,
										"max_expansions": 10
									}
								}
							}
						]
					}
				}
			],
			"filter": [{
					"term": {
						"communityId": {
							"value": 1
						}
					}
				}
			],
			"minimum_should_match": 1
		}
	}
}

```

Search performance stat is:

Average ES Server time for Aggregation-search-query ("took" response field): **110 ms**

The search-query without aggregation takes 50 ms in average.

So the questions are:

1. Why aggregation-query is slow (110 ms)?

2. Can it be improved?

---

<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:** [December 18, 2018, 11:50am UTC](https://discuss.elastic.co/t/aggregation-search-query-is-slow/161313/2 "2018-12-18T11:50:30Z")

</div>

Try remove the script.  
Your result from `top_tags_hits` should have the max score anyway?

---

<div class="post-metadata">

**Author:** ![bronzik](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bronzik/32/32950_2.png) [@bronzik](https://discuss.elastic.co/u/bronzik)\
**Post date:** [December 18, 2018, 12:03pm UTC](https://discuss.elastic.co/t/aggregation-search-query-is-slow/161313/3 "2018-12-18T12:03:38Z")

</div>

Yep, "max\_score" is identical to the top-scored document in a bucket. Could you provide proper query?

---

<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:** [January 15, 2019, 12:03pm UTC](https://discuss.elastic.co/t/aggregation-search-query-is-slow/161313/4 "2019-01-15T12:03:43Z")

</div>

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