In this lesson, you will learn how to design your tables and queries to retrieve data directly from indexes, bypassing the heap entirely to achieve peak performance.
The Problem: The Cost of the Heap
As we’ve discussed in our look at The Anatomy of a PostgreSQL Page, a table (the "heap") is a collection of pages containing rows. When you run a SELECT query, the database engine usually finds the location of your data using an index, but then must perform a separate "heap fetch" to read the actual row contents from the table pages. If your result set is large, these random I/O operations on the heap become a significant performance bottleneck.
The Solution: Index-Only Scans
An Index-Only Scan occurs when the database finds all the requested columns directly within the B-Tree index structure. Because the index already contains the required data, the engine never has to look at the heap. This saves disk I/O and reduces pressure on the Buffer Cache.
To maximize the chances of an index-only scan, you must ensure that all columns in your SELECT list and your WHERE clause are present in the index.
Concrete Illustration
Imagine a users table where you frequently query email addresses by status:
-- A standard index
CREATE INDEX idx_users_status_email ON users(status, email);
-- This query can now perform an Index-Only Scan
SELECT email FROM users WHERE status = 'active';
Because both status (for filtering) and email (for projection) are inside the index, PostgreSQL doesn't need to touch the table's heap pages.
The Visibility Map Connection
There is one catch: even if the columns are in the index, PostgreSQL must verify that the rows are "visible" to your current transaction based on MVCC and Tuple Visibility rules. PostgreSQL uses the Visibility Map to check if all tuples on a heap page are visible. If the map confirms visibility, the scan stays entirely within the index. If the page is not marked as all-visible, the engine might still fall back to the heap to verify the tuple's state, even if the index contains the data.
Key Takeaway: By including all queried columns in your indexes, you enable Index-Only Scans, which eliminate costly heap fetches and dramatically reduce disk I/O.