Mastering PostgreSQL Index-Only Scans: 40x Speed for Heavy Queries

Introduction

As a DBA with over two decades of experience in PostgreSQL performance tuning, I’ve often watched developers stare in frustration at an EXPLAIN ANALYZE output. You’ve built what seems like the perfect index, yet PostgreSQL stubbornly falls back to a Sequential Scan, dragging query performance into the gutter. The secret to breaking this bottleneck isn’t simply “adding more indexes”—it’s mastering PostgreSQL Index Only Scans (IOS).

When configured correctly, an IOS allows the engine to skip the table (the “Heap”) entirely, fetching data directly from the index. In high-concurrency environments, this shift can reduce I/O overhead by 90% and deliver PostgreSQL speed improvement of up to 40x. This guide dives deep into SQL query optimization techniques, database indexing strategies, and the mechanics of the Visibility Map, showing you how to fine-tune query execution plans so PostgreSQL stops hitting the disk unnecessarily.

Prerequisites

To get the most out of this deep dive, you should be comfortable with basic SQL syntax and the concept of B-Tree indexes. If you need a refresher on fundamental PostgreSQL administration or indexing strategies, I highly recommend reviewing these existing resources:

• PostgreSQL Architecture Overview
• Understanding B-Tree vs GIN Indexes
• Introduction to EXPLAIN ANALYZE

Why Your Index is Being Ignored

Imagine a table sales_records with 50 million rows. You run a query filtering by transaction_date and selecting amount. You have an index on transaction_date. Logically, the database should use the index, right?

Yet, the planner chooses a Sequential Scan. Why? Because PostgreSQL is an MVCC (Multi-Version Concurrency Control) database. Even if the index finds the row, the engine doesn’t know if that specific version of the row is “visible” to your current transaction. It usually has to go to the Heap to check the row’s metadata (xmin and xmax).

An Index-Only Scan is the “Holy Grail” because it bypasses this check by using the Visibility Map. If the Visibility Map marks a page as “all-visible,” the engine trusts the index and ignores the Heap entirely.

Best Practices for Index-Only Scans

Achieving a consistent 40x speed boost requires more than just luck. Follow these battle-tested standards:

• Use the INCLUDE Clause: Introduced in PostgreSQL 11, “Covering Indexes” allow you to attach non-key columns to an index. This is vital for columns you frequently SELECT but never filter by.
• Tune Autovacuum: An Index-Only Scan lives and dies by the Visibility Map. If your autovacuum isn’t aggressive enough, the map becomes stale, and PostgreSQL is forced back into “Heap Fetches.”
• Monitor Index Bloat: High churn (many UPDATEs/DELETEs) creates holes in your indexes. Use REINDEX CONCURRENTLY to maintain high-density indexes that fit in RAM.
• Target Static Data: IOS works best on tables where data is mostly static or append-only. Frequently updated rows will constantly invalidate the Visibility Map’s “all-visible” status.
• Keep Indexes Slim: Don’t include every column. A massive index is slow to maintain and can be larger than the table itself, defeating the purpose of reduced I/O.
• Check Data Types: Ensure your query parameters match the index data types exactly to avoid implicit casting, which disables index usage.

The Technical Deep-Dive: Visibility Maps & Heap Fetches

The Visibility Map (VM)

The VM is a simple bitmask for each relation. Each heap page has two bits:

1. All-visible: All transactions can see all rows on this page.
2. All-frozen: All rows are frozen (relevant for VACUUM optimization).

When the “All-visible” bit is set, the Index-Only Scan can skip the Heap. If it’s not set, the execution plan will show “Heap Fetches,” which indicates the engine had to verify visibility manually.

Diagnostic Script 1: Identifying “False” Index Scans

Use this script to find queries that are performing Index-Only Scans but are still being slowed down by heavy Heap Fetches.

-- Identify tables with high Heap Fetches in Index-Only Scans
SELECT 
    relname AS table_name, 
    idx_scan AS total_index_scans,
    i.reltuples::bigint AS estimated_rows,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables t
JOIN pg_class i ON t.relid = i.oid
WHERE idx_scan > 0 
ORDER BY idx_scan DESC;

-- Detailed Index-Only Scan Efficiency
EXPLAIN (ANALYZE, BUFFERS)
SELECT transaction_date, amount 
FROM sales_records 
WHERE transaction_date > '2025-01-01';

In the output of the second query, look for Heap Fetches. If that number is high, your Visibility Map is out of date.

Diagnostic Script 2: Forcing a Visibility Map Refresh

If you notice performance degradation, you likely need to “vacuum” the table to update the bits in the Visibility Map.

-- Manually update the Visibility Map for a specific table
VACUUM (ANALYZE, VERBOSE) sales_records;

-- Check the status of the Visibility Map (Requires pg_visibility extension)
-- CREATE EXTENSION pg_visibility;
SELECT count(*) AS dirty_pages
FROM pg_visibility('sales_records')
WHERE not all_visible;

Frequently Asked Questions (FAQs) on Mastering PostgreSQL Index-Only Scans

Question 1. Can I use Index-Only Scans with GIN indexes?

No, IOS is primarily supported for B-Tree, GiST, and SP-GiST indexes. GIN indexes do not store enough information to satisfy an IOS.

Question 2. Why does my EXPLAIN plan show a Seq Scan even with an index?

PostgreSQL determines that reading the whole table sequentially is cheaper than jumping between the index and the heap (Random I/O), especially if the query returns a large percentage of the table.

Question 3. Does the INCLUDE clause increase index size?

Yes. Added columns are stored in the leaf nodes. Use them only for columns frequently present in the SELECT list of high-frequency queries.

Question 4. How does IOS handle NULLs?

B-Tree indexes include NULLs, so IOS can satisfy queries checking for IS NULL if the index is not a partial index excluding them.

Question 5. Is a multi-column index the same as a covering index?

Not quite. In a multi-column index, all columns can be used for filtering. In a covering index (INCLUDE), the extra columns are only for data retrieval.

Question 6. Can partial indexes support Index-Only Scans?

Yes, provided the query’s WHERE clause is a subset of the partial index’s predicate.

Question 7. Why did my IOS speed drop after a bulk load?

Bulk loads don’t automatically update the Visibility Map. You must run VACUUM ANALYZE after a large data import.

Question 8. What is a “Heap Fetch”?

It occurs when the Index-Only Scan must visit the table anyway because the Visibility Map bit for that page is not set.

Question 9. Does IOS work on JOINs?

Absolutely. If the join keys and the selected columns are all covered by an index on the joined table, you can achieve massive join performance gains.

Conclusion

Mastering the Index-Only Scan is the hallmark of a senior PostgreSQL developer. By understanding that an index is not just a search tool but a data source, you can eliminate the most expensive part of database operations: Heap I/O. Remember that performance is a moving target; a fresh Visibility Map is the engine that drives a 40x speed increase. Monitor your Heap Fetches, tune your autovacuum settings, and strategically use the INCLUDE clause to transform heavy, sluggish queries into lightning-fast operations. With these techniques, you ensure your database remains responsive even under the most demanding workloads.

Next Steps

To continue your journey in PostgreSQL performance tuning, consider these immediate actions:

• Audit Your Top 10 Queries: Run pg_stat_statements to find queries with high I/O and test if a covering index enables an IOS.
• Review MyTechMantra’s Deep Dives:
  • Advanced Autovacuum Tuning for High-Volume Databases
  • Mastering the INCLUDE Clause in PostgreSQL 11+
• Implement Monitoring: Set up alerts for index bloat and stale statistics using the diagnostic scripts provided in this article.
• Stay Updated: Read our upcoming article on Partitioning vs. Index-Only Scans at MyTechMantra.com.