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

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

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

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

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

6. Report findings in this order.

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