How to Speed Up Slow Elasticsearch Aggregations with execution_hint "map"

Problem Introduction

In a high‑traffic e‑commerce scenario the data volume is huge, so the team split the database into multiple shards (sharding by user‑id). While this solves the single‑machine performance bottleneck, it makes cross‑shop queries extremely inefficient because the data for a single shop is scattered across many shards.

To avoid scanning all shards, the orders were synchronized to Elasticsearch, leveraging its fast full‑text search capabilities. However, the aggregation queries that combine user name, masked phone and pending order count became very slow as the data grew.

Slow Aggregation Example

The business needs to list orders for a shop, filter by user name, and aggregate the number of pending shipments per user. The query without aggregation returns ~13 k hits in 332 ms, but adding a two‑level terms aggregation (by to_url and from_url) takes more than 37 seconds.

{
    "query": {
        "bool": {
            "must": [
                {"range": {"date": {"gte":"2016-08-01 00:00:00","lte":"2016-08-30 23:59:59","format":"yyyy-MM-dd HH:mm:ss"}}},
                {"term": {"sc":"0"}},
                {"terms": {"channel": [".4399sj.com"]}}
            ]
        }
    },
    "aggs": {
        "new_to_url": {
            "terms": {"field":"to_url"},
            "aggs": {
                "new_from_url": {
                    "terms": {"field":"from_url"},
                    "aggs": {
                        "sum_m__visit": {"sum": {"field":"m_visit"}}
                    }
                }
            }
        }
    }
}

The index mapping defines fields such as channel, date, from_url, to_url, m_visit, etc.

Proposed Solution

An Elasticsearch expert suggested adding "execution_hint": "map" inside each terms aggregation. The modified aggregation looks like:

"aggs": {
    "new_to_url": {
        "terms": {"field":"to_url","execution_hint":"map"},
        "aggs": {
            "new_from_url": {
                "terms": {"field":"from_url","execution_hint":"map"},
                "aggs": {
                    "sum_m__visit": {"sum": {"field":"m_visit"}}
                }
            }
        }
    }
}

Terms aggregation does not first fetch all matching documents and then aggregate. Instead it builds a global ordinals structure that maps each unique term to a bucket, which is memory‑efficient for massive result sets. When execution_hint":"map" is set, Elasticsearch skips the global ordinals step and builds a simple in‑memory map from the returned documents. This is much faster when the result set is small. There is a trade‑off: for very large result sets the map approach can become slower because of higher memory usage, so testing is required to find the sweet spot.

Result

Applying the execution_hint":"map" configuration reduced the API response time from about 1100 ms to 80 ms, confirming the effectiveness of the optimization.