Index-only scan

What an index-only scan is

Allow me to draw the distinction. In a regular index scan, PostgreSQL traverses the B-tree to find matching entries, then follows each entry's tuple identifier (TID) to the table heap to retrieve the full row. This heap fetch is a random I/O operation — and for queries returning hundreds or thousands of rows, the cumulative cost of those random reads dominates execution time. It is, if I may say so, a great deal of back-and-forth for information that could have been at hand.

An index-only scan eliminates that second step entirely. If every column the query references — in the SELECT list, WHERE clause, JOIN condition, and ORDER BY — is stored in the index, PostgreSQL reads all the data it needs directly from the index leaf pages. The leaf pages are laid out sequentially in the index, making the scan fast and predictable.

-- Index scan: reads the index, then fetches each row from the table heap
-- Index Scan using idx_orders_customer on orders
--   Index Cond: (customer_id = 42)
--   Buffers: shared hit=3 read=47

-- Index-only scan: reads the index only, no heap access
-- Index Only Scan using idx_orders_covering on orders
--   Index Cond: (customer_id = 42)
--   Heap Fetches: 0
--   Buffers: shared hit=3

The difference in the Buffers line tells the story. The index scan reads 47 additional pages from the heap. The index-only scan reads 3 pages total — all from the index — because it never visits the table. Three pages. That is the kind of efficiency one aspires to.

Requirements for index-only scans

PostgreSQL will only choose an index-only scan when two requirements are satisfied:

1. The index covers all referenced columns. Every column in the SELECT list, WHERE clause, JOIN condition, and ORDER BY must be present in the index — either as a key column or as an INCLUDE column. If the query references even one column not in the index, PostgreSQL must visit the heap and will use a regular index scan instead.
2. The visibility map confirms all-visible pages. PostgreSQL's MVCC model means a row in the index might belong to a transaction that is not yet visible to the current query. To verify visibility without touching the heap, PostgreSQL checks the visibility map — a compact bitmap with one bit per table page. If a page is marked all-visible, every row on that page is visible to all transactions, and the heap fetch can be skipped. If the page is not all-visible, PostgreSQL must fetch the row from the heap to check.

The first requirement is structural — it depends on how the index is defined. The second is dynamic — it depends on how recently VACUUM has run. Even a perfectly crafted index cannot deliver an index-only scan if the visibility map has been neglected. This is the honest counterpoint: the index does its part, but it needs VACUUM to do the rest.

The visibility map connection

The visibility map is a small auxiliary file that PostgreSQL maintains alongside each table. It stores one bit per heap page: all-visible or not. VACUUM is the process that sets these bits — after removing dead tuples from a page, VACUUM marks it as all-visible.

This means that a freshly loaded or heavily updated table may have very few all-visible pages — and here is where expectations meet reality. An index-only scan on such a table will still appear in the plan, but it will fall back to heap fetches for every page that is not all-visible. In EXPLAIN ANALYZE output, this shows up as a high Heap Fetches count, and that number deserves your attention:

-- Run EXPLAIN with ANALYZE and BUFFERS to see heap fetches
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_total, status FROM orders WHERE customer_id = 42;

-- Heap Fetches: 0     — all pages are all-visible, pure index-only scan
-- Heap Fetches: 1200  — 1,200 rows required a trip to the table heap
--                       because their pages were not marked all-visible

Heap Fetches: 0 — that is the number I want to see. It means the visibility map is clean and the scan is truly index-only. A high heap fetch count means VACUUM has not caught up with recent writes. The remedy is straightforward: run VACUUM, or tune autovacuum to run more aggressively on that table.

-- Check the visibility map health of a table
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- If dead_pct is high or last_vacuum is old, run VACUUM
VACUUM orders;

Tables with low write activity tend to have clean visibility maps and benefit most from index-only scans. Tables with high write throughput need attentive autovacuum settings to keep the visibility map current. A well-maintained household does not wait for the dust to become visible before attending to it.

Covering indexes for index-only scans

If a query references columns that are not in the index, you have two options: add those columns to the index key (creating a composite index), or use the INCLUDE clause (creating a covering index). PostgreSQL 11 introduced INCLUDE specifically for this use case.

-- Standard index: only customer_id is stored
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: customer_id for lookup, order_total and status as payload
CREATE INDEX idx_orders_covering ON orders (customer_id)
  INCLUDE (order_total, status);

-- Now this query can use an index-only scan
SELECT order_total, status
FROM orders
WHERE customer_id = 42;

The INCLUDE clause adds columns to the index leaf pages without including them in the B-tree's sort order. This keeps the index smaller than an equivalent composite index — the included columns do not appear in internal tree pages and do not affect how entries are sorted. They exist solely so that index-only scans can read them without visiting the heap. A purpose-built arrangement, and a rather elegant one.

When deciding which columns to include, examine the query pattern: identify every column referenced in SELECT, WHERE, JOIN, and ORDER BY. The WHERE and ORDER BY columns should be index key columns (they drive lookup and sort). The remaining columns — typically those in SELECT that are not used for filtering — are candidates for INCLUDE.

Monitoring index-only scan effectiveness

If I may suggest two tools that will give you proper visibility into how well your index-only scans are performing:

EXPLAIN (ANALYZE, BUFFERS)

Run EXPLAIN with the ANALYZE and BUFFERS options on any query you expect to use an index-only scan. Look for two things:

• Index Only Scan in the plan node — confirms the planner chose this scan method
• Heap Fetches: N — the number of rows that required a trip to the heap. Zero is ideal. A high number relative to the rows returned indicates the visibility map needs attention.

EXPLAIN ANALYZE
SELECT customer_id, order_total
FROM orders
WHERE customer_id = 42;

-- Index Only Scan using idx_orders_covering on orders
--   Index Cond: (customer_id = 42)
--   Heap Fetches: 0
--   Buffers: shared hit=3
--   Planning Time: 0.08 ms
--   Execution Time: 0.03 ms

pg_stat_user_indexes

For a broader view across your entire database, query the statistics views to see which indexes are being scanned and how often:

-- Check index-only scan effectiveness across all indexes
SELECT
  schemaname,
  relname AS table,
  indexrelname AS index,
  idx_scan AS scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_scan DESC;

-- For a specific table, compare index scans vs index-only scans
SELECT
  relname,
  idx_scan,
  idx_tup_fetch,
  seq_scan,
  n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Indexes with high idx_scan counts are your workhorses — if they are triggering index-only scans, the benefit compounds with every query. If they are triggering regular index scans and you see an opportunity to add INCLUDE columns, the payoff is proportional to the scan frequency. The busiest indexes have the most to gain.

How Gold Lapel relates

Gold Lapel monitors every query that passes through the proxy, tracking which scan methods PostgreSQL chooses and how many heap fetches each index-only scan incurs. When we observe a frequent query pattern triggering an index scan with heap fetches — where the index covers the filter columns but not the output columns — we recommend a covering index with the appropriate INCLUDE columns to enable a true index-only scan. The query was almost there. We see to the last step.

We also watch heap fetch trends over time. If a table's index-only scans begin accumulating heap fetches — a sign that the visibility map is falling behind — Gold Lapel flags the table as a candidate for more aggressive autovacuum tuning. A covering index only delivers its full benefit when the visibility map is clean. We monitor both sides, because attending to one without the other would be doing half the job.