/elasticsearch-query-optimization

name: elasticsearch-query-optimization
description: >
  Diagnose slow Elasticsearch Query DSL searches and propose measured fixes. Use when
  a search is slow, profile output shows an expensive clause, exact-match filters
  sit in scoring context, or leading wildcards dominate latency. Ground every recommendation
  in search profiling — move non-scoring clauses to filter context, eliminate leading
  wildcards, and re-profile to confirm improvement.
metadata:
  author: elastic
  version: 0.1.0
  universal: true
compatibility: Elasticsearch 8.x or 9.x, self-managed, Elastic Cloud Hosted, or Elastic
  Cloud Serverless; relies on the search profiling API available on all deployment
  types. Requires the `elastic` CLI ≥ 0.2 with `stack es` support.

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](https://github.com/elastic/cli#configuration) 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).

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 | Pr