Index-Only Scan Efficiency in MVCC

Optimize query performance by leveraging version metadata already present in indexes.

In this lesson, you will learn how to leverage index data to fulfill queries without ever touching the actual table rows, even when using MVCC.

The MVCC Overhead Problem

In a standard MVCC database, every row in your table carries hidden metadata fields—specifically xmin (the ID of the transaction that created the row) and xmax (the ID of the transaction that deleted or updated the row). When you perform a standard SELECT, the database engine must fetch the row from the data page (the "heap") to check these transaction IDs against your snapshot to see if the data is visible to you.

Fetching from the heap is expensive because it requires extra I/O operations.

The Index-Only Scan Solution

An "Index-Only Scan" is a performance optimization where the database determines that all the information requested by your query is contained entirely within the index structure.

However, in an MVCC system, the index alone isn't always enough to guarantee visibility. Even if the index contains the value you are searching for, the engine still needs to know if that version of the data is "visible" to your current snapshot.

To solve this, modern MVCC databases store enough information within the index entries themselves to perform a Visibility Map check. Instead of jumping to the heap, the database looks at a separate, compact structure called a "Visibility Map." This map tracks whether all tuples on a data page are known to be visible to everyone.

How it works in practice

If your query is:

SELECT email FROM users WHERE user_id = 50;

If you have an index on (user_id, email), the database performs the following check:

It locates the entry in the index.
It checks the Visibility Map for the corresponding heap page.
If the map confirms all rows on that page are visible, the database returns the email from the index immediately.
It avoids the "heap fetch" entirely, saving significant time and I/O.

If the visibility cannot be guaranteed by the map, the engine is forced to perform a standard scan to inspect the xmin/xmax values on the actual row.

Key Takeaway: Index-only scans boost performance by using a Visibility Map to bypass the expensive heap-fetch process, allowing your query to remain entirely within the lightweight index structure.