# Query optimization!

**URL:** <https://discuss.elastic.co/t/query-optimization/95906>\
**Category:** Elasticsearch\
**Created:** [August 4, 2017, 3:01pm UTC](https://discuss.elastic.co/t/query-optimization/95906 "2017-08-04T15:01:26Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mr.Flakes](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@Mr.Flakes](https://discuss.elastic.co/u/Mr.Flakes)\
**Post date:** [August 4, 2017, 3:01pm UTC](https://discuss.elastic.co/t/query-optimization/95906/1 "2017-08-04T15:01:26Z")

</div>

Hello elastic gurus, I am pretty new to the whole stack and some help would be much appreciated. So I have this query running pretty often and it eats A LOT of resources. It works as intended results wise but I just want to know if you would have any advice how to make it any better!

```auto
GET cdr/_search
{
  "query":{
    "constant_score":{
      "filter":{
        "bool":{
          "should":[
            {
              "range":{
                "startTime":{
                  "gte":"2017-01-21T23:00+02:00",
                  "lte":"2017-01-21T23:50+02:00"
                }
              }
            },
            {
              "range":{
                "stopTime":{
                  "gte":"2017-01-21T23:00+02:00",
                  "lte":"2017-01-21T23:50+02:00"
                }
              }
            },
            {
              "bool":{
                "must":[
                  {
                    "range":{
                      "startTime":{
                        "lte":"2017-01-21T23:00+02:00"
                      }
                    }
                  },
                  {
                    "range":{
                      "stopTime":{
                        "gte":"2017-01-21T23:50+02:00"
                      }
                    }
                  }
                ]
              }
            }
          ],
          "must":[
            {
              "term":{
                "sourceIp":{
                  "value":"XX.XX.X.XX"
                }
              }
            }
          ]
        }
      }
    }
  }
}

```

Sorry for the bad formatting. Anyway, so the first part of the query is pretty simple and is used to get all the documents in the provided date range and the second bool is used to get all the documents that crosses the provided range but is not exactly in it (for example documents that have 2017-01-21T22:59+02:00 as startTime and 2017-01-21T23:59+02:00 as stopTime). The last step is to get only those documents that are associated with particular client (IP address). The data types are startTime DATE, stopTime DATE, sourceIp KEYWORD. Any ideas how I could make this better? The total data size on disk is around 50 TB if that plays any role.

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [August 11, 2017, 7:50am UTC](https://discuss.elastic.co/t/query-optimization/95906/2 "2017-08-11T07:50:34Z")

</div>

I could be wrong but it seems to me that the first two range queries are not necessary since they are covered by the inner `bool` query?

This will require reindexing but I suspect you might want to look into indexing your `startTime` and `stopTime` fields as a single date range field rather than two separate `date` fields. Then you can directly query the range field to find the intersection with a single range query, and it is usually faster than using two range queries. [https://www.elastic.co/guide/en/elasticsearch/reference/current/range.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/range.html)

---

<div class="post-metadata">

**Author:** ![Mr.Flakes](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@Mr.Flakes](https://discuss.elastic.co/u/Mr.Flakes)\
**Post date:** [August 11, 2017, 10:00am UTC](https://discuss.elastic.co/t/query-optimization/95906/3 "2017-08-11T10:00:55Z")

</div>

Nah, the inner bool query only covers records that are less than startTime and more than stopTime so it does not take into account the records that for example are inside the given range, it takes only records that are outside the given range but crosses it. I actually was thinking about the date\_range type but I kinda fear reindexing because I have around 10 TB of data running on one node ☹ I guess that could take some time... but I have no choice I guess. Thanks for help.

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [August 11, 2017, 10:28am UTC](https://discuss.elastic.co/t/query-optimization/95906/4 "2017-08-11T10:28:04Z")

</div>

I had misread your query indeed. But then could you do range matching with just this query:

```auto
            {
              "bool":{
                "must":[
                  {
                    "range":{
                      "startTime":{
                        "lte":"2017-01-21T23:50+02:00" // query stop time
                      }
                    }
                  },
                  {
                    "range":{
                      "stopTime":{
                        "gte":"2017-01-21T23:00+02:00" // query start time
                      }
                    }
                  }
                ]
              }
            }

```

It looks similar to your inner `bool` query except that I swapped the start / stop bounds. If all your indexed documents have a stop time that is gte the start time, then this should match all intersecting ranges?

---

<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:** [September 8, 2017, 10:28am UTC](https://discuss.elastic.co/t/query-optimization/95906/5 "2017-09-08T10:28:40Z")

</div>

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