PostgreSQL EXPLAIN ANALYZE reading guide

EXPLAIN ANALYZE output is complex and hard to interpret. Need to know what to look for to diagnose slow queries.

How to read EXPLAIN ANALYZE output:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123;

-- Key metrics per node:
-- cost=0.00..123.45 (startup cost..total cost, in arbitrary units)
-- rows=100 (estimated rows)
-- actual time=0.5..2.3 (startup..total in ms)
-- actual rows=95 (actual rows returned)
-- loops=1 (times this node executed)

-- Red flags to look for:

-- 1. Seq Scan on large table (needs index):
-- Seq Scan on orders (actual rows=1000000)
-- Fix: CREATE INDEX ON orders(user_id);

-- 2. Estimated vs actual rows way off:
-- rows=10 vs actual rows=100000
-- Fix: ANALYZE orders; (update statistics)

-- 3. Nested Loop with high outer rows:
-- Nested Loop (actual rows=1000)
-- -> Seq Scan on a (actual rows=1000)
-- -> Index Scan on b (loops=1000) <- 1000 lookups!
-- Consider: Hash Join or add covering index

-- 4. Sort with high memory:
-- Sort Method: external merge (means disk sort, slow)
-- Fix: Increase work_mem or add index for ORDER BY

-- 5. Buffers: shared hit vs read:
-- Buffers: shared hit=100 read=5000 (5000 from disk!)
-- Fix: Increase shared_buffers or add index

-- Quick wins:
-- ANALYZE tablename; (update stats)
-- CREATE INDEX CONCURRENTLY ...; (don't lock table)
-- SET work_mem = '256MB'; (for current session)

EXPLAIN ANALYZE is the single most important tool for PostgreSQL performance. Learning to read it saves hours of guessing.