Why a PostgreSQL index-only scan can still fetch table rows

Scope and versions

This guide covers PostgreSQL 16, 17, and 18, using B-tree indexes in the examples. The visibility-map mechanism and Heap Fetches plan detail apply to these releases. Plans and results depend on the query, schema, data, concurrent activity, vacuum history, and PostgreSQL version.

What “index-only” means

A regular index entry identifies a heap tuple, but PostgreSQL indexes do not store the tuple’s MVCC visibility information. PostgreSQL can avoid visiting the heap only when the visibility map says the corresponding heap page is all-visible to active transactions. If that bit is not set, PostgreSQL visits the heap to check visibility, even when the index contains every column the query needs.

Vacuum can set all-visible bits after checking a page. Data changes clear the relevant bits. The map is deliberately conservative: an unset bit does not prove a row is invisible; it means PostgreSQL must check.

Worked example

Suppose an orders table has a B-tree index that stores the customer and date as search keys and the selected total as payload:

CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at DESC)
    INCLUDE (total);

This query’s filter and output columns are represented in the index:

SELECT created_at, total
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 20;

An illustrative plan from EXPLAIN (ANALYZE, BUFFERS) could contain:

Limit  (cost=0.42..12.34 rows=20 width=16)
       (actual time=0.035..0.081 rows=20 loops=1)
  ->  Index Only Scan using orders_customer_created_idx on orders
        (cost=0.42..123.45 rows=250 width=16)
        (actual time=0.034..0.078 rows=20 loops=1)
        Index Cond: ((customer_id = 42)
                     AND (created_at >= '2026-01-01 00:00:00+00'::timestamptz))
        Heap Fetches: 7
        Buffers: shared hit=18
Planning Time: 0.120 ms
Execution Time: 0.095 ms

The node is an index-only scan because it can obtain the required values from the index. Heap Fetches: 7 means it still visited heap tuples to check visibility for some entries. The output is illustrative; costs, counts, timings, and buffer totals vary with the actual data and execution.

Why heap fetches happen

• Recently changed pages: Inserts, updates, and deletes clear all-visible status for affected heap pages until vacuum can safely mark them again.
• Active transactions: Vacuum may be unable to mark pages all-visible while older snapshots or transactions can still need prior row versions.
• Mixed-age data: A query may touch older, stable pages and frequently updated pages. Some entries can avoid heap checks while others cannot.
• Index coverage is separate: The scan must have all values needed by the query in the index, but coverage alone does not establish heap visibility.

Autovacuum and manual vacuum behavior depend on workload and configuration. Do not run VACUUM or change vacuum settings solely to force a particular plan without checking the operational context.

Check the plan and the visibility map

1. Capture the complete plan with the exact SQL, parameter values, PostgreSQL version, table definition, and index definition.
2. Confirm the node says Index Only Scan, then inspect its Index Cond, any Filter, Heap Fetches, loops, and buffer counts.
3. Compare Heap Fetches with rows and loops at that node. It is a count of heap visibility checks, not a measure of elapsed time or proof that every output row needed a fetch.
4. Check table update activity and vacuum history. If installed and permitted, the pg_visibility extension can inspect visibility-map state; treat its output as a snapshot that can change with writes and vacuum.
5. Repeat the observation under representative workload and compare complete plans and latency distributions before and after any operational or index change.

Plain EXPLAIN shows the planned node but not actual heap-fetch counts. EXPLAIN (ANALYZE, BUFFERS) runs the query and reports actual execution details. Run it only where executing the SELECT is acceptable; it consumes resources, can contend with application work, and its timings may not match production conditions.

Review covering index tradeoffs

INCLUDE columns are payload, not search keys: they can supply output values but do not help locate matching entries as key columns do. Keep included columns narrow and limited to query needs. They duplicate table data in the index, increase index size and write work, and wide index tuples can make inserts fail if the access method’s maximum tuple size is exceeded. B-tree deduplication is also not used on indexes with included columns.

Do not add payload columns only because one plan reports heap fetches. First confirm which values the query needs, whether the index is genuinely useful across the workload, and whether visibility-map coverage is plausible for the pages it reads.

Verify a proposed change

1. Save the baseline query, plan, index definitions, and representative measurements.
2. Form one hypothesis: for example, a required output column is absent from the index, or the affected pages are frequently modified.
3. Test on representative data and workload. Compare result correctness, full plan shape, heap fetches, buffers, read latency, and write overhead.
4. Keep the change only if the evidence supports it for the relevant workload; recheck after ordinary vacuum and write activity.

Heap fetches are diagnostic evidence, not a target that must always be zero. PostgreSQL may choose a different plan when estimates and costs favor it.

Related guides

• How to read a PostgreSQL EXPLAIN plan
• Why PostgreSQL does not use an index

Primary sources

• PostgreSQL 18: Index-Only Scans and Covering Indexes
• PostgreSQL 18: Visibility Map
• PostgreSQL 18: Using EXPLAIN
• PostgreSQL 18: CREATE INDEX and INCLUDE
• PostgreSQL 16: Visibility Map

About this guide

TuningLens editorial team · Published 29 September 2026.

Product availability: TuningLens beta currently supports MySQL and MariaDB SELECT analysis only. PostgreSQL analysis is not available.