# Postgres: index vs. sequential scan ## Concept 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. ## Provenance 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_cost` is 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: - **§11.9 "Index-Only Scans and Covering Indexes"** for Part 5 — "it must verify that each retrieved row be 'visible' to the query's MVCC snapshot… Visibility information is not stored in index entries, only in heap entries; so at first glance it would seem that every row retrieval would require a heap access anyway. And this is indeed the case, **if the table row has been modified recently.**" - **§76.1 "Row Estimation Examples"** (Chapter 76, *How the Planner Uses Statistics*) for where `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.)* ## Prerequisites 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 estimation in page fetches** — the arithmetic behind every `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](https://www.postgresql.org/docs/16/using-explain.html#USING-EXPLAIN-BASICS) and [§20.7.2 Planner Cost Constants](https://www.postgresql.org/docs/16/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS). - **MVCC row versions and the visibility map** — Part 5 turns on the fact that an `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](https://www.postgresql.org/docs/16/indexes-index-only-scans.html) and the sibling exercise [MVCC and vacuum](mvcc-and-vacuum.md). - **Heap page layout and the correlation statistic** — Parts 4 and 6 turn on how many *pages* a set of rows occupies, which is a property of physical order, reported by `pg_stats.correlation`. [§54.22 pg_stats](https://www.postgresql.org/docs/16/view-pg-stats.html). ## Environment - **Host:** macOS 26.5.2 (build 25F84), Apple Silicon (arm64) - **Docker:** Docker Desktop, Engine 29.6.2 — Linux VM kernel `6.12.76-linuxkit`, 16 GiB allocated to the VM - **Postgres image:** `postgres:16` — PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), digest `sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20` - **Captured:** 2026-07-26 ## Mental model: four access paths, priced by the page 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. | The single most important consequence, and the one this exercise keeps returning to: **cost is counted in pages, not rows.** A query returning 5,000 rows is cheap if they share 161 pages and expensive if they're spread across 5,000. The row count is the same; the work is not. 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. > **Careful:** the `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: ``` Seq Scan on events (cost=0.00..21402.00 rows=10 width=215) <- planner's ESTIMATE (actual time=0.027..68.788 rows=10 loops=1) <- what REALLY happened | | | +- rows it actually returned +- ms to first row .. ms to last row ``` `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. ## Setup One terminal is enough for this one. ```bash docker run --name pg-index --rm -e POSTGRES_PASSWORD=postgres -d postgres:16 docker exec -it pg-index psql -U postgres ``` Build a table with three deliberately different data distributions: ```sql 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: ```sql SELECT pg_size_pretty(pg_relation_size('events')) AS heap, pg_relation_size('events')/8192 AS pages, count(*) FROM events; ``` ``` heap | pages | count --------+-------+-------- 118 MB | 15152 | 500000 ``` 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: ```sql SET max_parallel_workers_per_gather = 0; ``` Postgres would otherwise wrap several of these plans in `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. ## Steps ### Part 1 — the baseline: no index at all 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. 1. **Predict**: `user_id = 42` matches exactly 10 rows out of 500,000. With no index on `user_id`, how many pages does Postgres read, and how long does it take? 2. Run it: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 42; ``` 3. **Observe**: ``` Seq Scan on events (cost=0.00..21402.00 rows=10 width=215) (actual time=0.027..68.788 rows=10 loops=1) Filter: (user_id = 42) Rows Removed by Filter: 499990 Buffers: shared hit=14626 read=526 Planning: Buffers: shared hit=63 read=3 Planning Time: 0.186 ms Execution Time: 68.821 ms ``` `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. ### Part 2 — the plan flips 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. 4. **Predict**: add an index on `user_id` and re-run the identical query. How far does 15,152 buffers drop? 5. Run it: ```sql CREATE INDEX idx_events_user ON events (user_id); EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 42; ``` 6. **Observe**: ``` Index Scan using idx_events_user on events (cost=0.42..44.22 rows=10 width=215) (actual time=0.028..0.045 rows=10 loops=1) Index Cond: (user_id = 42) Buffers: shared hit=9 read=4 Planning: Buffers: shared hit=19 read=2 Planning Time: 0.210 ms Execution Time: 0.058 ms ``` **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. ### Part 3 — the index the planner refuses to use 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. 7. Build an index on `status` and refresh statistics: ```sql CREATE INDEX idx_events_status ON events (status); ANALYZE events; ``` 8. **Predict**: `status = 'ok'` matches 495,000 of 500,000 rows (99%). There is now an index on `status`. Will the planner use it? 9. Run it: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'ok'; ``` 10. **Observe**: ``` Seq Scan on events (cost=0.00..21402.00 rows=495733 width=215) (actual time=0.017..76.496 rows=495000 loops=1) Filter: (status = 'ok'::text) Rows Removed by Filter: 5000 Buffers: shared hit=14737 read=415 Planning: Buffers: shared hit=21 read=2 Planning Time: 0.333 ms Execution Time: 91.764 ms ``` 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.) 11. **Predict**: that looks like a missed optimization. Force the index and it should be faster — right? 12. Force it: ```sql SET enable_seqscan = off; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'ok'; RESET enable_seqscan; ``` 13. **Observe**: ``` Index Scan using idx_events_status on events (cost=0.42..26878.13 rows=495733 width=215) (actual time=0.045..91.473 rows=495000 loops=1) Index Cond: (status = 'ok'::text) Buffers: shared hit=14769 read=802 Planning Time: 0.057 ms Execution Time: 109.902 ms ``` **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. ### Part 4 — pages, not rows What this part tests: the claim from the mental model that cost is counted in pages. Two queries below return essentially the same number of rows and differ by 30× in the work they do. 14. **Predict**: `status = 'error'` matches 5,000 rows — 1%, squarely in index territory. `user_id BETWEEN 100 AND 600` matches about the same number. Should these two queries cost about the same? 15. Run both: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'error'; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id BETWEEN 100 AND 600; ``` 16. **Observe** — first the `status` query: ``` Index Scan using idx_events_status on events (cost=0.42..707.07 rows=4267 width=215) (actual time=0.044..3.174 rows=5000 loops=1) Index Cond: (status = 'error'::text) Buffers: shared hit=5003 read=4 Planning Time: 0.110 ms Execution Time: 3.361 ms ``` Then the `user_id` range: ``` Bitmap Heap Scan on events (cost=73.37..10189.77 rows=4775 width=215) (actual time=0.177..0.682 rows=5010 loops=1) Recheck Cond: ((user_id >= 100) AND (user_id <= 600)) Heap Blocks: exact=161 Buffers: shared hit=163 read=6 -> Bitmap Index Scan on idx_events_user (cost=0.00..72.17 rows=4775 width=0) (actual time=0.162..0.162 rows=5010 loops=1) Index Cond: ((user_id >= 100) AND (user_id <= 600)) Buffers: shared hit=2 read=6 Planning: Buffers: shared hit=10 Planning Time: 0.135 ms Execution Time: 0.835 ms ``` **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 — 5,000 rows scattered across 5,000 separate pages. 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. 17. **Predict**: would a Bitmap Heap Scan rescue the `status` query? 18. Force one and compare: ```sql SET enable_indexscan = off; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status = 'error'; RESET enable_indexscan; ``` 19. **Observe**: ``` Bitmap Heap Scan on events (cost=49.49..9490.25 rows=4267 width=215) (actual time=0.865..3.494 rows=5000 loops=1) Recheck Cond: (status = 'error'::text) Heap Blocks: exact=5000 Buffers: shared hit=5007 -> Bitmap Index Scan on idx_events_status (cost=0.00..48.42 rows=4267 width=0) (actual time=0.429..0.429 rows=5000 loops=1) Index Cond: (status = 'error'::text) Buffers: shared hit=7 Planning Time: 0.031 ms Execution Time: 3.651 ms ``` `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. One caveat on timings here. Run the `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 31 — 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. This is why every comparison in this exercise leans on buffer counts. 20. One bonus shape, since it only exists in bitmap form — combining two indexes on the same table: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 42 OR status = 'error'; ``` ``` Bitmap Heap Scan on events (cost=55.06..9520.74 rows=4277 width=215) (actual time=0.801..3.200 rows=5010 loops=1) Recheck Cond: ((user_id = 42) OR (status = 'error'::text)) Heap Blocks: exact=5010 Buffers: shared hit=5020 -> BitmapOr (cost=55.06..55.06 rows=4277 width=0) (actual time=0.339..0.340 rows=0 loops=1) Buffers: shared hit=10 -> Bitmap Index Scan on idx_events_user (cost=0.00..4.50 rows=10 width=0) (actual time=0.007..0.007 rows=10 loops=1) Index Cond: (user_id = 42) Buffers: shared hit=3 -> Bitmap Index Scan on idx_events_status (cost=0.00..48.42 rows=4267 width=0) (actual time=0.332..0.332 rows=5000 loops=1) Index Cond: (status = 'error'::text) Buffers: shared hit=7 Planning Time: 0.047 ms Execution Time: 3.354 ms ``` Two separate indexes scanned into two bitmaps, OR'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. ### Part 5 — the index-only scan, and the thing that silently breaks it 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](mvcc-and-vacuum.md) 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](../ddia/ch04/index-only-scan-visibility-map.md) 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.) 21. **Predict**: `count(*)` over a primary-key range needs no column except `id`, which is already in the index. Does Postgres need to touch the table at all? 22. Run it: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE id BETWEEN 1 AND 50000; ``` 23. **Observe**: ``` Aggregate (cost=1638.11..1638.12 rows=1 width=8) (actual time=7.079..7.080 rows=1 loops=1) Buffers: shared hit=4 read=136 -> Index Only Scan using events_pkey on events (cost=0.42..1516.14 rows=48786 width=0) (actual time=0.008..5.107 rows=50000 loops=1) Index Cond: ((id >= 1) AND (id <= 50000)) Heap Fetches: 0 Buffers: shared hit=4 read=136 Planning: Buffers: shared hit=4 read=3 Planning Time: 0.098 ms Execution Time: 7.097 ms ``` `Heap Fetches: 0` — 50,000 rows counted, table never opened. 140 buffers total, all of them index pages. 24. **Predict**: now update those rows in a way that changes nothing — `SET payload = payload` writes the identical value back. The query is unchanged, the data is unchanged, the index is unchanged. Does the plan still cost 140 buffers? 25. Run it: ```sql UPDATE events SET payload = payload WHERE id <= 50000; EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE id BETWEEN 1 AND 50000; ``` 26. **Observe**: ``` Aggregate (cost=1950.84..1950.85 rows=1 width=8) (actual time=51.297..51.298 rows=1 loops=1) Buffers: shared hit=100277 -> Index Only Scan using events_pkey on events (cost=0.42..1816.69 rows=53663 width=0) (actual time=0.012..48.983 rows=50000 loops=1) Index Cond: ((id >= 1) AND (id <= 50000)) Heap Fetches: 100000 Buffers: shared hit=100277 Planning: Buffers: shared hit=6 Planning Time: 1.415 ms Execution Time: 51.339 ms ``` **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. 27. **Predict**: `VACUUM` doesn't change data, statistics, the query, or the index. Does it fix a 100,277-buffer query? 28. Run it: ```sql VACUUM events; EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE id BETWEEN 1 AND 50000; ``` 29. **Observe**: ``` Aggregate (cost=1690.06..1690.07 rows=1 width=8) (actual time=5.408..5.408 rows=1 loops=1) Buffers: shared hit=277 -> Index Only Scan using events_pkey on events (cost=0.42..1568.10 rows=48784 width=0) (actual time=0.008..3.570 rows=50000 loops=1) Index Cond: ((id >= 1) AND (id <= 50000)) Heap Fetches: 0 Buffers: shared hit=277 Planning: Buffers: shared hit=23 Planning Time: 0.109 ms Execution Time: 5.427 ms ``` 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. ### Part 6 — fix the layout, not the plan 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. 30. First, what the indexes are costing you: ```sql 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; ``` ``` relname | size -------------------+--------- events | 130 MB events_pkey | 12 MB idx_events_user | 4640 kB idx_events_status | 3776 kB ``` 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. 31. Confirm the `status` query is still where Part 4 left it: ```sql EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF) SELECT * FROM events WHERE status='error'; ``` ``` Index Scan using idx_events_status on events (actual time=0.010..4.308 rows=5000 loops=1) Index Cond: (status = 'error'::text) Buffers: shared hit=5007 ``` 32. **Predict**: `CLUSTER` physically reorders the table to match an index's order. It won't change the query, the index, or the row count. What happens to those 5,007 buffers? 33. Run it: ```sql CLUSTER events USING idx_events_status; ANALYZE events; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE status='error'; ``` 34. **Observe**: ``` Index Scan using idx_events_status on events (cost=0.42..256.60 rows=4867 width=215) (actual time=0.026..0.946 rows=5000 loops=1) Index Cond: (status = 'error'::text) Buffers: shared read=164 Planning: Buffers: shared hit=14 read=3 Planning Time: 0.127 ms Execution Time: 1.160 ms ``` **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: ```sql SELECT attname, correlation FROM pg_stats WHERE tablename='events' AND attname IN ('id','user_id','status'); ``` ``` attname | correlation ---------+------------- id | 0.44234928 user_id | 0.09654511 status | 1 ``` `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.) Two caveats before reaching for this in production. `CLUSTER` takes an `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. ## What you should see 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. ## Why 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. Where the effect vanishes: shrink `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." ## Go deeper - Turn parallelism back on and re-run the 99% query as a `count(*)`: ```sql RESET max_parallel_workers_per_gather; EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF) SELECT count(*) FROM events WHERE status = 'ok'; ``` ``` Finalize Aggregate (actual time=31.787..33.314 rows=1 loops=1) -> Gather (actual time=31.662..33.310 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (actual time=30.246..30.246 rows=1 loops=3) -> Parallel Seq Scan on events (actual time=0.144..24.093 rows=165000 loops=3) Filter: (status = 'ok'::text) Rows Removed by Filter: 1667 ``` 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: ```sql 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; ``` ``` relname | size ------------------------+--------- idx_events_status | 3408 kB idx_events_err_partial | 56 kB ``` **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 30 because `CLUSTER` rebuilt it more densely.) Indexing a column where you only query one rare value is a common and expensive mistake. - Find the crossover point yourself. Set `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. - Compare column order in a composite index: build `(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. - The production version of step 30: `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. - The obvious follow-on to Part 3: the planner's decisions are only as good as its row estimates. Skew the data, skip the `ANALYZE`, and watch it pick a genuinely bad plan — the subject of [query planner statistics](query-planner-statistics.md). - The follow-on to Part 5, from the storage-layout side: [ddia/ch04/index-only-scan-visibility-map](../ddia/ch04/index-only-scan-visibility-map.md) builds a proper covering index (`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.) Further reading: [Using EXPLAIN](https://www.postgresql.org/docs/16/using-explain.html) for the plan-node reference, and [Planner Cost Constants](https://www.postgresql.org/docs/16/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS) for what the cost numbers actually mean. ## Cleanup ```bash docker stop pg-index ``` The 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).