Separate covering values from visibility
An index-only scan becomes possible when the index can supply the required column values, but PostgreSQL must also establish MVCC visibility. A usable all-visible bit in the visibility map can allow the executor to avoid visiting the corresponding heap page. This procedure compares conditions before and after an update. It targets PostgreSQL 18 and is a reproduction procedure rather than a published transcript of a database run. Use a disposable local database, not a production table. Record actual plans in the environment where it is executed, because the presence of a suitable index does not force the planner to choose one particular path.
Prepare a table with a covering index
Create a temporary table in one session and insert twenty thousand rows. The index key is id and its included payload column supplies an output value. An included column is not an additional search key. Keep the selected columns identical across comparisons so the experiment does not silently become another query. Fillfactor leaves page space available but does not determine an exact heap-fetch count after updates. For small tables the planner may prefer a sequential scan. Record row count and statistics, inspect the actual path and avoid treating a feasible access method as a compulsory one.
Collect three comparable plans
Run VACUUM with ANALYZE, inspect the range query, update the selected rows and inspect the identical query again. Vacuum once more and collect a third plan. Execute VACUUM outside an explicit transaction block. EXPLAIN ANALYZE actually executes its statement; this example uses SELECT, but applying the same habit to a modification statement would change data. Do not label every diagnostic-looking command read-only. Record the node type together with Heap Fetches, when present, rather than extracting a number from a different plan shape. Query identity and measurement phase must remain explicit throughout the comparison.
-- PostgreSQL 18: use a disposable local database and one session.
CREATE TEMP TABLE lab_visibility (id integer, payload text) WITH (fillfactor=70);
INSERT INTO lab_visibility SELECT i, repeat('x', 40) FROM generate_series(1, 20000) AS i;
CREATE INDEX lab_visibility_cover ON lab_visibility(id) INCLUDE(payload);
VACUUM (ANALYZE) lab_visibility;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_visibility WHERE id BETWEEN 100 AND 110;
UPDATE lab_visibility SET payload=repeat('y',40) WHERE id BETWEEN 100 AND 110;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_visibility WHERE id BETWEEN 100 AND 110;
VACUUM (ANALYZE) lab_visibility;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_visibility WHERE id BETWEEN 100 AND 110;
Read Heap Fetches and BUFFERS as different units
Heap Fetches reports heap visits performed by an index-only scan for visibility or related needs. It is not the same unit as buffer hits or reads. A heap visit can find its page already in shared buffers without a physical disk read. Likewise, fewer heap fetches alone do not prove lower total query latency: returned rows, index and heap page counts, and cache state still matter. The key lesson is that a node named Index Only Scan can still perform heap accesses. Preserve the surrounding plan and buffer context rather than interpreting the access-method name as a promise of zero visits.
Consider why an updated page is no longer all-visible
After a page is modified, previously established all-visible information cannot simply be reused. Even after vacuuming, retained versions or snapshots can affect what state is attainable. A temporary table in one session also differs from a normal table used by many connections and automatic vacuum workers. If an observed result differs from the intended comparison, inspect long transactions, selected plan, statistics and the actual updated rows. Do not alter production settings merely to reproduce an illustrative number. The procedure is a way to investigate visibility conditions, not a guaranteed sequence of universal counters.
Connect the observation to an index decision
A covering index can reduce heap work, but storing more values increases index size and modification cost. Frequently changing an included payload needs a workload-based trade-off evaluation. This procedure establishes that availability of output values and proof of visibility are separate requirements. It does not show that one additional index improves every query. After testing, close the session containing the temporary table and retain plans and settings as the reproduction record. For a real design, assess representative reads, updates, storage demand and retained snapshots rather than choosing an index from its appealing access-method name alone.