0Pricing
PostgreSQL Performance & Query Optimization · 강의

가시성 맵과 인덱스 전용 스캔

플래너가 힙을 가져오지 않고 인덱스 전용 스캔을 제공할 수 있도록 가시성 맵을 최신 상태로 유지하는 방법을 배웁니다.

가시성 맵과 인덱스 전용 스캔은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 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.

자주 묻는 질문

“가시성 맵과 인덱스 전용 스캔” 강의는 무료인가요?

네 — “가시성 맵과 인덱스 전용 스캔” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“가시성 맵과 인덱스 전용 스캔”에서 뭘 배우나요?

플래너가 힙을 가져오지 않고 인덱스 전용 스캔을 제공할 수 있도록 가시성 맵을 최신 상태로 유지하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“가시성 맵과 인덱스 전용 스캔” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 튜플 가시성, xmin과 xmax
  2. HOT UPDATE와 힙 전용 튜플 체인
  3. 가시성 맵과 인덱스 전용 스캔
  4. WAL 생성과 쓰기 증폭
← PostgreSQL Performance & Query Optimization(으)로 돌아가기