Index-Only Scans & the Visibility Map.

🐛The symptom

Dashboard query, 400ms p95. There IS an index. EXPLAIN says it's used. Nobody can see the problem.

SELECT user_id, status
FROM orders
WHERE tenant_id = 42
  AND created_at > now() - interval '30 days';

CREATE INDEX ON orders (tenant_id, created_at);

🔍Step 1 — read the plan, not the index list

Index Scan using orders_tenant_created_idx  (rows=48213)
  Buffers: shared hit=112 read=46901

112 index pages... and ~47k other page reads. Where from?

The index stores only (tenant_id, created_at, ctid). Your SELECT also wants user_id and status. Those live in the table heap. So for every one of the 48k matching rows Postgres walks back to a heap page — in physical-order-random sequence. That's the 47k reads. The index found the rows fast; fetching the columns is what's slow.

🧩Step 2 — the concept: make the index cover the query

CREATE INDEX CONCURRENTLY orders_tenant_created_cov
  ON orders (tenant_id, created_at) INCLUDE (user_id, status);

Now every column the query needs is inside the index → Postgres can answer without touching the heap at all. Plan node changes to:

Index Only Scan ... Heap Fetches: 0

Same query, 400ms → ~15ms.

⚠️Step 3 — the part that makes you intermediate

Run it on a hot table and you'll see this instead:

Index Only Scan ...  Heap Fetches: 43887

Covering index, still hitting the heap. Why?

An index entry does not know whether its row is visible to your transaction — MVCC visibility lives in the heap tuple. So Postgres checks the visibility map: 1 bit per heap page saying "every tuple on this page is visible to everyone." All-visible page → skip the heap. Otherwise → fetch it anyway.

VACUUM is what sets those bits. On a table with steady writes, recently-touched pages are not all-visible, so "index-only" quietly degrades to a regular index scan.

VACUUM (ANALYZE) orders;   -- then re-run EXPLAIN

If Heap Fetches stays high on a write-heavy table, tune autovacuum for it:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

🎯Gotchas worth memorizing

• INCLUDE columns are payload only — not searchable, not sortable, not part of a UNIQUE constraint. WHERE status='paid' cannot use an INCLUDE'd status. Put it in the key if you filter on it.
• Every extra column widens the index → more write amplification and less of it fits in cache. Cover 2-3 hot columns, not the whole row.
• SELECT * can never be index-only. Covering indexes reward narrow SELECTs.
• MySQL/InnoDB: no visibility map issue, and secondary indexes already carry the PK implicitly. Look for "Using index" in EXPLAIN Extra — that's the same win.

✍️Your exercise

Take your slowest list/dashboard endpoint. Run EXPLAIN (ANALYZE, BUFFERS) on its query and note read=. Add an INCLUDE index for exactly the columns it selects, then run it twice: once right away, once after VACUUM.

What's Heap Fetches before and after the vacuum — and what does that tell you about how often that table is written?