elasticsearch-query-optimization

Files (2)

• SKILL.md
• query-optimization-reference.md · references/

SKILL.md (first lines)

# 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:

   ```json
   {
     "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:

   ```json
   {
     "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

From elastic/agent-skills, Apache-2.0.

More from elastic/agent-skills