PostgreSQL Performance & Query Optimization · 课时

可见性映射与仅索引扫描

及时更新可见性映射，使规划器能够执行仅索引扫描而无需读取堆元组。

可见性映射与仅索引扫描 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why Index Scans Still Touch the Heap

In PostgreSQL, a normal index scan finds matching rows in the index, but it cannot trust the index alone to know whether each row is visible to your transaction. MVCC stores visibility information (xmin/xmax) only in the heap tuple, not in the index entry.

So for every index match, the executor must do a heap fetch to check visibility.
These random heap accesses dominate the cost of an index scan, especially on a large table.

The visibility map (VM) exists to let PostgreSQL skip that heap fetch when it is provably safe, enabling an index-only scan.

What the Visibility Map Stores

The visibility map is a compact bitmap stored alongside each table (in a _vm fork). It holds two bits per heap page:

all-visible: every tuple on the page is visible to all current and future transactions.
all-frozen: every tuple on the page is frozen (used to skip pages during anti-wraparound vacuum).

For index-only scans only the all-visible bit matters. If a heap page's all-visible bit is set, the planner knows any tuple it points to on that page is visible, so it can answer from the index entry alone.

Who Sets the All-Visible Bit

The all-visible bit is set by VACUUM (including autovacuum). When vacuum processes a heap page and finds that all tuples are visible to everyone and there is no dead tuple needing removal, it sets the all-visible bit for that page.

Inserts, updates, and deletes clear the bit for the affected page.
The bit only gets set again when vacuum revisits the page.

Consequence: a table that is written frequently but vacuumed rarely will have a stale visibility map, and index-only scans will silently degrade into ordinary index scans with heap fetches.

-- Force a vacuum so the VM bits get set for an existing table
VACUUM (VERBOSE) orders;

-- See how many heap pages are currently marked all-visible / all-frozen
SELECT relname,
       relpages,
       pg_relation_size(oid) AS heap_bytes
FROM pg_class
WHERE relname = 'orders';

Inspecting VM Coverage with pg_visibility

The pg_visibility extension lets you measure exactly how much of a table is marked all-visible. This is the single most useful diagnostic for index-only-scan health.

pg_visibility_map_summary('tbl') returns counts of all-visible and all-frozen pages.
Compare those counts against relpages to get a coverage ratio.

Low coverage on a table you expect to serve index-only scans is the red flag: the VM is stale and needs vacuuming.

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT c.relname,
       c.relpages,
       v.all_visible,
       v.all_frozen,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

Requirements for an Index-Only Scan

For the planner to choose an index-only scan, three things must line up:

The index must cover every column the query needs (in the index key or as an INCLUDE payload).
The query must reference only those covered columns in SELECT, WHERE, ORDER BY, etc.
Enough of the table's pages must be marked all-visible so the saved heap fetches outweigh the index scan.

Even a perfectly covering index will fall back to heap fetches if the VM is stale. Coverage and freshness are equally necessary.

-- A covering index for: SELECT customer_id, status WHERE customer_id = ?
CREATE INDEX idx_orders_cust_status
    ON orders (customer_id) INCLUDE (status);

Reading the Plan: Heap Fetches

The proof that the VM is doing its job is in EXPLAIN (ANALYZE, BUFFERS). An index-only scan node reports a Heap Fetches counter.

Heap Fetches: 0 means every matching row came from a page marked all-visible — the ideal case.
A large Heap Fetches count means many pages were not all-visible, so the scan paid the random-heap cost anyway.

Watch this number after a heavy write burst: it will spike until the next vacuum re-sets the VM bits.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customer_id, status
FROM orders
WHERE customer_id = 42;

-- Look for:
--   Index Only Scan using idx_orders_cust_status on orders
--     Heap Fetches: 0

Demo: Stale VM Causes Heap Fetches

You can reproduce the degradation deterministically. Insert rows, run the index-only query, and watch Heap Fetches jump because the freshly inserted pages have their all-visible bit cleared.

Right after the insert, the new pages are not all-visible, so Heap Fetches > 0.
After an explicit VACUUM, the bits are re-set and Heap Fetches drops back to 0.

This is exactly the silent regression that hits write-heavy tables in production.

INSERT INTO orders (customer_id, status)
SELECT 42, 'NEW' FROM generate_series(1, 50000);

-- Heap Fetches will be high here (new pages not all-visible)
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

VACUUM orders;

-- Now Heap Fetches should be back near 0
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

Tuning Autovacuum to Keep the VM Fresh

The durable fix is to make autovacuum run often enough on hot tables. The key per-table knobs:

autovacuum_vacuum_scale_factor — fraction of the table that must change before a vacuum is triggered. Lower it on large, churny tables.
autovacuum_vacuum_threshold — a flat floor of changed rows.
autovacuum_vacuum_insert_scale_factor / _insert_threshold — added in PG13, these trigger vacuums on insert-only tables, which previously never got vacuumed and so never had their VM set.

Per-table overrides via ALTER TABLE ... SET are preferred over global changes.

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_vacuum_insert_threshold = 1000
);

The Insert-Only Table Trap

Before PostgreSQL 13, append-only tables (logs, events, time-series) were a classic index-only-scan failure: autovacuum is driven by dead tuples, and pure inserts create none, so vacuum never ran and the VM stayed empty.

Result: index-only scans on these tables always paid full heap fetches.
PG13's insert-based autovacuum triggers fixed the default behavior.

On older versions, the workaround is a scheduled VACUUM (e.g. via cron) so the all-visible bits get set after each batch load.

-- Pre-PG13 workaround: vacuum the append-only table after each batch load
-- (run on a schedule)
VACUUM (FREEZE) events;

-- FREEZE also sets all-frozen bits, helping anti-wraparound vacuum later

Long Transactions Hold the VM Hostage

Even aggressive autovacuum cannot mark a page all-visible if some old transaction might still need to see (or might have created) tuples on it. A long-running transaction or an old replication slot holds back the xmin horizon.

Vacuum cannot advance past that horizon, so it cannot set all-visible bits for recently changed pages.
Symptom: VM coverage stays low and Heap Fetches stay high no matter how often you vacuum.

Hunt down idle-in-transaction sessions and stale replication slots — they are a common hidden cause of failed index-only scans.

-- Find the oldest transaction holding back the xmin horizon
SELECT pid,
       state,
       now() - xact_start AS xact_age,
       backend_xmin,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Verifying the Whole Loop

Put the pieces together into a repeatable health check for any table you expect to serve index-only scans:

Confirm a covering index exists for the hot query.
Measure VM coverage with pg_visibility_map_summary.
Run EXPLAIN (ANALYZE, BUFFERS) and check Heap Fetches is low.
If coverage is low: tune autovacuum, kill long transactions, or schedule manual vacuums.

The goal is a stable state where Heap Fetches stays near zero between vacuums rather than spiking after every write burst.

-- One-shot coverage + size snapshot for a candidate table
SELECT c.relname,
       c.relpages,
       v.all_visible,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS n_indexes
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

Quick Check

An index-only scan on a write-heavy table shows a high Heap Fetches count right after a bulk insert, even though a fully covering index exists. What is the most direct cause and fix?

Recap

Index-only scans depend on the visibility map, not just on having a covering index.

The VM's all-visible bit lets the executor skip the heap fetch; it is set by VACUUM and cleared by any write to the page.
Measure freshness with pg_visibility_map_summary and verify with Heap Fetches in EXPLAIN (ANALYZE, BUFFERS).
Keep the VM fresh by tuning autovacuum (including insert-based triggers for append-only tables) and by eliminating long-running transactions and stale replication slots that pin the xmin horizon.

Coverage plus freshness together are what keep Heap Fetches at zero.

常见问题解答

「可见性映射与仅索引扫描」课时是免费的吗？

是的 — 「可见性映射与仅索引扫描」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「可见性映射与仅索引扫描」这节课中我会学到什么？

及时更新可见性映射，使规划器能够执行仅索引扫描而无需读取堆元组。你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「可见性映射与仅索引扫描」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization