The Hidden Cost of "Index-Only": Unmasking PostgreSQL’s Most Misunderstood Performance Feature

In the architecture of PostgreSQL, few features are as alluring—or as frequently misunderstood—as the Index-Only Scan. On paper, it represents the holy grail of database performance: the ability to retrieve query results directly from an index without ever touching the heavy, disk-intensive heap storage. When implemented correctly, it can yield performance gains ranging from 2x to 20x. However, beneath the surface lies a complex mechanism involving Multi-Version Concurrency Control (MVCC) and the Visibility Map—a "catch" that often turns a high-performance plan into a silent I/O nightmare.

Main Facts: The Promise and the Pitfall

At its core, a standard index scan operates in two distinct stages. First, the database engine traverses the index to locate the specific entry. Second, it performs a "heap fetch" to retrieve the actual data rows stored in the main table. An Index-Only Scan is designed to eliminate that second, costly step by ensuring every column requested by the query is already present within the index itself.

By utilizing covering indexes—either by placing columns directly in the key or by leveraging the INCLUDE clause introduced in PostgreSQL 11—developers can build structures that theoretically provide all the answers. The system, in theory, bypasses the heap entirely.

However, the "asterisk" in this promise is significant. PostgreSQL uses MVCC to manage concurrent transactions, meaning that the "visibility" of a row—whether a specific transaction is allowed to see that row’s data—is stored within the heap, not the index. If the system had to visit the heap for every row to confirm its visibility, the Index-Only Scan would provide zero performance advantage over a standard index scan.

Chronology: The Evolution of Visibility

To understand why Index-Only Scans work, one must understand the evolution of the Visibility Map. This component was not a core part of early database design but became an essential innovation as concurrency demands grew.

The Mechanism of the Visibility Map

The Visibility Map is a compact, per-table bitmap. Each bit represents a heap page, and when set, it serves as a guarantee: every row on this specific page is old enough to be visible to all current and future transactions.

1. The Inquiry: When a query engine initiates an Index-Only Scan, it finds a candidate entry in the index.
2. The Check: Before fetching the heap, it inspects the corresponding bit in the Visibility Map.
3. The Shortcut: If the bit is set, the engine knows the row is visible without checking the heap. The scan remains "purely" index-only.
4. The Fallback: If the bit is not set, the database is forced to visit the heap page to verify visibility. In this scenario, the "Index-Only" scan has performed an extra layer of logic for no gain; it is essentially performing a standard index scan with the overhead of an extra check.

Supporting Data: Why "Index-Only" Can Fail in Production

The performance of an Index-Only Scan is entirely tethered to the VACUUM process. The Visibility Map is only as accurate as the last vacuum cycle. On a large, rapidly changing table, the gap between vacuum operations can lead to "stale" visibility maps.

Case Study: The 200-Million-Row Problem

Consider a real-world scenario involving a table of 200 million rows. After initial optimization, developers saw massive speed improvements using Index-Only Scans. However, weeks later, I/O latency spiked significantly. The query plan still proudly claimed it was using an Index Only Scan, but the underlying storage was being hammered with random heap fetches.

The culprit was the autovacuum threshold. Under default settings, the table was being vacuumed only once every two months. As rows were updated, bits in the visibility map were cleared, forcing the scan to visit the heap. The index was perfectly tuned, but the visibility map had become a liability.

Identifying the Symptom: The "Heap Fetches" Metric

The most precise diagnostic tool for this issue is the EXPLAIN (ANALYZE) command. It explicitly reports a Heap Fetches: count.

• Low or Zero: The index is functioning as intended.
• High: The visibility map is stale. The system is paying the cost of a heap access while hiding behind the label of an index-only scan.

Official Guidance and Professional Recommendations

Database administrators and PostgreSQL core contributors consistently emphasize that enable_indexonlyscan should be treated as a diagnostic instrument, not a primary tuning knob. Like enable_indexscan or enable_bitmapscan, disabling it should only be done to isolate performance bottlenecks.

The Trap of Configuration

A common "family mistake" in PostgreSQL management is setting enable_indexonlyscan = off in postgresql.conf as a permanent fix. This is akin to cutting off a limb because of a scratch. It discards a powerful optimization tool to avoid a configuration problem that should be solved via vacuuming.

Proper Tuning Strategies

When a high Heap Fetches count is observed, the following steps are recommended:

1. Refresh the Visibility Map: Manually trigger VACUUM on the affected table to see if performance immediately improves.
2. Aggressive Autovacuuming: For large, volatile tables, lower the autovacuum_vacuum_scale_factor and the insert-driven thresholds. This forces the system to keep the visibility map current without waiting for the cluster-wide default triggers.
3. The Comparative Test: Use SET enable_indexonlyscan = off only within a specific session to compare the cost of a plain index scan against the "failed" index-only scan. This tells the developer whether the planner is over-estimating the visibility of the table.

Implications for Database Design

The existence of the Index-Only Scan imposes a specific set of rules on how developers should approach indexing.

The "INCLUDE" Clause vs. Bloat

Developers often fall into the trap of using the INCLUDE clause to pack as much data as possible into an index. This is counterproductive if the table is highly volatile. If the index is so large that it forces heap access anyway due to visibility map staleness, the index is simply bloating the database, slowing down write operations, and consuming cache space without providing the promised read-time speedup.

The HOT (Heap Only Tuple) Conflict

Covering indexes can inadvertently suppress HOT updates. If an update does not change indexed columns, PostgreSQL can sometimes perform a HOT update, which is highly efficient and keeps pages "all-visible." However, by adding extra columns to an index, developers increase the likelihood that an update will modify an indexed column, forcing a full index update and making it harder for pages to remain marked as "all-visible." Before adding wide columns to an index, developers should weigh the n_tup_hot_upd statistics of their table.

Conclusion

The Index-Only Scan is a testament to the sophistication of PostgreSQL’s query planner, but it is not a "set it and forget it" feature. Its success is entirely dependent on the health of the visibility map, which in turn is dependent on the cadence of the autovacuum process.

For the database professional, the lesson is clear: when the promise of high-speed performance begins to degrade, look first to the vacuum logs, not the query planner flags. By keeping the visibility map fresh and being judicious about when to use covering indexes, developers can harness the true power of PostgreSQL without falling victim to the hidden costs of heap I/O. Use the Heap Fetches metric as your guide, treat the GUCs as diagnostic tools, and ensure your vacuum strategy is as refined as your indexing strategy.