Understand why a PostgreSQL covering index still reads heap pages
Guepard Team
November 14, 2026
PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.
An index can contain every column a query needs and still require table access. The missing information is often visibility: whether the indexed tuple is visible to the current transaction snapshot. PostgreSQL's index-only scan can avoid heap visits when the visibility map establishes that the relevant heap page is all-visible. Column coverage alone does not establish that fact.
This distinction explains a common surprise. A covering index performs well after a load and vacuum, then the same query starts reporting heap fetches as updates arrive. The index definition has not changed. The table's visibility state has.
Build coverage and visibility separately
Use PostgreSQL 18 in a disposable database. Run VACUUM outside a transaction block. The example role must be allowed to maintain the table.
CREATE TABLE cover_order (
id bigint PRIMARY KEY,
account_id integer NOT NULL,
total numeric(10,2) NOT NULL,
note text NOT NULL DEFAULT ''
);
INSERT INTO cover_order(id, account_id, total)
SELECT g, g % 100, (g % 500)::numeric / 10
FROM generate_series(1,100000) AS s(g);
CREATE INDEX cover_order_lookup
ON cover_order(account_id, id) INCLUDE (total);
VACUUM (ANALYZE) cover_order;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, total
FROM cover_order
WHERE account_id = 42
ORDER BY id;The key columns support locating one account's rows and ordering by ID. total is payload: it can be returned from the index but is not a search-order key. An index-only scan is possible in principle because all referenced values are available. The planner still decides whether to choose it.
Capture the actual plan rather than assuming Heap Fetches: 0. Other sessions, transaction horizons, or table activity can affect what vacuum can establish. On PostgreSQL 18.6 the first plan reported Heap Fetches: 0. After the update below, the same query reported 2,000 heap fetches while returning the same 1,000 rows, and after the second vacuum it was back to 0.
Change visibility without changing the index keys
UPDATE cover_order
SET note = 'reviewed'
WHERE account_id = 42;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, total
FROM cover_order
WHERE account_id = 42
ORDER BY id;The updated column is not in the covering index. Nevertheless, the update changes row versions and page visibility. Depending on page space and other conditions, the update may qualify for HOT, but HOT does not mean the visibility map remains all-visible. The query can need heap checks even though it returns only indexed columns.
Run vacuum again and compare:
VACUUM (ANALYZE) cover_order;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, total FROM cover_order
WHERE account_id = 42 ORDER BY id;This sequence isolates a useful hypothesis: changing visibility state can alter heap-fetch behavior without changing the query or index definition. It does not isolate all timing variables, because repeated execution also changes cache state.
Reference access path. The visibility map is checked for the heap page; coverage supplies values, while visibility determines whether a heap check can be skipped.
Read Heap Fetches carefully
Heap fetches are not the same thing as distinct heap pages or physical storage reads. Multiple candidate tuples can cause accesses to the same page, and that page may already be cached. Use buffer counters and, where appropriately enabled, I/O timing alongside the heap-fetch count.
An index-only scan with some heap fetches can still be valuable. The relevant comparison is total work under the real workload, not whether one counter is exactly zero. Conversely, a zero-heap-fetch plan can still be slow if it scans a huge index range or returns too much data.
Visibility-map state is page-oriented. A small number of frequently updated rows spread across many pages can affect a much larger set of otherwise stable rows. A workload's physical layout therefore matters in addition to its overall update percentage.
Count the cost of included columns
Including a large payload increases index size and write maintenance. B-tree index tuples have size limits, so an oversized included value can cause insertion failures rather than merely making the index slower. Avoid treating INCLUDE as a license to copy the entire row into every index.
Updates to included columns also affect index maintenance and can interfere with HOT eligibility. A frequently changing total or status might be a poor payload candidate even if one read query benefits. The best covering index often contains a narrow, stable projection used by an important endpoint.
For a unique index, included columns do not participate in uniqueness. A unique index on an account identifier with an included display name enforces uniqueness of the key, not the key-payload combination. Keep that distinction explicit in schema review.
Turn the lab into a workload test
Measure an append-only historical table separately from a frequently updated operational table. Run the read workload during representative write churn, not only after a manual vacuum. Capture index size, update throughput, vacuum activity, query buffers, and latency distribution. A benchmark performed only in the most favorable visibility state can overstate the benefit.
If heap fetches remain high, first ask whether vacuum can progress and whether old snapshots retain versions. Making autovacuum more aggressive cannot always overcome a retained visibility horizon. If the table is intentionally hot, accepting some heap checks may be the correct tradeoff.
The durable mental model is two gates: the index must contain the required values, and the visibility map must let the executor trust visibility without visiting the heap. Passing the first gate creates the opportunity. The workload and maintenance lifecycle determine how often the second gate opens.
Sources and review scope
Guepard Team
Guepard Engineering
