# Slow sub-aggregation for low-cardinality field + high-cardinality field

**URL:** <https://discuss.elastic.co/t/slow-sub-aggregation-for-low-cardinality-field-high-cardinality-field/316992>\
**Category:** Elasticsearch\
**Created:** [October 19, 2022, 12:36pm UTC](https://discuss.elastic.co/t/slow-sub-aggregation-for-low-cardinality-field-high-cardinality-field/316992 "2022-10-19T12:36:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![astrodi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/astrodi/32/117732_2.png) [@astrodi](https://discuss.elastic.co/u/astrodi)\
**Post date:** [October 19, 2022, 12:36pm UTC](https://discuss.elastic.co/t/slow-sub-aggregation-for-low-cardinality-field-high-cardinality-field/316992/1 "2022-10-19T12:36:37Z")

</div>

Hi there,

I'm having index having 14M documents, occupying 1.6TB of pri.storage with 50shards.

There is query doing 22 aggregations (mainly simple terms aggs), took 0.3s.  
I needed to add one sub-aggregation to every of those 22 aggs and **execution time (took) grows to 5.3s**.

This sub-agg is "cardinality" aggregation over field having ~1.15M distinct values (DOC\_DEAL\_NO)

```auto
      "uwrtYear" : {
        "terms" : {
          "field" : "content.ACCP_UWRT_YR.keyword",
          "order" : {
            "_key" : "desc"
          },
          "size" : 50
        },
        "aggs" : {
          "deals_count" : {
            "cardinality" : {
              "field" : "content.DOC_DEAL_NO.keyword"
            }
          }
        }
      }

```

Average times collected from "profile":true response:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/7/27e37f59dd0a02d3b05150610f842b47ede8c80d.png)

The most interesting fact is, that aggregations slowing down the request mostly are the ones, with low cardinality (at the end of the table - uwrtStatus, reinsurer, uwrtYear - column A)

Checking the "profile" part of the response, "collect" phase takes most of the time.

```auto
{
	"type": "GlobalOrdinalsStringTermsAggregator",
	"description": "uwrtStatus",
	"time": "257.2ms",
	"time_in_nanos": 257260689,
	"breakdown": {
		"reduce": 0,
		"post_collection_count": 1,
		"build_leaf_collector": 2930416,
		"build_aggregation": 1053952,
		"build_aggregation_count": 1,
		"build_leaf_collector_count": 36,
		"post_collection": 10203,
		"initialize": 584,
		"initialize_count": 1,
		"reduce_count": 0,
		"collect": 253265534,
		"collect_count": 184731
	},
	"debug": {
		"segments_with_multi_valued_ords": 0,
		"collection_strategy": "remap using single bucket ords",
		"segments_with_single_valued_ords": 36,
		"total_buckets": 26,
		"built_buckets": 1,
		"result_strategy": "terms",
		"has_filter": false
	},
	"children": [
		{
			"type": "CardinalityAggregator",
			"description": "deals_count",
			"time": "230.8ms",
			"time_in_nanos": 230810795,
			"breakdown": {
				"reduce": 0,
				"post_collection_count": 1,
				"build_leaf_collector": 1278362,
				"build_aggregation": 1033225,
				"build_aggregation_count": 1,
				"build_leaf_collector_count": 36,
				"post_collection": 8816,
				"initialize": 59,
				"initialize_count": 1,
				"reduce_count": 0,
				**"collect": 228490333,**
				"collect_count": 184731
			},
			"debug": {
				"ordinals_collectors_used": 32,
				"ordinals_collectors_overhead_too_high": 4,
				"built_buckets": 26,
				"string_hashing_collectors_used": 4,
				"numeric_collectors_used": 0,
				"empty_collectors_used": 0
			}
		}
	]
}

```

I have tried to apply `"eager_global_ordinals": true` for DOC\_DEAL\_NO, but no luck, still getting 5.3s response time.

Can anybody help with ideas, what else I can do/check to speed up it?

Thanks!  
Dominik

---

<div class="post-metadata">

**Author:** ![astrodi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/astrodi/32/117732_2.png) [@astrodi](https://discuss.elastic.co/u/astrodi)\
**Post date:** [October 21, 2022, 8:08am UTC](https://discuss.elastic.co/t/slow-sub-aggregation-for-low-cardinality-field-high-cardinality-field/316992/2 "2022-10-21T08:08:42Z")

</div>

I was able to move forward with this solution [pre\_computed\_hashes](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-cardinality-aggregation.html#_pre_computed_hashes) with "mapper-murmur3" plugin over high-cardinality field (DOC\_DEAL\_NO).

Running the same query (using .hash field in sub-aggregation) the **request time drops to 1.6s**.

---

<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:** [November 18, 2022, 8:08am UTC](https://discuss.elastic.co/t/slow-sub-aggregation-for-low-cardinality-field-high-cardinality-field/316992/3 "2022-11-18T08:08:56Z")

</div>

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