references/query-optimization-reference.md
# Query DSL Optimization Reference
Supporting detail for profile interpretation, query vs filter context, and wildcard alternatives.
## Search profiling
Enable profiling with `"profile": true` on `POST /{index}/_search`. The response adds a `profile` object parallel to
`hits`.
### Reading the query tree
Each shard returns `profile.shards[].searches[].query` — a tree of collectors. For each node, inspect:
| Field | Meaning |
| --------------- | ------------------------------------------------------------------------------- |
| `type` | Collector class (`WildcardQuery`, `TermQuery`, `MatchQuery`, `BooleanQuery`, …) |
| `description` | Lucene description (for example `message:*timeout*`, `status:active`) |
| `time_in_nanos` | Total time for this collector on this shard |
| `breakdown` | Sub-timers; `next_doc` high on wildcards indicates per-doc scanning |
**Triage rule:** the collector with the highest `time_in_nanos` (summed across shards when comparing) is the primary
optimization target. Secondary collectors matter only after the top cost is addressed.
### Common profile signatures
| Profile signal | Typical cause | First fix |
| ---------------------------------------------------- | ------------------------------------- | ---------------------------------------------- |
| `WildcardQuery` + high `next_doc` + `*term*` pattern | Leading or infix wildcard | `match`/`match_phrase`, prefix, or ngram field |
| Multiple `TermQuery` in `must` + one `MatchQuery` | Filters scored unnecessarily | Move terms to `filter` |
| `MatchQuery` dominates after filter fix | Large candidate set or heavy analyzer | Narrow with `filter`; check analyzer |
| `BooleanQuery` with many `should` | Disjunction max over many clauses | Reduce clauses; move constants to `filter` |
Profiling adds overhead — use it for diagnosis and before/after comparison, not on every production request.
## Query context vs filter context
In a `bool` query:
| Context | Scoring | Caching | Use for |
| ---------- | ------- | --------------------------- | -------------------------------------------- |
| `must` | Yes | No | Clauses that must match **and** affect score |
| `should` | Yes | No | Optional relevance boosts |
| `filter` | No | Yes (filter cache / bitset) | Exact match, ranges, non-scoring matches |
| `must_not` | No | No (exclusion) | Exclusions |
Moving a `term` from `must` to `filter`:
- **Same matching documents** when the clause is required (wrap in `bool` with the same logical AND).
- **Different `_score`** — filter clauses do not contribute to score; the remaining `must` clauses define ranking.
- **Better repeat-query performance** — identical filter clauses can reuse cached bitsets.
## Wildcard and substring search
### Why leading wildcards are slow
Lucene's inverted index maps **terms → documents**. A prefix or suffix wildcard (`timeout*`) can sometimes use the term
dictionary. A leading wildcard (`*timeout*`) cannot narrow the term set upfront; the engine scans many terms and visits
documents (`next_doc` in the profile).
### Alternatives by requirement
| Requirement | Approach |
| ----------------------------- | ------------------------------------------------------------------------- |
| Search analyzed log text | `match` or `match_phrase` on `text` field |
| Case-sensitive substring | `wildcard`-typed field (still costly for leading `*`; better than `text`) |
| Fast prefix autocomplete | Edge n-gram tokenizer at index time + `match` or `prefix` on keyword |
| Fast infix/substring at scale | N-gram analyzer (index-time cost; query-time `match`) |
| Known suffix on keyword | `wildcard` with pattern `*suffix` (no leading star on short prefix) |
When replacing `wildcard` `*foo*` with `match`, warn that analysis may tokenize differently (for example `timeout` vs
`timeouts`) — compare hit counts or use `validate/query` when precision matters.
## Field type cheat sheet
| Query type | Expected mapping | Common mistake |
| ---------- | ------------------------ | -------------------------------------- |
| `term` | `keyword`, numeric, date | `term` on analyzed `text` |
| `match` | `text` | Using `match` for exact ID equality |
| `wildcard` | `keyword`, `wildcard` | Leading `*` on high-cardinality fields |
| `range` | Numeric, date, keyword | Range on `text` |
Always confirm with `GET /{index}/_mapping` — ECS and custom schemas use different sub-field names (`service` vs
`service.keyword`).
SKILL.md
---
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:
```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
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](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.