Postgres Index-Only Scans: Covering Indexes, INCLUDE, and the Visibility Map

Postgres · Intermediate · 0 min read · published 2026-08-09

Overview

An index-only scan lets Postgres answer a query entirely from an index, never touching the heap (table) pages. It only works when every column the query needs is present in the index and the visibility map marks the containing heap pages as all-visible. The INCLUDE clause (Postgres 11+) lets you add non-key payload columns to a B-tree index so it becomes "covering" without bloating the search key or breaking uniqueness semantics.

flowchart TD
    A[Query arrives] --> B{All needed columns<br/>in the index?}
    B -- No --> C[Index Scan:<br/>probe index, then fetch heap tuple]
    B -- Yes --> D{Heap page marked<br/>all-visible in VM?}
    D -- No --> E[Index Only Scan<br/>with Heap Fetches > 0]
    D -- Yes --> F[Index Only Scan<br/>Heap Fetches: 0]
    E --> G[Visibility check<br/>reads heap page]
    C --> H[Rows returned]
    G --> H
    F --> H
    I[VACUUM / autovacuum] -.->|sets all-visible bits| D

Pros

• Eliminates random heap I/O for the hottest read paths — often a 5–20x latency drop on wide tables.
• INCLUDE columns are stored only in leaf pages, so the index stays shallow and inner pages stay dense compared to adding them as key columns.
• Works with UNIQUE indexes: CREATE UNIQUE INDEX ... (email) INCLUDE (tenant_id) keeps uniqueness on email alone.
• Great for count/aggregate queries and for pagination that only needs id + sort key.

Cons

• Every extra column widens the index, increasing write amplification and slowing HOT-update eligibility (updating an indexed key column forces a new index entry).
• Depends on the visibility map: a write-heavy table with lazy autovacuum shows high Heap Fetches and the benefit evaporates.
• INCLUDE columns cannot be used for filtering with index quals in the same way key columns can — they're payload only (though the planner can still apply them as a filter after fetching the tuple from the index).
• Expression indexes generally can't produce index-only scans for the underlying column.

Real-World Example

A multi-tenant SaaS dashboard renders an order list: 40M-row orders table, query selects id, status, total_cents filtered by tenant and ordered by created_at. The existing index on (tenant_id, created_at) forces 500 random heap fetches per page load.

-- Before: Index Scan + 500 heap fetches
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

-- Make the index covering: keys drive the search + sort, INCLUDE carries payload
CREATE INDEX CONCURRENTLY idx_orders_tenant_created_cov
  ON orders (tenant_id, created_at DESC)
  INCLUDE (status, total_cents);

-- Ensure the visibility map is current so Heap Fetches drops to 0
VACUUM (ANALYZE) orders;

-- After: "Index Only Scan using idx_orders_tenant_created_cov ... Heap Fetches: 0"
-- Keep the VM fresh on this hot table:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents FROM orders
WHERE tenant_id = 42 ORDER BY created_at DESC LIMIT 50;
-- Heap Fetches: 41,332   shared read=39k

SELECT relname, n_dead_tup, last_autovacuum,
       pg_relation_size(relid) / 8192 AS heap_pages,
       (SELECT count(*) FROM pg_visibility_map('orders') v WHERE v.all_visible) AS av_pages
FROM pg_stat_user_tables WHERE relname = 'orders';

-- inside the batch job, after the UPDATE commits
VACUUM (ANALYZE, PARALLEL 4) orders;