Elasticsearch Query Optimization: How Three Settings Cut a 5‑Second Aggregation to 80 ms

Kibana showed that a daily aggregation query for order amounts per shop, running on five shards with 70 million documents, took 5.2 seconds. The DSL consists of a filter, a date_histogram, and a sum aggregation.

Profiling revealed that 92% of the time was spent reading doc_values, meaning Elasticsearch spent almost the entire query time loading field values from disk.

Parameter 1: eager_global_ordinals

The shop_id field is a keyword. By default, global ordinals are built on‑the‑fly for each query, scanning all 70 million values. Enabling eager_global_ordinals forces Elasticsearch to build the ordinals during index refresh and store them on disk, eliminating the on‑the‑fly cost.

PUT /orders/_mapping
{
  "properties": {
    "shop_id": {
      "type": "keyword",
      "eager_global_ordinals": true
    }
  }
}

Note: this can only be set at index creation or after reindexing, and it slightly increases index size and refresh time, which is acceptable for low‑cardinality fields. After the change the query ran in 1.7 seconds.

Parameter 2: doc_values compression (v2)

Profiling the 1.7 second run showed 180 MB of disk reads for the order_amount field’s doc_values. In ES 7.x+, the doc_values_format: v2 provides a more compact encoding for numeric fields.

PUT /orders/_settings
{
  "index": {
    "mapping": {
      "doc_values_format": "v2"
    }
  }
}

After applying the setting, the query time dropped to 450 ms and the doc_values size shrank from 1.2 GB to 400 MB, reducing disk reads by two‑thirds.

Parameter 3: index.sort pre‑sorting

Even at 450 ms, the query still scanned all documents for the shop_id filter. By defining index.sort.field as shop_id (and create_time) with appropriate orders, documents for the same shop are stored contiguously on disk, enabling faster filter skipping and sequential doc_values reads.

PUT /orders
{
  "settings": {
    "index.sort.field": ["shop_id", "create_time"],
    "index.sort.order": ["asc", "desc"]
  }
}

This change reduced the query time to about 80 ms, stable between 75 ms and 85 ms across multiple runs.

Three‑step optimization results

Original: 5200 ms – no changes.

+ eager_global_ordinals: 1700 ms – eliminated on‑the‑fly ordinal building.

+ doc_values v2 compression: 450 ms – cut disk reads by two‑thirds.

+ index.sort pre‑sorting: 80 ms – sequential reads and faster filter.

Important considerations

Do not enable eager_global_ordinals on very high‑cardinality keyword fields (e.g., user IDs) as it slows refresh and writes. doc_values_format v2 is available only in ES 7.x and later. index.sort increases write latency because each document must maintain the sort order; acceptable when write QPS is low.

All three settings require a reindex; the author used the Reindex API, which took about 20 minutes for the dataset.

Broader insight

Performance problems in aggregation queries are often storage‑layer issues rather than compute limits. Adding nodes or shards rarely helps if doc_values storage, global ordinal construction, or data layout are suboptimal. Exhaust Elasticsearch’s built‑in tuning before resorting to external solutions such as pre‑aggregation or heterogeneous architectures.