query-optimization

Query Optimization

Diagnose and fix slow SQL queries using EXPLAIN ANALYZE, proper indexing, query rewriting, and N+1 detection.

When to Use

• User reports slow database queries
• User asks about EXPLAIN ANALYZE output
• User needs to optimize query performance
• User has N+1 query problems in an ORM
• User asks about index tuning or covering indexes

Core Patterns

EXPLAIN ANALYZE

Always start with EXPLAIN ANALYZE to understand what the database is actually doing.

-- Basic usage
EXPLAIN ANALYZE
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.ordered_at DESC
LIMIT 20;

Key things to look for in the output:

-- BAD: Sequential scan on large table
Seq Scan on orders  (cost=0.00..15234.00 rows=500000 width=48)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 450000

-- GOOD: Index scan
Index Scan using idx_orders_status_ordered_at on orders
  (cost=0.42..85.20 rows=50000 width=48)
  Index Cond: (status = 'pending'::text)

-- Check actual vs estimated rows (large discrepancies mean stale statistics)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 12345;

-- If estimates are wrong, update statistics
ANALYZE orders;

Index Tuning

Create indexes that match your query patterns. Column order in composite indexes matters.

-- Query: WHERE status = ? AND ordered_at > ? ORDER BY ordered_at
-- Index must match: equality columns first, then range/sort columns
CREATE INDEX idx_orders_status_ordered_at ON orders(status, ordered_at DESC);

-- Query: WHERE customer_id = ? AND status = ? ORDER BY ordered_at DESC
CREATE INDEX idx_orders_customer_status_date
  ON orders(customer_id, status, ordered_at DESC);

-- Verify the index is being used
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 100 AND status = 'pending'
ORDER BY ordered_at DESC
LIMIT 10;

Covering Indexes (Index-Only Scans)

Include all columns the query needs in the index to avoid hitting the table at all.