0Pricing
PostgreSQL Performance & Query Optimization · Aula

Mapa de visibilidade e varreduras somente por índice

Mantenha o mapa de visibilidade atualizado para que o planejador possa executar varreduras somente por índice sem buscar dados no heap.

Mapa de visibilidade e varreduras somente por índice é uma aula grátis de PostgreSQL Performance & Query Optimization no CoddyKit. Esta é a aula 3 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de PostgreSQL Performance & Query Optimization, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

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.

Perguntas Frequentes

A aula “Mapa de visibilidade e varreduras somente por índice” é grátis?

Sim — o texto completo de “Mapa de visibilidade e varreduras somente por índice” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de PostgreSQL Performance & Query Optimization, atualize para CoddyKit PRO. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

O que vou aprender em “Mapa de visibilidade e varreduras somente por índice”?

Mantenha o mapa de visibilidade atualizado para que o planejador possa executar varreduras somente por índice sem buscar dados no heap. Você pratica PostgreSQL Performance & Query Optimization com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar PostgreSQL Performance & Query Optimization?

Nenhuma experiência prévia é necessária. PostgreSQL Performance & Query Optimization no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 3 de 4.

Quanto tempo leva a aula “Mapa de visibilidade e varreduras somente por índice”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de PostgreSQL Performance & Query Optimization?

Sim. Cada aula de PostgreSQL Performance & Query Optimization inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Visibilidade de tuplas, xmin e xmax
  2. Atualizações HOT e cadeias de tuplas somente no heap
  3. Mapa de visibilidade e varreduras somente por índice
  4. Geração de WAL e amplificação de gravação
← Voltar para PostgreSQL Performance & Query Optimization