Query with two date range filters 8x slower than an equivalent query with one?

[adamleonard]

We recently added a query that had two date range filters applied for the same field, in short, if dates X, Y, and Z are sorted chronologically, the query was:

1. "Date between X and Y"
2. "Date before Z"

This query is of course equivalent to a single date range filter like "Date between X and Z". However, we found it surprising that the query with 2 filters was ~8x slower than the query with 1 filter. The full before and after queries are at the bottom. Some more details:

• ElasticSearch 7.4
• Dates are stored in a strict_date_optional_time_nanos format (yyyy-MM-dd'T'HH:mm:ss.SSSSSSZ)
• Some queries were variants of the above (e.g., "date between Y and Z and before X" which should match 0 documents)
• Time is as measured by the "took" response.

We easily optimized this query ourselves to only use filter. But I was curious why this was so much slower:

• Is some optimization disabled when using two filters?
• Is it a bug that ES isn't able to automatically merge date filters into an equivalent minimal set of filters?

Thanks!

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "prompt__class_id": "[redacted]"
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "prompt__assigned_to_entire_class": true
                }
              },
              {
                "term": {
                  "prompt__assignees": "[redacted]"
                }
              }
            ]
          }
        },
        {
          "range": {
            "prompt__publish_date": {
              "gte": "2020-10-02T05:00:00Z",
              "lte": "2020-10-03T05:00:00Z"
            }
          }
        },
        {
          "range": {
            "prompt__publish_date": {
              "lte": "2020-10-08T19:38:42.885997Z"
            }
          }
        }
      ]
    }
  },
  "size": 0
}

[Ignacio_Vera]

One interesting this to do is to compare the query profile when you use two date range queries with just one. Would it be possible to post the outputs here? Thanks!