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

**URL:** <https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064>\
**Category:** Elasticsearch\
**Created:** [August 15, 2016, 8:19pm UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064 "2016-08-15T20:19:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mahdouch](https://avatars.discourse-cdn.com/v4/letter/m/8edcca/32.png) [@mahdouch](https://discuss.elastic.co/u/mahdouch)\
**Post date:** [August 15, 2016, 8:19pm UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064/1 "2016-08-15T20:19:19Z")

</div>

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": []
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [August 15, 2016, 8:43pm UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064/2 "2016-08-15T20:43:16Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![mahdouch](https://avatars.discourse-cdn.com/v4/letter/m/8edcca/32.png) [@mahdouch](https://discuss.elastic.co/u/mahdouch)\
**Post date:** [August 16, 2016, 3:48am UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064/3 "2016-08-16T03:48:30Z")

</div>

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](https://gist.github.com/mahdibh/24b7783f37c4e6c007dd3029652845dd)

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [August 16, 2016, 7:50am UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064/4 "2016-08-16T07:50:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mahdouch](https://avatars.discourse-cdn.com/v4/letter/m/8edcca/32.png) [@mahdouch](https://discuss.elastic.co/u/mahdouch)\
**Post date:** [September 27, 2016, 11:56pm UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064/5 "2016-09-27T23:56:08Z")

</div>

That was it, added a map execution\_hint and everything became blazingly fast

---

<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:** [July 5, 2017, 10:16pm UTC](https://discuss.elastic.co/t/high-cardinality-field-term-agg-results-in-slow-query-regardless-of-hit-count/58064/6 "2017-07-05T22:16:50Z")

</div>