Test Case
mytest=# \dt pgbench_accounts;
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | donghual
(1 row)mytest=# delete from pgbench_accounts where aid=1;
DELETE 1
Time: 16.197 ms
Observation 1
mytest=# explain (buffers,analyze) select aid from pgbench_accounts where aid=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..8.31 rows=1 width=4) (actual time=1.692..1.693 rows=0 loops=1)
Index Cond: (aid = 1)
Heap Fetches: 1
Buffers: shared hit=3 read=1 dirtied=1
Planning Time: 0.079 ms
Execution Time: 1.758 ms
(6 rows)
Time: 5.305 ms
Observation 2
mytest=# explain (buffers,analyze) select aid from pgbench_accounts where aid=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.29..8.31 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: (aid = 1)
Heap Fetches: 0
Buffers: shared hit=2
Planning Time: 0.056 ms
Execution Time: 0.066 ms
(6 rows)
Time: 0.414 ms
Why the first execution shows "Heap Fetches: 1" but the second shows "Heap Fetches: 0"
The difference relates to how PostgreSQL's Index Only Scan works and the visibility information in the index:
First execution (Heap Fetches: 1):
• You just deleted the row with aid=1
• When PostgreSQL performs an Index Only Scan, it first checks the index • The index entry for aid=1 was still present, but PostgreSQL needed to verify if this row was visible ( not deleted)
• Since the visibility information wasn't current in the index's visibility map, PostgreSQL had to fetch the heap (table) page to check if the row was actually visible
• That's why you see "Heap Fetches: 1" - it had to check the actual table data
• You also see "Buffers: shared hit=3 read=1 dirtied=1" showing more I/O activity
Second execution (Heap Fetches: 0):
• By this time, PostgreSQL had updated its visibility information
• The system now knew that the row with aid=1 was deleted
• The index's visibility map was updated to reflect this
• When executing the same query again, PostgreSQL could determine directly from the index that no visible rows exist with aid=1
• No need to check the heap (table) data, hence "Heap Fetches: 0"
• You also see reduced buffer activity: "Buffers: shared hit=2"
This is one of the key optimizations in PostgreSQL's Index Only Scans. The first query after a data modification often needs to do heap fetches to verify visibility, but subsequent queries can avoid these fetches once the visibility map is updated.
The execution time difference (1.758ms vs 0.066ms) clearly shows the performance benefit of avoiding those heap fetches.
