UITLEG ANALYSE: Lees en optimaliseer PostgreSQL-queryplannen
Een query die in productie 8 seconden duurt en vaak kan worden geoptimaliseerd tot 50 ms met de juiste index of
een beetje herschrijven. EXPLAIN ANALYZE en de tool die precies onthult waarom
PostgreSQL voert een query op een bepaalde manier uit: hoeveel rijen worden gescand, welke indexen worden gebruikt, waar wordt geld aan uitgegeven
90% van de tijd. Het leren lezen van queryplannen is de meest impactvolle vaardigheid voor een PostgreSQL-ontwikkelaar.
Wat je gaat leren
- Lees de uitvoer van EXPLAIN ANALYSE en identificeer de dure knooppunten
- Verschil tussen sequentiële scan, indexscan en alleen-indexscan
- Wanneer PostgreSQL Nested Loop, Hash Join of Merge Join kiest
- Hoe u onjuiste plannerschattingen kunt corrigeren met bijgewerkte statistieken
- Gebruik
auto_explainom langzame zoekopdrachten automatisch op te vangen
De structuur van een queryplan
Een queryplan en een boom met bewerkingen: elk knooppunt vertegenwoordigt een bewerking (scannen, meedoen, sorteren) en onderliggende knooppunten leveren gegevens aan het bovenliggende knooppunt. PostgreSQL voert de boomstructuur van onder naar boven uit.
-- EXPLAIN ANALYZE: aggiunge il tempo reale e le righe effettive
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
o.id AS ordine_id,
u.email,
SUM(ol.quantita * ol.prezzo_unitario) AS totale
FROM ordini o
JOIN utenti u ON u.id = o.utente_id
JOIN ordini_linee ol ON ol.ordine_id = o.id
WHERE o.stato = 'completato'
AND o.creato_il >= '2026-01-01'
GROUP BY o.id, u.email
ORDER BY totale DESC
LIMIT 100;
Typische uitvoer (geannoteerd):
Limit (cost=45823.12..45823.37 rows=100 width=48)
(actual time=892.341..892.363 rows=100 loops=1)
-> Sort (cost=45823.12..46073.12 rows=100000 width=48)
(actual time=892.340..892.350 rows=100 loops=1)
Sort Key: (sum((ol.quantita * ol.prezzo_unitario))) DESC
Sort Method: top-N heapsort Memory: 32kB
-> HashAggregate (cost=40000.00..42500.00 rows=100000 ...)
(actual time=820.123..880.234 rows=98432 loops=1)
Group Key: o.id, u.email
Batches: 5 Memory Usage: 4096kB Disk Usage: 1024kB <- PROBLEMA!
-> Hash Join (cost=1200.00..35000.00 rows=500000 ...)
(actual time=45.123..650.234 rows=512000 loops=1)
Hash Cond: (ol.ordine_id = o.id)
-> Seq Scan on ordini_linee ol <- Sequential Scan!
(actual time=0.012..280.123 rows=2100000 loops=1)
-> Hash (cost=800.00..800.00 rows=32000 ...)
(actual time=44.123..44.123 rows=31845 loops=1)
Buckets: 32768 Batches: 1 Memory: 2048kB
-> Hash Join (cost=100.00..800.00 ...)
(actual time=2.123..38.123 ...)
Hash Cond: (o.utente_id = u.id)
-> Index Scan using idx_ordini_stato_data on ordini o
(actual time=0.234..15.123 rows=31845 loops=1)
Index Cond: ((stato = 'completato') AND ...)
-> Hash (cost=50.00..50.00 rows=4000 ...)
...
Planning Time: 3.456 ms
Execution Time: 892.987 ms
Lees de belangrijkste statistieken
Kosten: schatting versus realiteit
Elk knooppunt toont twee paar waarden: (cost=startup..total rows=stima width=N) Ik ben
le schattingen van planners, Terwijl (actual time=startup..total rows=reali loops=N)
het zijn de waarden meet jezelf echt. Wanneer er een grote discrepantie bestaat tussen schatting en werkelijkheid,
de planner beschikt over onjuiste informatie over statistieken.
-- Discrepanza grave: stima 1000 righe, realta 500000
-- Questo causa scelta errata del tipo di join e degli indici
--
-- (cost=... rows=1000 ...) <- planner si aspettava 1000 righe
-- (actual ... rows=500000 ...) <- ma ne ha trovate 500000
--
-- Soluzione: aggiornare le statistiche
ANALYZE VERBOSE ordini_linee;
-- Per controllare le statistiche attualmente in uso
SELECT
tablename,
attname,
n_distinct,
correlation,
most_common_vals,
most_common_freqs
FROM pg_stats
WHERE tablename = 'ordini_linee'
AND attname IN ('ordine_id', 'stato');
Seq-scan versus indexscan versus alleen-indexscan
Het type scan heeft een enorme impact op de prestaties. PostgreSQL kiest op basis van type aan de geschatte selectiviteit en tabelgrootte.
-- Quando PostgreSQL sceglie Sequential Scan nonostante un indice esista:
-- 1. Troppe righe restituite (>5-10% della tabella): seq scan e piu veloce
-- 2. Statistiche obsolete: il planner crede ci siano poche righe
-- 3. work_mem insufficiente per materializzare il result set
-- 4. L'indice non copre le colonne del WHERE
-- Forzare l'Index Scan per debugging (NON usare in produzione)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = on;
-- Index-Only Scan: il piu efficiente (nessun accesso alla tabella heap)
-- Richiede che tutte le colonne nel SELECT siano nell'indice
-- E che le pagine siano nel visibility map (dopo autovacuum)
-- Esempio: creare un covering index per abilitare index-only scan
CREATE INDEX idx_ordini_covering
ON ordini (stato, creato_il)
INCLUDE (utente_id, totale); -- colonne extra non usate nel filtro
-- Verificare se il visibility map e aggiornato
VACUUM ordini; -- o aspettare autovacuum
Het identificeren van kostbare knooppunten
De snelste manier om het knelpunt te vinden is door te gebruiken FORMAT JSON met gereedschap
visueel of zoek handmatig naar het knooppunt met actual time groter.
-- Usare EXPLAIN con FORMAT JSON e pgMustard / explain.dalibo.com
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;
-- Pattern da cercare nell'output:
-- 1. "Disk Usage" > 0 in HashAggregate o Sort: work_mem troppo basso
-- 2. "Rows Removed by Filter" molto alto: indice mancante o inefficiente
-- 3. "loops" > 1 su Seq Scan: Nested Loop con tabella grande
-- 4. Stima vs reale molto diversa: statistiche obsolete
-- Calcolo del "costo effettivo" di un nodo:
-- actual_total_time * loops = tempo totale del nodo
-- Se un nodo con loops=50000 ha actual_time=0.05ms,
-- il costo totale e 2500ms!
Soorten joins en wanneer u ze moet gebruiken
Geneste lusverbinding
Herhaal elke rij van de "buitenste" tabel en kijk voor elke rij in de "binnenste" tabel. Uitstekend als de binnenkant klein en geïndexeerd is. Rampzalig als beide tafels groot zijn.
-- Nested Loop: ideale per join su PK con pochi record
-- outer: 100 ordini recenti -> inner: users con indice su id
-- Costo: 100 * O(log N) = molto efficiente
-- Quando diventa un problema:
-- outer: 50.000 righe -> inner: 50.000 righe -> 2.5 miliardi di confronti!
-- Il planner dovrebbe preferire Hash Join in questo caso
-- Se vedi Nested Loop su tabelle grandi, verifica:
-- 1. Statistiche: il planner credeva di trovare poche righe
-- 2. join_collapse_limit: aumentare se il planner non esplora tutte le alternative
-- 3. enable_nestloop = off per testare se Hash/Merge Join sarebbe meglio
SET enable_nestloop = off;
EXPLAIN ANALYZE ...;
SET enable_nestloop = on;
Hash Doe mee
Creëert een hashtabel uit de kleinere tabel en scant vervolgens de grotere op zoek naar de correspondenties. Efficiënt voor joins op middelgrote/grote tabellen zonder bruikbare indexen.
-- Hash Join con spill to disk (Batches > 1): aumentare work_mem
-- "Batches: 5 Memory Usage: 4096kB Disk Usage: 1024kB"
-- significa che la hash table non entra in memoria
-- Calcolare la work_mem necessaria
-- La hash table occupa circa: righe * larghezza_media * 2
-- Aumentare per sessione specifica (non globalmente)
SET work_mem = '256MB';
EXPLAIN ANALYZE ...;
SET work_mem = DEFAULT;
-- Oppure aumentare solo per le query analitiche
ALTER ROLE analyst_user SET work_mem = '256MB';
Samenvoegen Doe mee
Vereist dat beide invoer wordt gesorteerd op de join-sleutel. PostgreSQL kiest wanneer beide tabellen hebben indexen die de volgorde garanderen.
Plannerschattingen corrigeren
Onjuiste schattingen zijn de meest voorkomende oorzaak van suboptimale queryplannen. Hier zijn de technieken om ze te corrigeren.
-- 1. Aumentare il target di statistiche per colonne ad alta cardinalita
ALTER TABLE ordini ALTER COLUMN stato SET STATISTICS 500; -- default: 100
ANALYZE ordini;
-- 2. Creare statistiche estese per colonne correlate
-- (PostgreSQL non sa che stato='completato' implica certi range di date)
CREATE STATISTICS stat_ordini_stato_data (dependencies, ndistinct)
ON stato, creato_il FROM ordini;
ANALYZE ordini;
-- 3. Verificare le statistiche con pg_statistic_ext
SELECT
stxname,
stxkeys::int[] AS colonne,
stxdependencies,
stxndistinct
FROM pg_statistic_ext
JOIN pg_statistic_ext_data ON pg_statistic_ext_data.stxoid = pg_statistic_ext.oid
WHERE stxrelid = 'ordini'::regclass;
-- 4. Forzare il re-planning disabilitando la plan cache
-- (utile per query parametriche con distribuzione skewed)
SET plan_cache_mode = force_generic_plan;
auto_explain: Vang trage zoekopdrachten automatisch op
In productie kunt u EXPLAIN niet handmatig uitvoeren voor elke query. auto_explain
registreert automatisch het plan van zoekopdrachten die een tijdsdrempel overschrijden.
-- Abilitare auto_explain in postgresql.conf
# shared_preload_libraries = 'auto_explain' # richiede riavvio
# auto_explain parametri
auto_explain.log_min_duration = '500ms' # logga query > 500ms
auto_explain.log_analyze = true # mostra i tempi reali
auto_explain.log_buffers = true # mostra I/O
auto_explain.log_timing = true # timing dettagliato
auto_explain.log_triggers = false # non loggare trigger
auto_explain.log_verbose = false # output compatto
auto_explain.log_format = 'text' # json per tools
-- In alternativa: abilitare per sessione (senza riavvio)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = true;
-- Ora ogni query > 200ms nel log di PostgreSQL includera il piano
-- Cercare in /var/log/postgresql/postgresql-17-main.log:
-- duration: 892.987 ms plan:
-- ...Seq Scan on ordini_linee (actual time=0.012..280.123)...
Praktische optimalisatieworkflow
Hier ziet u het systematische proces voor het optimaliseren van een langzame zoekopdracht die tijdens de productie is geïdentificeerd.
-- STEP 1: Identificare la query con pg_stat_statements
SELECT
LEFT(query, 100) AS q,
calls,
ROUND(mean_exec_time::numeric, 1) AS mean_ms,
ROUND(max_exec_time::numeric, 1) AS max_ms,
ROUND(total_exec_time::numeric / 1000, 1) AS total_s
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;
-- STEP 2: Ottenere il piano con EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ;
-- STEP 3: Identificare il nodo piu costoso
-- Cercare: actual time alto, Seq Scan su tabelle grandi, Batches > 1
-- STEP 4: Verificare gli indici esistenti
SELECT
indexname,
indexdef,
pg_relation_size(indexrelid) AS size_bytes
FROM pg_indexes
WHERE tablename = 'ordini_linee';
-- STEP 5: Creare l'indice mancante
-- CONCURRENTLY: non blocca le scritture durante la creazione
CREATE INDEX CONCURRENTLY idx_ordini_linee_ordine_id
ON ordini_linee (ordine_id)
INCLUDE (quantita, prezzo_unitario);
-- STEP 6: Verificare che il nuovo piano usi l'indice
EXPLAIN (ANALYZE, BUFFERS)
SELECT ol.ordine_id, SUM(ol.quantita * ol.prezzo_unitario)
FROM ordini_linee ol
WHERE ol.ordine_id IN (SELECT id FROM ordini WHERE stato = 'completato')
GROUP BY ol.ordine_id;
-- STEP 7: Misurare il miglioramento con pg_stat_statements
-- Attendere almeno 100 esecuzioni, poi confrontare mean_exec_time
SELECT pg_stat_statements_reset();
-- ... attendere ...
SELECT calls, mean_exec_time, max_exec_time FROM pg_stat_statements WHERE ...;
Antipatroon: Wijzig de planner tijdens de productie niet
Voorkomen SET enable_seqscan = off o SET enable_hashjoin = off in productie.
Deze instellingen schakelen strategieën uit die in andere contexten optimaal zijn. De juiste oplossing
en werk altijd de statistieken bij, maak de juiste indexen of herschrijf de query.
Visuele hulpmiddelen voor queryplannen
Voor complexe queryplannen versnellen visuele tools de analyse. De beste zijn gratis.
- verklaren.dalibo.com: Plak de JSON-uitvoer van EXPLAIN, verkrijg een interactieve grafiek. Het langzaamste knooppunt is rood gemarkeerd.
- pgMosterd: begeleide analyse met specifieke suggesties ("overweeg een index in kolom X").
- pspg: pager voor psql die de uitvoer van EXPLAIN leesbaar kleurt.
-- Copiare l'output JSON per explain.dalibo.com
\pset format unaligned
\pset tuples_only on
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;
-- Output e un JSON singolo che puoi incollare direttamente
Conclusies
EXPLAIN ANALYZE en de lens die de innerlijke werking van PostgreSQL onthult.
Leer Sequential Scan versus Index Scan lezen, begrijp wanneer Hash Join de voorkeur heeft
Nested Loop, en het identificeren van discrepanties tussen schattingen en realiteit, transformeert een ontwikkelaar
van "SQL schrijven" tot "database optimaliseren".
In het volgende artikel gaan we dieper in op geavanceerde indexering: gedeeltelijke indexering, dekkingsindex, BRIN voor tijdreeksen en GIN voor full-text zoeken. Dit zijn de tools die, gecombineerd met inzicht in queryplannen maken ze dramatische optimalisaties mogelijk.
Serie: Database-engineering en -optimalisatie
- Artikel 1: PostgreSQL 17/18 - Nieuws en prestaties
- Artikel 2 (dit): UITLEG ANALYSE - Queryplannen lezen
- Artikel 3: Geavanceerde Indexering - Gedeeltelijk, Dekkend, BRIN, GIN
- Artikel 4: Tabelpartitionering - Miljarden rijen beheren
- Artikel 5: Verbindingspooling - PgBouncer versus Pgpool-II