Index-only scans & the visibility map

An index scan is two steps

1 · walk the index → get a ctid (page, slot).

2 · heap fetch the tuple at that ctid.

No clustered index → every index scan is potentially two-step, even the primary key. (InnoDB's PK read is one descent.)

Why the heap trip is needed

Row visibility lives in the heap tuple, not the index entry. So an index entry alone can't prove the row is visible to your snapshot — even a fully-covering index.

The visibility map

One bit per heap page = "all tuples here are visible to everyone." An index-only scan checks it: bit set → skip the heap; bit unset → heap fetch anyway.

VACUUM sets those bits. So index-only scans are only fast on a well-vacuumed table; heavy updates clear the bits until the next vacuum.

Covering indexes

CREATE INDEX i ON t (x) INCLUDE (y); — x searchable, y payload.

Enables an index-only scan for SELECT y … WHERE x = … — but only on all-visible pages. Little point on a churny table.

Diagnose with EXPLAIN (ANALYZE) → the Heap Fetches: line (0 = fully index-only).

Postgres vs InnoDB

Read	InnoDB	Postgres
By PK	one descent	two-step
Covering	Using index	only if all-visible
Payload syntax	add cols	INCLUDE
Skip powered by	index has it	visibility map (VACUUM)