query-optimization

description: Query DSL patterns, aggregation optimization, caching strategies, and search profiling for performance tuning

OpenSearch/Elasticsearch Query Optimization

> **Scope**: Query DSL performance patterns, filter vs query context, aggregation tuning, and profile API usage. Covers OpenSearch 2.x and Elasticsearch 8.x (compatible APIs). > **Version range**: OpenSearch 2.0+ / Elasticsearch 8.0+ > **Generated**: 2026-04-08

---

Overview

Query performance in OpenSearch/Elasticsearch fails in three predictable ways: using query context where filter context is appropriate (query context scores and doesn't cache), returning all fields when only a subset is needed (network and memory overhead), and running large aggregations without sampling (cardinality explosions block query threads). The profile API pinpoints which part of a query is slow; without it, optimization is guesswork.

---

Pattern Table

| Pattern | Version | Use When | Avoid When | |---------|---------|----------|------------| | Filter context (`filter: []`) | All versions | Exact matches, ranges, term filters | Full-text relevance scoring needed | | `_source: ['field1', 'field2']` | All versions | Partial document retrieval | Mapping has stored fields separately | | `request_cache: true` | All versions | Aggregation queries, date-histogram | Frequently-changing data | | Profile API (`"profile": true`) | All versions | Diagnosing slow queries | Production traffic (25% overhead) | | `track_total_hits: false` | ES 7.0+/OS 1.0+ | Pagination beyond 10k hits | When exact count is required | | Async search | ES 7.7+/OS 1.0+ | Aggregations > 10 seconds | Real-time user-facing queries |

---

Correct Patterns

Filter vs Query Context

Use `filter` for non-scoring conditions — filter results are cached and reused across requests.

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "title": "kubernetes deployment"
          }
        }
      ],
      "filter": [
        { "term": { "status": "published" } },
        { "range": { "published_at": { "gte": "2024-01-01" } } },
        { "terms": { "tags": ["kubernetes", "devops"] } }
      ]
    }
  }
}

**Why**: `filter` context is cached in the filter cache. `must` (query context) recalculates relevance scores on every request. Status checks, date ranges, and term filters have no relevance to scoring — they belong in `filter`.

---

Source Filtering for Large Documents

{
  "query": { "match": { "content": "deployment strategy" } },
  "_source": {
    "includes": ["title", "summary", "author", "published_at"],
    "excludes": ["content", "raw_html", "embedding_vector"]
  },
  "size": 20
}

**Why**: `content` fields of 50KB+ per document mean a 20-hit response could return 1MB+ over the wire. Excluding large fields reduces network transfer by 90%+ for content-heavy indices.

---

Aggregation Cardinality Limits

{
  "aggs": {
    "by_category": {
      "terms": {
        "field": "category.keyword",
        "size": 20,
        "shard_size": 100,
        "min_doc_count": 5
      }
    },
    "user_count": {
      "cardinality": {
        "field": "user_id",
        "precision_threshold": 1000
      }
    }
  }
}

**Why**: `size: 0` or missing `size` defaults to 10. Missing `shard_size` means each shard returns `size` results — set `shard_size` to 5x `size` for accuracy. `cardinality` with high `precision_threshold` uses more memory — tune to accuracy needs.

---

Profile API for Diagnosis

{
  "profile": true,
  "query": {
    "bool": {
      "should": [
        { "match": { "title": "kubernetes" } },
        { "match": { "content": "kubernetes" } }
      ]
    }
  }
}

Interpret the response:

{
  "profile": {
    "shards": [{
      "searches": [{
        "query": [{
          "type": "BooleanQuery",
          "time_in_nanos": 450000,
          "breakdown": {
            "score": 120000,
            "build_scorer": 330000
          }
        }]
      }]
    }]
  }
}

**Why**: `build_scorer` dominates? The query is computing relevance scores for many documents — add filters to reduce candidate set. `score` dominates? Complex scoring function — consider a simpler similarity model.

---

Pattern Catalog

Use Prefix or N-gram Search Instead of Leading Wildcards

**Detection**:

# Find wildcard queries with leading wildcard
grep -rn '"wildcard"' --include="*.json" queries/
# Also check application code
rg '"wildcard"' --type json queries/
grep -rn 'wildcard.*\*.*value\|value.*\*.*wild' --include="*.py" --include="*.ts" --include="*.go" src/

**Signal**:

{
  "query": {
    "wildcard": {
      "username": {
        "value": "*smith*"
      }
    }
  }
}

**Why this matters**: Leading wildcards (`*smith`) force a full index scan — every term in the inverted index must be checked. On a 10M document index with 500K unique usernames, this scans 500K terms per shard. Query latency jumps from ~5ms to 5+ seconds. Trailing wildcards (`smith*`) can use the index efficiently.

**Preferred action**: Use `match_phrase_prefix` for prefix searches or `n-gram` tokenization for infix searches. For substring search, configure `edge_ngram` analyzer at index time.

{
  "query": {
    "match_phrase_prefix": {
      "username": "smith"
    }
  }
}

---

Use search_after for Deep Pagination

**Detection**:

grep -rn '"from"' --include="*.json" queries/ | grep -v '"from": [0-9]$\|"from": [1-9][0-9]$'
# Find large from values
rg '"from":\s*[0-9]{4,}' --type json

**Signal**:

{
  "from": 10000,
  "size": 20,
  "query": { "match_all": {} }
}

**Why this matters**: `from: 10000` fetches 10,020 documents per shard, discards 10,000, returns 20. On a 5-shard index: 50,100 documents fetched, 50,080 discarded. Memory and CPU scale linearly with `from` value. Default limit is `index.max_result_window: 10000` — exceeding it throws