DBAtlas · Database operations atlas

Check visibility map coverage and Index Only Scan efficiency

PostgreSQLMaintenance

Command

-- 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');

What it does

Shows the all-visible page ratio of tables, rows inserted since the last vacuum, and how often an Index Only Scan had to visit the heap (Heap Fetches).

When to use it

When an Index Only Scan underperforms; on insert-heavy/append-only tables and when validating a covering (INCLUDE) index design.

Caution

relpages/relallvisible are estimates recorded by the last VACUUM/ANALYZE. Only VACUUM sets visibility map bits; every UPDATE/DELETE clears the bit of the affected page. For a low ratio on frequently updated tables, lower autovacuum_vacuum_scale_factor per table. pg_visibility_map_summary reads the small VM fork, not the heap.

Open in DBAtlas →

Related