Optimizing PostgreSQL Retrieval with B-Tree Index-Only Scans

The Performance Gap: Index Scans vs. Index-Only Scans

When retrieving data from PostgreSQL, the primary goal is to minimize disk I/O. A standard Index Scan finds the location of a row in the B-tree index and then performs a "heap fetch" to retrieve the actual row from the table (the heap). If you are selecting five different columns but only indexing one, PostgreSQL must jump between the index and the heap for every matching row.

The most effective way to eliminate this overhead is the Index-Only Scan. This occurs when the index contains all the data required by the query, allowing PostgreSQL to skip the heap entirely. This significantly reduces latency for high-volume read operations and pagination.

Implementing the COVERING Index with INCLUDE

Traditionally, adding columns to a composite index to enable Index-Only Scans increased the size of the B-tree and slowed down sorting, because every column in a composite index is part of the search key. PostgreSQL (version 11+) introduced the INCLUDE clause, which allows you to attach "payload" columns to the leaf nodes of the index without making them part of the search tree.

Scenario: Optimizing User Profile Lookups

Imagine a users table where you frequently query a user's email to retrieve their username and last_login timestamp.

-- Run this as a database owner or superuser
CREATE INDEX idx_users_email_covering 
ON users (email) 
INCLUDE (username, last_login);

In this configuration, email is the search key. The B-tree is organized by email, but the username and last_login values are stored directly in the leaf nodes. When you run the following query, PostgreSQL can satisfy the request using only the index:

SELECT username, last_login FROM users WHERE email = 'user@example.com';

Verifying the Execution Plan

To confirm that PostgreSQL is avoiding the heap, use the EXPLAIN ANALYZE command. Run this in your psql terminal or query editor:

EXPLAIN ANALYZE SELECT username, last_login FROM users WHERE email = 'user@example.com';

What to look for:

• Index Only Scan: This indicates the optimization is working.
• Heap Fetches: If you see "Heap Fetches" in the output, it means the Visibility Map (a bitset that tracks which pages have been modified) is not up to date, forcing PostgreSQL to check the heap to ensure the data is visible to the current transaction.

Critical Limitations and Trade-offs

The Cost of Write Amplification

Every index you create adds overhead to INSERT, UPDATE, and DELETE operations. When you use INCLUDE, you are storing redundant data. If the last_login column is updated every few seconds, the index must also be updated, which can lead to index bloat.

The Cardinality Trap

Avoid creating B-tree indexes on columns with very low cardinality (e.g., a boolean column like is_active). The PostgreSQL query planner often determines that a sequential scan (reading the whole table) is faster than jumping back and forth through an index that matches 50% of the rows.

Maintenance and Bloat

Because PostgreSQL uses Multi-Version Concurrency Control (MVCC), updated rows leave "dead tuples" in both the heap and the index. If a table is update-heavy, the index can grow significantly larger than the actual data. Use the pg_stat_user_indexes view to monitor if an index is actually being used before deciding to keep it:

SELECT relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

Rollback Procedure

If you observe a significant spike in write latency or disk usage after adding a covering index, remove it to restore original performance:

DROP INDEX CONCURRENTLY idx_users_email_covering;

Note: Using CONCURRENTLY prevents the table from being locked during the drop operation, allowing your application to continue reading and writing.