Covering Indexes & Index-Only Scans

A covering index carries every column a query needs inside the index itself, so the database answers from the index alone and never visits the table heap. That heap-free execution — the index-only scan — eliminates the random-I/O hop that dominates query cost at scale, and it is the single biggest tuning lever after simply having an index.

Intermediate indexes covering-index index-only-scan include-clause heap-fetch visibility-map mvcc Created 2026-06-03 · Updated 2026-06-03

Key Components

Covering index: An index that contains every column a particular query references, so that query can be satisfied without ever reading the underlying table.
Index-only scan: The execution plan that results from a covering index: the database descends the B-tree, reads the answer straight out of the leaf, and skips the heap fetch entirely.
Heap fetch: The second step of a normal index scan — following the leaf entry's row-pointer (TID) to the row's scattered physical location in the table heap to read columns not stored in the index. This is the random-I/O cost a covering index removes.
INCLUDE / payload column: A column stored only in the index leaf for retrieval, never used to find or sort rows. Added via INCLUDE (...) (Postgres 11+); leaner than a key column because it is stripped from upper tree nodes.
Visibility map (VM): A tiny per-page bitmap in Postgres marking which heap pages are all-visible. An index-only scan checks it to decide whether it can trust the index value or must fall back to the heap for an MVCC visibility check.

Concrete Example

Start with the cheapest covering index there is — equality on a key column with the SELECT-ed column riding along as payload:

-- Plain index: filters on id, then heap-fetches the row to read email
CREATE INDEX ON users (id);

-- Covering index: email rides along in the leaf → index-only scan, no heap
CREATE INDEX ON users (id) INCLUDE (email);

SELECT email FROM users WHERE id = 42;   -- answered from the index alone

The plain index can locate the matching leaf entry, but that entry stores only (id → TID); reading email requires a heap fetch. Adding INCLUDE (email) stores the email value in the leaf next to the pointer, so the planner can return it directly — an index-only scan.

A more realistic aggregate, from a sales table. This index covers the query because both the filtered column and the summed column live in the index:

CREATE INDEX ON sales (subsidiary_id) INCLUDE (eur_value);

-- Covered: subsidiary_id finds the rows, eur_value is read from the leaf
SELECT SUM(eur_value) FROM sales WHERE subsidiary_id = 5;

Confirm it actually happened with EXPLAIN. Three plan shapes tell the whole story:

Seq Scan          -- no usable index; whole table read
Index Scan        -- index found rows, then heap-fetched (two-step)
Index Only Scan   -- covered; heap skipped. Reports "Heap Fetches: N"
                  --   Heap Fetches: 0  =  perfect, fully index-only

Heap Fetches counts the rows where the visibility map forced a fallback to the heap. Zero is the target.

Visual Model

Think of a plain index as a library catalog card: it tells you the shelf, then you walk across the building to fetch the book — that walk is the heap fetch. A covering index prints the answer directly on the card, so you never leave the catalog. The Postgres twist: before trusting a card, you glance at a tiny "is this card still current?" list (the visibility map) — usually instant, but if the row was recently touched you still have to walk to the shelf.

Step through both execution paths below. The top path is a normal Index Scan that must hop to the heap; the bottom path is the Index-Only Scan that returns straight from the leaf. Watch the eliminated heap hop.

Step 1 of N

Loading…

Deeper — Edge Cases & Gotchas

Key column vs INCLUDE payload

There are two ways to put a column into an index, and choosing wrong wastes space and write throughput.

Aspect	Key columns `(id, email)`	INCLUDE payload `(id) INCLUDE (email)`
Purpose	search, sort, range	just stored for retrieval
Sorted in tree?	yes	no
In upper (branch) nodes?	yes — copied to every level	no — leaves only
Counts toward UNIQUE?	yes	no

Mental model: key columns are how you find the row; INCLUDE columns are what you carry back. Use a composite key only when you actually filter or sort by the column; otherwise INCLUDE keeps the navigational tree lean via suffix truncation (the payload is stripped from branch nodes), whereas a composite copies the second key into every level.

The visibility-map caveat — why coverage isn't enough in Postgres

Postgres uses MVCC: an UPDATE writes a new row version and marks the old one expired; a DELETE marks a version dead; each transaction sees the version valid when it began. So the heap holds a mix of live, dead, and not-yet-visible rows, and "is this row visible to me?" has a per-transaction answer. The index leaf stores the value but not this visibility bookkeeping. So even a perfectly covering index must consult the visibility map:

for each matching index entry:
    if VM says its page is all-visible:  return value from index   ✅ no heap
    else:                                heap-fetch to check visibility ⚠️ speedup lost

VACUUM is what sets VM bits. The consequence: index-only scans shine on static / read-heavy tables (bits stay set), but on write-hot tables version churn clears the bits, forcing constant fallback — the benefit evaporates. An under-vacuumed table will not get clean index-only scans even with a flawless covering index.

Anti-pattern: assuming a covering index stays covering as the query evolves. Extending the WHERE clause silently un-covers it.

-- Covered by  CREATE INDEX ON sales (subsidiary_id) INCLUDE (eur_value)
SELECT SUM(eur_value) FROM sales WHERE subsidiary_id = 5;          -- Index Only Scan ✅

-- Add one predicate on a column NOT in the index…
SELECT SUM(eur_value) FROM sales
WHERE subsidiary_id = 5 AND sale_date > '2026-01-01';             -- drops to Index Scan ✗

Why it breaks: sale_date is neither a key nor an INCLUDE column, so it cannot be read from the index. Postgres must heap-fetch each candidate row to read sale_date and apply the filter — the plan degrades from Index Only Scan back to Index Scan. Always re-check EXPLAIN after changing a query.

Other trade-offs — when NOT to cover

It re-taxes writes. Updating a payload column forces index maintenance: MVCC writes a new heap version, which needs a new index entry carrying the new payload value (the old leaf entry lingers until VACUUM). Changing an INCLUDE'd column also disqualifies Postgres's HOT optimization, which can otherwise skip index updates.
Wide payloads bloat the index → more pages per scan and more cache pressure. Don't INCLUDE large text/json columns.
Don't speculatively cover. Index first without the SELECT list; add INCLUDE only when a measured hot query justifies it.

Test Yourself

A normal Index Scan does two things. Which one does an Index-Only Scan eliminate, and why does it matter at scale?

When should you use INCLUDE (col) rather than making col a key column?

(subsidiary_id) INCLUDE (eur_value) covers SELECT eur_value WHERE subsidiary_id = 5. You add AND sale_date > '2026-01-01'. What happens, and why?

Why can a perfectly covering index still read the heap in Postgres, what structure decides it, and what kind of table makes index-only scans unreliable?

What's the cost of INCLUDE columns, and the rule of thumb for adding them?