---
title: Optimize ES|QL query performance
description: Techniques for writing fast ES|QL queries and operating ES|QL workloads at scale.
url: https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-query-performance
products:
- Elasticsearch
applies_to:
- Elastic Cloud Serverless: Generally available
- Elastic Stack: Generally available
---
# Optimize ES|QL query performance
This guide covers practical techniques for writing fast ES|QL queries and operating ES|QL workloads at scale. It starts with common anti-patterns, then shows how to reduce scanned data, reduce returned data, and avoid expensive operations. It also covers tools for monitoring query performance and investigating slow queries across multiple clusters.
For a quick overview of the most common issues with ES|QL queries, refer to [Common anti-patterns](#common-anti-patterns).
## Before you begin
This guide assumes familiarity with ES|QL syntax and command pipelines. To learn the basics, refer to [Get started with ES|QL queries](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-getting-started).
This guide serves two audiences:
- If you write ES|QL queries, the following sections cover the techniques most relevant to you:
- [Common anti-patterns](#common-anti-patterns)
- [Reduce what you scan](#reduce-what-you-scan)
- [Reduce what you return](#reduce-what-you-return)
- [Avoid expensive operations](#avoid-expensive-operations)
- If you administer clusters and need to monitor query performance across multiple clusters, the following section describes the tools available:
- [Monitor query performance](#monitor-query-performance)
### Check your Elastic Stack version
If you're not on [Elastic Cloud Serverless](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/deploy/elastic-cloud/serverless), check your Elastic Stack version. The ES|QL query engine improves with each release, so upgrading is often one of the highest-impact performance changes you can make.
Some tips on this page require a recent version of the Elastic Stack, and individual subsections carry an applicability badge when this is the case. Sections without a version badge apply to all versions where ES|QL is generally available.
The most important version-specific performance improvements are visible in the table below, including improvements in 9.x for query logging, time series support, query activity, and full-text search functions.
For clusters on a version before 8.17, upgrading provides the largest single performance improvement, because full-text search functions and Lucene pushdowns become available. For clusters on 8.17 but before 8.18, upgrading to 8.18 provides the next largest improvement. That release adds [`LIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-like) and [`RLIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-rlike) pushdown to Lucene and a mapping discovery optimization that reduces overhead on clusters with many indices.
| Version | What improved | Impact |
|-----------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 8.13 | [`CIDR_MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/ip-functions/cidr_match) pushed to Lucene | Faster IP filtering in security queries |
| 8.16 | Per-aggregation [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where) | Replaces slow [`CASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case)-based conditional aggregation |
| 8.17 | [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match) and [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/qstr) full-text search functions | Orders of magnitude faster than [`LIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-like) or [`RLIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-rlike) for text search |
| 8.18, 9.0 | [`LIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-like) and [`RLIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-rlike) pushed to Lucene, mapping discovery optimization, [`LOOKUP JOIN`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/lookup-join) | Faster pattern matching, cheaper queries on clusters with many indices, and native lookup joins |
| 9.1 | [ES|QL query log](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-query-log), full-text functions GA | Dedicated query performance logging, [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match), [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/qstr), and [`KQL`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/kql) stable |
| 9.2 | [`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/inlinestats-by), [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/ts) command with [`RATE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/time-series-aggregation-functions/rate) and [`TBUCKET`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/grouping-functions/tbucket) in preview, [`CHANGE_POINT`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/change-point) GA | Window-function-like queries, native time series support |
| 9.3 | [`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/inlinestats-by) GA, [`TRANGE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/date-time-functions/trange), Lucene-pushable [`LOOKUP JOIN`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/lookup-join) predicates | Faster filtered joins, simpler time range syntax |
| 9.4+ | [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/ts) and time series aggregation functions GA, [Query activity](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/query-activity), [unified query logging](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/logging-configuration/query-logs) | Native time series support, real-time view of in-flight queries in Kibana, single log for all query types |
### Index only what you need
Query performance starts at index time. Your field mappings control what ES|QL can do efficiently.
If you only aggregate or sort on a field, and never filter, set [`index: false`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/mapping-index) to save disk space. ES|QL can still read the field through doc values. Keep doc values enabled for fields that ES|QL needs to read, group, sort, or return. For fields that are rarely needed in results, remove them from the query output with [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/keep) or [`DROP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/drop).
### Know your circuit breaker limits
ES|QL enforces memory limits through [circuit breakers](/elastic/docs-builder/docs/3562/reference/elasticsearch/configuration-reference/circuit-breaker-settings#circuit-breakers-page-esql). When a query exceeds the limit, the cluster rejects it to protect node stability. High-cardinality aggregations are the most common trigger. To learn more, refer to [Avoid high-cardinality STATS BY](#avoid-high-cardinality-stats-by).
## Common anti-patterns
These anti-patterns are the most common causes of ES|QL query latency in production.
[AutoOps](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/autoops) detects most of these patterns automatically and surfaces actionable recommendations. To browse detected events, refer to [AutoOps events](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/autoops/ec-autoops-events).
| Pattern | What to look for | Why it's slow |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------|
| Broad index pattern | [`FROM *`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/from) or wide wildcards | Expensive mapping discovery, plus scans across many indices |
| Wide time range | `@timestamp` range spanning weeks or months | Scans proportionally more data |
| Missing [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where) | No filter conditions at all | Full index scan |
| Missing [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/keep) | No column selection | Returns all fields, producing large payloads |
| Missing [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/limit) | Unbounded result set | Slow serialization, can trigger deserialization errors in Kibana |
| High-cardinality [`STATS BY`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/stats-by) | Grouping by raw timestamps, full URLs, or document IDs | Produces millions of buckets, can trip circuit breakers |
| [`LIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-like) or [`RLIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-rlike) | Wildcard or regex text matching | Slower than full-text functions for text search, especially pre 8.18/9.0 |
| [`GROK`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/grok) or [`DISSECT`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/dissect) | Text parsing on large datasets | CPU-intensive regex or tokenization per row |
| [`CASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case) | Conditional aggregation through `CASE` | Lazy evaluation, slow |
| [`LOOKUP JOIN`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/lookup-join) | Join against a large lookup index | Cost is proportional to the lookup index size |
The most impactful fixes are usually: add a time range filter, add a [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where), and add a [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/keep).
## Reduce what you scan
Most ES|QL queries spend the bulk of their time reading data from disk. The fastest queries read the least data. This section covers the levers that most directly control scan size.
### Narrow the time range
A tight time range is the single biggest performance lever in most workloads. ES|QL uses the `@timestamp` field to skip entire shards and segments that fall outside the range, so a narrower window directly reduces the amount of data read.
Avoid running queries that span more time than the result actually needs:
❌ **Don't:** Query without a time bound
```esql
FROM logs-*
| WHERE host.name == "web-01"
| STATS count = COUNT(*) BY log.level
```
✅ **Do:** Add an explicit `@timestamp` filter to bound the scan
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
AND host.name == "web-01"
| STATS count = COUNT(*) BY log.level
```
In Kibana, the time picker automatically applies a time range filter. When writing queries directly in the [Kibana Console](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/explore-analyze/query-filter/tools/console) or through the API, always include an explicit `@timestamp` filter.
### Filter early with WHERE
A [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where) clause earlier in the pipeline reduces the dataset before downstream commands process it. Conditions on indexed fields such as `keyword`, [numeric](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/number), `date`, `ip`, `geo_point`, `geo_shape`, `cartesian_point`, or `cartesian_shape` types are pushed down to Lucene, which skips irrelevant documents entirely.
Without a [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where), ES|QL scans every document in the matched indices:
❌ **Don't:** Filter after the aggregation
```esql
FROM logs-*
| STATS count = COUNT(*) BY host.name, log.level
| WHERE log.level == "error"
```
✅ **Do:** Push the filter up so it runs before the aggregation
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
AND log.level == "error"
| STATS count = COUNT(*) BY host.name
```
### Restrict the index pattern
A broad [`FROM *`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/from) forces ES|QL to discover field mappings across every index and then query each one. On clusters with thousands of indices, that discovery overhead alone can dominate query time. In Serverless, `FROM *` can also expand to all linked projects in [cross-project search](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-cross-serverless-projects). Use [`project_routing`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-cross-serverless-projects#use-project-routing) to limit a cross-project query to the projects that it actually needs.
❌ **Don't:** Use wildcards that match more indices than the query needs
```esql
FROM *
| WHERE @timestamp > NOW() - 1 hour
AND event.category == "authentication"
| STATS failures = COUNT(*) BY user.name
```
✅ **Do:** Target a specific index pattern instead
```esql
FROM logs-system-*
| WHERE @timestamp > NOW() - 1 hour
AND event.category == "authentication"
| STATS failures = COUNT(*) BY user.name
```
When a query genuinely needs multiple patterns, list them explicitly with [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/from). For example:
```esql
FROM logs-system-*, logs-auth-*
```
### Use TS for time series data
- Elastic Cloud Serverless: Generally available
- Elastic Stack: Generally available since 9.4
- Elastic Stack: Preview from 9.2 to 9.3
For time series data streams (TSDS), use [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/ts) rather than [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/from). `TS` understands time series structure, including dimensions, metrics, and time ordering, and skips data more efficiently than `FROM` paired with [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where). It also unlocks time series functions such as [`RATE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/time-series-aggregation-functions/rate) and bucketing through [`TBUCKET`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/grouping-functions/tbucket).
❌ **Don't:** Query TSDS indices through [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/from) when you intend to aggregate metrics
```esql
FROM metrics-system.cpu-*
| WHERE @timestamp > NOW() - 1 hour
| STATS avg_cpu = AVG(system.cpu.user.pct) BY host.name, bucket = DATE_TRUNC(5 minutes, @timestamp)
```
✅ **Do:** Use [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/ts) with [`TBUCKET`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/grouping-functions/tbucket) for time series metrics
```esql
TS metrics-system.cpu-*
| STATS avg_cpu = AVG(AVG_OVER_TIME(system.cpu.user.pct))
BY host.name, TBUCKET(5 minutes)
```
[`TS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/ts) only works on indices created as time series data streams. For non-TSDS indices, continue to use [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/from).
## Reduce what you return
Every column returned has to be read from storage, serialized, and transmitted. Shrinking the result set, by returning fewer columns or rows, often produces significant gains on large indices.
### Select columns with KEEP
[`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/keep) selects which columns to return. [`DROP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/drop) does the inverse. Without either, ES|QL returns every field in every matching document. This is the single biggest source of avoidable overhead on indices with hundreds or thousands of fields.
❌ **Don't:** Return every field by default
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour AND log.level == "error"
| SORT @timestamp DESC
| LIMIT 100
```
✅ **Do:** Project only the fields the consumer actually needs
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour AND log.level == "error"
| KEEP @timestamp, host.name, message, log.level
| SORT @timestamp DESC
| LIMIT 100
```
Use wildcards in [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/keep) sparingly. `host.*` is better than no `KEEP` at all, but `host.name` is better than `host.*` because it avoids pulling in adjacent fields.
When using the REST API on sparse datasets where many columns are `null`, consider setting the [`drop_null_columns`](https://www.elastic.co/docs/api/doc/elasticsearch/operation/operation-esql-async-query-get#operation-esql-async-query-get-drop_null_columns) query parameter. This removes columns that contain only `null` values from the response, which can significantly reduce serialization overhead.
### Cap rows with LIMIT
Always include a [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/limit) on queries that return raw rows. ES|QL appends a default limit of 1000 rows to every query. Reducing it with an explicit `LIMIT` is one of the simplest ways to speed up a query. Increasing it beyond the default makes serialization slower and can trigger deserialization errors in Kibana. The maximum configurable limit is 10,000 rows.
❌ **Don't:** Leave the result set unbounded
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day AND log.level == "error"
| SORT @timestamp DESC
```
✅ **Do:** Cap the result to the rows the consumer actually needs
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day AND log.level == "error"
| SORT @timestamp DESC
| LIMIT 100
```
## Avoid expensive operations
Some ES|QL operations are intrinsically more expensive than their alternatives. Knowing the cheaper substitute, and when it applies, often replaces a slow query with a fast one. The subsections below are ordered roughly by impact, with the highest-leverage changes first.
### Use full-text search instead of LIKE or RLIKE
For text search, prefer [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match), [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match_phrase), [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/qstr), or [`KQL`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/kql) over [`LIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-like) or [`RLIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-rlike). The full-text search functions use the inverted index and are optimized for analyzed text. `LIKE` and `RLIKE` are pattern-matching operators. Pre 8.18/9.0 they are especially costly because they are not pushed down to Lucene. Leading wildcards (for example `*something`) are particularly expensive because they cannot use the inverted index efficiently.
❌ **Don't:** Use pattern matching on free text with [`LIKE`](/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/operators#esql-like)
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
AND message LIKE "*connection refused*"
```
✅ **Do:** Use [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match_phrase) against the inverted index
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
AND MATCH_PHRASE(message, "connection refused")
```
[`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match) works on `text` and `keyword` fields. Use [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/match_phrase) when the words must appear together in order. For Lucene query syntax with `field:value` and boolean operators, use [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/qstr). For Kibana Query Language syntax, use [`KQL`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/search-functions/kql).
To learn more about using ES|QL for search use cases, refer to [ES|QL for search](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/solutions/search/esql-for-search).
### Avoid high-cardinality STATS BY
Each unique combination of `BY` values creates a bucket in memory. Grouping by high-cardinality fields such as raw timestamps, full URLs, or document IDs, or by many fields at once, can produce millions of buckets.
High-cardinality groupings can exhaust memory and trip circuit breakers. Always bucket timestamps and choose the lowest-cardinality representation of a field that still answers the question.
❌ **Don't:** Group by raw, high-cardinality fields
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
| STATS count = COUNT(*) BY url.full, user.name, @timestamp
```
✅ **Do:** Reduce cardinality before grouping
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
| STATS count = COUNT(*) BY url.path, user.name, bucket = DATE_TRUNC(1 hour, @timestamp)
```
Common reductions include: bucketing timestamps with [`DATE_TRUNC`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/date-time-functions/date_trunc) or [`BUCKET`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/grouping-functions/bucket), using `url.path` instead of `url.full`, and filtering to a known subset before the [`STATS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/stats-by).
### Prefer fields backed by doc values
ES|QL reads field values through a block-loading system that strongly prefers [doc values](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/doc-values). Fields with doc values, such as `keyword`, [numeric](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/number), `date`, `ip`, `geo_point`, `geo_shape`, `cartesian_point`, and `cartesian_shape` types, are read in fast columnar batches. Fields without doc values, such as `text` and `match_only_text`, fall back to reading [`_source`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/mapping-source-field), which requires decompressing and parsing the full JSON document per row. This applies to any operation that reads the field value, including filtering, grouping, sorting, and returning fields through [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/keep).
If an exact `.keyword` subfield exists, the query planner automatically rewrites expressions to use it, so `message` and `message.keyword` perform the same in that case. However, if the `text` field has no `keyword` subfield, or if the subfield is not exact (for example, it uses `ignore_above`), the planner cannot rewrite and falls back to reading from `_source`, which is significantly slower. When no exact subfield is available, filter aggressively to limit the number of documents that require `_source` reads.
❌ **Don't:** Group by an analyzed field
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| STATS count = COUNT(*) BY message
```
✅ **Do:** Use the `.keyword` subfield
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| STATS count = COUNT(*) BY message.keyword
```
For free-text grouping, [`CATEGORIZE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/grouping-functions/categorize) Elastic Stack: Generally available since 9.1, Elastic Stack: Preview in 9.0 groups similar messages automatically:
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| STATS count = COUNT(*) BY category = CATEGORIZE(message)
| SORT count DESC
| LIMIT 20
```
### Return spatial fields only when you need source precision
The spatial types `geo_point`, `geo_shape`, `cartesian_point`, and `cartesian_shape` are maintained at source precision in the original documents, but indexed at reduced precision by Lucene for performance. Reading spatial values from doc values is fast and usually precise enough. Returning the original spatial field preserves source precision, but requires reading from `_source`, which is slower. To prioritize performance, drop original spatial fields from the result unless the query consumer needs the exact original value.
To learn more, refer to [Spatial precision](/elastic/docs-builder/docs/3562/reference/query-languages/esql/limitations#esql-limitations-spatial-precision).
### Use per-aggregation WHERE instead of CASE
For conditional aggregations, attach a [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where) clause directly to each [`STATS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/stats-by) expression rather than wrapping values in [`CASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case). `CASE` is lazy-evaluated and slow for this pattern.
❌ **Don't:** Emulate conditional aggregations through [`CASE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case) and [`SUM`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/functions-operators/aggregation-functions/sum)
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
| EVAL is_error = CASE(log.level == "error", 1, 0)
| EVAL is_warn = CASE(log.level == "warning", 1, 0)
| STATS
total = COUNT(*),
errors = SUM(is_error),
warnings = SUM(is_warn)
BY service.name
```
✅ **Do:** Compute each conditional metric directly with a per-aggregation [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/where)
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
| STATS
total = COUNT(*),
errors = COUNT(*) WHERE log.level == "error",
warnings = COUNT(*) WHERE log.level == "warning"
BY service.name
```
### Prefer DISSECT over GROK
[`GROK`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/grok) uses regular expressions, which are CPU-intensive per row. [`DISSECT`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/dissect) uses delimiter-based tokenization and is much cheaper. When the log format uses consistent delimiters, prefer `DISSECT`. When you must use `GROK`, filter aggressively first to shrink the dataset.
❌ **Don't:** Use regex parsing when a delimiter is available
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| GROK message "%{TIMESTAMP_ISO8601:ts} %{LOGLEVEL:level} %{GREEDYDATA:msg}"
```
✅ **Do:** Use [`DISSECT`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/dissect) for delimiter-based formats
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| DISSECT message "%{ts} %{level} %{msg}"
```
### Filter before LOOKUP JOIN
[`LOOKUP JOIN`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/lookup-join) combines each incoming row with matching rows from a lookup index. Joining fewer incoming rows is usually faster, and large lookup matches can increase memory pressure.
Filter the source data before joining, and keep the lookup index as small and purpose-built as possible:
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
AND event.category == "network"
| LOOKUP JOIN threat_list ON source.ip
| KEEP @timestamp, source.ip, threat_list.risk, event.action
```
ES|QL tries to push filters before the join when possible. Write the query with the selective filters before `LOOKUP JOIN` so the intended execution order is clear and the join receives the smallest practical input.
### Use approximate aggregations where possible
- Elastic Cloud Serverless: Preview
- Elastic Stack: Preview since 9.4
For large [`STATS`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/commands/stats-by) queries, exact results can be expensive. If approximate results are acceptable, [approximate `STATS` queries](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-query-approximation) can trade exactness for much faster execution on large datasets.
Approximation is useful for exploratory analysis, dashboard panels, and high-cardinality aggregations where a close estimate is enough. Use exact aggregations when the result feeds billing, compliance, alerting, or other workflows that require precise values.
## Monitor query performance
Once a query is written, several tools help confirm whether it is actually fast and identify regressions over time. When reviewing query logs, scan for [common anti-patterns](#common-anti-patterns) first.
- [**Inspect panel**](#inspect-panel-in-kib): Check a query from Discover or a dashboard.
- [**Query activity**](#query-activity): Find and cancel in-flight queries.
- [**Profile API responses**](#profile-api-responses): Inspect how a query executed through the API.
- [**Query logging**](#query-logging): Analyze historical slow queries.
- [**Task management API**](#task-management-api): Monitor or cancel ES|QL tasks from the API.
### Inspect panel in Kibana
In [Discover](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/explore-analyze/discover) or within a [dashboard](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/explore-analyze/dashboards), select **Inspect** to see the ES|QL query sent to the cluster and the `took` value, which is the server-side execution time in milliseconds. This helps clarify if the root cause is the query itself, the network, or Kibana's rendering.
### Query activity
- Elastic Cloud Serverless: Preview
- Elastic Stack: Preview since 9.4
The [**Query activity**](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/query-activity) page in Kibana provides a real-time view of all in-flight search work in your cluster, including ES|QL, Query DSL, EQL, and SQL queries. Use it to find long-running queries, trace them back to their source in Kibana, and cancel them when needed.
### Profile API responses
When running an ES|QL query through the [ES|QL query API](https://www.elastic.co/docs/api/doc/elasticsearch/operation/operation-esql-query), set the `profile` body parameter to `true` to include a `profile` object in the response. The profile output is intended for human debugging and can help identify which parts of a query contribute to its runtime. The response format can change at any time, so use it for investigation rather than automation.
### Query logging
- Elastic Cloud Serverless: Unavailable
- Elastic Stack: Preview since 9.4
Query logging captures Query DSL, EQL, KQL, and ES|QL queries that exceed configurable duration thresholds and stores them in a managed data stream for analysis. This is the recommended way to log slow queries. To configure it, refer to [Query logging](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/logging-configuration/query-logs).
For clusters on earlier versions, a legacy ES|QL-specific query log Elastic Stack: Generally available since 9.1 writes slow queries to a `_esql_querylog.json` file in the Elasticsearch log directory. To configure it, refer to [ES|QL query log](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-query-log).
### Task management API
The [task management API](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-task-management) lets you monitor and cancel long-running ES|QL queries.
List running ES|QL tasks:
```json
```
Cancel a specific task:
```json
```
## Related pages
- Inspect query logs:
- (recommended) [Query logging](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/deploy-manage/monitor/logging-configuration/query-logs) Elastic Stack: Preview since 9.4: Log all query types through a managed data stream
- [ES|QL query log](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-query-log) Elastic Stack: Generally available since 9.1: Log ES|QL queries to a file on each node
- [Circuit breaker settings](/elastic/docs-builder/docs/3562/reference/elasticsearch/configuration-reference/circuit-breaker-settings#circuit-breakers-page-esql): Configure ES|QL memory limits and troubleshoot circuit breaker errors
- [ES|QL task management](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-task-management): Monitor and cancel long-running queries
- [Approximate STATS queries](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-query-approximation) Elastic Stack: Preview since 9.4: Trade exact results for faster aggregations on large datasets
- [Filing a support case](/elastic/docs-builder/docs/3562/reference/query-languages/esql/esql-troubleshooting#filing-a-support-case): Learn what to include when reporting a slow or failing query
- [Explicit mapping](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/manage-data/data-store/mapping/explicit-mapping): Control which fields are indexed and how
- [ES|QL for search](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3562/solutions/search/esql-for-search): Use ES|QL for full-text search, vector search, and AI-powered retrieval
- [`doc_values`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/doc-values): Learn how columnar storage enables fast sorting, aggregations, and field reads
- [`_source`](https://www.elastic.co/elastic/docs-builder/docs/3562/reference/elasticsearch/mapping-reference/mapping-source-field): Learn how the original JSON document is stored with each record