Index Selection and Index-Only Scans

In this lesson, you will learn how to structure your schemas to satisfy queries entirely from an index, effectively bypassing the expensive "heap fetch" process.

The Problem: Heap Latency

Normally, when you run a query like SELECT email FROM users WHERE username = 'alice';, the PostgreSQL planner performs two steps:

1. It traverses the index on username to find the physical address (the TID) of the record.
2. It performs a Heap Fetch to retrieve the actual email column data from the table's disk pages.

If your table is large, this second step is a massive performance killer. It requires random I/O to access the heap, which is far slower than staying within the index tree.

The Solution: Index-Only Scans

An Index-Only Scan occurs when the index contains all the information requested by the query, meaning the database never needs to touch the main table heap. To force this, we can use an INCLUDE clause, which appends non-indexed columns to the index leaf nodes.

-- Standard index only stores the key
CREATE INDEX idx_users_username ON users (username);

-- Optimized index stores the key + the data we need to fetch
CREATE INDEX idx_users_covering ON users (username) INCLUDE (email);

When you execute SELECT email FROM users WHERE username = 'alice'; with the second index, PostgreSQL sees that all necessary data exists within the index itself. It performs an Index-Only Scan.

Visibility Maps

One vital detail: even with a covering index, PostgreSQL must verify that the rows are "visible" to your current transaction (checking MVCC status). It does this using the Visibility Map. If a page in the heap has been recently modified, the visibility map might show it as "dirty," forcing a heap fetch even if you have a covering index. This is why Index-Only Scans are most efficient on read-heavy, static datasets.

By strategically using INCLUDE to create "covering indexes," you reduce the need for heap lookups, keeping your working set in memory and drastically lowering query latency.

Key Takeaway: By using the INCLUDE clause to create covering indexes, you allow the query planner to retrieve all required column values directly from the index tree, eliminating costly random I/O heap fetches.