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

Elastic Stack Elasticsearch
1

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.

2

Hi @mmiliauskas,

Welcome to the community! Have you run the query through the Search profiler to confirm which clauses are taking the most time?

1 Like
3

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

{
    "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
    }
}
4

Ok, what about the Kibana query profiler as shown in the screenshot in the documentation?