Fix Elasticsearch Query Taking Too Long

What's Actually Happening

Elasticsearch search queries take excessive time to execute, causing application timeouts, poor user experience, and cluster resource exhaustion. Queries that should complete in milliseconds take seconds or minutes.

The Error You'll See

Slow search response:

```bash $ curl -X GET "localhost:9200/my-index/_search?pretty" -d '{"query": {"match": {"content": "test"}}}'

{ "took": 15000, # 15 seconds - too slow! "timed_out": false, "hits": { "total": {"value": 1000} } } ```

Query timeout:

```bash $ curl -X GET "localhost:9200/my-index/_search?timeout=5s"

{ "took": 5000, "timed_out": true, # Query timed out "hits": { "total": {"value": 0} # No results } } ```

Search thread pool exhausted:

```bash $ curl localhost:9200/_cat/thread_pool?v

node_name name active queue rejected node1 search 100 100 5000 # Rejected queries! ```

Why This Happens

1. 1.Complex query - Too many clauses or nested queries
2. 2.Large result set - Returning too many documents
3. 3.No index optimization - Missing mappings or settings
4. 4.Heavy aggregations - Complex aggregation calculations
5. 5.Insufficient cache - Query cache not configured
6. 6.Shard imbalance - Uneven shard distribution

Step 1: Identify Slow Queries

```bash # Enable slow query logging curl -X PUT "localhost:9200/my-index/_settings" -d ' { "index.search.slowlog.threshold.query.warn": "10s", "index.search.slowlog.threshold.query.info": "5s", "index.search.slowlog.threshold.fetch.warn": "1s" }'

# Check slow logs tail -f /var/log/elasticsearch/my-index_search_slowlog.json

# Example slow log entry: { "type": "index_search_slowlog", "level": "WARN", "took": "15s", "query": {"match": {"content": "test"}}, "total_hits": 10000 }

# Use profile API to analyze query curl -X GET "localhost:9200/my-index/_search?profile=true" -d '{"query": {"match": {"content": "test"}}}' ```

Step 2: Analyze Query Profile

```bash # Profile query execution curl -X GET "localhost:9200/my-index/_search?profile" -d ' { "query": { "bool": { "must": [ {"match": {"title": "test"}}, {"match": {"content": "search"}} ], "filter": [ {"range": {"date": {"gte": "2025-01-01"}}} ] } } }'

# Profile output shows: { "profile": { "shards": [ { "id": "[my-index][0]", "searches": [ { "query": [...], "rewrite_time": 12345, "collector": [...] } ] } ] } }

# Identify expensive operations curl localhost:9200/_nodes/stats/indices/search?pretty | grep query_time ```

Step 3: Optimize Query Complexity

```bash # Reduce query complexity

# Avoid wildcard queries (very slow) curl -X GET "localhost:9200/my-index/_search" -d ' {"query": {"wildcard": {"name": "*test*"}}}' # Wildcard scans all terms - avoid!

# Use prefix instead of leading wildcard: curl -X GET "localhost:9200/my-index/_search" -d ' {"query": {"prefix": {"name": "test"}}}'

# Avoid too many clauses in bool query # Default max_clause_count = 1024

# For term queries with many values, use terms lookup: curl -X GET "localhost:9200/my-index/_search" -d ' { "query": { "terms": { "id": { "index": "users", "id": "user1", "path": "following" } } } }'

# Use constant_score for filter-only queries: curl -X GET "localhost:9200/my-index/_search" -d ' { "query": { "constant_score": { "filter": {"term": {"status": "active"}} } } }' # No scoring = faster execution ```

Step 4: Limit Result Size

```bash # Limit number of results returned

# Default size is 10, increase cautiously curl -X GET "localhost:9200/my-index/_search" -d ' {"query": {"match": {"content": "test"}}, "size": 100}'

# For large result sets, use scroll API curl -X GET "localhost:9200/my-index/_search?scroll=1m" -d ' {"query": {"match": {"content": "test"}}, "size": 1000}'

# Get scroll ID { "_scroll_id": "FGluY2x1ZGVfY29udmVyc2F0aW9uX...", "hits": {...} }

# Continue scrolling curl -X GET "localhost:9200/_search/scroll" -d ' {"scroll": "1m", "scroll_id": "FGluY2x1ZGVfY29udmVyc2F0aW9uX..."}'

# Clear scroll when done curl -X DELETE "localhost:9200/_search/scroll" -d ' {"scroll_id": "FGluY2x1ZGVfY29udmVyc2F0aW9uX..."}'

# Use search_after for pagination (more efficient) curl -X GET "localhost:9200/my-index/_search" -d ' { "query": {"match": {"content": "test"}}, "size": 100, "sort": [{"date": "desc"}, {"_id": "asc"}], "search_after": ["2025-01-01", "doc123"] }' ```

Step 5: Optimize Index Settings

```bash # Check current index settings curl localhost:9200/my-index/_settings?pretty

# Increase refresh interval (default 1s) # Longer interval = fewer segments = faster search curl -X PUT "localhost:9200/my-index/_settings" -d ' {"index": {"refresh_interval": "30s"}}'

# For bulk indexing, disable refresh temporarily: curl -X PUT "localhost:9200/my-index/_settings" -d ' {"index": {"refresh_interval": "-1"}}'

# After bulk, restore and force refresh: curl -X PUT "localhost:9200/my-index/_settings" -d ' {"index": {"refresh_interval": "1s"}}' curl -X POST "localhost:9200/my-index/_refresh"

# Increase number of replicas for search load: curl -X PUT "localhost:9200/my-index/_settings" -d ' {"number_of_replicas": 2}'

# But replicas increase indexing cost ```

Step 6: Optimize Field Mappings

```bash # Check mappings curl localhost:9200/my-index/_mapping?pretty

# Optimize mappings: # 1. Use keyword for exact match fields curl -X PUT "localhost:9200/my-index/_mapping" -d ' { "properties": { "status": {"type": "keyword"}, "user_id": {"type": "keyword"} } }'

# 2. Disable _all field (removed in ES 6+) # 3. Use doc_values for sorting/aggregations { "properties": { "date": { "type": "date", "doc_values": true } } }

# 4. Disable norms for keyword fields { "properties": { "category": { "type": "keyword", "norms": false } } }

# 5. Use index: false for fields not searched { "properties": { "metadata": { "type": "object", "index": false } } } ```

Step 7: Configure Query Cache

```bash # Check cache statistics curl localhost:9200/_nodes/stats/indices/query_cache?pretty

# Enable query cache (default on) curl -X PUT "localhost:9200/my-index/_settings" -d ' { "index.queries.cache.enabled": true, "index.queries.cache.size": "10%" }'

# Query cache stores frequently used filter results

# Check cache hit rate curl localhost:9200/_nodes/stats/indices/query_cache?pretty | grep -E "cache_size|hit_count|miss_count"

# Use filter context for cacheable queries curl -X GET "localhost:9200/my-index/_search" -d ' { "query": { "bool": { "filter": {"term": {"status": "active"}} # Cached } } }'

# Must clauses are not cached # Filter clauses are cached ```

Step 8: Optimize Aggregations

```bash # Aggregations can be slow on large datasets

# Use bucket aggregation with reasonable size: curl -X GET "localhost:9200/my-index/_search" -d ' { "size": 0, "aggs": { "categories": { "terms": {"field": "category", "size": 10} } } }'

# Use composite aggregation for pagination: curl -X GET "localhost:9200/my-index/_search" -d ' { "size": 0, "aggs": { "my_buckets": { "composite": { "sources": [{"category": {"terms": {"field": "category"}}}], "size": 100 } } } }'

# Pre-aggregate data for common queries # Use transform feature (ES 7+): curl -X PUT "localhost:9200/_transform/my-transform" -d ' { "source": {"index": "my-index"}, "dest": {"index": "my-index-aggregated"}, "pivot": { "group_by": {"category": {"terms": {"field": "category"}}}, "aggregations": {"total": {"sum": {"field": "value"}}} } }' ```

Step 9: Balance Shard Distribution

```bash # Check shard distribution curl localhost:9200/_cat/shards?v

index shard prirep state docs store ip my-index 0 p STARTED 1M 5GB 10.0.0.1 my-index 1 p STARTED 2M 10GB 10.0.0.1 # Large shard! my-index 2 p STARTED 500K 2GB 10.0.0.2

# Check shard size curl localhost:9200/_cat/shards?v&h=index,shard,prirep,store,docs

# Rebalance shards curl -X PUT "localhost:9200/_cluster/settings" -d ' { "transient": { "cluster.routing.rebalance.enable": "all" } }'

# Check cluster routing settings curl localhost:9200/_cluster/settings?include_defaults=true&flat_settings=true | grep routing

# Adjust shard count for future indices curl -X PUT "localhost:9200/my-new-index" -d ' { "settings": { "number_of_shards": 5, "number_of_replicas": 1 } }' ```

Step 10: Monitor Query Performance

```bash # Create monitoring script cat << 'EOF' > monitor_es_queries.sh #!/bin/bash echo "=== Search Thread Pool ===" curl -s localhost:9200/_cat/thread_pool?v | grep search

echo "" echo "=== Slow Query Stats ===" curl -s localhost:9200/_nodes/stats/indices/search?pretty | jq '.nodes | to_entries | .[] | {name: .value.name, query_time: .value.indices.search.query_time_in_millis}'

echo "" echo "=== Query Cache Stats ===" curl -s localhost:9200/_nodes/stats/indices/query_cache?pretty | jq '.nodes | to_entries | .[] | {name: .value.name, hit_count: .value.indices.query_cache.hit_count, miss_count: .value.indices.query_cache.miss_count}'

echo "" echo "=== Index Stats ===" curl -s localhost:9200/_cat/indices?v&h=index,docs.count,store.size,search.query_current EOF

chmod +x monitor_es_queries.sh

# Monitor with Elasticsearch monitoring tools # - Kibana APM # - Elasticsearch Exporter for Prometheus # - Cerebro/Kopf

# Set up alert for slow queries curl -X PUT "localhost:9200/my-index/_settings" -d ' { "index.search.slowlog.threshold.query.warn": "5s", "index.search.slowlog.threshold.fetch.warn": "1s" }' ```

Elasticsearch Query Optimization Checklist

Verify the Fix

```bash # After optimizing queries and index

# 1. Test query performance curl -X GET "localhost:9200/my-index/_search" -d '{"query": {"match": {"content": "test"}}}' // took should be < 100ms

# 2. Profile query curl -X GET "localhost:9200/my-index/_search?profile" -d '{"query": {...}}' // No expensive operations

# 3. Check cache hit rate curl localhost:9200/_nodes/stats/indices/query_cache | grep -E "hit|miss" // High hit count relative to miss

# 4. Monitor thread pool curl localhost:9200/_cat/thread_pool?v | grep search // No rejected queries

# 5. Check slow logs tail /var/log/elasticsearch/*_search_slowlog.json // Few or no entries

# 6. Benchmark query throughput # Run multiple queries and measure response time ```

Related Issues

• [Fix Elasticsearch Index Not Found](/articles/fix-elasticsearch-index-not-found)
• [Fix Elasticsearch Cluster Red Status](/articles/fix-elasticsearch-cluster-red-status)
• [Fix Elasticsearch Shard Allocation Failed](/articles/fix-elasticsearch-shard-allocation-failed)