Heap Fetches in PostgreSQL EXPLAIN

What it does

An Index Only Scan tries to answer the query from the index without reading the table (the heap). An index entry doesn’t record whether its row is visible to your transaction, though, so for each entry PostgreSQL looks at the table’s visibility map: one bit per table page meaning “every row on this page is visible to every transaction”.

• Bit set: the value comes straight from the index.
• Bit not set: PostgreSQL reads the row from the table to check it. That’s a heap fetch.

Heap Fetches is the number of those table look-ups. VACUUM (manual or autovacuum) sets the bits; any insert, update or delete on a page clears that page’s bit until the next vacuum.

When you see it

On every Index Only Scan in EXPLAIN ANALYZE output, including when it’s 0. Plain EXPLAIN doesn’t show it.

Reading its numbers

Index Only Scan using readings_taken_at_idx on readings  (cost=0.42..392.54 rows=10106 width=8) (actual time=0.044..1.533 rows=10080.00 loops=1)
  Index Cond: (…)
  Heap Fetches: 10080

• Compare it with rows. Heap Fetches: 0 is the goal. Equal to rows means every row was checked in the table: an index scan in disguise.
• It can be higher than rows. The index still holds entries for old versions of updated or deleted rows until vacuum removes them. Each is checked in the table and found dead, so it counts as a fetch but not as a returned row.
• It’s a total, not per loop. Unlike rows and actual time, it isn’t divided by loops.
• Buffers grow with it. Each fetch can touch a table page, so a high count shows up as more shared hit or read on the same line.

When it’s a problem

When it’s a large share of rows on a scan you run often. The causes:

1. The table was loaded or changed recently and hasn’t been vacuumed. Run VACUUM <table> and check again. Since PostgreSQL 13, autovacuum also runs after enough inserts, so append-only tables get there on their own, but not straight after a bulk load.
2. The table changes faster than autovacuum visits it. Lower the threshold for that table, for example ALTER TABLE <table> SET (autovacuum_vacuum_scale_factor = 0.02), so it’s vacuumed after 2% of rows change instead of the default 20%.
3. A long-running transaction holds vacuum back. VACUUM can’t mark a page all-visible while a transaction that started before the change is still open. Look for old xact_start values in pg_stat_activity, often sessions in idle in transaction.

Check how much of the table is marked:

SELECT relpages, relallvisible FROM pg_class WHERE oid = '<table>'::regclass;

The planner reads relallvisible too: the fewer pages it expects to be all-visible, the more it charges for an index-only scan, and the sooner it picks something else.

Example

PostgreSQL 18.6. A table with autovacuum turned off, so we decide when it’s vacuumed:

SET search_path = seo_terms;

CREATE TABLE readings (
  id        bigint PRIMARY KEY,
  sensor_id int NOT NULL,
  taken_at  timestamptz NOT NULL,
  value     double precision NOT NULL
) WITH (autovacuum_enabled = false);
INSERT INTO readings
SELECT i, i % 500, timestamptz '2026-01-01' + i * interval '1 minute', (i % 1000) / 10.0
FROM generate_series(1, 200000) AS i;
CREATE INDEX readings_taken_at_idx ON readings (taken_at);
ANALYZE readings;
SET max_parallel_workers_per_gather = 0;

A week of timestamps needs only taken_at, which is in the index. Freshly loaded, no page is marked all-visible (relallvisible was 0 of 1,471 pages), so every row is checked in the table:

EXPLAIN (ANALYZE, BUFFERS)
SELECT taken_at FROM readings WHERE taken_at >= '2026-02-01' AND taken_at < '2026-02-08';

Index Only Scan using readings_taken_at_idx on readings  (cost=0.42..392.54 rows=10106 width=8) (actual time=0.044..1.533 rows=10080.00 loops=1)
  Index Cond: ((taken_at >= '2026-02-01 00:00:00+00'::timestamp with time zone) AND (taken_at < '2026-02-08 00:00:00+00'::timestamp with time zone))
  Heap Fetches: 10080
  Index Searches: 1
  Buffers: shared hit=75 read=31
Planning:
  Buffers: shared hit=16 read=1
Planning Time: 0.099 ms
Execution Time: 1.830 ms

After VACUUM readings (relallvisible 1,471 of 1,471):

Index Only Scan using readings_taken_at_idx on readings  (cost=0.42..314.54 rows=10106 width=8) (actual time=0.061..0.795 rows=10080.00 loops=1)
  Index Cond: ((taken_at >= '2026-02-01 00:00:00+00'::timestamp with time zone) AND (taken_at < '2026-02-08 00:00:00+00'::timestamp with time zone))
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=32
Planning:
  Buffers: shared hit=81
Planning Time: 0.272 ms
Execution Time: 1.159 ms

Same rows, 32 pages instead of 106. The planner’s cost fell from 392.54 to 314.54 because it now knows the pages are all-visible.

Then we updated 1% of the rows, spread through the table:

UPDATE readings SET value = value + 1 WHERE id % 100 = 0;   -- UPDATE 2000

Index Only Scan using readings_taken_at_idx on readings  (cost=0.42..320.60 rows=10209 width=8) (actual time=0.038..1.359 rows=10080.00 loops=1)
  Index Cond: ((taken_at >= '2026-02-01 00:00:00+00'::timestamp with time zone) AND (taken_at < '2026-02-08 00:00:00+00'::timestamp with time zone))
  Heap Fetches: 10181
  Index Searches: 1
  Buffers: shared hit=306
Planning Time: 0.057 ms
Execution Time: 1.675 ms

Updating one row in a hundred touched every page, which cleared every page’s bit, so all 10,080 rows were checked again. The extra 101 fetches are the old versions of the 101 updated rows in that week: the index still pointed at them, and each was looked up and found dead. Another VACUUM brought it back to Heap Fetches: 0.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step. Its Activity monitor lists sessions with their state, which helps find an idle in transaction session that’s holding vacuum back.