Everyone’s first fix for a slow query is “add an index,” and it usually works, right up until it doesn’t. What that reflex hides is that Postgres has more than one way to actually reach the rows you asked for, and the index only helps in some of them. I built the three ways on a single 5,000,000-row table this week and watched the identical predicate get 600x faster, then do nothing at all, then get slower than the full table scan it was supposed to beat.

The problem

When you run SELECT ... WHERE user_id = 500001, Postgres has to decide how to find those rows, and it has three real options. It can read the whole table and throw away everything that doesn’t match (a Seq Scan). It can walk a B-tree index to find the matching row locations and then fetch each row from the heap (an Index Scan). Or, if every column you asked for lives in the index itself, it can answer from the index alone and never touch the heap (an Index Only Scan). Those three do wildly different amounts of work for the same result, and which one you get depends on whether the right index exists, whether the index covers your columns, whether you’ve vacuumed recently, and how many rows your predicate actually matches. Get the wrong one and a query that should touch eight disk pages touches forty thousand.

Point lookup: one user_id, 5 rows out of 5,000,000

Median latency (ms)

Seq Scan134.0

Index Scan0.22

Buffers touched

Seq Scan41,667

Index Scan8

Median over 100 reps, cache warm. The Seq Scan reads all 41,667 pages of the table to return 5 rows; the Index Scan touches 8. Measured on PostgreSQL 16.14, results in benchmarks/postgres-scan-methods/results/.

What I built

One table, events, five million rows, seeded deterministically so anyone can reproduce it (no random(), everything derived from id arithmetic). About a million distinct user_id values, roughly five rows each. A status column that’s id % 5, and a bucket column that’s id % 1000, which I’ll use later to dial selectivity. On disk the whole thing is 616 MB, of which the heap is 326 MB. Every query was warmed once and then timed steady-state, parallelism and JIT turned off, so the numbers isolate the scan method and not the core count or the JIT warming up.

CREATE TABLE events (
  id          bigint PRIMARY KEY,
  user_id     bigint NOT NULL,
  status      smallint NOT NULL,
  amount      integer NOT NULL,
  created_at  timestamptz NOT NULL,
  bucket      int NOT NULL
);

Seq Scan vs Index Scan

Start with no index on user_id and ask for one user. Five rows come back, and Postgres reads the entire table to find them:

Seq Scan on events  (cost=0.00..104167.50 rows=6 width=34) (actual time=14.391..131.850 rows=5 loops=1)
  Filter: (user_id = 500001)
  Rows Removed by Filter: 4999995
  Buffers: shared hit=12401 read=29266
  I/O Timings: shared read=16.661
Execution Time: 131.865 ms

Rows Removed by Filter: 4999995 is the whole story. It looked at every row, kept five, threw away the other 4,999,995, and touched 41,667 buffers doing it. Median over 100 runs was 134.0 ms. Now add the index and ask the exact same question:

Index Scan using idx_events_user_id on events  (cost=0.43..24.52 rows=5 width=34) (actual time=0.010..0.013 rows=5 loops=1)
  Index Cond: (user_id = 500001)
  Buffers: shared hit=3 read=5
  I/O Timings: shared read=0.008
Execution Time: 0.021 ms

Eight buffers, 0.22 ms median, no rows removed by filter because the index went straight to the five that matched. That’s 600x on the median latency for changing nothing but whether the index exists. This is the case the “just add an index” reflex was built for, and when your predicate is this selective the reflex is exactly right.

The covering index that did nothing until I vacuumed

Here’s where it stops being obvious. The Index Scan above was fast, but it still did two steps: walk the index to find where the rows are, then go to the heap to actually read them. If the only column you need is already in the index, that second step is pure waste, and Postgres can skip it with an Index Only Scan. So I asked for just the id over a range of a thousand users, 5,005 rows, first with the plain (user_id) index:

Index Scan using idx_events_user_id on events  (actual time=0.006..1.889 rows=5005 loops=1)
  Index Cond: ((user_id >= 500000) AND (user_id <= 501000))
  Buffers: shared hit=5015

Then I built a covering index, (user_id) INCLUDE (id), so the index carries the id too. Same query. I expected the heap fetches to vanish. They didn’t:

Index Only Scan using idx_events_user_id_covering on events  (actual time=0.013..2.285 rows=5005 loops=1)
  Index Cond: ((user_id >= 500000) AND (user_id <= 501000))
  Heap Fetches: 5005
  Buffers: shared hit=5005 read=22

It says Index Only Scan right there in the plan, and it still went to the heap 5,005 times, once per row, touching more buffers than the plain index it was supposed to improve on. The reason is the visibility map. Postgres can only trust the index copy of a row if it knows that row is visible to every transaction, and it tracks that per page in the visibility map, which only gets populated by VACUUM. On a freshly loaded table the map is empty, so an “index only” scan has to check the heap for every single row to confirm visibility. It isn’t really index-only until the visibility map says those pages are safe to trust, and only VACUUM sets that. One command later:

Index Only Scan using idx_events_user_id_covering on events  (actual time=0.007..0.345 rows=5005 loops=1)
  Index Cond: ((user_id >= 500000) AND (user_id <= 501000))
  Heap Fetches: 0
  Buffers: shared hit=2024

Heap Fetches: 0. Buffers touched dropped from 5,015 to 2,024, and the only thing that changed between the two Index Only Scans was a VACUUM events.

Covering query: SELECT id WHERE user_id BETWEEN a AND b, 5,005 rows

Index Scan (plain)5,015

Index Only, before VACUUM5,027

Index Only, after VACUUM2,024

Buffers touched. The plain Index Scan hits the heap once per row. The covering index calls itself an Index Only Scan but does 5,005 heap fetches until VACUUM sets the visibility map, after which heap fetches go to 0 and buffers halve. The before-VACUUM plan is kept under results/attempts/. Measured on PostgreSQL 16.14, results in benchmarks/postgres-scan-methods/results/.

When the index loses to a full scan

The point lookup made the index look like a free 600x. It isn’t, and the reason is that an Index Scan pays a random heap access for every row it returns, while a Seq Scan reads the table sequentially and only pays once for the whole thing. When you’re fetching five rows, random access is nothing. When you’re fetching a million, all those random fetches add up to more work than just reading the table start to finish. So I indexed bucket and swept a range predicate, SELECT sum(amount) FROM events WHERE bucket < n, from 0.1% of the table up to 90%, and at each point I forced a pure Index Scan and forced a Seq Scan and timed both.

Index Scan vs Seq Scan as the predicate widens (log-log)

forced Index Scan forced Seq Scan planner's own pick

Median ms over 20 reps at each selectivity. The Index Scan wins up to 10% (109ms vs 159ms) and loses by 20% (221ms vs 166ms); the Seq Scan is nearly flat because it reads the whole table regardless. Measured on PostgreSQL 16.14, results in benchmarks/postgres-scan-methods/results/.

The Seq Scan line is almost flat, hovering around 150ms whether the predicate matches 5,000 rows or 4.5 million, because it does the same full-table read either way. The Index Scan starts a hundred times faster and climbs with a slope that never lets up: 109ms at 10% selectivity, 221ms at 20%, and a brutal 1,529ms at 90%, more than five times slower than just scanning the whole table. Somewhere around 15% the two lines cross, and past that the index is a liability.

The interesting part is that the planner never actually walks into that trap. Left to its own choice it never picked the pure Index Scan for these ranges at all. Below 90% it reached for a Bitmap Heap Scan, which builds a bitmap of matching heap pages from the index first and then reads those pages in physical order, turning the index’s random heap access back into something sequential:

Bitmap Heap Scan on events  (actual time=7.576..35.663 rows=500000 loops=1)
  Recheck Cond: (bucket < 100)
  Heap Blocks: exact=10000
  ->  Bitmap Index Scan on idx_events_bucket  (actual time=6.860..6.860 rows=500000 loops=1)
        Index Cond: (bucket < 100)

That green dashed line stays below both the pure index and the seq scan across the whole low and mid range, and only at 90% does the planner give up on the index entirely and switch to a plain Seq Scan. The planner isn’t blundering into that crossover, it’s choosing sides every time from the row estimates ANALYZE gave it. Which is also why stale statistics hurt so much: if the planner thinks a predicate matches 5,000 rows when it really matches 5 million, it’ll pick the index scan that the chart above says costs you 1.5 seconds.

The takeaway

There are three ways Postgres reaches a row, and the index is only the fastest one in a specific window. A Seq Scan costs the same no matter how selective your predicate is; an Index Scan is nearly free when you’re fetching a handful of rows and turns punishing as that fraction grows; an Index Only Scan is the cheapest of all but only once VACUUM has set the visibility map, and a covering index is doing nothing for you until then. Read your EXPLAIN (ANALYZE, BUFFERS) output for the parts that don’t lie about work done: Rows Removed by Filter, Heap Fetches, and Buffers touched. Those three lines tell you which of the three scans you actually got, and whether the index you added is helping or quietly costing you a full table scan and then some. Keep ANALYZE current so the planner’s estimate matches reality, and don’t assume that because the plan says “Index Only Scan,” it’s actually staying out of the heap.

The full harness, the deterministic seed, and every EXPLAIN plan including the ones I kept as attempts are on GitHub. These are laptop numbers meant to show the mechanics of each scan method, not capacity-planning figures for your production box.