EXPLAIN Fluency: Reading Query Plans

EXPLAIN ANALYZE runs your query for real and shows the plan with actual timings — it's the single most leveraged debugging tool in Postgres. You don't need to master the whole plan tree; you need to recognize three shapes.

The scan shapes

• Seq Scan — reads every row. Fine for small tables or queries matching most of the table; a red flag on a filtered query over a big table.
• Index Scan — uses the index to find rows, then fetches them from the heap. Fast for selective predicates.
• Index Only Scan — the index itself answers the query; never touches the heap. The fastest read Postgres offers.
• Bitmap Heap Scan — a middle ground: the index finds many candidate rows, and they're fetched in physical order (cheaper than random access when the match set is large-ish).

Join strategies

• Nested Loop — for each outer row, probe the inner table (usually via index). Great when the outer side is small.
• Hash Join — build a hash of the smaller side, probe with the larger. The default for big joins on equality.
• Merge Join — both sides sorted, walk them in lockstep. Used when inputs are already ordered (e.g., by index).

What to look for first

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

1. Rows estimated vs actual — rows=100 estimated, rows=180000 actual means the planner is flying blind (stale stats, correlated columns). A bad estimate poisons every decision downstream.
2. Execution time and the hot node — find the node eating the time; the plan tree reads bottom-up.
3. Buffers hit vs read — shared hit is cache, read is disk. A "fast" query reading from disk is one big table away from being slow.

::exercise(read-the-plan)

::lab(explain-yourself)

Field notes

• EXPLAIN without ANALYZE shows the estimated plan — enough to check the shape without touching the table.
• auto_explain with log_min_duration captures plans for slow queries in production — set it up before you need it.
• work_mem too small forces hash joins to spill to disk (Batches: 8 in the plan) — memory tuning often fixes "slow join" instantly.