Prestaties en queryoptimalisatie in PostgreSQL · Les

De visibility map en index-only scans

Houd de visibility map actueel, zodat de planner index-only scans zonder heap fetches kan uitvoeren.

Les 3 van 413 stappen

De visibility map en index-only scans is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 3 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Prestaties en queryoptimalisatie in PostgreSQL. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Waarom indexscans de heap nog steeds aanraken

In PostgreSQL vindt een normale indexscan overeenkomende rijen in de index, maar de index alleen kan niet betrouwbaar bepalen of elke rij zichtbaar is voor uw transactie. MVCC slaat zichtbaarheidsinformatie (xmin/xmax) alleen op in de heap-tuple, niet in de indexvermelding.

Daarom moet de uitvoerder voor elke overeenkomst in de index een heap-ophaling uitvoeren om de zichtbaarheid te controleren.
Deze willekeurige toegangen tot de heap bepalen grotendeels de kosten van een indexscan, vooral bij een grote tabel.

De visibility map (VM) zorgt ervoor dat PostgreSQL deze heap-ophaling kan overslaan wanneer dat aantoonbaar veilig is, zodat een index-only-scan mogelijk wordt.

Wat de visibility map opslaat

De visibility map is een compacte bitmap die naast elke tabel wordt opgeslagen (in een _vm-fork). De map bevat twee bits per heap-pagina:

all-visible: elke tuple op de pagina is zichtbaar voor alle huidige en toekomstige transacties;
all-frozen: elke tuple op de pagina is bevroren (zodat pagina's kunnen worden overgeslagen tijdens een anti-wraparound-vacuum).

Voor index-only-scans is alleen de bit all-visible van belang. Als de bit all-visible van een heap-pagina is ingesteld, weet de planner dat elke tuple waarnaar op die pagina wordt verwezen zichtbaar is. De query kan dan alleen met de indexvermelding worden beantwoord.

Wie de bit all-visible instelt

De bit all-visible wordt ingesteld door VACUUM (inclusief autovacuum). Wanneer vacuum een heap-pagina verwerkt en vaststelt dat alle tuples voor iedereen zichtbaar zijn en er geen dode tuple hoeft te worden verwijderd, stelt het de bit all-visible voor die pagina in.

Invoegingen, updates en verwijderingen wissen de bit voor de betreffende pagina;
de bit wordt pas opnieuw ingesteld wanneer vacuum de pagina opnieuw bezoekt.

Gevolg: een tabel waarin vaak wordt geschreven maar zelden vacuum wordt uitgevoerd, heeft een verouderde visibility map. Index-only-scans veranderen dan stilzwijgend in gewone indexscans met heap-ophalingen.

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

VM-dekking controleren met pg_visibility

Met de extensie pg_visibility kunt u precies meten welk deel van een tabel als all-visible is gemarkeerd. Dit is de nuttigste afzonderlijke diagnose voor de gezondheid van index-only-scans.

pg_visibility_map_summary('tbl') retourneert aantallen all-visible- en all-frozen-pagina's;
vergelijk die aantallen met relpages om een dekkingsverhouding te berekenen.

Lage dekking op een tabel waarvan u verwacht dat die index-only-scans uitvoert, is een duidelijk alarmsignaal: de VM is verouderd en moet worden gevacuumd.

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

Vereisten voor een index-only-scan

Voordat de planner een index-only-scan kiest, moeten drie zaken op elkaar aansluiten:

de index moet elke kolom bevatten die de query nodig heeft (als indexsleutel of als INCLUDE-payload);
de query mag in SELECT, WHERE, ORDER BY enzovoort alleen naar die opgenomen kolommen verwijzen;
genoeg pagina's van de tabel moeten als all-visible zijn gemarkeerd, zodat de bespaarde heap-ophalingen opwegen tegen de indexscan.

Zelfs een perfect dekkende index valt terug op heap-ophalingen als de VM verouderd is. Dekking en actualiteit zijn beide noodzakelijk.

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

Het plan lezen: heap-ophalingen

Het bewijs dat de VM haar werk doet, vindt u in EXPLAIN (ANALYZE, BUFFERS). Een node voor een index-only-scan rapporteert een teller voor heap-ophalingen.

Heap-ophalingen: 0 betekent dat elke overeenkomende rij afkomstig was van een als all-visible gemarkeerde pagina — het ideale geval;
een groot aantal heap-ophalingen betekent dat veel pagina's niet all-visible waren, waardoor de scan toch de kosten van willekeurige heap-toegang betaalde.

Houd dit getal in de gaten na een grote schrijfpiek: het zal stijgen totdat de volgende vacuum de VM-bits opnieuw instelt.

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: een verouderde VM veroorzaakt heap-ophalingen

U kunt de verslechtering reproduceerbaar demonstreren. Voeg rijen in, voer de index-only-query uit en kijk hoe het aantal heap-ophalingen stijgt, omdat bij nieuw ingevoegde pagina's de bit all-visible is gewist.

Direct na de invoeging zijn de nieuwe pagina's niet all-visible, dus het aantal heap-ophalingen is groter dan 0;
na een expliciete VACUUM worden de bits opnieuw ingesteld en daalt het aantal heap-ophalingen weer naar 0.

Dit is precies de stille regressie die productietabellen met veel schrijfbewerkingen treft.

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;

Autovacuum afstemmen om de VM actueel te houden

De duurzame oplossing is autovacuum vaak genoeg te laten uitvoeren op drukke tabellen. De belangrijkste instellingen per tabel zijn:

autovacuum_vacuum_scale_factor — het fractieaandeel van de tabel dat moet veranderen voordat een vacuum wordt gestart. Verlaag deze waarde op grote tabellen met veel wijzigingen;
autovacuum_vacuum_threshold — een vaste ondergrens voor het aantal gewijzigde rijen;
autovacuum_vacuum_insert_scale_factor / _insert_threshold — deze instellingen zijn toegevoegd in PG13 en starten vacuum op tabellen met alleen invoegingen, die eerder nooit werden gevacuumd en daardoor nooit hun VM-bits ingesteld kregen.

Overschrijvingen per tabel via ALTER TABLE ... SET hebben de voorkeur boven globale wijzigingen.

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

De valkuil van tabellen met alleen invoegingen

Vóór PostgreSQL 13 waren tabellen waaraan alleen gegevens werden toegevoegd (logboeken, gebeurtenissen, tijdreeksen) een klassiek probleem voor index-only-scans: autovacuum wordt gestuurd door dode tuples, en zuivere invoegingen maken die niet aan. Daardoor werd vacuum nooit uitgevoerd en bleef de VM leeg.

Resultaat: index-only-scans op deze tabellen moesten altijd alle heap-ophalingen uitvoeren;
de insertgebaseerde autovacuum-triggers van PG13 hebben het standaardgedrag opgelost.

Op oudere versies is de oplossing een geplande VACUUM (bijvoorbeeld via cron), zodat de bits all-visible na elke batchlading worden ingesteld.

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

Lange transacties houden de VM gegijzeld

Zelfs agressief autovacuum kan een pagina niet als all-visible markeren als een oude transactie de tuples erop mogelijk nog moet zien of mogelijk heeft aangemaakt. Een langdurige transactie of een oude replicatieslot houdt de xmin-grens tegen.

Vacuum kan niet voorbij die grens gaan en kan daarom geen bits all-visible instellen voor recent gewijzigde pagina's;
symptoom: de VM-dekking blijft laag en het aantal heap-ophalingen blijft hoog, ongeacht hoe vaak u vacuum uitvoert.

Zoek sessies die inactief zijn binnen een transactie en verouderde replicatieslots op — ze zijn een veelvoorkomende verborgen oorzaak van mislukte 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;

De volledige cyclus controleren

Voeg de onderdelen samen tot een herhaalbare gezondheidscontrole voor elke tabel waarvan u verwacht dat die index-only-scans uitvoert:

controleer of er een dekkende index bestaat voor de veelgebruikte query;
meet de VM-dekking met pg_visibility_map_summary;
voer EXPLAIN (ANALYZE, BUFFERS) uit en controleer of het aantal heap-ophalingen laag is;
als de dekking laag is: stem autovacuum af, beëindig lange transacties of plan handmatige vacuums.

Het doel is een stabiele toestand waarin het aantal heap-ophalingen tussen vacuums dicht bij nul blijft, in plaats van na elke schrijfpiek te stijgen.

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

Snelle controle

Een index-only-scan op een schrijfintensieve tabel toont direct na een bulk-invoeging een hoog aantal Heap Fetches, hoewel er een volledig dekkende index bestaat. Wat is de meest directe oorzaak en oplossing?

Beantwoord dit in de interactieve les

Samenvatting

Index-only-scans zijn afhankelijk van de visibility map, niet alleen van het bestaan van een dekkende index.

Met de bit all-visible van de VM kan de uitvoerder de heap-ophaling overslaan; VACUUM stelt deze bit in en elke schrijfbewerking naar de pagina wist haar;
meet de actualiteit met pg_visibility_map_summary en controleer dit met Heap Fetches in EXPLAIN (ANALYZE, BUFFERS);
houd de VM actueel door autovacuum af te stemmen (inclusief insertgebaseerde triggers voor tabellen waaraan alleen gegevens worden toegevoegd) en door langdurige transacties en verouderde replicatieslots te verwijderen die de xmin-grens vastzetten.

Dekking en actualiteit samen zorgen ervoor dat het aantal heap-ophalingen nul blijft.

Gratis beginnen

Leer SQL met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 22
Lessen: 88

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “De visibility map en index-only scans” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “De visibility map en index-only scans”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Wat leer ik in “De visibility map en index-only scans”?

Houd de visibility map actueel, zodat de planner index-only scans zonder heap fetches kan uitvoeren. Je oefent met Prestaties en queryoptimalisatie in PostgreSQL door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met Prestaties en queryoptimalisatie in PostgreSQL te beginnen?

Ervaring vooraf is niet nodig. Prestaties en queryoptimalisatie in PostgreSQL op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 3 van 4.

Hoe lang duurt de les “De visibility map en index-only scans”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over Prestaties en queryoptimalisatie in PostgreSQL?

Ja. Elke les over Prestaties en queryoptimalisatie in PostgreSQL bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL