EXPLAIN & EXPLAIN ANALYZE
EXPLAIN shows the plan plus estimates. EXPLAIN (ANALYZE, BUFFERS) executes and shows actual time/rows plus buffer hits. Always compare Plan Rows vs Actual Rows; big mismatches mean stats or correlation problems. Never run ANALYZE on unchecked writes in prod without a transaction you can roll back.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
If they say the index exists, ask for the plan. EXPLAIN is a story. EXPLAIN ANALYZE is a trace. The interview is the gap between rows=1 and rows=8000.
By the end you should be able to:
- Parse
cost,rows,width,actual time,loops - Know which join/scan node you are looking at
- Use BUFFERS to see cache vs disk
- Avoid executing a prod
DELETEvia ANALYZE - Turn a 100× row misestimate into ANALYZE / rewrite / index
Debugging a slow query
Prefer
EXPLAIN (ANALYZE, BUFFERS) on a replica / rollback txn
Actual time and rows. Nested loop exploding because the inner estimate was 1. Fix stats or the join key, then re-read the plan.
- Plan Rows vs Actual Rows is the first line you speak.
- BUFFERS: hit vs read tells you if the algorithm or the cache is the story.
- FORMAT JSON for tooling (Dalibo) on fat plans.
Alternative
EXPLAIN without ANALYZE, or ANALYZE a prod UPDATE
You optimize a plan that never ran, or you write the UPDATE for real. Both are incidents.
- COSTS OFF still does not execute — you need ANALYZE for actuals.
- Triggers, RLS, and volatile functions run under ANALYZE.
- Wrap writes: BEGIN; EXPLAIN ANALYZE …; ROLLBACK;
How to read a plan
Bottom/inside first. Estimates vs actuals second. Buffers third.
- 1
Find the scans
Seq Scan, Index Scan, Index Only, Bitmap Index → Bitmap Heap. - 2
Find the joins
Nested Loop (small outer + indexed inner), Hash Join (big =), Merge Join (ordered). - 3
Compare Plan Rows vs Actual Rows × loops
Inner of a nested loop: actual total = rows × loops. - 4
BUFFERS shared hit/read
read=5000 on a tiny estimate means random I/O or a bad join. - 5
ANALYZE a write without a txn
It ran. The rows are gone.
EXPLAIN vs ANALYZE vs BUFFERS
EXPLAIN SELECT …; -- estimates only
EXPLAIN (ANALYZE, BUFFERS) SELECT …; -- runs it
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) …;
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE … ;
ROLLBACK; -- required for writes| Field | Meaning |
|---|---|
cost=a..b | Startup .. total, planner units |
rows= | Estimated cardinality |
width= | Estimated avg row bytes |
actual time=a..b | ms, startup .. total this node |
rows= (actual) | Average rows per loop |
loops= | How many times the node ran |
Buffers: shared hit/read | Postgres cache vs OS/disk read |
ANALYZE the statistics command is not EXPLAIN ANALYZE. One fills pg_statistic. The other executes a query. Do not mix the names in an interview.
Nodes you must recognize
Scans: Seq, Index, Index Only, Bitmap Index + Bitmap Heap (good when many matches or OR of indexes).
Joins: Nested Loop (probe inner per outer row — needs a good inner estimate), Hash (build one side), Merge (both ordered).
Misc: Sort, Gather (parallel), Limit (can make a cheap-looking startup cost win).
Nested Loop (cost=0.29..8.45 rows=1) (actual time=0.01..12.3 rows=8000 loops=1)
-> Index Scan on users rows=1 actual rows=1
-> Index Scan on orders rows=5 actual rows=8000 loops=1Planner expected 5 orders per user, got 8000. Nested loop became 8000 indexed lookups that should have been a hash join. Fix: stats, rewrite, or a better composite.
Decisions
- 1
EXPLAIN
- nextEstimates only
- 2
Estimates only
- 3
EXPLAIN ANALYZE
- nextExecutes + actual time/rows
- 4
Executes + actual time/rows
- nextPlan Rows vs Actual?
- ?
Plan Rows vs Actual?
- 10x+ANALYZE / extended stats / rewrite
- okBUFFERS: hit vs read
- 6
ANALYZE / extended stats / rewrite
- 7
BUFFERS: hit vs read
Lesson map
EXPLAIN & EXPLAIN ANALYZE
EXPLAIN shows the plan plus estimates. EXPLAIN (ANALYZE, BUFFERS) executes and shows actual time/rows plus buffer hits. Always compare Plan Rows vs Actual Rows; big mismatches mean stats or correlation problems. Never run ANALYZE on unchecked writes in prod without a transaction you can roll back.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB e["EXPLAIN ANALYZE"] est["Estimates only"] a["EXPLAIN ANALYZE"] run["Executes + actual time/rows"] e -->|EXPLAIN to| est a -->|EXPLAIN ANALYZE to Executes + actual time/rows| run
Safety
EXPLAIN ANALYZEruns triggers,nextval,random(), RLS policies.- Writes: always a transaction you roll back, or a replica / copy.
EXPLAIN ANALYZEon a 2-hour query takes 2 hours.LIMITin a wrapping CTE can change the plan — know that you are not measuring the prod shape if you wrap carelessly.
Plan math (run this)
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Interview Q&A
EXPLAIN vs EXPLAIN ANALYZE?
Answer
EXPLAIN prints the chosen plan and estimates. ANALYZE executes and adds actual time and rows. Estimates can be fiction.
cost=0.29..8.45 vs actual time=0.01..12.3?
Answer
Cost is unitless planner currency. Actual time is milliseconds. Do not compare cost to milliseconds as if they were the same.
Why multiply by loops?
Answer
Inner nested-loop nodes show per-iteration averages. Total work ≈ actual rows × loops (for that accounting). Ignoring loops under-reads the inner scan.
What does Buffers: shared hit=2 read=5000 say?
Answer
Almost all pages missed the Postgres shared buffers (read from OS/disk). Cold cache or a plan that touches too much heap. Warm it and compare; consider seq vs random.
Why is ANALYZE on UPDATE dangerous?
Answer
It runs the UPDATE. Use a transaction and ROLLBACK, or a clone. Same for DELETE/DDL-ish functions.
Nested loop vs hash vs merge?
Answer
Nested: small outer, indexed inner. Hash: large equijoin. Merge: both ordered (index or Sort). Wrong inner cardinality picks nested when hash would win.
Index Only Scan still heap fetching?
Answer
Visibility map not all-visible. BUFFERS / “Heap Fetches” in verbose. VACUUM. Covering indexes.
FORMAT JSON?
Answer
Easier to walk children in tools (Dalibo, pganalyze). Same data as text.
Does EXPLAIN use the same plan as prod?
Answer
Bind parameters, plan_cache_mode, and SET GUCs matter. EXPLAIN (GENERIC_PLAN) / actual binds. A literal WHERE id = 1 may not match a prepared id = $1.
COSTS OFF?
Answer
Hides cost for teaching node shapes. Still estimates unless ANALYZE. Not a shortcut to actuals.
Pitfalls
Take the nested-loop snippet above. Name outer scan, inner scan, why nested was chosen, what 8000 vs 5 means, and whether you ANALYZE the table or change the join. Then write the safe wrapper for EXPLAIN ANALYZE DELETE.
Go Deeper
Cluster: stats · next partial / expression