PricingLoginStart free
PostgreSQL Performance & Query Optimization · Lezione

Visibility map e scansioni solo indice

Mantenga aggiornata la visibility map, così il planner può eseguire scansioni solo indice senza accedere all’heap.

Lezione 3 di 413 passaggi

Visibility map e scansioni solo indice è una lezione PostgreSQL Performance & Query Optimization gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento PostgreSQL Performance & Query Optimization, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Perché le scansioni degli indici toccano comunque l'heap

In PostgreSQL, una scansione normale di un indice trova le righe corrispondenti nell'indice, ma non può fidarsi del solo indice per sapere se ogni riga è visibile alla sua transazione. MVCC memorizza le informazioni sulla visibilità (xmin/xmax) solo nella tupla dell'heap, non nella voce dell'indice.

  • Perciò, per ogni corrispondenza nell'indice, l'executor deve eseguire un heap fetch per verificare la visibilità.
  • Questi accessi casuali all'heap determinano gran parte del costo di una scansione dell'indice, soprattutto su una tabella di grandi dimensioni.

La visibility map (VM) consente a PostgreSQL di saltare quell'accesso all'heap quando è dimostrabilmente sicuro farlo, abilitando una scansione solo indice.

Cosa memorizza la visibility map

La visibility map è una bitmap compatta memorizzata insieme a ogni tabella (in un fork _vm). Contiene due bit per ogni pagina dell'heap:

  • all-visible: ogni tupla della pagina è visibile a tutte le transazioni presenti e future.
  • all-frozen: ogni tupla della pagina è congelata (serve a saltare le pagine durante il vacuum anti-wraparound).

Per le scansioni solo indice conta soltanto il bit all-visible. Se il bit all-visible di una pagina dell'heap è impostato, il planner sa che qualsiasi tupla a cui punta su quella pagina è visibile, quindi può rispondere usando solo la voce dell'indice.

Chi imposta il bit all-visible

Il bit all-visible viene impostato da VACUUM (incluso l'autovacuum). Quando vacuum elabora una pagina dell'heap e rileva che tutte le tuple sono visibili a tutti e che non ci sono tuple morte da rimuovere, imposta il bit all-visible per quella pagina.

  • Inserimenti, aggiornamenti ed eliminazioni cancellano il bit della pagina interessata.
  • Il bit viene impostato nuovamente solo quando vacuum visita di nuovo la pagina.

Conseguenza: una tabella soggetta a scritture frequenti ma a vacuum rari avrà una visibility map non aggiornata e le scansioni solo indice peggioreranno silenziosamente fino a diventare normali scansioni degli indici con heap fetch.

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

Ispezionare la copertura della VM con pg_visibility

L'estensione pg_visibility consente di misurare esattamente quale parte di una tabella è contrassegnata come all-visible. È il diagnostico più utile per verificare lo stato delle scansioni solo indice.

  • pg_visibility_map_summary('tbl') restituisce il numero di pagine all-visible e all-frozen.
  • Confronti questi valori con relpages per ottenere un rapporto di copertura.

Una copertura bassa in una tabella che dovrebbe supportare scansioni solo indice è un chiaro segnale di allarme: la VM non è aggiornata e deve essere eseguito vacuum.

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

Requisiti per una scansione solo indice

Affinché il planner scelga una scansione solo indice, devono essere soddisfatte tre condizioni:

  • L'indice deve coprire ogni colonna richiesta dalla query (nella chiave dell'indice o come payload INCLUDE).
  • La query deve fare riferimento solo a quelle colonne coperte in SELECT, WHERE, ORDER BY e così via.
  • Un numero sufficiente di pagine della tabella deve essere contrassegnato come all-visible, in modo che gli heap fetch evitati compensino il costo della scansione dell'indice.

Anche un indice che copre perfettamente le colonne richieste ricorrerà agli heap fetch se la VM non è aggiornata. Copertura e aggiornamento sono entrambi necessari.

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

Leggere il piano: heap fetch

La prova che la VM sta funzionando si trova in EXPLAIN (ANALYZE, BUFFERS). Un nodo di scansione solo indice riporta un contatore Heap Fetches.

  • Heap Fetches: 0 significa che ogni riga corrispondente proveniva da una pagina contrassegnata come all-visible: è la situazione ideale.
  • Un numero elevato di Heap Fetches indica che molte pagine non erano all-visible, quindi la scansione ha comunque sostenuto il costo degli accessi casuali all'heap.

Controlli questo numero dopo un intenso picco di scritture: aumenterà fino a quando il vacuum successivo non reimposterà i bit della VM.

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

Dimostrazione: una VM non aggiornata causa heap fetch

Può riprodurre il peggioramento in modo deterministico. Inserisca le righe, esegua la query solo indice e osservi l'aumento di Heap Fetches, perché il bit all-visible delle pagine appena inserite è stato cancellato.

  • Subito dopo l'inserimento, le nuove pagine non sono all-visible, quindi Heap Fetches > 0.
  • Dopo un VACUUM esplicito, i bit vengono reimpostati e Heap Fetches torna a 0.

È esattamente il peggioramento silenzioso che colpisce le tabelle soggette a molte scritture in produzione.

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;

Ottimizzare l'autovacuum per mantenere aggiornata la VM

La soluzione duratura consiste nel fare in modo che l'autovacuum venga eseguito abbastanza spesso sulle tabelle soggette a molte modifiche. I principali parametri per tabella sono:

  • autovacuum_vacuum_scale_factor — frazione della tabella che deve cambiare prima che venga attivato un vacuum. Lo riduca sulle tabelle grandi e soggette a molte modifiche.
  • autovacuum_vacuum_threshold — soglia minima fissa di righe modificate.
  • autovacuum_vacuum_insert_scale_factor / _insert_threshold — introdotti in PG13, attivano vacuum sulle tabelle di sola inserzione, che in precedenza non venivano mai sottoposte a vacuum e quindi non avevano mai la VM impostata.

Le sostituzioni dei valori per tabella tramite ALTER TABLE ... SET sono preferibili alle modifiche globali.

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

Il problema delle tabelle di sola inserzione

Prima di PostgreSQL 13, le tabelle append-only (log, eventi, serie temporali) erano una causa classica di malfunzionamento delle scansioni solo indice: l'autovacuum è attivato dalle tuple morte, mentre gli inserimenti puri non ne creano, quindi vacuum non veniva mai eseguito e la VM rimaneva vuota.

  • Risultato: le scansioni solo indice su queste tabelle eseguivano sempre tutti gli heap fetch.
  • I trigger dell'autovacuum basati sugli inserimenti introdotti in PG13 hanno corretto il comportamento predefinito.

Nelle versioni precedenti, la soluzione consiste nell'eseguire VACUUM secondo una pianificazione (ad esempio tramite cron), così che i bit all-visible vengano impostati dopo ogni caricamento batch.

-- 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

Le transazioni lunghe tengono in ostaggio la VM

Anche un autovacuum aggressivo non può contrassegnare una pagina come all-visible se una vecchia transazione potrebbe ancora dover vedere le tuple presenti su di essa o potrebbe averle create. Una transazione di lunga durata o uno slot di replica obsoleto mantiene arretrato l'orizzonte xmin.

  • Vacuum non può oltrepassare quell'orizzonte, quindi non può impostare i bit all-visible per le pagine modificate di recente.
  • Sintomo: la copertura della VM rimane bassa e Heap Fetches rimane alto indipendentemente dalla frequenza del vacuum.

Individui le sessioni inattive durante una transazione e gli slot di replica obsoleti: sono una causa nascosta comune del fallimento delle scansioni solo indice.

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

Verificare l'intero ciclo

Riunisca tutti gli elementi in un controllo dello stato ripetibile per ogni tabella che dovrebbe supportare scansioni solo indice:

  • Verifichi che esista un indice di copertura per la query principale.
  • Misuri la copertura della VM con pg_visibility_map_summary.
  • Esegua EXPLAIN (ANALYZE, BUFFERS) e verifichi che Heap Fetches sia basso.
  • Se la copertura è bassa: ottimizzi l'autovacuum, termini le transazioni lunghe o pianifichi vacuum manuali.

L'obiettivo è uno stato stabile in cui Heap Fetches rimanga vicino a zero tra un vacuum e l'altro, invece di aumentare dopo ogni picco di scritture.

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

Verifica rapida

Una scansione solo indice su una tabella soggetta a molte scritture mostra un numero elevato di Heap Fetches subito dopo un inserimento massivo, anche se esiste un indice che copre completamente le colonne richieste. Qual è la causa e la correzione più dirette?

Rispondi nella lezione interattiva

Riepilogo

Le scansioni solo indice dipendono dalla visibility map, non soltanto dalla presenza di un indice di copertura.

  • Il bit all-visible della VM consente all'executor di saltare l'heap fetch; viene impostato da VACUUM e cancellato da qualsiasi scrittura nella pagina.
  • Misuri l'aggiornamento con pg_visibility_map_summary e verifichi il risultato con Heap Fetches in EXPLAIN (ANALYZE, BUFFERS).
  • Mantenga aggiornata la VM ottimizzando l'autovacuum (inclusi i trigger basati sugli inserimenti per le tabelle append-only) ed eliminando le transazioni di lunga durata e gli slot di replica obsoleti che bloccano l'orizzonte xmin.

È la combinazione di copertura e aggiornamento a mantenere Heap Fetches a zero.

Gratis per iniziare

Impara SQL con un tutor IA — gratis

Scrivi ed esegui vero codice nel tuo browser, ricevi aiuto istantaneo da un tutor IA disponibile 24/7, e riprendi da dove hai lasciato sul web o nell'app.

Corsi
22
Lezioni
88
Inizia a imparare gratisVisualizza il corso completo

Domande Frequenti

La lezione «Visibility map e scansioni solo indice» è gratuita?

Sì — il testo completo di «Visibility map e scansioni solo indice» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso PostgreSQL Performance & Query Optimization, passa a CoddyKit PRO. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Cosa imparerò in «Visibility map e scansioni solo indice»?

Mantenga aggiornata la visibility map, così il planner può eseguire scansioni solo indice senza accedere all’heap. Eserciti PostgreSQL Performance & Query Optimization con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare PostgreSQL Performance & Query Optimization?

Non è richiesta alcuna esperienza precedente. PostgreSQL Performance & Query Optimization su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Visibility map e scansioni solo indice»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione PostgreSQL Performance & Query Optimization?

Sì. Ogni lezione PostgreSQL Performance & Query Optimization include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Visibilità delle tuple, xmin e xmax
  2. Aggiornamenti HOT e catene di tuple solo heap
  3. Visibility map e scansioni solo indice
  4. Generazione di WAL e write amplification
← Torna a PostgreSQL Performance & Query Optimization