ES007: Index-only scan with many heap fetches

• Signal: an Index Only Scan with heap fetches amounting to at least 25% of the rows returned and at least 100 in total, taking at least 10% of the runtime.
• Evidence: heap fetches and rows returned.
• Action: VACUUM the table to update its visibility map, and check that autovacuum keeps up with how often the table changes.

Example

The index_only_scan_heap_fetches scenario of the test corpus, captured on PostgreSQL 16: index-only scan on a table whose visibility map is out of date, so most rows need a heap fetch.

SELECT page FROM page_views WHERE page BETWEEN 10 AND 19;

Index Only Scan using page_views_page_idx on public.page_views  (cost=0.29..275.45 rows=2158 width=4) (actual time=0.399..1.224 rows=2000 loops=1)
  Output: page
  Index Cond: ((page_views.page >= 10) AND (page_views.page <= 19))
  Heap Fetches: 2200
  Buffers: shared hit=2060
Settings: enable_bitmapscan = 'off'
Planning:
  Buffers: shared hit=81
Planning Time: 0.350 ms
Execution Time: 1.311 ms

explainsql reports:

HIGH  ES007 Index-only scan with many heap fetches
Index Only Scan using page_views_page_idx on page_views visited the table 2,200 times for 2,000 rows: the visibility map of page_views is out of date.
Heap fetches: 2,200
Rows returned: 2,000
Time in the node: 1.22 ms (93% of the runtime)
→ VACUUM page_views to update its visibility map, and check that autovacuum keeps up with how often the table changes.

All rules