PostgreSQL implements an interesting optimization to make some scans index-only

PostgreSQL implements an interesting optimization to make some scans index-only and execute the query faster. Let’s explore the implementation.

An index-only scan is when PostgreSQL retrieves data entirely from the index without needing to access the actual rows (the heap). This is much faster as it avoids the extra step of reading rows from the disk.

For index-only scans to work, PostgreSQL needs to ensure that the index data is current and up-to-date. Because of MVCC implementation, there may be multiple versions of the same row in use by multiple active transactions.

In a non-optimized implementation, PostgreSQL needs to read the actual row to confirm its visibility and get the latest version if required. Thus your index-only scans are not as fast as you’d like them to do. To make this performant, PostgreSQL leverages the Visibility Map.

Visibility Map is an internal data structure that PostgreSQL maintains to track which pages (a block of rows) in a table contain only tuples (rows) that are visible to all transactions. If a page is marked in the visibility map as all-visible, the engine knows that it doesn’t need to check the visibility of the individual rows on that page.

Hence, during an index-only scan, PostgreSQL first computes the rows that need to be part of the result set and then it checks on the visibility map to see if the page is marked as all-visible. If yes, then it skips getting the actual page from the heap; thus making the entire execution faster and more performant.

In PostgreSQL, because of MVCC, update and delete operations lead to dead tuples, and vacuuming is required to clear them out. Thus, as part of vacuuming

1. the dead tuples are removed
2. and the visibility map is updated to mark pages as all-visible.

This implies that every row on that page is visible to all transactions and can now be leveraged to skip the heap fetch and complete index-only scans faster.

⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.