检查 visibility map 覆盖率和 Index Only Scan 效率

命令

-- Tables with a low all-visible page ratio: Index Only Scans must visit the heap
SELECT c.oid::regclass AS table_name,
       c.relpages,
       c.relallvisible,
       round(100.0 * c.relallvisible / NULLIF(c.relpages, 0), 1) AS all_visible_pct,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
       s.n_ins_since_vacuum,
       s.last_autovacuum
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relpages > 1000
ORDER BY all_visible_pct NULLS FIRST
LIMIT 20;

-- If "Heap Fetches" in the plan is close to the returned rows, the visibility map is stale
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM customer WHERE id BETWEEN 1 AND 50000;

-- Exact count (pg_visibility extension)
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('customer');

作用

显示表的 all-visible 页面比例、自上次 vacuum 以来插入的行数，以及 Index Only Scan 访问堆（Heap Fetches）的频率。

适用场景

当 Index Only Scan 表现不佳时；用于插入密集/仅追加的表，以及验证覆盖索引（INCLUDE）设计时。

注意

relpages/relallvisible 是最近一次 VACUUM/ANALYZE 记录的估算值。只有 VACUUM 会设置 visibility map 位；每次 UPDATE/DELETE 都会清除受影响页面的位。对于频繁更新的表比例偏低时，请按表调低 autovacuum_vacuum_scale_factor。pg_visibility_map_summary 读取的是很小的 VM fork，而不是堆。