Covering Index (Index-Only Scan)

TechniqueDatabase InternalsMidSeniorUpdated Nov 10, 2026

Add the columns you SELECT into the index so the query plan never visits the heap. The index alone covers the read.

When to Use Covering Index (Index-Only Scan)

Read-heavy queries with a stable column list, especially when the table is wide and the heap visit is the expensive part. Postgres INCLUDE, SQL Server included columns, MySQL secondary indexes that already carry the PK.

In Practice

CREATE INDEX idx ON orders (user_id) INCLUDE (status, total) lets SELECT status, total FROM orders WHERE user_id = ? run as an index-only scan; EXPLAIN shows Index Only Scan with zero heap fetches if the visibility map is up to date.

Sketch

Pseudocode

CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (status, total);

-- EXPLAIN: Index Only Scan using idx_orders_user_covering
SELECT status, total FROM orders WHERE user_id = 42;

How Covering Index (Index-Only Scan) Comes Up in Interviews

Scenario

Read-heavy endpoint runs SELECT status, total FROM orders WHERE user_id = ? millions of times a day. Updates are rare. EXPLAIN shows an index scan on user_id plus heap fetches. Which index change helps most?

An index-only scan avoids the heap visit entirely. Postgres INCLUDE (or MySQL secondary index that already carries the PK plus needed cols) lets the planner return rows straight from the leaf pages.

In Postgres the visibility map must mark the page all-visible for the index-only scan to skip the heap; otherwise the planner falls back to a normal index scan. VACUUM keeps the visibility map fresh.