Tied to Mission: Backend Depth — the mission's success criterion is literally "reads an EXPLAIN ANALYZE plan, explains why Postgres chose the plan it did." This is that lesson, building on Lesson 1's index rules and Lesson 8's visibility map.
The query has a perfectly good index on customer_id. There's an index on every column the WHERE touches. You run EXPLAIN and Postgres says: Seq Scan on orders. The index you were so proud of is being ignored. Or the query was fast last week and is slow today, with the same plan shown in EXPLAIN — and the plan itself never changed. What does the planner actually know, and why does it keep making choices that look wrong?
Postgres doesn't "see" your table. It sees statistics about your table, and it compares the estimated cost of candidate plans, picking the cheapest. Cost is measured in arbitrary units that roughly model work: reading a page sequentially costs 1.0 (seq_page_cost), reading a page at random costs 4.0 (random_page_cost), processing a row costs 0.01 (cpu_tuple_cost), and so on. The units don't mean "milliseconds" — they mean "relative expense of this plan shape." If the estimates feeding those numbers are wrong, the winner is wrong, and no amount of index creation fixes that.
ANALYZE (run manually or by autovacuum) samples the table and writes to pg_statistic: the most common values (MCV list), a histogram of value distribution, distinct-value counts, and the fraction of NULLs. Row counts and page counts live in pg_class. The planner uses these to guess how many rows each WHERE clause matches, and that guess drives everything else — join order, scan choice, whether an index is used at all.
An index lookup is random I/O: each matching row may live in a different 8 KB page, and each page fetch is a separate seek (or, on SSD, a separate read). A sequential scan reads pages in order and can be prefetched by the OS and parallelized across cores. So the planner's decision is a threshold problem: if it estimates that a large fraction of the table matches, the sequential scan wins even though it reads "more" pages, because it reads them cheaply and in bulk. As a rule of thumb, once a filter matches more than roughly 5–10% of rows, an index usually stops being worth it.
Two consequences follow, and both are common production gotchas:
ANALYZE (bulk load, mass update), the planner is deciding on last month's numbers. ANALYZE first; the fix is often not a new index at all.WHERE org_id = $1 AND status = $2 where every org's rows are all one status — so its row estimate is off by orders of magnitude. That's what extended statistics (CREATE STATISTICS) exists for: teach the planner about the correlation it can't see.Some patterns make the index unusable by construction, independent of statistics:
WHERE function(column) = $1 — the expression isn't in the index; only WHERE column = $1 matches a B-tree entry directly.LIKE '%text' — leading wildcard can't use a left-to-right B-tree search.OR across columns — usually resolved with bitmap scans or a seq scan rather than one index.The plan is a tree; read it bottom-up. Every node reports two things that look the same but are the whole story: estimated rows (what the planner believed before running) and actual rows (what it got). The single highest-signal diagnostic act in Postgres performance work is comparing those two numbers on every node:
ANALYZE, extended statistics, or rewriting the query so the planner can reason about it.Also look at actual time per node, loops (a node with loops=1000 is costing 1000× its per-loop time), and Planning Time vs Execution Time.
Lesson 1's composite index eliminated a Sort node — a plan-shape change you can now read directly in EXPLAIN. Lesson 8's visibility map is what enables Index Only Scan: if the map says a page is all-visible, the planner can answer from the index alone and skip heap fetches entirely — which is why vacuum keeping the visibility map current shows up as query speedup, not just disk reclamation.
Seq Scan on orders (actual rows=10) for a query whose WHERE clause matches 10 rows out of 1M and has an index on the filtered column. Postgres chose the seq scan anyway and it took 500ms. Most likely why?rows with actual rows on the Seq Scan node). Correct the statistics, and the planner will pick the index on its own.
Not quite — indexes aren't "broken or working"; they're chosen or not chosen by estimated cost. When the estimate says most rows match, a sequential scan legitimately wins its cost comparison. Look for the estimate-vs-actual mismatch on the Seq Scan node — that's the signal.
Create a table with a skewed distribution (e.g. 1M rows where 999,990 share one status value and 10 have another). Run EXPLAIN ANALYZE SELECT * FROM t WHERE status = 'rare'; — read the estimated vs actual rows and the scan type. Then ANALYZE t; and rerun: the estimate should snap to reality and the plan should switch to an index scan. Then try status = 'common' (the 99.999% value) and watch the planner correctly prefer a Seq Scan even with the index in place — selectivity in action.
PostgreSQL's docs on Using EXPLAIN and Planner Statistics are the primary source for both the output format and the statistics model. Use The Index, Luke! (already in the reference list) covers the selectivity rules of thumb and index-usage conditions from the practitioner side. Keep Lesson 1's indexing cheat sheet handy while reading this.
The same cost-vs-capacity reasoning — "when everything gets slower together, it's usually a shared resource" — shows up at the network layer next: Lesson 10 is about why saturated links behave the way they do.