Chapter 16: EXPLAIN ANALYZE — Reading Query Plans

EXPLAIN is your X-ray into the database's brain. It shows you exactly how the database will (or did) execute your query. This is the #1 tool for diagnosing slow queries.

EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN: shows the PLAN (estimated, doesn't run the query)
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN ANALYZE: runs the query and shows ACTUAL timings
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN with all details (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'alice@example.com';

Reading a Query Plan

-- Query:
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25 ORDER BY name LIMIT 10;

-- Output:
Limit (cost=0.42..1.23 rows=10 width=72) (actual time=0.05..0.08 rows=10 loops=1)
  → Index Scan using idx_users_name on users
      (cost=0.42..8523.42 rows=33333 width=72)
      (actual time=0.04..0.07 rows=10 loops=1)
      Filter: (age > 25)
      Rows Removed by Filter: 3
Planning Time: 0.12 ms
Execution Time: 0.10 ms

How to Read Each Line

Limit (cost=0.42..1.23 rows=10 width=72) (actual time=0.05..0.08 rows=10 loops=1) │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─ iterations │ │ │ │ │ │ │ └─ actual rows returned │ │ │ │ │ │ └─ actual time (ms) last row │ │ │ │ │ └─ actual time (ms) first row │ │ │ │ └─ estimated row width (bytes) │ │ │ └─ estimated rows │ │ └─ estimated total cost │ └─ estimated startup cost └─ node type (operation) cost = arbitrary units (sequential page read = 1.0) time = actual milliseconds

Common Node Types

Node	Meaning	Watch For
Seq Scan	Full table scan	On large tables = slow
Index Scan	B-tree lookup + heap fetch	Good for selective queries
Index Only Scan	All data from index, no heap	Best case — very fast
Bitmap Index Scan	Build bitmap from index	Multiple conditions combined
Nested Loop	For each outer row, scan inner	OK if inner is indexed
Hash Join	Build hash table, probe	Good for large equi-joins
Sort	Sort in memory or disk	Check if spilling to disk
Aggregate	GROUP BY / COUNT / SUM	HashAggregate vs GroupAggregate

Red Flags in Query Plans

⚠️ Performance Red Flags

Seq Scan on large table — missing index?
Rows Removed by Filter: 999000 — reading 1M rows to return 1000. Need better index.
Sort Method: external merge Disk — not enough work_mem, sorting on disk
Nested Loop with Seq Scan inner — O(n×m), needs index on join column
Estimated rows vs actual rows differ wildly — stale statistics, run ANALYZE

Example: Diagnosing a Slow Query

-- Slow query: 2 seconds
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'Sweden'
GROUP BY u.name;

-- Plan reveals:
Hash Join (actual time=1800..1950 rows=500)
  → Seq Scan on users (actual time=0.01..50 rows=5000)
      Filter: (country = 'Sweden')
      Rows Removed by Filter: 995000    ← RED FLAG! Scanning 1M to get 5K
  → Seq Scan on orders (actual time=0.01..800 rows=2000000)  ← RED FLAG!

-- Fix: add indexes
CREATE INDEX idx_users_country ON users (country);
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- After indexes: 5ms
Nested Loop (actual time=0.1..5.0 rows=500)
  → Index Scan using idx_users_country on users (actual time=0.05..0.5 rows=5000)
      Index Cond: (country = 'Sweden')
  → Index Scan using idx_orders_user_id on orders (actual time=0.01..0.01 rows=4)
      Index Cond: (user_id = u.id)

BUFFERS: I/O Details

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE id = 123;

-- Output includes:
-- Buffers: shared hit=3          ← 3 pages read from buffer pool (RAM)
-- Buffers: shared hit=1 read=2   ← 1 from RAM, 2 from disk
-- Buffers: shared hit=0 read=5000 ← all from disk! Cold cache.

Quick Reference: Optimization Workflow

Identify slow query (from logs or monitoring)
Run EXPLAIN (ANALYZE, BUFFERS)
Look for red flags (Seq Scan on large table, high Rows Removed)
Add appropriate index
Re-run EXPLAIN to verify improvement
If estimates are wrong, run ANALYZE tablename

Key Takeaways

EXPLAIN shows the plan. EXPLAIN ANALYZE runs it and shows actual timings.
Read bottom-up: innermost nodes execute first
Red flags: Seq Scan on large tables, high "Rows Removed by Filter"
Compare estimated rows vs actual rows — big differences mean stale stats
BUFFERS shows cache hit ratio — high "read" count means cold cache or missing index
This is the #1 tool for performance debugging. Use it constantly.

← Previous: Query Planner Next: ACID Transactions →