Covering Indexes: The Shortcut

The Double Jump Problem

When a database uses a standard index to answer a query, it performs two physical operations:

1. Index Scan: Traverse the B-Tree (or other index structure) to find the matching key and retrieve the heap pointer — the physical disk address of the actual row.
2. Heap Fetch: Jump to that disk address in the main table (called the Heap) to retrieve the columns you actually asked for.

Step 2 is random I/O. The rows matching your query may be scattered across many different disk pages. For a query returning thousands of rows, this means thousands of random disk reads — the slowest operation a database performs.

Index-Only Scans with INCLUDE

A Covering Index eliminates the Heap Fetch by storing the payload columns you need directly inside the index leaf nodes, alongside the key.

-- Standard index: only stores the key (zipcode) + heap pointer
CREATE INDEX idx_zip ON users (zipcode);

-- Covering index: stores key + payload (first_name, last_name) in the leaf
CREATE INDEX idx_zip_covering ON users (zipcode) INCLUDE (first_name, last_name);

Now, for the query SELECT first_name, last_name FROM users WHERE zipcode = '90210':

1. The database finds '90210' in the index.
2. first_name and last_name are right there in the index leaf node.
3. Result returned. The Heap is never touched.

This is called an Index-Only Scan and can be 10x–100x faster than a standard Index Scan + Heap Fetch for read-heavy workloads.

INCLUDE vs. Adding Columns to the Key

You might wonder: why use INCLUDE instead of just adding the columns to the index key itself (CREATE INDEX idx_zip_key ON users (zipcode, first_name, last_name))? Both approaches work, but they're different:
- Key columns (zipcode, first_name, last_name) are used for sorting and can be used in WHERE, ORDER BY, and range queries.
- INCLUDE columns are stored only in the leaf nodes. They cannot be used for filtering or sorting, but they add no overhead to the internal tree nodes, keeping the index smaller and the tree shallower.

Use INCLUDE when you want to cover a SELECT clause without making the index key larger than necessary.

Visibility Map Caveat

Index-Only Scans have one hidden dependency: the Visibility Map. PostgreSQL must confirm that the row version in the index is visible to your transaction (i.e., not a dead tuple from a previous update). It checks the Visibility Map — a compact bitmap — to do this without touching the Heap. If VACUUM has not run recently and many pages are "dirty" (not marked all-visible), PostgreSQL falls back to Heap Fetches even with a covering index. This is why regular VACUUM is essential for covering indexes to deliver their full benefit.