# Help Optimizing Terms Aggregation

**URL:** <https://discuss.elastic.co/t/help-optimizing-terms-aggregation/142661>\
**Category:** Elasticsearch\
**Created:** [August 2, 2018, 12:10am UTC](https://discuss.elastic.co/t/help-optimizing-terms-aggregation/142661 "2018-08-02T00:10:18Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sudhanne](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@sudhanne](https://discuss.elastic.co/u/sudhanne)\
**Post date:** [August 2, 2018, 12:10am UTC](https://discuss.elastic.co/t/help-optimizing-terms-aggregation/142661/1 "2018-08-02T00:10:18Z")

</div>

Trying to design a query that will return customer data bucketed into various time ranges. The total number of documents being aggregated on will always be scoped to a given client, which in practice should never exceed a few thousand. It seems that this query takes ~3-4 seconds regardless of what I do to tune it and irrespective of the size of the client.

Based on my research, this delay is coming from building the global ordinals. This is a very high cardinality field. The only thing that speeds up the query is to enable `eager_global_ordinals` on the `invoice_payer_contact_token` field, but that causes our CPU / RAM usage to spike like crazy.

```auto
# customer aging report (full)
GET /invoices/invoice/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "invoice_unit_token": "KM9TQK5WVWXV3"
          }
        },
        {
          "term": {
            "invoice_state": "UNPAID"
          }
        }        
      ]
    }
  },
  "aggs": {
    "range": {
      "date_range": {
        "field": "invoice_due_on",
        "ranges": [
          {
            "from": "now/d-30d/d",
            "key": "0-30 days"
          },
          {
            "from": "now-60d/d",
            "to": "now/d-31d/d",
            "key": "31-60 days"
          },
          {
            "from": "now/d-90d/d",
            "to": "now/d-61d/d",
            "key": "61-90 days"
          },
          {
            "from": "now/d-120d/d",
            "to": "now/d-91d/d",
            "key": "91-120 days"
          },
          {
            "to": "now/d-91d/d",
            "key": "91 days or older"
          },          
          {
            "to": "now/d",
            "key": "all-time"            
          }                    
        ]
      },
      "aggs": {
        "customers": {
          "terms": {
            "field": "invoice_payer_contact_token",
            "size": 1000,
            "order": {
              "total_requested_amount": "desc"
            }
          },
          "aggs": {
            "total_requested_amount": {
              "sum": {
                "field": "invoice_requested_amount_cents"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 2, 2018, 11:19am UTC](https://discuss.elastic.co/t/help-optimizing-terms-aggregation/142661/2 "2018-08-02T11:19:14Z")

</div>

If the number of customer is low you could try set the [execution\_hint](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-execution-hint) on the `customers` agg to `"map"`

---

<div class="post-metadata">

**Author:** ![sudhanne](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@sudhanne](https://discuss.elastic.co/u/sudhanne)\
**Post date:** [August 2, 2018, 2:51pm UTC](https://discuss.elastic.co/t/help-optimizing-terms-aggregation/142661/3 "2018-08-02T14:51:06Z")

</div>

I've tried this but unfortunately it does not help. There is almost no difference in query execution times. Customers is an extremely high cardinality field.

Is there a way that I could possibly break this query into multiple parts somehow to be more efficient?

---

<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:** [August 30, 2018, 2:51pm UTC](https://discuss.elastic.co/t/help-optimizing-terms-aggregation/142661/4 "2018-08-30T14:51:09Z")

</div>

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