Why ESQL so slow compared to KQL - Please inform how to optimize

Hi I convert lots of old KQL to ESQL, mainly because it is easier to understand and I'm and old Splunker used to the pipeline there :slight_smile:

but ESQL queries are so much slower, How can I optimize?, I tried adding time filtering and other filtering to the front just after the FROM, but no change in execution time. The less entries that matches the longer it takes just to parse through the data.

I used to have a timeout=10s, now I've changed it to 120, but I still see error.

in kibana I have same index bps-trace-* and datespan = today on both:

KQL:
bps.application:"id-pro-trk-keycloak-trk-keycloak-server-test12" AND message:/WARN|INFO/

Hits (total)
5337
Data view
bps-trace-*
Data view ID
cc6145a205da7fff1fd2e53ef37a697b86ab94b009fca406737352921a423871
Query time
35ms
Request timestamp
2025-01-22T13:11:45.103Z

ESQL:
FROM bps-trace-* | WHERE bps.application=="id-pro-trk-keycloak-trk-keycloak-server-test12" | WHERE message RLIKE ".*(INFO|WARN).*" | limit 1000
Hits
1000
Query time
52000ms
Request timestamp
2025-01-22T13:09:59.973Z

Why is is so much slower, what can be done to optimize it?

1 Like

ESQL do not seem do any keyword lookup filtering so it most likely drags all data into the logic engine, should be easy to add some filtering here :slight_smile:

      "bps": {
        "properties": {
          "application": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      },

POST /bps-trace*/_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=1738143821231
{
  "size": 0,
  "track_total_hits": true,
  "_source": {
    "excludes": []
  },
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "bool": {
                  "should": [
                    {
                      "match_phrase": {
                        "bps.application": "id-pro-trk-keycloak-trk-keycloak-server-test12"
                      }
                    }
                  ],
                  "minimum_should_match": 1
                }
              },
              {
                "bool": {
                  "should": [
                    {
                      "match": {
                        "message": "/WARN|ERROR/"
                      }
                    }
                  ],
                  "minimum_should_match": 1
                }
              }
            ]
          }
        },
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2025-01-26T23:00:00.000Z",
              "lte": "2025-02-02T22:59:59.999Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  },
  "stored_fields": [
    "*"
  ],
  "runtime_mappings": {},
  "script_fields": {},
  "fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ]
}

take 8ms 73 results.

POST /_query/async?drop_null_columns
{
  "query": "FROM bps-trace-* | KEEP bps.application,message | WHERE bps.application==\"id-pro-trk-keycloak-trk-keycloak-server-test12\" | WHERE message RLIKE \".*(ERROR|WARN).*\"\r\n",
  "locale": "en",
  "include_ccs_metadata": true,
  "filter": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2025-01-26T23:00:00.000Z",
              "lte": "2025-02-02T22:59:59.999Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

take 30s returns 73 results

So what is the difference between _query/async and _async_search, how can I optimize?