Reading EXPLAIN ANALYZE — Estimated vs Actual Rows.

🧭The mental model

Postgres does not know your data. Before running a query it places a BET: "this WHERE clause will match about 50 rows." From that guess it picks the strategy — index scan or seq scan, nested loop or hash join.

Cost numbers are the bet's price. Row counts are the odds. Wrong odds = wrong plan, no matter how fast your disk is.

So when reading EXPLAIN ANALYZE, read ONE thing first:

rows=<estimate>   vs   actual rows=<reality>

Example:

Nested Loop  (cost=0.42..8.71 rows=1 width=64)
             (actual time=0.03..4210 rows=48213 loops=1)

The planner bet on 1 row. Reality: 48,213. A nested loop is perfect for 1 row and catastrophic for 48k (48k separate index lookups). That RATIO — not the cost number — is your bug.

🔍The command

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

ANALYZE really executes the query. BUFFERS shows actual page reads.

Scan for the DEEPEST node where estimate and actual diverge by 100x or more — every node above it inherited that mistake.

And always multiply by loops: "rows=3 ... loops=9000" means 27,000 rows, not 3.

⚠️Where the analogy breaks (the intermediate part)

The gambler isn't guessing blindly — it has a stats table (pg_stats: histograms, most-common-values, n_distinct). Three ways that table misleads it:

1. Stale stats. After a bulk load, autovacuum can lag for hours. A 5M-row import still planned as a 10k-row table. Fix: run ANALYZE orders; yourself after the load.
2. Correlated columns. WHERE city='Baghdad' AND country='Iraq' — the planner multiplies selectivities as if the columns were independent: 0.01 × 0.01 = 0.0001. Estimate ends up ~100x too low. Fix:

CREATE STATISTICS addr_stats (dependencies) ON city, country FROM addresses;
ANALYZE addresses;

3. Expression opacity. WHERE date_trunc('month', created_at) = '2026-09-01' — there are no stats on an expression, so the planner falls back to a fixed ~0.5% guess. Fix: an expression index, or rewrite as a range (created_at >= ... AND created_at < ...), which can use the plain index AND the real histogram.

🧨What people get wrong

• Chasing the cost= number. It's in arbitrary units — only meaningful when comparing two plans of the SAME query.
• Running EXPLAIN without ANALYZE, then "optimizing" against an estimate nobody verified.
• Adding an index when the actual defect is a bad n_distinct.
• Forgetting ANALYZE executes for real — wrap EXPLAIN ANALYZE of an UPDATE/DELETE in BEGIN; ... ROLLBACK;.

🏋️Your exercise

Take your slowest endpoint's query and run EXPLAIN (ANALYZE, BUFFERS). Find the node with the worst estimate-to-actual ratio and write it down literally (e.g. "rows=1 vs actual rows=48213").

Then classify it: stale stats, correlated columns, or an expression? Run ANALYZE on that table and re-run the query — did the plan shape change, or did the bad estimate survive?