A covering index contains the values needed by a particular query. PostgreSQL can sometimes return those values through an index-only scan, avoiding heap visits for qualifying rows. The word “sometimes” matters: having the columns is a structural requirement, while visibility information determines whether execution can actually skip the heap. An Index Only Scan label alone does not prove zero table access.

Cover the actual projection

For a lookup by sku that returns price, a B-tree on sku with INCLUDE (price) stores price as payload. The sku key guides the search; the included price does not become another search key or establish an ordering by price. If the index is unique, uniqueness applies to its key columns, not to the included payload.

A later query change can invalidate the coverage assumption. Selecting a description column absent from the index requires another source for that value, usually the heap. Selecting every column casually can therefore turn a carefully covered lookup into a different access pattern. Start from the real predicate and projected columns, including expressions and visibility needs, rather than adding all table columns to a speculative index.

Not every access method can reconstruct complete values for index-only execution. B-tree supports it, while GIN generally stores extracted keys rather than the complete original value and does not support index-only scans. A matching index must support the needed operation as well as contain or reconstruct the requested data. Similar terminology across database engines does not imply identical implementation details.

The visibility map decides heap visits

PostgreSQL needs to know whether a candidate tuple is visible to the query’s MVCC snapshot. Index entries do not carry all the required visibility information. An index-only scan checks the heap page’s all-visible bit in the visibility map; when that bit is set, the value can be returned without fetching the heap tuple.

A write can clear the page’s all-visible status, making later scans visit the heap again even when their plan name remains unchanged. Vacuum can establish all-visible status when the page’s tuples meet the visibility requirements. Long-running transactions and ongoing changes affect that process. It is a page-level property, so an update to one row can influence the visibility check for another row on that page.

When it breaks

The common mistake is declaring success from EXPLAIN’s plan label. Read Heap Fetches and relevant buffers in EXPLAIN ANALYZE, and confirm the actual returned values. A write-heavy table may require frequent heap checks, reducing the read benefit while still paying for duplicated index payload. Repeating VACUUM after every application write is not a substitute for evaluating the workload.

Wide payload columns also consume space and cache, increase maintenance work, and can exceed index tuple limits. Updating an included column must maintain the index and can prevent a heap-only update that might otherwise have been possible. Include only values whose read benefit justifies their write and storage cost; a covering index is not a free materialized copy of the table.

Check it quickly

Save this as covering.sql and run psql -X -v ON_ERROR_STOP=1 -f covering.sql against a disposable PostgreSQL 18 database. Keep psql’s normal autocommit behavior: VACUUM cannot run inside an explicit transaction block. The temporary table remains confined to this session.

\set ON_ERROR_STOP on
CREATE TEMP TABLE products (sku integer, price integer);
INSERT INTO products SELECT g, g*10 FROM generate_series(1,10000) AS g;
CREATE INDEX products_price_lookup ON products (sku) INCLUDE (price);
VACUUM (ANALYZE) products;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT price FROM products WHERE sku=4321;
UPDATE products SET price=price+5 WHERE sku=4321;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT price FROM products WHERE sku=4321;
VACUUM (ANALYZE) products;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT price FROM products WHERE sku=4321;
DO $$ BEGIN
  IF (SELECT price FROM products WHERE sku=4321) IS DISTINCT FROM 43215 THEN
    RAISE EXCEPTION 'covering lookup returned an obsolete price';
  END IF;
END $$;
DROP TABLE products;

The first vacuum makes this quiescent fixture eligible for heap-free reads. PostgreSQL 18.6 used Index Only Scan in all three plans, with Heap Fetches zero, then two after the price update, then zero after vacuum. The assertion confirms the new price is 43215 rather than an obsolete indexed value. Temporary objects are removed explicitly and also disappear when the session ends.

The exact intermediate count can vary with implementation and page state; its significance is that heap access can occur under the same plan label. Heap Fetches counts heap visits, not necessarily physical disk reads: the pages may already be cached. This is a controlled visibility demonstration, not a guarantee that production vacuum will immediately make every page all-visible. Compare representative reads before and after real update activity when deciding whether coverage pays off.

Sources