# Adding additional aggregation causes 15x performance degradation despite small result set

**URL:** <https://discuss.elastic.co/t/adding-additional-aggregation-causes-15x-performance-degradation-despite-small-result-set/380132>\
**Category:** Elasticsearch\
**Tags:** aggregations\
**Created:** [July 15, 2025, 10:11am UTC](https://discuss.elastic.co/t/adding-additional-aggregation-causes-15x-performance-degradation-despite-small-result-set/380132 "2025-07-15T10:11:06Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mmiliauskas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mmiliauskas/32/141043_2.png) [@mmiliauskas](https://discuss.elastic.co/u/mmiliauskas)\
**Post date:** [July 15, 2025, 10:11am UTC](https://discuss.elastic.co/t/adding-additional-aggregation-causes-15x-performance-degradation-despite-small-result-set/380132/1 "2025-07-15T10:11:07Z")

</div>

I have an Elasticsearch query that returns only 107 documents but takes 1.5 seconds to execute. When I remove one specific aggregation (values\_brand), the same query completes in 100ms. The brand field only contains 8 unique values (although it is a high cardinality field in general, ~200k unique values) in the result set, so I'm confused why this aggregation is causing such a significant performance impact.

Query:

```
{
  "track_total_hits": true,
  "size": 100,
  "query": {
    "query_string": {
      "query": "<some query>",
      "default_field": "<some default field>"
    }
  },
  "aggs": {
    "values_retailer": {
      "terms": {
        "size": 5,
        "field": "retailer"
      }
    },
    "values_brand": {
      "terms": {
        "size": 5,
        "field": "brand"
      }
    },
    "values_subindustry": {
      "terms": {
        "size": 5,
        "field": "subindustry"
      }
    }
  }
}

```

`retailer`, `brand` and `subindustry` are all mapped to `keyword` field type. Elasticsearch version is 9.0.3.

I have also noticed that if I add `"execution_hint": "map"` to `values_brand` aggregation, the speed is greatly improved. However, only for the cases with a handful search results.

---

<div class="post-metadata">

**Author:** ![carly.richmond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carly.richmond/32/104935_2.png) [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)\
**Post date:** [July 15, 2025, 2:31pm UTC](https://discuss.elastic.co/t/adding-additional-aggregation-causes-15x-performance-degradation-despite-small-result-set/380132/2 "2025-07-15T14:31:38Z")

</div>

Hi @mmiliauskas,

Welcome to the community! Have you run the query through the [Search profiler](https://www.elastic.co/docs/explore-analyze/query-filter/tools/search-profiler) to confirm which clauses are taking the most time?

---

<div class="post-metadata">

**Author:** ![mmiliauskas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mmiliauskas/32/141043_2.png) [@mmiliauskas](https://discuss.elastic.co/u/mmiliauskas)\
**Post date:** [July 18, 2025, 10:09am UTC](https://discuss.elastic.co/t/adding-additional-aggregation-causes-15x-performance-degradation-despite-small-result-set/380132/3 "2025-07-18T10:09:29Z")

</div>

I can only get the output from the profiler if it is helpful:

```auto
{
    "type": "GlobalOrdinalsStringTermsAggregator",
    "description": "values_brand",
    "time_in_nanos": 2267874863,
    "breakdown": {
        "reduce": 0,
        "build_aggregation_count": 1,
        "post_collection": 404,
        "reduce_count": 0,
        "initialize_count": 1,
        "collect_count": 638,
        "post_collection_count": 1,
        "build_leaf_collector": 2262322739,
        "build_aggregation": 4493671,
        "build_leaf_collector_count": 935,
        "initialize": 498,
        "collect": 1057551
    },
    "debug": {
        "segments_with_multi_valued_ords": 0,
        "collection_strategy": "dense",
        "segments_with_single_valued_ords": 935,
        "total_buckets": 183121,
        "built_buckets": 1,
        "result_strategy": "terms",
        "has_filter": false
    }
}

```

---

<div class="post-metadata">

**Author:** ![carly.richmond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carly.richmond/32/104935_2.png) [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)\
**Post date:** [July 18, 2025, 2:30pm UTC](https://discuss.elastic.co/t/adding-additional-aggregation-causes-15x-performance-degradation-despite-small-result-set/380132/4 "2025-07-18T14:30:37Z")

</div>

Ok, what about the Kibana query profiler [as shown in the screenshot in the documentation](https://www.elastic.co/docs/explore-analyze/query-filter/tools/search-profiler)?