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.
Every 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 -p 5432:5432 -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 will match the transcripts below exactly. Timings won't — those depend on your machine and what's in cache. Compare the Buffers numbers instead.
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? Click to check.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. 64.745 ms → 0.061 ms, a factor of about 1,060.
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. Three or 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? Click to check.A brand-new index on exactly the filtered column, and the planner walked straight past it. Estimated 495,250 rows against an actual 495,000 — it isn't confused, it's declining.
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'ok';
RESET enable_seqscan;Slower. 111.589 ms against 89.701 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 27039.48 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.
On this warm cache the margin is only ~24%. On a cold cache, where those random fetches are real disk seeks rather than buffer hits, the gap is far wider — which is precisely the situation random_page_cost = 4.0 is modelling.
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? Click to check.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 twice in a row and you'll see 17.445 ms then 2.979 ms — same plan, same Buffers: shared hit=5007, 6× the speed, purely because the pages were already in cache the second time. The buffer count stayed identical while the wall-clock number moved 6×.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events
WHERE user_id = 42 OR status = 'error';OR across two indexed columns? Click to check.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.
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? Click to check.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? Click to check.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? Click to check.Back to Heap Fetches: 0 and 277 buffers. 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? Click to check.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. The estimated cost fell from 847.64 to 232.30 — 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.457457. That's the trade CLUSTER makes: there is only one physical ordering, so optimizing it for one column de-optimizes it for the others.
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,060× faster. A 99% query where the planner walks past a perfectly good index, and a forced index scan that proves it right by running slower (111.589 ms vs 89.701 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 → 140 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.
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 versus 27039 in estimated cost, and the measured 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.
Turn parallelism back on (RESET max_parallel_workers_per_gather;) and re-run the 99% query as a count(*):
Note loops=3 and rows=165000 — in a parallel plan the inner numbers are per worker, so the real row count is roughly 165,000 × 3. 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'; comes out at 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. 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 the "query planner statistics" exercise still on the backlog.Sources: PostgreSQL Docs: Using EXPLAIN for the plan-node reference · Planner Cost Constants for what the cost numbers actually mean
docker stop pg-index