Question on ES query optimization

Elastic Search
1

Hi,

Could you please help to understand why particular query is slow when filtering with date range in bool query?

Using ES 8.14.3.

GET /receipts-v1-2024.08/_search
{
  "profile": true,
  "query": {
    "bool": {
      "filter": [
        { "range": { "create_time": { "gte": 1721520000000, "lte": 1724223870162 } } },
        { "bool": { "must_not": [ { "terms": { "state": [4, 50] } } ] } },
        { "terms": { "card.number": ["<redacted>","<redacted>" , "<redacted>","<redacted>" , "<redacted>","<redacted>","<redacted>"] } },
        { "bool": { "should": [
          { "terms": { "merchant": ["903120543f97006609000000"] } },
          { "terms": { "p2pMerchant": ["903120543f97006609000000"] } }
        ] } },
        { "term": { "operation": -1 } },
        { "terms": { "category": ["5668112e7d08b7b5f58f61a7"] } }
      ]
    }
  },
  "size": 25,
  "from": 0,
  "track_total_hits": false,
    "sort": [ { "create_time": { "order": "desc" } } ]
}

The most time consuming step in profiling results is at using date range in filter. Removing entirely date range filter or even removing lte value makes the query much faster.
With date range the query takes about 800-900ms, without range it takes about 80ms.

Thank you.

2

What is the mapping of the create_time field?

3

It's date type with epoch_millis format.

Thanks.

4

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