An index is not a switch that makes queries fast — it's one of four access paths, and the planner picks by counting pages. Watch it refuse an index and be right, then watch two queries return the same rows for 30× different cost.
An index is not a switch you flip to make a query fast. It's one of four access paths the planner can choose from, and it chooses by estimating which one touches the fewest 8 KB pages — sometimes correctly refusing to use the index you just built. This exercise makes that arithmetic visible: you'll watch a query get 1000× faster from one CREATE INDEX, then watch the planner ignore an index on purpose and prove it was right to, then watch two queries that return the same number of rows differ 30× in cost because of where those rows physically sit.
PostgreSQL 16 documentation, §14.1.1 “EXPLAIN Basics” (in §14.1 Using EXPLAIN). The claim under test is that plan choice is a cost comparison denominated in page fetches, not a rule about indexes:
“The very first line (the summary line for the topmost node) has the estimated total execution cost for the plan; it is this number that the planner seeks to minimize.”
“The costs are measured in arbitrary units determined by the planner's cost parameters… Traditional practice is to measure the costs in units of disk page fetches; that is,
seq_page_costis conventionally set to 1.0 and the other cost parameters are set relative to that.”“The estimated cost is computed as (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost).”
The same section walks the exact ladder Parts 1–4 reproduce. With WHERE unique1 < 7000 on a 10,000-row table it gets a Seq Scan despite an index being available — “the scan will still have to visit all 10000 rows, so the cost hasn't decreased” — and only when the condition is made “more restrictive” (unique1 < 100) does a Bitmap Heap Scan win, because “Fetching rows separately is much more expensive than reading them sequentially, but because not all the pages of the table have to be visited, this is still cheaper than a sequential scan.” That last clause is the whole exercise in one sentence: what you pay for is pages visited.
Two supporting sections:
rows=495733 comes from: for a value in most_common_vals, “the selectivity is merely the corresponding entry in the list of most common frequencies”, and “the estimated number of rows is just the product of this with the cardinality”. (Note: this chapter is numbered 76 in the PostgreSQL 16 docs, not 71.)Everything here is explained in-line, but three ideas carry most of the result. The first two are the ones to read if you read anything.
cost=X..Y you'll see, and the reason Part 3's planner decision is defensible rather than lucky. This is the load-bearing one. §14.1.1 EXPLAIN Basics and §20.7.2 Planner Cost Constants.UPDATE writes a new tuple and leaves the old one dead, and that an index-only scan is only index-only for heap pages marked all-visible. Also load-bearing. §11.9 Index-Only Scans and the sibling exercise MVCC and vacuum.pg_stats.correlation. §54.22 pg_stats.6.12.76-linuxkit, 16 GiB allocated to the VMpostgres:16 — PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), digest sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20Every WHERE clause is a question the planner answers by picking one of these. They are not ranked best-to-worst — each is optimal in a different selectivity band.
| Access path | What it actually does | Reach for it when... |
|---|---|---|
| Seq Scan | Reads every page of the table front to back and throws away rows that don't match. Purely sequential I/O, priced at seq_page_cost = 1.0 per page. |
The planner picks it when a large fraction of rows match — and it's also the right plan for any small table, where reading five pages beats descending a B-tree. Never "a bug to be fixed with an index". |
| Index Scan | Descends the B-tree once per matching key, then does a random heap fetch per row to get the remaining columns and check visibility. Random reads are priced at random_page_cost = 4.0 — 4× a sequential page. |
High selectivity: a handful of rows out of many. Also when you need rows in the index's order and want the planner to skip a Sort node entirely. |
| Bitmap Heap Scan | Two phases. Walk the index and build an in-memory bitmap of page numbers, then visit those pages once each in physical order. Converts random I/O back into near-sequential, and never visits a page twice. | The middle band, where an index scan would fetch the same page repeatedly. Also the only way to combine two indexes on one table — that's the BitmapOr / BitmapAnd node. |
| Index Only Scan | Answers entirely from the index, never touching the table — but only for pages the visibility map marks all-visible. Any other page costs a heap fetch anyway, reported as Heap Fetches: N. |
The index contains every column the query needs (a covering index, optionally via INCLUDE). Depends on the table being recently vacuumed, which is what Part 5 is about. |
That 4.0 vs 1.0 ratio is where the plan flips come from. It's a spinning-disk-era default — four random seeks cost what one sequential read does — and it's why the planner abandons an index somewhere around 5–10% selectivity. On SSDs, where random reads are nearly free, the conventional tuning is random_page_cost = 1.1, which moves that crossover a long way.
ANALYZE in EXPLAIN ANALYZE means "actually run the query and report real timings". The standalone ANALYZE command is a completely different thing — it collects the column statistics the planner estimates from. Same word, unrelated jobs. Both appear below.
Reading a plan node, since every step depends on it:
cost is in arbitrary units (page fetches, scaled) — only useful compared against another plan for the same query. rows appears twice on purpose: estimated, then actual. A large gap between them is the root cause of most bad plans. And Buffers — which you only get by asking for it — is the honest measure of work, because unlike wall-clock time it doesn't change depending on what's already cached.
One terminal is enough for this one.
docker run --name pg-index --rm -e POSTGRES_PASSWORD=postgres -d postgres:16
docker exec -it pg-index psql -U postgresBuild a table with three deliberately different data distributions:
CREATE TABLE events (
id int PRIMARY KEY,
user_id int,
status text,
payload text
);
INSERT INTO events
SELECT g, g % 50000,
CASE WHEN g % 100 = 0 THEN 'error' ELSE 'ok' END,
repeat('x', 200)
FROM generate_series(1, 500000) g;
VACUUM ANALYZE events;Everything is deterministic (no random()), so your row counts and buffer counts will match the transcripts below. Two things won't: timings, which depend on your machine and what's in cache, and the planner's rows= estimates for status, which come from a randomized ANALYZE sample and move a little on every re-run. Compare the Buffers numbers.
Three distributions, one per part:
user_id = g % 50000 — 50,000 distinct values, exactly 10 rows each.status — 1% 'error', 99% 'ok', with the error rows spread evenly every 100th row.id — the primary key, perfectly in physical order.Confirm the shape:
SELECT pg_size_pretty(pg_relation_size('events')) AS heap,
pg_relation_size('events')/8192 AS pages, count(*) FROM events;15,152 pages is the number to keep in mind — it's what a full table scan costs, and several plans below come in just under or just over it.
Finally, turn off parallel query for the rest of the exercise:
SET max_parallel_workers_per_gather = 0;Gather nodes and split the work across worker processes, which is real and useful but makes the plan shapes harder to read. You'll turn it back on in Go deeper and see what it looks like.
What this part tests: what it costs to answer a needle-in-a-haystack query with only a Seq Scan available. Per the mental model, that means reading all 15,152 pages regardless of how few rows match.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 42;user_id = 42 matches exactly 10 rows out of 500,000. With no index on user_id, how many pages does Postgres read?Rows Removed by Filter: 499990 is the whole story — Postgres examined every row and discarded 99.998% of them. Buffers: shared hit=14626 read=526 sums to 15,152: precisely the whole table, exactly as predicted by the page count from setup.
Note that rows=10 in the estimate matches rows=10 in the actual. The planner knew perfectly well only 10 rows would match — it just had no faster way to find them.
What this part tests: the straightforward case for an index, where selectivity is extreme (10 rows in 500,000) and the B-tree wins by orders of magnitude.
CREATE INDEX idx_events_user ON events (user_id);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 42;15,152 buffers → 13, a factor of about 1,170. (Wall clock moved 68.821 ms → 0.058 ms here, but that ratio is illustrative — it is partly a cache-warmth artifact. The buffer ratio is the honest one.)
Read the plan for why, not just how much: Filter became Index Cond. That's the meaningful change. A Filter is applied to rows already fetched — you paid for them before discarding them. An Index Cond is used to decide which rows to fetch at all. Four pages of B-tree descent, then nine heap pages for the matching rows.
This is the case everyone has in mind when they say "add an index". The next three parts are the cases they don't.
What this part tests: the low-selectivity end of the band. Per the mental model, an index scan pays random_page_cost per row fetched, so once enough rows match, reading the table straight through is cheaper — and the planner knows it.
CREATE INDEX idx_events_status ON events (status);
ANALYZE events;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'ok';status = 'ok' matches 495,000 of 500,000 rows. There is now an index on status. Will the planner use it?A brand-new index on exactly the filtered column, and the planner walked straight past it. Estimated 495,733 rows against an actual 495,000 — it isn't confused, it's declining. (That estimate is a sampled one; expect it to land a few hundred rows either side of 495,000 on your run.)
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'ok';
RESET enable_seqscan;Slower. 109.902 ms against 91.764 ms, and 15,571 buffers against 15,152 — it read the entire table plus the entire index. The planner was right, and the estimated costs show it had the ranking correct before running anything: 21402.00 for the Seq Scan versus 26878.13 for the Index Scan.
Note enable_seqscan = off doesn't actually forbid a Seq Scan; it just adds a huge penalty to its cost so other plans win. It's a diagnostic tool for exactly this question — "what would the planner have done otherwise, and how much worse is it?" — not a setting to leave on.
Both plans here ran almost entirely out of shared buffers, which is why the measured margin is only ~20% while the estimated margin is ~26%. random_page_cost = 4.0 is modelling a cold cache, where those extra random fetches would be real disk seeks; this exercise doesn't measure that case, it just shows the ranking surviving even when the cache flatters the index.
What this part tests: the claim from the mental model that cost is counted in pages. The two queries below return essentially the same number of rows and differ by 30× in the work they do.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'error';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id BETWEEN 100 AND 600;First the status query:
Then the user_id range:
5,000 rows for 5,007 buffers, versus 5,010 rows for 169. Same row count, 30× the work — and a different plan shape to match.
Heap Blocks: exact=161 is the number that explains it:
The user_id range rows are packed about 31 to a page, because user_id = g % 50000 puts ids 100–600 in contiguous runs. The 'error' rows are every 100th row, and with ~33 rows per page that means almost exactly one error row per page.
So the second query touches 161 pages to return the same data the first needs 5,000 for. Nothing about indexes explains this. It's physical layout.
SET enable_indexscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'error';
RESET enable_indexscan;user_id query got a Bitmap Heap Scan and was 30× cheaper. Force one for status too — does it help?Heap Blocks: exact=5000, Buffers: shared hit=5007 — identical to the index scan. No plan can help, because the rows genuinely live on 5,000 distinct pages and every one of them has to be visited. A bitmap scan removes duplicate page visits and reorders them; when there are no duplicates to remove, there is nothing to win.
That's worth sitting with: the fix for this query isn't a better plan, it's a better layout. Part 6 does exactly that.
status query three times and the buffer count is the only thing that holds still. In this session the same Index Scan node reported Execution Time: 3.361 ms, then 2.522 ms on an immediate repeat, then 4.308 ms again at Step 12 — while Buffers was 5,007 every single time (hit=5003 read=4 on the first, hit=5007 after). Those millisecond figures are illustrative; the 5,007 is not.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events
WHERE user_id = 42 OR status = 'error';OR across two indexed columns?Two separate indexes scanned into two bitmaps, BitmapOr'd together, then a single pass over the heap. A plain Index Scan can only ever use one index per table — this is the mechanism that lifts that restriction, and the reason "one index per column" is sometimes a reasonable design instead of one composite index per query.
What this part tests: the fourth access path, and its dependency on the visibility map — the same map VACUUM maintains in the MVCC and vacuum exercise. That exercise showed vacuum unlocking this plan. This one shows the same plan staying in place and quietly getting 700× more expensive.
(Run ddia/ch04/index-only-scan-visibility-map alongside it for the third case: a covering index, built correctly, on a table that has never been vacuumed — where the index-only scan is chosen and reads the heap from the very first query, 1,637 blocks that one VACUUM takes to zero. Same map, three directions: never enabled, enabled, revoked.)
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE id BETWEEN 1 AND 50000;id, which is already in the primary key index. Does Postgres touch the table at all?Heap Fetches: 0 — 50,000 rows counted, table never opened. 140 buffers total, all of them index pages.
UPDATE events SET payload = payload WHERE id <= 50000;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE id BETWEEN 1 AND 50000;SET payload = payload writes the identical value back. Query unchanged, data unchanged, index unchanged. Does it still cost 140 buffers?Still an Index Only Scan. Heap Fetches: 0 → 100000. 140 buffers → 100,277.
This is the trap. The plan node did not change, so nothing in EXPLAIN without BUFFERS would look wrong — you'd still see the "good" plan while the query ran 700× more I/O. An index-only scan is only index-only for pages the visibility map marks all-visible. The UPDATE cleared that bit for every page it touched, and each row then required a heap visit to determine whether it was visible.
And 100000 fetches for 50000 rows, because per MVCC an UPDATE writes a new row version and leaves the old one dead. The index now holds entries for both, and each has to be checked.
VACUUM events;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE id BETWEEN 1 AND 50000;VACUUM changes no data, no statistics, no query, no index. Does it fix a 100,277-buffer query?Back to Heap Fetches: 0 and 277 buffers — 362× cheaper than a moment ago, though not quite back to the original 140: vacuum reclaimed the dead index entries, but the extra leaf pages the UPDATE made events_pkey allocate across that range are still there, now half-empty.
Vacuum set the all-visible bits again and the plan went back to doing what its name says. This is the concrete answer to "why does my query get slower over time when nothing changed" — nothing in the query did.
What this part tests: Part 4's conclusion that the status query is limited by physical layout rather than by plan choice. If that's true, then rearranging the rows should fix it without touching the query or the index.
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('events', 'events_pkey', 'idx_events_user', 'idx_events_status')
ORDER BY pg_relation_size(oid) DESC;About 20 MB of index for 130 MB of table — and every one of those has to be updated on every write. Indexes are not free; they're a read optimization paid for on the write path.
First confirm the status query is still where Part 4 left it:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF) SELECT * FROM events WHERE status='error';Then reorder the table to match that index and re-measure:
CLUSTER events USING idx_events_status;
ANALYZE events;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status='error';CLUSTER reorders the table to match an index's order. It changes no query, no index, no row count. What happens to those 5,007 buffers?5,007 buffers → 164. Same query, same index, same plan node, same 5,000 rows. The 5,000 error rows are now contiguous, so they occupy 164 pages instead of being sprinkled one-per-page across 5,000. Note the buffers are all read, not hit — CLUSTER rewrote the table into a brand-new relfilenode, so none of those pages were in shared buffers yet.
The estimated cost fell from 707.07 to 256.60 — the planner can see it too, via the correlation statistic:
SELECT attname, correlation FROM pg_stats
WHERE tablename='events' AND attname IN ('id', 'user_id', 'status');status is now perfectly correlated with physical order (1), and id — previously a perfect 1, since rows were inserted in id order — has fallen to 0.44234928. That's the trade CLUSTER makes: there is only one physical ordering, so optimizing it for one column de-optimizes it for the others. (These two numbers come from a sampled ANALYZE; the 1 for status is exact, the others will wobble.)
ACCESS EXCLUSIVE lock and rewrites the whole table, exactly like VACUUM FULL — every reader and writer blocks for the duration. And it is a one-time operation, not a maintained property: subsequent inserts and updates land wherever there's room, and the correlation decays back down. pg_repack does the same reordering online if you need it.
A needle-in-a-haystack query reading all 15,152 pages of the table, then 13 pages after one CREATE INDEX — about 1,170× fewer buffers. A 99% query where the planner walks past a perfectly good index, and a forced index scan that proves it right by reading 15,571 buffers instead of 15,152 and running slower (109.902 ms vs 91.764 ms). Two 1%-selectivity queries returning ~5,000 rows each that differ 30× in buffers (5,007 vs 169) purely because of where their rows sit. An index-only scan whose plan node never changes while its cost goes 140 → 100,277 → 277 buffers across an UPDATE and a VACUUM. And a CLUSTER that fixes the 5,007-buffer query down to 164 without touching the query at all.
Because the planner is not choosing between "fast" and "slow", it's running an arithmetic comparison of estimated page fetches, priced by seq_page_cost = 1.0 and random_page_cost = 4.0. Every result above falls out of that one calculation.
The Seq Scan cost is the one the docs spell out: (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost), which for this table is 15152 * 1.0 + 500000 * 0.01 = 20152, plus 500000 * 0.0025 of cpu_operator_cost for evaluating the one WHERE clause per row — exactly the 21402.00 printed in Parts 1 and 3, and identical in both because a Seq Scan's cost does not depend on how many rows survive the filter.
At 10 rows out of 500,000, a few random fetches at 4.0 each obviously beat 15,152 sequential pages at 1.0 — so the index wins by three orders of magnitude. At 495,000 rows out of 500,000, the index scan still has to visit essentially every page, only now in random order and after also reading the whole index; 21402.00 versus 26878.13 in estimated cost, and the measured buffers (15,152 vs 15,571) and times ranked the same way. There is no threshold in the code that says "stop using indexes above 10%" — the crossover is wherever those two sums happen to cross, which is why it moves when you change random_page_cost.
Part 4's 30× gap is the same formula with the page count as the variable rather than the price. Selectivity tells you how many rows match; correlation between the index order and physical order tells you how many pages those rows occupy, and only the second one is what you pay for. That's why a bitmap scan couldn't rescue the status query — its advantage is de-duplicating and reordering page visits, and there were 5,000 distinct pages either way. It's also why CLUSTER could: it changed the input to the calculation instead of the calculation.
And Part 5 is the reminder that the plan node is not the whole story. An Index Only Scan is a promise the planner makes conditionally, on the visibility map being current. When it isn't, the promise silently degrades into a heap fetch per row while the plan output looks exactly the same — visible only in Heap Fetches and Buffers, which is the argument for always running EXPLAIN with BUFFERS on.
events to a few hundred rows and every one of these gaps disappears. Once the whole table fits in a handful of pages, "read all of it" and "read the two pages you need" cost the same, and the planner will pick a Seq Scan whatever you index — the docs say so directly in §14.1.3: "on a table that only occupies one disk page, you'll nearly always get a sequential scan plan whether indexes are available or not."
Turn parallelism back on and re-run the 99% query as a count(*):
RESET max_parallel_workers_per_gather;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF) SELECT count(*) FROM events WHERE status = 'ok';Note loops=3 and rows=165000 — in a parallel plan the inner numbers are per worker, so the real row count is 165,000 × 3 = 495,000. Misreading that is a classic way to conclude a plan is broken when it isn't.
Build a partial index and compare sizes:
CREATE INDEX idx_events_err_partial ON events (status) WHERE status = 'error';
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class
WHERE relname IN ('idx_events_status', 'idx_events_err_partial')
ORDER BY pg_relation_size(oid) DESC;56 kB against 3408 kB for the full index on the same column — 60× smaller, because it only indexes the 1% of rows anyone ever searches for. (idx_events_status is 3408 kB rather than the 3776 kB from Step 11 because CLUSTER rebuilt it more densely.) Indexing a column where you only query one rare value is a common and expensive mistake.
random_page_cost = 1.1 (the usual SSD tuning) and binary-search the predicate user_id < N to find the N where the plan flips from Bitmap Heap Scan to Seq Scan. Then set it back to 4 and find it again. The gap between those two answers is how much this one setting is worth.(status, user_id) and (user_id, status) and see which queries can use which. A B-tree can only use a prefix of its columns for an Index Cond, so WHERE user_id = 42 cannot use the first index — the leading column is the one that matters most.SELECT relname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan; — indexes with idx_scan = 0 have never been used for a lookup and are pure write-path overhead.ANALYZE, and watch it pick a genuinely bad plan — the subject of query planner statistics.INCLUDE (amount)) and finds it buys nothing at all until the table is vacuumed — the mirror image of Part 5, where a working index-only scan is broken by an UPDATE that changes no data. Predict, before running either, whether Heap Fetches is a property of the index or of the table. (The table. The index never knows what's visible to you; the visibility map is heap metadata, and that is the whole reason Postgres's heap-file design puts this asterisk on covering indexes that a clustered-index engine doesn't have.)Sources: PostgreSQL 16 Docs §14.1: Using EXPLAIN for the plan-node reference · §20.7.2 Planner Cost Constants for what the cost numbers actually mean · §11.9 Index-Only Scans and Covering Indexes
docker stop pg-indexThe container was started with --rm, so stopping it removes the container and its anonymous data volume. Nothing else was created — no named volumes, no networks, no host ports published. Confirm with docker ps -a --filter name=pg-index (should print only the header).