Elasticsearch Query DSL Optimization
Diagnose why a Query DSL search is slow, identify the dominant cost from the profile (not guesswork), rewrite the query
to remove that cost while preserving match semantics, and re-measure with profiling enabled.
Environment Configuration
This skill executes Elasticsearch operations through the elastic CLI. If the
elastic CLI is not installed, tell the user what it is needed for. Do
not guess credentials, call the HTTP API directly, or attempt other workarounds.
This skill references operations in HTTP-shorthand form (e.g., GET /, GET /_cat/indices, GET /{index}/_mapping,
GET /{index}/_settings/index.mode, POST /_query). The Operations table at the end of this document
maps each shorthand to the equivalent elastic CLI command — always use the CLI rather than calling the HTTP API
directly.
Scope: Query DSL searches via POST /{index}/_search. This skill does not migrate queries to ES|QL — it optimizes
the existing bool/match/term/wildcard structure the user already runs.
Ground rule: Never recommend "add shards" or "scale hardware" as the primary fix when the profile names a specific
clause (for example WildcardQuery at ~3.8s). Fix the query first; infrastructure changes require evidence the query
is already optimal.
Process
Confirm connectivity and locate the target index. Call GET /. If the call fails, stop — do not guess endpoints
or credentials. When the user names an index pattern (for example logs-*), narrow candidates with
GET /_cat/indices and pick the index or pattern the query actually targets.
Decision: proceed only when the index is known. Data needed: index name or pattern, and the slow Query DSL
body (from the user or from a saved search).
Profile the slow query to find the dominant cost. Call POST /{index}/_search with "profile": true and the
user's query unchanged. Read took, then inspect profile.shards[].searches[].query — sort child collectors by
time_in_nanos and identify the top contributor.
Decision: classify the bottleneck from profile evidence:
TermQuery / PointRangeQuery / MatchNoDocsQuery inside must alongside a scoring clause — exact-match or
range filters are being scored unnecessarily. Likely fix: move them to filter context (step 4a).
WildcardQuery with a leading * (for example message:*timeout*) — cannot use the inverted index; scans
terms per document. Likely fix: remove the leading wildcard (step 4b).
MatchQuery on a text field — expected scoring cost; optimize only if profile shows it dominates after
filter-context fixes.
- High
aggregation time — separate from query tuning; profile the agg tree (out of scope unless the user asked
about aggs).
Data needed: profile tree with type, description, time_in_nanos, and breakdown (especially next_doc for
wildcards). Quote the top contributor verbatim when explaining the diagnosis.
Inspect field mappings before rewriting. Call GET /{index}/_mapping. For every clause you will move or rewrite,
confirm the field type:
term / terms / filter on exact values — field must be keyword (or another non-analyzed type). A term
on a text field is a common bug; if types are wrong, say so and suggest the correct sub-field (for example
service.keyword) or a mapping change — do not silently rewrite.
match / match_phrase — target a text field (analyzed).
wildcard — works on keyword or wildcard types; leading * still forces a scan regardless of type.
Decision: only propose rewrites that match confirmed types. Data needed: mapping for each field referenced in
the query.
Rewrite the query to remove the profiled bottleneck.
4a. Move non-scoring clauses from must to filter
When exact-match term/terms/range/match on a keyword (or other non-scoring intent) clauses sit in must
alongside a full-text match that should drive relevance:
- Move exact-match clauses into
bool.filter (or a filter array entry).
- Keep only clauses that must affect
_score in bool.must (typically the full-text match).
Why: filter context skips scoring and participates in the filter/bitset cache on repeated queries. Semantics:
the same documents match; only scoring and performance change — state this explicitly.
Example rewrite pattern:
{
"query": {
"bool": {
"filter": [{ "term": { "status": "active" } }, { "term": { "tenant_id": "acme" } }],
"must": [{ "match": { "description": "wireless keyboard" } }]
}
}
}
4b. Eliminate leading wildcards
When the profile shows WildcardQuery with description like message:*timeout* and high next_doc time, the
leading * prevents index lookup. Choose a fix based on mapping and user intent (substring vs prefix vs exact):
| Intent |
Preferred rewrite |
| Full-text substring in logs |
match or match_phrase on the analyzed message text field |
| Literal substring on keyword |
wildcard-typed field, or reindex with ngram analyzer |
Prefix only (timeout*) |
prefix query on keyword, or edge ngram at index time |
Also move any non-scoring exact match (for example { "match": { "service": "checkout" } } on a keyword) into
filter — use term on the keyword field when the mapping confirms it.
Example rewrite pattern:
{
"query": {
"bool": {
"filter": [{ "term": { "service.keyword": "checkout" } }],
"must": [{ "match": { "message": "timeout" } }]
}
}
}
Adjust field names (service vs service.keyword) to match the mapping from step 3.
4c. Optional — validate rewrite before profiling
When semantics are uncertain (for example changing wildcard to match may include analyzed tokens the wildcard
excluded), call POST /{index}/_validate/query?explain=true with the rewritten query and read the explanation for
obvious mismatches.
Decision: pick the smallest rewrite that addresses the profiled cost. Data needed: rewritten Query DSL body.
Re-profile the rewritten query and compare. Call POST /{index}/_search again with "profile": true and the
rewritten query. Compare took and the top profile collector to the baseline from step 2.
Decision: report success only when the dominant collector changed or time_in_nanos dropped materially. If the
profile still shows a leading wildcard or scored filters, iterate — do not declare victory from took alone without
profile confirmation.
Data needed: before/after profile summaries (top collector type, description, time_in_nanos).
Report findings in this order.
- Root cause — quote the profile (for example "
WildcardQuery message:*timeout* ≈ 3.8s, mostly next_doc").
- Rewrite — show the optimized bool structure with filter vs must separation.
- Mapping notes — keyword vs text confirmations from
GET /{index}/_mapping.
- Measured improvement — before/after profile or
took from step 5.
- Semantic caveat — only if the rewrite could change which documents match (for example
match vs substring
wildcard).
Guidelines
- Profile first. If the user supplies a profile summary, use it — but still recommend re-profiling after changes.
- Filter is for equality, must is for relevance. Status, tenant ID, service name, and time ranges rarely belong in
must when a text query drives ranking.
- Leading wildcards are almost never the right fix for log search. Prefer analyzed
match/match_phrase; reserve
wildcard for suffix patterns (timeout*) on keyword or wildcard-typed fields.
- Do not conflate slow with wrong. A slow query can return correct results; optimization preserves the result set
unless you explicitly warn about a semantic trade-off.
- Deep reference: profile collector types, filter-cache behavior, and wildcard alternatives —
references/query-optimization-reference.md.
Examples
Unscored terms in must
Input: bool.must contains term on status, term on tenant_id, and match on description.
Diagnosis: profile shows scored TermQuery collectors alongside MatchQuery; exact filters do not need scoring.
Fix: move both term clauses to filter; keep match in must. Confirm status and tenant_id are keyword.
Leading wildcard dominates latency
Input: wildcard message:*timeout* plus match on service in must. Profile: WildcardQuery ~3.8s.
Diagnosis: leading * forces term enumeration; not an index/shard problem.
Fix: match on analyzed message; move service to filter as term on keyword. Re-profile — expect
WildcardQuery to disappear or shrink to negligible time.
Operations
| HTTP API (shorthand) |
elastic CLI command |
GET / |
elastic es info |
GET /_cat/indices |
elastic es cat indices --index '<pattern>' |
GET /{index}/_mapping |
elastic es indices get-mapping --index '<index>' |
POST /{index}/_search |
elastic es search --index '<index>' --input-file '<search-body.json>' |
POST /{index}/_validate/query?explain=true |
elastic es indices validate-query --index '<index>' --explain true --query '<json>' |
Include "profile": true in the search JSON body (or pass --profile true) when profiling in steps 2 and 5.
1---
2name: elasticsearch-query-optimization
3description: >
4 Diagnose slow Elasticsearch Query DSL searches and propose measured fixes. Use when
5 a search is slow, profile output shows an expensive clause, exact-match filters
6 sit in scoring context, or leading wildcards dominate latency. Ground every recommendation
7 in search profiling — move non-scoring clauses to filter context, eliminate leading
8 wildcards, and re-profile to confirm improvement.
9metadata:
10 source: https://skillmd.com/skills/gabrielmoreira/elasticsearch-query-optimization
11 author: elastic
12 version: 0.1.0
13 universal: true
14compatibility: Elasticsearch 8.x or 9.x, self-managed, Elastic Cloud Hosted, or Elastic
15 Cloud Serverless; relies on the search profiling API available on all deployment
16 types. Requires the `elastic` CLI ≥ 0.2 with `stack es` support.
17---
18
19# Elasticsearch Query DSL Optimization
20
21Diagnose why a Query DSL search is slow, identify the dominant cost from the profile (not guesswork), rewrite the query
22to remove that cost while preserving match semantics, and re-measure with profiling enabled.
23
24<!-- begin-partial: preamble -->
25
26## Environment Configuration
27
28This skill executes Elasticsearch operations through the `elastic` CLI. If the
29[`elastic` CLI](https://github.com/elastic/cli#configuration) is not installed, tell the user what it is needed for. Do
30not guess credentials, call the HTTP API directly, or attempt other workarounds.
31
32This skill references operations in HTTP-shorthand form (e.g., `GET /`, `GET /_cat/indices`, `GET /{index}/_mapping`,
33`GET /{index}/_settings/index.mode`, `POST /_query`). The [Operations](#operations) table at the end of this document
34maps each shorthand to the equivalent `elastic` CLI command — always use the CLI rather than calling the HTTP API
35directly.
36
37<!-- end-partial: preamble -->
38
39> **Scope:** Query DSL searches via `POST /{index}/_search`. This skill does not migrate queries to ES|QL — it optimizes
40> the existing bool/match/term/wildcard structure the user already runs.
41>
42> **Ground rule:** Never recommend "add shards" or "scale hardware" as the primary fix when the profile names a specific
43> clause (for example `WildcardQuery` at ~3.8s). Fix the query first; infrastructure changes require evidence the query
44> is already optimal.
45
46## Process
47
481. **Confirm connectivity and locate the target index.** Call `GET /`. If the call fails, stop — do not guess endpoints
49 or credentials. When the user names an index pattern (for example `logs-*`), narrow candidates with
50 `GET /_cat/indices` and pick the index or pattern the query actually targets.
51
52 **Decision:** proceed only when the index is known. **Data needed:** index name or pattern, and the slow Query DSL
53 body (from the user or from a saved search).
54
552. **Profile the slow query to find the dominant cost.** Call `POST /{index}/_search` with `"profile": true` and the
56 user's query unchanged. Read `took`, then inspect `profile.shards[].searches[].query` — sort child collectors by
57 `time_in_nanos` and identify the top contributor.
58
59 **Decision:** classify the bottleneck from profile evidence:
60 - **`TermQuery` / `PointRangeQuery` / `MatchNoDocsQuery` inside `must` alongside a scoring clause** — exact-match or
61 range filters are being scored unnecessarily. Likely fix: move them to `filter` context (step 4a).
62 - **`WildcardQuery` with a leading `*` (for example `message:*timeout*`)** — cannot use the inverted index; scans
63 terms per document. Likely fix: remove the leading wildcard (step 4b).
64 - **`MatchQuery` on a `text` field** — expected scoring cost; optimize only if profile shows it dominates _after_
65 filter-context fixes.
66 - **High `aggregation` time** — separate from query tuning; profile the agg tree (out of scope unless the user asked
67 about aggs).
68
69 **Data needed:** profile tree with `type`, `description`, `time_in_nanos`, and `breakdown` (especially `next_doc` for
70 wildcards). Quote the top contributor verbatim when explaining the diagnosis.
71
723. **Inspect field mappings before rewriting.** Call `GET /{index}/_mapping`. For every clause you will move or rewrite,
73 confirm the field type:
74 - **`term` / `terms` / `filter` on exact values** — field must be `keyword` (or another non-analyzed type). A `term`
75 on a `text` field is a common bug; if types are wrong, say so and suggest the correct sub-field (for example
76 `service.keyword`) or a mapping change — do not silently rewrite.
77 - **`match` / `match_phrase`** — target a `text` field (analyzed).
78 - **`wildcard`** — works on `keyword` or `wildcard` types; leading `*` still forces a scan regardless of type.
79
80 **Decision:** only propose rewrites that match confirmed types. **Data needed:** mapping for each field referenced in
81 the query.
82
834. **Rewrite the query to remove the profiled bottleneck.**
84
85 ### 4a. Move non-scoring clauses from `must` to `filter`
86
87 When exact-match `term`/`terms`/`range`/`match` on a keyword (or other non-scoring intent) clauses sit in `must`
88 alongside a full-text `match` that should drive relevance:
89 - Move exact-match clauses into `bool.filter` (or a `filter` array entry).
90 - Keep only clauses that must affect `_score` in `bool.must` (typically the full-text `match`).
91
92 **Why:** filter context skips scoring and participates in the filter/bitset cache on repeated queries. **Semantics:**
93 the same documents match; only scoring and performance change — state this explicitly.
94
95 Example rewrite pattern:
96
97 ```json
98 {
99 "query": {
100 "bool": {
101 "filter": [{ "term": { "status": "active" } }, { "term": { "tenant_id": "acme" } }],
102 "must": [{ "match": { "description": "wireless keyboard" } }]
103 }
104 }
105 }
106 ```
107
108 ### 4b. Eliminate leading wildcards
109
110 When the profile shows `WildcardQuery` with `description` like `message:*timeout*` and high `next_doc` time, the
111 leading `*` prevents index lookup. Choose a fix based on mapping and user intent (substring vs prefix vs exact):
112
113 | Intent | Preferred rewrite |
114 | ---------------------------- | ---------------------------------------------------------------- |
115 | Full-text substring in logs | `match` or `match_phrase` on the analyzed `message` `text` field |
116 | Literal substring on keyword | `wildcard`-typed field, or reindex with ngram analyzer |
117 | Prefix only (`timeout*`) | `prefix` query on `keyword`, or edge ngram at index time |
118
119 Also move any non-scoring exact match (for example `{ "match": { "service": "checkout" } }` on a keyword) into
120 `filter` — use `term` on the keyword field when the mapping confirms it.
121
122 Example rewrite pattern:
123
124 ```json
125 {
126 "query": {
127 "bool": {
128 "filter": [{ "term": { "service.keyword": "checkout" } }],
129 "must": [{ "match": { "message": "timeout" } }]
130 }
131 }
132 }
133 ```
134
135 Adjust field names (`service` vs `service.keyword`) to match the mapping from step 3.
136
137 ### 4c. Optional — validate rewrite before profiling
138
139 When semantics are uncertain (for example changing `wildcard` to `match` may include analyzed tokens the wildcard
140 excluded), call `POST /{index}/_validate/query?explain=true` with the rewritten query and read the explanation for
141 obvious mismatches.
142
143 **Decision:** pick the smallest rewrite that addresses the profiled cost. **Data needed:** rewritten Query DSL body.
144
1455. **Re-profile the rewritten query and compare.** Call `POST /{index}/_search` again with `"profile": true` and the
146 rewritten query. Compare `took` and the top profile collector to the baseline from step 2.
147
148 **Decision:** report success only when the dominant collector changed or `time_in_nanos` dropped materially. If the
149 profile still shows a leading wildcard or scored filters, iterate — do not declare victory from `took` alone without
150 profile confirmation.
151
152 **Data needed:** before/after profile summaries (top collector `type`, `description`, `time_in_nanos`).
153
1546. **Report findings in this order.**
155 1. **Root cause** — quote the profile (for example "`WildcardQuery` `message:*timeout*` ≈ 3.8s, mostly `next_doc`").
156 2. **Rewrite** — show the optimized bool structure with filter vs must separation.
157 3. **Mapping notes** — keyword vs text confirmations from `GET /{index}/_mapping`.
158 4. **Measured improvement** — before/after profile or `took` from step 5.
159 5. **Semantic caveat** — only if the rewrite could change which documents match (for example `match` vs substring
160 `wildcard`).
161
162## Guidelines
163
164- **Profile first.** If the user supplies a profile summary, use it — but still recommend re-profiling after changes.
165- **Filter is for equality, must is for relevance.** Status, tenant ID, service name, and time ranges rarely belong in
166 `must` when a text query drives ranking.
167- **Leading wildcards are almost never the right fix for log search.** Prefer analyzed `match`/`match_phrase`; reserve
168 `wildcard` for suffix patterns (`timeout*`) on keyword or `wildcard`-typed fields.
169- **Do not conflate slow with wrong.** A slow query can return correct results; optimization preserves the result set
170 unless you explicitly warn about a semantic trade-off.
171- **Deep reference:** profile collector types, filter-cache behavior, and wildcard alternatives —
172 [references/query-optimization-reference.md](references/query-optimization-reference.md).
173
174## Examples
175
176### Unscored terms in `must`
177
178**Input:** `bool.must` contains `term` on `status`, `term` on `tenant_id`, and `match` on `description`.
179
180**Diagnosis:** profile shows scored `TermQuery` collectors alongside `MatchQuery`; exact filters do not need scoring.
181
182**Fix:** move both `term` clauses to `filter`; keep `match` in `must`. Confirm `status` and `tenant_id` are `keyword`.
183
184### Leading wildcard dominates latency
185
186**Input:** `wildcard` `message:*timeout*` plus `match` on `service` in `must`. Profile: `WildcardQuery` ~3.8s.
187
188**Diagnosis:** leading `*` forces term enumeration; not an index/shard problem.
189
190**Fix:** `match` on analyzed `message`; move service to `filter` as `term` on keyword. Re-profile — expect
191`WildcardQuery` to disappear or shrink to negligible time.
192
193## Operations
194
195| HTTP API (shorthand) | `elastic` CLI command |
196| -------------------------------------------- | ------------------------------------------------------------------------------------- |
197| `GET /` | `elastic es info` |
198| `GET /_cat/indices` | `elastic es cat indices --index '<pattern>'` |
199| `GET /{index}/_mapping` | `elastic es indices get-mapping --index '<index>'` |
200| `POST /{index}/_search` | `elastic es search --index '<index>' --input-file '<search-body.json>'` |
201| `POST /{index}/_validate/query?explain=true` | `elastic es indices validate-query --index '<index>' --explain true --query '<json>'` |
202
203Include `"profile": true` in the search JSON body (or pass `--profile true`) when profiling in steps 2 and 5.