# 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.

## 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.026..64.714 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 -p 5432:5432 -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 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:

```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.026..64.714 rows=10 loops=1)
      Filter: (user_id = 42)
      Rows Removed by Filter: 499990
      Buffers: shared hit=14626 read=526
    Planning Time: 0.200 ms
    Execution Time: 64.745 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.029..0.043 rows=10 loops=1)
      Index Cond: (user_id = 42)
      Buffers: shared hit=9 read=4
    Execution Time: 0.061 ms
   ```

   **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.

### 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=495250 width=215) (actual time=0.013..75.272 rows=495000 loops=1)
       Filter: (status = 'ok'::text)
       Rows Removed by Filter: 5000
       Buffers: shared hit=14823 read=329
     Execution Time: 89.701 ms
    ```

    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.

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..27039.48 rows=495250 width=215) (actual time=0.062..96.712 rows=495000 loops=1)
       Index Cond: (status = 'ok'::text)
       Buffers: shared hit=14855 read=716
     Execution Time: 111.589 ms
    ```

    **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.

### 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..847.64 rows=4750 width=215) (actual time=0.166..17.445 rows=5000 loops=1)
       Index Cond: (status = 'error'::text)
       Buffers: shared hit=5007
     Execution Time: 17.809 ms
    ```

    Then the `user_id` range:

    ```
     Bitmap Heap Scan on events  (cost=82.05..10744.45 rows=5232 width=215) (actual time=0.231..1.061 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..80.74 rows=5232 width=0) (actual time=0.211..0.211 rows=5010 loops=1)
             Index Cond: ((user_id >= 100) AND (user_id <= 600))
     Execution Time: 1.226 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=57.23..10129.95 rows=4750 width=215) (actual time=0.948..3.157 rows=5000 loops=1)
       Recheck Cond: (status = 'error'::text)
       Heap Blocks: exact=5000
       Buffers: shared hit=5007
     Execution Time: 3.325 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 twice in a row and
    you'll see something like `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. This is why every comparison in this
    exercise leans on buffer counts: they stayed identical while the
    wall-clock number moved 6×.

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=62.92..10159.26 rows=4760 width=215) (actual time=0.975..4.727 rows=5010 loops=1)
       Recheck Cond: ((user_id = 42) OR (status = 'error'::text))
       Heap Blocks: exact=5010
       ->  BitmapOr  (cost=62.92..62.92 rows=4760 width=0) (actual time=0.494..0.494 rows=0 loops=1)
             ->  Bitmap Index Scan on idx_events_user  (cost=0.00..4.50 rows=10 width=0) (actual time=0.008..0.008 rows=10 loops=1)
                   Index Cond: (user_id = 42)
             ->  Bitmap Index Scan on idx_events_status  (cost=0.00..56.05 rows=4750 width=0) (actual time=0.486..0.486 rows=5000 loops=1)
                   Index Cond: (status = 'error'::text)
    ```

    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](postgres-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.

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=1650.75..1650.76 rows=1 width=8) (actual time=6.683..6.684 rows=1 loops=1)
       Buffers: shared hit=4 read=136
       ->  Index Only Scan using events_pkey on events  (cost=0.42..1527.82 rows=49170 width=0) (actual time=0.010..4.842 rows=50000 loops=1)
             Index Cond: ((id >= 1) AND (id <= 50000))
             Heap Fetches: 0
             Buffers: shared hit=4 read=136
     Execution Time: 6.709 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=1966.36..1966.37 rows=1 width=8) (actual time=30.510..30.511 rows=1 loops=1)
       Buffers: shared hit=100277
       ->  Index Only Scan using events_pkey on events  (cost=0.42..1831.14 rows=54086 width=0) (actual time=0.008..28.683 rows=50000 loops=1)
             Index Cond: ((id >= 1) AND (id <= 50000))
             Heap Fetches: 100000
             Buffers: shared hit=100277
     Execution Time: 30.530 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=1702.70..1702.71 rows=1 width=8) (actual time=5.287..5.288 rows=1 loops=1)
       Buffers: shared hit=277
       ->  Index Only Scan using events_pkey on events  (cost=0.42..1579.78 rows=49168 width=0) (actual time=0.006..3.496 rows=50000 loops=1)
             Index Cond: ((id >= 1) AND (id <= 50000))
             Heap Fetches: 0
     Execution Time: 5.306 ms
    ```

    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.

### 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.024..4.623 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..232.30 rows=4450 width=215) (actual time=0.021..1.741 rows=5000 loops=1)
       Index Cond: (status = 'error'::text)
       Buffers: shared read=164
     Execution Time: 1.915 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. The
    estimated cost fell from `847.64` to `232.30` — 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.457457
     user_id |  0.10149254
     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.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.

    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,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.

## 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.

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.

## Go deeper

- Turn parallelism back on (`RESET max_parallel_workers_per_gather;`) and
  re-run the 99% query as a `count(*)`:

  ```
   Finalize Aggregate (actual time=26.812..28.216 rows=1 loops=1)
     ->  Gather (actual time=26.694..28.211 rows=3 loops=1)
           Workers Planned: 2
           Workers Launched: 2
           ->  Partial Aggregate (actual time=25.172..25.172 rows=1 loops=3)
                 ->  Parallel Seq Scan on events (actual time=0.148..19.414 rows=165000 loops=3)
                       Filter: (status = 'ok'::text)
   ```

  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:

  ```sql
  CREATE INDEX idx_events_err_partial ON events (status) WHERE status = 'error';
  ```

  It 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.

- 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 Part 6a: `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 the "query planner statistics"
  exercise in [EXERCISES.md](../EXERCISES.md).

Further reading:
[Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)
for the plan-node reference, and
[Planner Cost Constants](https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS)
for what the cost numbers actually mean.

## Cleanup

```bash
docker stop pg-index
```