Covering indexes: the index becomes a narrow copy of the table

When can an index answer a query without touching the table, and how does MVCC quietly undo it?

The constraint is that the table fetch is where the random I/O lives. The tree descent is 3 or 4 page reads, mostly cached. The fetch is k scattered reads. Delete the fetch and you delete the cost.

If the index carries every column the query references, the plan never touches the table. That is an index-only scan. The cost profile inverts:

index scan + fetch:  descent + k random page reads
index-only scan:     descent + k / f_idx  sequential index page reads

And f_idx > f, because the index is narrow: you fit more entries per page when an entry is two columns instead of forty. A covering index can therefore beat even a clustered scan for a narrow query over a wide table, because it is a denser copy of the relevant slice.

That is the real identity of this mechanism: a covering index is a materialized projection with a sort order attached. (A projection is a subset of columns.) The Vertica database took this to its conclusion and removed base tables entirely: a Vertica table is nothing but a set of projections. When a system converges on "everything is a covering index", it is telling you that the covering index was the primitive all along.

The cost is dial 4 in the open position: those columns now exist twice, and every update to them writes twice. INCLUDE columns (SQL Server, PostgreSQL 11 and later) are the refinement. Their payload lives in leaf pages only, does not take part in the key ordering, does not count against key size limits, and does not trigger key reordering when updated.

-- Key = (customer_id, created_at); status and city ride along as payload.
CREATE INDEX parcels_cust_cover
  ON parcels (customer_id, created_at)
  INCLUDE (status, city);

The boundary condition is MVCC, and it bites in practice. In PostgreSQL an index entry does not know whether the row version it points at is visible to your snapshot. So an "index-only" scan consults the visibility map (a per-page bit meaning "every version on this page is visible to everyone"), and if the page is not marked all-visible, it fetches the table row anyway. Index-only scans therefore depend on autovacuum having run recently enough. You can have a perfectly designed covering index that silently degrades into an ordinary index scan on a write-heavy table. That is Priya's second symptom.