0
Challenge · 35Correct & Fast

EXPLAIN ANALYZE: reading a plan

Perfused in ~70% of apps

You can write a perfectly correct query and still not know why it's slow, because SQL describes WHAT you want, not HOW Postgres gets it. EXPLAIN is Postgres showing its work. The skill this concept teaches is not tuning — it's reading: given a plan you've never seen, can you say which line is expensive, which one runs first, and whether the numbers next to it mean what you assume they mean?

Core idea. EXPLAIN shows the planner's chosen strategy and its cost ESTIMATE, without running anything. EXPLAIN ANALYZE actually EXECUTES the query and adds real timings and real row counts next to the estimates — which means it is genuinely dangerous to run on an UPDATE or DELETE you don't want to actually perform. A plan prints top-down but EXECUTES bottom-up: the most-indented lines are the leaves, and they run first, feeding rows up to the nodes above them. The single most common smell is a Seq Scan with a large 'Rows Removed by Filter' on a table where you expected an index to be used.
Playground, this isn't a screenshot
printed top-down · click 1 → 4 to see the order it actually RUNS in
Nested Loop
Index Scan using profiles_pkey on profiles a
Bitmap Heap Scan on posts p
Bitmap Index Scan on posts_user_id_idx

Runs FIRST — the deepest, most-indented node. It doesn't touch table rows yet; it collects the addresses (a "bitmap") of every heap block that might contain this user_id.

cost
0.00..4.67
planner's guess: startup..total, in abstract units — not ms
rows
50 (est.)
the planner's ESTIMATE, from table statistics
actual rows
50.00
PG18 shows fractional averages; PG17 prints a plain integer
loops
1
how many times this node ran
↑ play a little first, then dig in