# Question on ES query optimization

**URL:** <https://discuss.elastic.co/t/question-on-es-query-optimization/367181>\
**Category:** Elastic Search\
**Created:** [September 26, 2024, 12:04pm UTC](https://discuss.elastic.co/t/question-on-es-query-optimization/367181 "2024-09-26T12:04:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![akmalk](https://avatars.discourse-cdn.com/v4/letter/a/94ad74/32.png) [@akmalk](https://discuss.elastic.co/u/akmalk)\
**Post date:** [September 26, 2024, 12:04pm UTC](https://discuss.elastic.co/t/question-on-es-query-optimization/367181/1 "2024-09-26T12:04:05Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [September 26, 2024, 12:46pm UTC](https://discuss.elastic.co/t/question-on-es-query-optimization/367181/2 "2024-09-26T12:46:03Z")

</div>

What is the mapping of the `create_time` field?

---

<div class="post-metadata">

**Author:** ![akmalk](https://avatars.discourse-cdn.com/v4/letter/a/94ad74/32.png) [@akmalk](https://discuss.elastic.co/u/akmalk)\
**Post date:** [September 26, 2024, 2:25pm UTC](https://discuss.elastic.co/t/question-on-es-query-optimization/367181/3 "2024-09-26T14:25:58Z")

</div>

It's date type with epoch\_millis format.

Thanks.

---

<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:** [October 24, 2024, 2:26pm UTC](https://discuss.elastic.co/t/question-on-es-query-optimization/367181/4 "2024-10-24T14:26:48Z")

</div>

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