elasticsearch-query-optimization

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.

<!-- begin-partial: preamble -->

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](#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.

<!-- end-partial: preamble -->

> 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

1. 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).

1. 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.

1. 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.

1. 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.

1. 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).

1. Report findings in this order.
2. Root cause — quote the profile (for example "WildcardQuery message:timeout ≈ 3.8s, mostly next_doc").
3. Rewrite — show the optimized bool structure with filter vs must separation.
4. Mapping notes — keyword vs text confirmations from GET /{index}/_mapping.
5. Measured improvement — before/after profile or took from step 5.
6. 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](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.