patternsqlpostgresqlTip
Reading EXPLAIN ANALYZE output: key nodes and red flags
Viewed 0 times
EXPLAIN ANALYZEquery planSeq ScanIndex ScanBuffersnested looprow estimateBUFFERS
Problem
Developers run EXPLAIN ANALYZE but cannot parse the output to identify why a query is slow or which part of the plan is the bottleneck.
Solution
Learn the key nodes and what to look for:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
-- Key node types:
-- Seq Scan: full table scan. Red flag on large tables.
-- Index Scan: uses B-tree index. Good.
-- Index Only Scan: reads index without heap. Best for covered columns.
-- Bitmap Heap Scan: batches index lookups. Good for medium selectivity.
-- Hash Join / Merge Join / Nested Loop: join strategies.
-- What to check:
-- 'rows=X' (estimated) vs 'rows=X' (actual): large divergence = stale stats.
-- 'Buffers: hit=X read=Y': high 'read' = cache miss, I/O bound.
-- 'actual time=X..Y loops=Z': if loops is high in Nested Loop, inner side is executed many times.
-- 'Filter: (...)' below a Seq Scan: column needs an index.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
-- Key node types:
-- Seq Scan: full table scan. Red flag on large tables.
-- Index Scan: uses B-tree index. Good.
-- Index Only Scan: reads index without heap. Best for covered columns.
-- Bitmap Heap Scan: batches index lookups. Good for medium selectivity.
-- Hash Join / Merge Join / Nested Loop: join strategies.
-- What to check:
-- 'rows=X' (estimated) vs 'rows=X' (actual): large divergence = stale stats.
-- 'Buffers: hit=X read=Y': high 'read' = cache miss, I/O bound.
-- 'actual time=X..Y loops=Z': if loops is high in Nested Loop, inner side is executed many times.
-- 'Filter: (...)' below a Seq Scan: column needs an index.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';
Why
EXPLAIN ANALYZE executes the query and shows actual row counts and timing alongside the planner's estimates. Divergence between estimates and actuals points to stale statistics (run ANALYZE) or correlated columns requiring multi-column statistics.
Gotchas
- EXPLAIN ANALYZE actually runs the query; wrap in BEGIN/ROLLBACK for mutating queries
- Use EXPLAIN (ANALYZE, BUFFERS) not just EXPLAIN; BUFFERS reveals I/O vs cache behavior
- explain.depesz.com and explain.dalibo.com provide visual EXPLAIN ANALYZE rendering
- Add CREATE STATISTICS for correlated columns to improve estimate accuracy
Code Snippets
Safe EXPLAIN ANALYZE for writes and improving correlation statistics
-- Safe EXPLAIN ANALYZE for write queries:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;
-- Add multi-column statistics for correlated columns:
CREATE STATISTICS stat_orders_status_customer
ON status, customer_id FROM orders;
ANALYZE orders;Context
Diagnosing slow query performance and verifying index usage
Revisions (0)
No revisions yet.