Reading a Query Plan Like a Senior

You write SELECT. Postgres doesn't just execute it top to bottom. It hands the query to a planner that considers different ways to fetch the data, estimates the cost of each, and picks the cheapest. The plan it chooses is everything: the same query can be milliseconds or minutes depending on the plan, and the plan depends on your indexes, your data, and Postgres's statistics about that data. This is the most leveraged skill in backend work, and it's very learnable.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows the plan the planner would use, with its cost estimates, without running the query. EXPLAIN ANALYZE actually runs the query and shows the plan plus real timings and real row counts.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

You read a plan from the inside out and bottom up: the most-indented nodes run first, feeding their rows up to their parents. Each node says what it did, how many rows it expected, how many it actually got, and how long it took.

ANALYZE runs the query for real

EXPLAIN ANALYZE executes the statement. On a SELECT that's fine. On an UPDATE or DELETE it will actually modify data, so wrap those in a transaction you roll back (BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;) when you just want the plan.

Scan types, best to worst

The bottom of most plans is a scan: how Postgres reads rows from a table.

Index-Only Scananswered from the index alone, never touches the table

Index Scanuse the index to find rows, then fetch from the heap

Bitmap Index Scanlike index scan but batches heap reads · good for many matches

Sequential Scanread the whole table · fine if small, a red flag if huge

From cheapest to most expensive way to read the rows a query needs.

A sequential scan is not always bad. If you're reading most of the table, or the table is tiny, scanning is genuinely cheaper than bouncing through an index. The red flag is a sequential scan on a large table for a query that should match only a few rows. That's a missing or unused index.

The exact same lookup with and without an index is the whole story in miniature. Run this and watch the cost collapse when the index is present.

Sequential scan vs index lookuprun · edit · saved to you

Loading editor…

Join types

When a query touches more than one table, the planner picks how to join them, and the choice depends on how big each side is.

Nested Loop

For each row in the outer table, look up matching rows in the inner one (ideally via an index). Great when the outer side is small. Catastrophic when the planner thinks the outer side is small but it's actually huge, because the lookup runs once per outer row.

Hash Join

Build a hash table from one side, then probe it with the other. Good for joining two medium-to-large sets with no useful index on the join key. Needs memory for the hash table.

There's a third, the Merge Join: if both sides are already sorted on the join key, walk them together like a zipper. Good for large, pre-sorted joins. You don't choose the join type directly; you influence it by providing the right indexes and keeping statistics accurate so the planner estimates sizes correctly.

The number that reveals everything: estimated vs actual rows

This is the heart of reading a plan. Every node shows the planner's estimated row count and, under ANALYZE, the actual count. When they roughly match, the planner is well-informed and its choices are sound. When they diverge wildly, the planner is flying blind and probably picked a bad plan.

Estimated 10, actual 500000 — the classic

A huge gap means stale or coarse statistics. The planner expected a handful of rows, chose a nested loop on that assumption, and is now running an inner lookup half a million times. The query crawls. The fix is almost always to refresh the planner's knowledge: run ANALYZE on the table to update statistics, raise default_statistics_target for a skewed column, or use CREATE STATISTICS when two columns are correlated in a way the planner can't see. You're not rewriting the query; you're fixing what the planner believes.

This is why "I added the index but it's still slow" happens: the index exists, but the planner's bad row estimate made it choose not to use it, or to use it in the wrong join. Look at estimate-vs-actual before you blame the index.

A repeatable workflow

You don't need to memorise every plan node. You need a loop.

Reproduce with EXPLAIN ANALYZE
Run the real query (rolled back if it writes) and read the plan inside-out.
Find the expensive node
Look for the node consuming the most time, a sequential scan on a large table, or a row-estimate that's wildly off from actual.
Form one hypothesis
Missing index? Stale statistics? A join doing too much because of a bad estimate? Change exactly one thing.
Apply and re-run
Add the index, run ANALYZE, or rewrite the predicate. Re-run EXPLAIN ANALYZE and compare. Keep what helped, revert what didn't.

The discipline is changing one thing at a time and letting the plan tell you whether it worked, instead of sprinkling indexes and hoping. The plan is ground truth; your intuition is a hypothesis.

Common findings and their fixes

Seq scan on a big table for a selective query → add the index the WHERE clause wants, with columns in the right order.
Index exists but isn't used → usually bad statistics (ANALYZE), or the query isn't sargable (a function wrapped around the column, like WHERE lower(email) = ..., defeats a plain index; index the expression instead).
A filter applied after the join instead of before → restructure so the database filters early and joins fewer rows.
Sort or hash spilling to disk (you'll see it in the plan) → the working set exceeds work_mem; the query needs less data or more memory.

The one idea to take away

Postgres plans before it runs, and the plan is readable. Read it inside-out, find the expensive node, and check estimated-vs-actual rows first, because a bad estimate is behind most bad plans. Then change one thing (an index, a statistics refresh, a rewrite) and re-run. This loop, not memorising node types, is what turns a slow query fast and is the single most valuable database skill you can build.

Test yourself

Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.

QWhat's the difference between EXPLAIN and EXPLAIN ANALYZE?+

EXPLAIN shows the plan and the planner's cost estimates without running the query. EXPLAIN ANALYZE actually executes it and adds real timings and real row counts per node. Use ANALYZE to see what truly happened, but wrap writes in a transaction you roll back, since it really does run the statement.

QRank the scan types and say when a sequential scan is fine.+

Index-only scan (best, answered from the index alone), index scan (index then heap fetch), bitmap index scan (batched heap reads for many matches), sequential scan (read the whole table). A seq scan is fine when you're reading most of the table or it's small; it's a red flag only on a large table for a query that should match few rows.

QA plan shows 'estimated rows 10, actual rows 500000'. What does that tell you?+

The planner's statistics are stale or coarse, so it made a decision (often a nested loop) based on a wrong assumption and is now doing far more work than it should. Fix the planner's knowledge: run ANALYZE, raise default_statistics_target for a skewed column, or add CREATE STATISTICS for correlated columns. The query usually doesn't need rewriting; the statistics do.

QWhen is a nested loop join dangerous?+

When the planner thinks the outer side is small but it's actually large. A nested loop runs the inner lookup once per outer row, so a bad size estimate turns it into hundreds of thousands of lookups. It's great when the outer side really is small and the inner side is indexed; the danger is the estimate being wrong.

QYou added an index but the query is still slow and ignores it. Why might that be?+

Often a bad row estimate makes the planner choose not to use the index, so run ANALYZE first. Or the query isn't sargable: a function wrapped around the column (WHERE lower(email) = ...) prevents a plain index from matching, so index the expression instead. Check estimate-vs-actual and sargability before assuming the index is wrong.

QHow do you read a query plan's structure?+

Inside-out and bottom-up: the most-indented nodes execute first and feed rows up to their parents. Each node reports what it did, estimated vs actual rows, and time. You trace from the leaf scans up through joins and aggregates to the top, finding where the time and the row-count surprises are.

QWhat's a repeatable workflow for fixing a slow query?+

Reproduce it with EXPLAIN ANALYZE, find the most expensive node or the biggest estimate-vs-actual gap, form one hypothesis (missing index, stale stats, bad join), change exactly that one thing, and re-run to compare. Change one variable at a time and let the plan confirm the fix, rather than guessing with many changes at once.