High cardinality field term agg results in slow query regardless of hit count

(Mahdi) #1

Given an index with a field that has high cardinality (1M+ distinct values), the following query has 0 hits and takes a ton of time to return even though the number of hits is 0.

If I add "execution_hint":"map" to the aggregation then it returns pretty quickly.

Can someone explain the behavior ? Is ES doing some expensive work before actually running the query ?

Tried this with both 1.7 and 2.3 and see the same behavior. Btw, the profiling in 2.3 (which is pretty neat) doesn't explain where most of the time is being spent.

  "size": 0,
  "query": {
    "query_string": {
      "query": "nothing:should_match_this"
  "aggregations": {    
    "members": {
      "terms": {
        "field": "high_card_field",
        "size": 10


  "took": 2417,
  "timed_out": false,
  "_shards": {
    "total": 6,
    "successful": 6,
    "failed": 0
  "hits": {
    "total": 0,
    "max_score": 0,
    "hits": []
  "aggregations": {
    "members": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": []

(Zachary Tong) #2

What kind of field is high_card_field? Is it an analyzed, string field? Those still use field data, which must be loaded into memory. If the field hasn't been used before, the field data structure is cold and populates on first usage, which can have a noticeable impact on latency.

Is it slow on every execution or just the first one?

Could you gist up the profile results somewhere?

(Mahdi) #3

It's a not-analyzed string field with doc values enabled. The field actually contains b64 encoded snowflake ids (something like "CpbkCTuAAAA"). The cardinality of the field is in the 5M+ ballpark

The query is slow most of the time. If I run it every 5s, it randomly switches back and forth between returning in 50ms and returning in more than 1s.

Here is a gist of the profiler output: https://gist.github.com/mahdibh/24b7783f37c4e6c007dd3029652845dd

(Colin Goodheart-Smithe) #4

This came up in a Github issue recently, maybe that thread will be helpful to you? https://github.com/elastic/elasticsearch/issues/19780

(Mahdi) #5

That was it, added a map execution_hint and everything became blazingly fast

(system) #6