[ES|QL] `WHERE` Command filtering can be significantly slower than DSL

[BenB196]

Elasticsearch Version

8.18.1

Installed Plugins

No response

Java Version

bundled

OS Version

Container

Problem Description

I recently came across a problem a relatively simple query/agg was performing extremely slow in ES|QL (~62000ms), but was fairly fast with DSL (~150ms). I was able to track it down to a single WHERE filter in the ES|QL query. Removing the offending WHERE clause, caused the ES|QL query to perform on-par with the DSL one.

Steps to Reproduce

1. Run the following query and observe poor performance.

POST /_query
{
  "query": "FROM logs-istio.access_logs-*\r\n| WHERE istio.access.upstream.cluster != \"outbound|8200||apm.apm.svc.cluster.local\"\r\n| STATS COUNT()\r\n",
  "locale": "en",
  "include_ccs_metadata": true,
  "filter": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2025-05-27T16:00:00.000Z",
              "lte": "2025-05-27T17:00:00.000Z"
            }
          }
        }
      ]
    }
  }
}

(ES|QL query)

FROM logs-istio.access_logs-*
| WHERE istio.access.upstream.cluster != "outbound|8200||apm.apm.svc.cluster.local"
| STATS COUNT()

(Is the query I noticed the issue with)

{
  "rawResponse": {
    "is_running": false,
    "took": 61944,
    "is_partial": false,
    "all_columns": [
      {
        "name": "COUNT()",
        "type": "long"
      }
    ],
    "columns": [
      {
        "name": "COUNT()",
        "type": "long"
      }
    ],
    "values": [
      [
        835129
      ]
    ]
  }
}

2. Run the same query, but without the WHERE clause

POST /_query
{
  "query": "FROM logs-istio.access_logs-*\r\n| STATS COUNT()\r\n",
  "locale": "en",
  "include_ccs_metadata": true,
  "filter": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2025-05-27T16:00:00.000Z",
              "lte": "2025-05-27T17:00:00.000Z"
            }
          }
        }
      ]
    }
  }
}

(ESQL query)

FROM logs-istio.access_logs-*
| STATS COUNT()

Response:

{
  "rawResponse": {
    "is_running": false,
    "took": 121,
    "is_partial": false,
    "all_columns": [
      {
        "name": "COUNT()",
        "type": "long"
      }
    ],
    "columns": [
      {
        "name": "COUNT()",
        "type": "long"
      }
    ],
    "values": [
      [
        1121299
      ]
    ]
  }
}

3. Run the same query with DSL

POST /logs-istio.access_logs-*/_async_search?batched_reduce_size=64&ccs_minimize_roundtrips=true&wait_for_completion_timeout=200ms&keep_on_completion=true&keep_alive=60000ms&ignore_unavailable=true&preference=1748371077976
{
  "aggs": {},
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2025-05-27T16:00:00.000Z",
              "lte": "2025-05-27T17:00:00.000Z"
            }
          }
        }
      ],
      "must_not": [
        {
          "match_phrase": {
            "istio.access.upstream.cluster": "outbound|8200||apm.apm.svc.cluster.local"
          }
        }
      ]
    }
  }
}

Response:

{
  "id": "FjRJX19mdF9iUVNTZGtyd0ZJQVExTXcgYU1Lang5Y1NTYmU2N3h6VE92OHdWdzoyMzUxMzUyMjU=",
  "rawResponse": {
    "took": 130,
    "timed_out": false,
    "_shards": {
      "total": 132,
      "successful": 132,
      "skipped": 128,
      "failed": 0
    },
    "hits": {
      "total": 838731,
      "max_score": null,
      "hits": []
    }
  },
  "isPartial": false,
  "isRunning": false,
  "total": 132,
  "loaded": 132,
  "isRestored": false
}

Observe that both queries 2 & 3 perform about the same.

Logs (if relevant)

I've attached 2 profiles, 1 for the query executed in step 1 and another for the query executed in step 2.

esql-profile-query-step-1.json
esql-profile-query-step-2.json

[dnhatn]

@BenB196 We have an optimization for this case in #128156. Is there any chance you could test with a 8.19 snapshot? Thank you!

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[BenB196]

Thanks @dnhatn, I'll see if I can get a test cluster spun up in the next few days to validate, but thanks for the link to the PR as well, as that definitely does seem to be related.

[nik9000]

"must_not": [
{
"match_phrase": {
"istio.access.upstream.cluster": "outbound|8200||apm.apm.svc.cluster.local"
}
}
]

| WHERE istio.access.upstream.cluster != "outbound|8200||apm.apm.svc.cluster.local"

These are pretty different. != is checking the actual bytes of the text field. In the profile you can see:

stored_fields[requires_source:true, fields:0, sequential: false]

and

stored_fields[requires_source:true, fields:0, sequential: true]

in the profile. It's loading that field from _source - that's what that's saying. Then it's doing an equality filter.

The query dsl is doing a phrase query. ESQL is just scanning in the slowest way possible.

If you have a keyword field without a normalizer living under istio.access.upstream.cluster then this should help:
#127355 . That's the default auto-mapping.

It should be in 8.19 or 9.1.

You can do istio.access.upstream.cluster.keyword != "outbound|8200||apm.apm.svc.cluster.local" in the mean time. It's almost the same. If none of the values in istio.access.upstream.cluster.keyword are over the ignore_above in the keyword then it's exactly the same.

[nik9000]

Also: are you sure you want == and not a match phrase like you had before?

[BenB196]

Unfortunately, this field doesn't have a keyword subfield (like most Elastic Integrations that use text)

https://github.com/elastic/integrations/blob/299c5edeefa427a03920b0fdeb5edff794de0908/packages/istio/data_stream/access_logs/fields/fields.yml#L68-L71

So unfortunately, I don't think #127355 will apply here.

Also: are you sure you want == and not a match phrase like you had before?

I'll be 100% honest, the match_phase is just Kibana's default when building a visualization and that's where I copied that query from, in reality, I just care about equality and filtering out that specific value.

---

That being said, I did try a few other query combo's and did see some improvements:

Using KQL:

FROM logs-istio.access_logs-*
| WHERE KQL("not istio.access.upstream.cluster : \"outbound|8200||apm.apm.svc.cluster.local\"") 
| STATS COUNT()

Brings it down to ~200ms

---

Using MATCH:

FROM logs-istio.access_logs-*
| WHERE NOT MATCH(istio.access.upstream.cluster, "outbound|8200||apm.apm.svc.cluster.local", {"operator": "AND"}) 
| STATS COUNT()

Brings it down to ~175ms

---

Using QSTR:

FROM logs-istio.access_logs-*
| WHERE NOT QSTR("istio.access.upstream.cluster : \"outbound|8200||apm.apm.svc.cluster.local\"") 
| STATS COUNT()

Brings it to ~300ms

[BenB196]

An interesting one that I tested was explicit type casting using TO_STRING

FROM logs-istio.access_logs-*
| WHERE TO_STRING(istio.access.upstream.cluster) != "outbound|8200||apm.apm.svc.cluster.local"
| STATS COUNT()

Given the table provided, I'd expect it to convert the text to keyword, and then have the similar performance to a standard keyword field, but I got the same poor performance, I assume because of some underlying order of operations happening.

---

Another one I tried, was reordering the query a bit to have the WHERE happen after the STATS, but I got similar poor performance

FROM logs-istio.access_logs-*
| STATS COUNT() BY istio.access.upstream.cluster
| WHERE istio.access.upstream.cluster != "outbound|8200||apm.apm.svc.cluster.local"

I assume under the hood, ES|QL just tries to "optimize" this, and moves the WHERE before the STATS.

[nik9000]

I assume under the hood, ES|QL just tries to "optimize" this, and moves the WHERE before the STATS.

@costin would have to double check me but I don't think we push WHERE through STATS at the moment.

Gotcha on == being what you want. Cool.

About TO_STRING - it does convert the field type to keyword, but there's no index to push the query into. The index structures for text are just super different.

Unfortunately, this field doesn't have a keyword subfield (like most Elastic Integrations that use text)

Let me ask around about this one. I had a conversation with someone else about this same thing this morning so I suppose it's more common than I thought. For most text fields it should be possible to to a match style query to generate candidate matches. That's not nearly as fast as the keyword == queries, but it could work.

Except I don't know that it could work for !=. With text fields I get an index on the tokens. So outbound|8200||apm.apm.svc.cluster.local is indexed as ["outbound", "8200", "apm", "svc", "cluster", "local"] kind of. Mostly. so I could generate candidate matches for == by checking for all of those tokens in the same document. It won't tell me about the | and the like. And, depending on the tokenizer, it might tell me less or more. There's a lot of complexity in text. But it's probably possible to generate candidates for ==.

Maybe there's something we could to for !=, but I've thought of it yet.

[BenB196]

I assume under the hood, ES|QL just tries to "optimize" this, and moves the WHERE before the STATS.

would have to double check me but I don't think we push WHERE through STATS at the moment.

You might be right here, as simplifying the query to just:

FROM logs-istio.access_logs-*
| STATS COUNT() BY istio.access.upstream.cluster

Also experiences the slowness. So, it's most likely the BY clause has the same/similar issue as the WHERE clause when it comes to text fields.