Covering indexes — when the index is enough

Day 1 taught you indexes make reads fast. Day 3 taught you EXPLAIN. Day 6 taught you composite indexes. Day 17 taught you keyset pagination. Today: covering indexes — when the index contains everything your query needs, PostgreSQL never touches the table.

The problem: the index finds the row, then goes back for the data

You have a query:

SELECT user_id, event_type, created_at
FROM events
WHERE created_at > '2026-06-01'
ORDER BY created_at DESC
LIMIT 20;

You have an index on (created_at). PostgreSQL uses it — finds the matching rows fast. But then, for each row, it goes back to the table (heap) to fetch user_id and event_type. Those columns aren’t in the index.

This is the two-step dance: index scan (find the row) → heap fetch (get the data). Each heap fetch is a random page access. For 20 rows, it’s negligible. For 291,000 rows, it’s catastrophic.

The fix: put everything in the index

A covering index includes the columns the query needs — not just the search key, but the SELECT columns too:

CREATE INDEX idx_events_covering ON events (created_at)
INCLUDE (user_id, event_type);

The INCLUDE clause (PostgreSQL 11+) adds user_id and event_type to the index’s leaf pages — but NOT to the B-tree sort key. They’re payload, not navigation. PostgreSQL can still only seek on created_at, but when it finds a matching entry, the data is already there.

Result: Index Only Scan. PostgreSQL reads the index and returns. Zero heap fetches.

How to spot it in EXPLAIN

The magic line is Heap Fetches: 0:

Index Only Scan Backward using idx_events_covering on events
  Index Cond: (created_at > '2026-06-01')
  Heap Fetches: 0
  Buffers: shared hit=5

Compare with a regular Index Scan:

Index Scan Backward using idx_events_created_at on events
  Index Cond: (created_at > '2026-06-01')
  Buffers: shared hit=23

The Index Scan touches 23 buffer pages (index pages + heap pages). The Index Only Scan touches 5 — just the index leaf pages. No heap access.

The benchmark: 500K rows, 30 runs, median

LIMIT 20 (small result — the index scan is already fast)

Approach	Median	Buffer pages	Speedup
Seq Scan + Sort	38.21ms	4,791	1×
Index Scan	1.79ms	23	21.3×
Index Only Scan	1.22ms	5	31.3×

The covering index is 1.5× faster than the plain index even for 20 rows — 5 buffer pages vs 23. The difference grows with result size.

No LIMIT (291K matching rows — where covering indexes shine)

Approach	Median	Buffer pages	Notes
Index on (created_at)	419.30ms	4,909 + 800 temp	Bitmap Heap Scan + external sort (9.7 MB on disk)
Covering index (INCLUDE)	249.78ms	1,441	Index Only Scan, Heap Fetches: 0, no sort

Here the covering index wins big: 1.7× faster and 3.4× fewer buffer pages (1,441 vs 4,909+800). The plain index used a Bitmap Heap Scan — it fetched all 291K matching rows from the table (heap), then sorted them on disk (external merge sort, 9.7 MB). The covering index returned rows already sorted from the index — no heap access, no sort.

Even more interesting: the plain index query triggered an external merge sort on disk because the planner chose a Bitmap Heap Scan that returned rows unordered, then sorted them. The covering index returned rows already sorted (index order) — no sort needed.

The tradeoff: bigger indexes, slower writes

Covering indexes aren’t free. Every extra INCLUDE column makes the index larger:

• Storage: The index stores copies of the INCLUDE columns. More columns = more disk and memory.
• Write speed: Every INSERT/UPDATE must update the index too. A covering index on 3 columns costs more to maintain than a single-column index.
• UPDATE sensitivity: If an INCLUDE column is updated, the index entry must be updated too — even though the column isn’t a search key. Updating user_id would trigger an index update that a plain (created_at) index wouldn’t notice.

The interview answer: “A covering index includes the SELECT columns in the index itself, so PostgreSQL can answer the query from the index alone — no table access. Use INCLUDE for columns you return but don’t filter on. The cost is larger indexes and slower writes.”

The visibility map: why VACUUM matters

Index-only scans require the table pages to be all-visible — meaning PostgreSQL knows the index entries are up-to-date and no transaction has pending changes. This information is stored in the visibility map, a small bitmap that tracks which pages are visible.

If a page is NOT all-visible, PostgreSQL must fetch the row from the heap to verify it’s visible to the current transaction — a heap fetch. This turns your Index Only Scan into a regular Index Scan for those rows.

Here’s the proof. After updating 30,000 of 100,000 rows (without VACUUM), the same covering index query shows:

-- After UPDATE 30,000 rows, before VACUUM:
Index Only Scan Backward using idx_events_covering on events
  Index Cond: (created_at > '2026-01-01')
  Heap Fetches: 82         ← can't skip the heap for dirty pages
  Buffers: shared hit=86

-- After VACUUM:
Index Only Scan Backward using idx_events_covering on events
  Index Cond: (created_at > '2026-01-01')
  Heap Fetches: 0          ← index-only scan fully effective
  Buffers: shared hit=5

82 out of 100 returned rows required a heap fetch — because those rows lived on pages dirtied by the UPDATE. The index had the data, but PostgreSQL couldn’t trust it without checking the heap for visibility. After VACUUM, the visibility map is restored and Heap Fetches: 0 returns.

This is why autovacuum matters: it keeps the visibility map current, which keeps your covering indexes fast.

INCLUDE vs composite index: what’s the difference?

Feature	Composite (a, b, c)	Covering (a) INCLUDE (b, c)
Can seek on a	✓	✓
Can seek on (a, b)	✓	✗ (b is not a sort key)
Can seek on (a, b, c)	✓	✗
Can return b, c without heap fetch	✓	✓
Index size	Larger (b, c in tree)	Smaller (b, c in leaf only)
Write cost	Higher (tree rebalancing)	Lower (leaf-only update)

Rule of thumb: Use composite when you need to filter on multiple columns. Use INCLUDE when you only filter on one column but want to return others.

The connection: the indexing curriculum

• Day 1: Indexes make reads fast (B-tree seek vs seq scan)
• Day 3: EXPLAIN shows the cost (Seq Scan → Index Scan, 1,822× speedup)
• Day 6: Composite indexes (leftmost prefix rule)
• Day 17: Keyset pagination (cursor-based seek, 86.6× faster than OFFSET)
• Day 20: Covering indexes (skip the table entirely, Heap Fetches: 0)

The progression: find the row → understand the cost → combine keys → seek efficiently → don’t touch the table at all.