EXPLAIN & ANALYZE
EXPLAIN is how you stop guessing and start knowing what PostgreSQL does with your queries. EXPLAIN shows the query plan. EXPLAIN ANALYZE actually runs the query and shows you what really happened. The gap between those two is where performance problems hide.
EXPLAIN: The Query Plan
EXPLAIN shows the planner's strategy without executing the query.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=5 width=64)
Index Cond: (customer_id = 42)
The planner chose an Index Scan. The cost numbers are estimates: startup cost (0.43) and total cost (8.45). The rows estimate says it expects 5 rows. The width is the average row size in bytes.
EXPLAIN ANALYZE: What Actually Happened
EXPLAIN ANALYZE executes the query and reports real numbers alongside the estimates.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders
(cost=0.43..8.45 rows=5 width=64)
(actual time=0.028..0.035 rows=3 loops=1)
Index Cond: (customer_id = 42)
Buffers: shared hit=4
Planning Time: 0.085 ms
Execution Time: 0.052 ms
Now you see actual time (in milliseconds), actual rows returned (3, not the estimated 5), and buffer usage. The BUFFERS option shows how many pages were read from shared buffers (cache hits) vs disk.
Warning: EXPLAIN ANALYZE executes the query. For INSERT, UPDATE, or DELETE, wrap it in a transaction and roll back.
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2020-01-01';
ROLLBACK;
Scan Types
Seq Scan (Sequential Scan)
Reads every row in the table. Fine for small tables or when you need most of the rows. Bad when you need a handful of rows from a large table.
Seq Scan on orders (cost=0.00..18334.00 rows=500000 width=64)
Filter: (status = 'pending')
Rows Removed by Filter: 450000
"Rows Removed by Filter" tells you the scan read 450,000 rows just to keep 50,000. If you only need a small fraction, an index would help.
Index Scan
Walks the B-tree index to find matching rows, then fetches the actual rows from the heap (table). Efficient when selecting a small percentage of rows.
Index Scan using idx_orders_status on orders (cost=0.42..856.33 rows=500 width=64)
Index Cond: (status = 'pending')
Bitmap Scan
A two-phase scan. First, it builds a bitmap of matching pages from the index. Then it reads those pages sequentially from the table. Used when too many rows for a plain Index Scan but too few for a Seq Scan.
Bitmap Heap Scan on orders (cost=112.50..5678.90 rows=5000 width=64)
Recheck Cond: (status = 'pending')
-> Bitmap Index Scan on idx_orders_status (cost=0.00..111.25 rows=5000 width=0)
Index Cond: (status = 'pending')
The "Recheck Cond" step is needed because the bitmap tracks pages, not individual rows.
Join Types
Nested Loop
For each row in the outer table, scan the inner table. Best when the outer table is small and the inner table has a good index.
Nested Loop (cost=0.43..1250.00 rows=100 width=128)
-> Seq Scan on customers (cost=0.00..25.00 rows=10 width=64)
Filter: (region = 'us-west')
-> Index Scan using idx_orders_customer on orders (cost=0.43..122.00 rows=10 width=64)
Index Cond: (customer_id = customers.id)
Hash Join
Builds a hash table from the smaller relation, then probes it with rows from the larger one. Good for larger joins without useful indexes.
Hash Join (cost=250.00..6543.21 rows=10000 width=128)
Hash Cond: (orders.customer_id = customers.id)
-> Seq Scan on orders (cost=0.00..4321.00 rows=200000 width=64)
-> Hash (cost=200.00..200.00 rows=5000 width=64)
-> Seq Scan on customers (cost=0.00..200.00 rows=5000 width=64)
Merge Join
Sorts both inputs on the join key and then merges them. Efficient when both inputs are already sorted or when the data set is large enough to justify sorting.
Merge Join (cost=1500.00..3000.00 rows=50000 width=128)
Merge Cond: (orders.customer_id = customers.id)
-> Sort (cost=900.00..950.00 rows=200000 width=64)
Sort Key: orders.customer_id
-> Seq Scan on orders (cost=0.00..4321.00 rows=200000 width=64)
-> Sort (cost=600.00..625.00 rows=5000 width=64)
Sort Key: customers.id
-> Seq Scan on customers (cost=0.00..200.00 rows=5000 width=64)
Reading Cost Estimates
Cost is measured in arbitrary units (by default, a sequential page read = 1.0). The two numbers are:
- Startup cost: work before the first row can be returned.
- Total cost: estimated total work to return all rows.
These are cumulative up the tree. A parent node's cost includes its children.
When the Planner Is Wrong
The planner relies on table statistics collected by ANALYZE (not EXPLAIN ANALYZE). Stale statistics lead to bad plans.
-- Force a statistics refresh
ANALYZE orders;
Common reasons the planner gets row estimates wrong:
- Stale statistics: autovacuum hasn't run ANALYZE recently.
- Correlated columns: the planner assumes columns are independent. If
status = 'pending'andregion = 'us-west'are correlated, the combined estimate will be off. Extended statistics (CREATE STATISTICS) can help. - Complex expressions: the planner uses a default selectivity for functions and complex WHERE clauses.
- Skewed data: the default statistics target (100 most common values) may miss rare but important values.
-- Check estimated vs actual rows
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending' AND region = 'us-west';
If estimated rows are 10 but actual rows are 10,000, the planner picked the wrong join strategy or scan type.
Useful EXPLAIN Options
-- Full diagnostic output
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;
-- JSON output for programmatic parsing
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;
-- Verbose shows output columns and schema-qualified names
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT ...;
-- SETTINGS shows non-default planner settings affecting the query (v12+)
EXPLAIN (ANALYZE, SETTINGS)
SELECT ...;
-- WAL shows WAL usage for write queries (v13+)
EXPLAIN (ANALYZE, WAL, BUFFERS)
INSERT INTO ...;
Practical Workflow
- Run EXPLAIN to see the plan.
- Run EXPLAIN (ANALYZE, BUFFERS) to see reality.
- Compare estimated rows to actual rows at every node.
- Look for nodes with large discrepancies — those are where the planner went wrong.
- Check if ANALYZE has been run recently on the involved tables.
- Look at BUFFERS output — large "shared read" counts mean data is not in cache.
- Focus on the most expensive nodes first.
-- Find tables with stale statistics
SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE last_analyze IS NULL AND last_autoanalyze IS NULL;
Common Pitfalls
- Running EXPLAIN ANALYZE on destructive queries without a transaction. Always wrap mutating EXPLAIN ANALYZE in BEGIN/ROLLBACK.
- Optimizing queries based on EXPLAIN alone. The cost estimates are only as good as the statistics. Always verify with EXPLAIN ANALYZE.
- Ignoring "Rows Removed by Filter". A Seq Scan that removes 99% of rows is screaming for an index.
- Tuning based on a warm cache. Run EXPLAIN (ANALYZE, BUFFERS) and check for "shared read" (disk) vs "shared hit" (cache). Production cold-cache behavior matters.
- Forgetting that EXPLAIN ANALYZE includes execution time. If you run it on a slow query, you wait for it to finish. Use EXPLAIN (without ANALYZE) first for an initial look.
- Not reading the plan bottom-up. Execution starts at the deepest nodes and flows up. The top node is the last step.
Key Takeaways
- EXPLAIN shows what the planner intends. EXPLAIN ANALYZE shows what actually happened.
- Always use BUFFERS to see I/O behavior.
- Compare estimated rows to actual rows at every node. Large gaps indicate stale or insufficient statistics.
- Seq Scan is not always bad. Index Scan is not always good. Context matters.
- Run ANALYZE on tables with stale statistics before tuning queries.
- Wrap mutating EXPLAIN ANALYZE calls in a transaction and roll back.
- The plan is a tree — read from the innermost (bottom) nodes outward.