Explain Analyze

EXPLAIN and ANALYZE are PostgreSQL keywords for inspecting how the query optimizer plans to execute a query. This is the primary tool for diagnosing slow queries and verifying that indexes are being used.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN — Shows the planned execution strategy without running the query. Safe for destructive queries (DELETE, UPDATE).
• EXPLAIN ANALYZE — Actually runs the query and shows real execution times alongside the plan. Use on SELECT or wrap mutations in a rolled-back transaction.

-- Plan only (safe)
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
 
-- Plan + actual execution (runs the query!)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';

Reading the Output

Key fields in the output:

Field	Meaning
Seq Scan	Sequential scan — reads every row. Means no useful Index was found
Index Scan	Uses a B-Tree index to find matching rows
Index Only Scan	All needed columns are in the index — fastest
Bitmap Index Scan	Builds a bitmap of matching pages, then fetches them
cost=0.00..X	Estimated startup cost and total cost (arbitrary units)
rows=N	Estimated number of rows returned
actual time=X..Y	Real execution time in ms (only with ANALYZE)
rows=N (actual)	Actual rows returned (compare with estimate)
Rows Removed by Filter	How many rows were scanned but discarded

What to Look For

• Seq Scan on large tables — Usually means a missing Index. Add one on the filtered column.
• Estimated vs actual rows far apart — Stale statistics. Run ANALYZE tablename; to update.
• Nested Loop on large tables — May indicate a missing Index on the inner table’s join column.
• Sort with high cost — Consider adding an index that matches the ORDER BY.
• Rows Removed by Filter is high — The query is scanning many rows and discarding most. Tighten the Index or WHERE clause.

Useful Variants

-- Include buffer usage statistics
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
 
-- Machine-readable JSON format
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;
 
-- Verbose output with column details
EXPLAIN (VERBOSE) SELECT ...;

Practical Tips

• In any indexed query we can improve the performance by using LIMIT to fetch only the data required and stop a complete Sequential Scan
• Always run ANALYZE on tables after bulk data loads to update statistics
• Use \timing in psql to see total query execution time
• Compare plans before and after adding an Index to verify improvement