EXPLAIN ANALYZE: Přečtěte si a optimalizujte plány dotazů PostgreSQL
Dotaz, který ve výrobě zabere 8 sekund a lze jej často optimalizovat na 50 ms se správným indexem resp
trochu přepisování. EXPLAIN ANALYZE a nástroj, který přesně odhalí proč
PostgreSQL spouští dotaz určitým způsobem: kolik řádků skenuje, jaké indexy používá, kde utrácí
90 % času. Naučit se číst plány dotazů je pro vývojáře PostgreSQL nejpůsobivější dovedností.
Co se naučíte
- Přečtěte si výstup EXPLAIN ANALYZE a identifikujte drahé uzly
- Rozdíl mezi sekvenčním skenováním, indexovým skenováním a indexovým skenováním
- Když PostgreSQL vybere Nested Loop, Hash Join nebo Merge Join
- Jak opravit nesprávné odhady plánovače pomocí aktualizovaných statistik
- Použití
auto_explainpro automatické zachycení pomalých dotazů
Struktura plánu dotazů
Plán dotazů a strom operací: každý uzel představuje operaci (skenování, spojení, řazení) a podřízené uzly poskytují data nadřazenému uzlu. PostgreSQL spouští strom zdola nahoru.
-- 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;
Typický výstup (anotovaný):
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
Přečtěte si klíčové metriky
Cena: Odhad versus realita
Každý uzel zobrazuje dvě dvojice hodnot: (cost=startup..total rows=stima width=N) jsem
le odhady plánovače, Zatímco (actual time=startup..total rows=reali loops=N)
jsou to hodnoty opravdu se změřte. Když existuje velký rozpor mezi odhadem a skutečností,
plánovač má nesprávné informace o statistikách.
-- 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 vs Index Scan vs Index-Only Scan
Typ skenování má obrovský vliv na výkon. PostgreSQL vybírá na základě typu na odhadovanou selektivitu a velikost tabulky.
-- 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
Identifikace nákladných uzlů
Nejrychlejší způsob, jak najít úzké místo, je použít FORMAT JSON s nástroji
vizuálně nebo ručně vyhledejte uzel pomocí actual time vyšší.
-- 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!
Typy spojení a kdy je použít
Nested Loop Join
Iterujte přes každý řádek "vnější" tabulky a pro každý se podívejte do "vnitřní" tabulky. Vynikající, když je vnitřek malý a indexovaný. Katastrofální, když jsou oba stoly velké.
-- 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 Připojte se
Vytvoří hašovací tabulku z menší tabulky a poté prohledá větší tabulku a hledá ji korespondence. Efektivní pro spojení na středních/velkých tabulkách bez použitelných indexů.
-- 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';
Sloučit Připojte se
Vyžaduje, aby byly oba vstupy seřazeny na klíči spojení. PostgreSQL si to vybere kdy obě tabulky mají indexy, které zaručují pořadí.
Oprava odhadů plánovače
Nesprávné odhady jsou nejčastější příčinou neoptimálních plánů dotazů. Zde jsou techniky, jak je opravit.
-- 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: Automaticky zachytit pomalé dotazy
V produkci nemůžete EXPLAIN spustit ručně na každý dotaz. auto_explain
automaticky zaznamenává plán dotazů, které překročí časový limit.
-- 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)...
Praktický pracovní postup optimalizace
Zde je systematický proces optimalizace pomalého dotazu identifikovaného v produkci.
-- 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 ...;
Anti-Pattern: Neměňte plánovač ve výrobě
Vyhněte se SET enable_seqscan = off o SET enable_hashjoin = off ve výrobě.
Tato nastavení deaktivují strategie, které jsou optimální v jiných kontextech. Správné řešení
a vždy aktualizujte statistiky, vytvořte vhodné indexy nebo přepište dotaz.
Vizuální nástroje pro plány dotazů
U komplexních plánů dotazů urychlují vizuální nástroje analýzu. Ty nejlepší jsou zdarma.
- vysvětlit.dalibo.com: Vložte výstup JSON z EXPLAIN a získejte interaktivní graf. Nejpomalejší uzel je zvýrazněn červeně.
- pgMustard: řízená analýza s konkrétními návrhy („zvažte index ve sloupci X“).
- pspg: pager pro psql, který vybarvuje výstup EXPLAIN čitelně.
-- 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
Závěry
EXPLAIN ANALYZE a čočka, která odhaluje vnitřní fungování PostgreSQL.
Naučte se číst Sekvenční skenování vs Indexové skenování, pochopte, kdy je vhodnější Hash Join
Vnořená smyčka a identifikace nesrovnalostí mezi odhady a realitou transformuje vývojáře
od "zapisování SQL" po "optimalizaci databáze".
V příštím článku se ponoříme hlouběji do pokročilého indexování: částečný index, krycí index, BRIN pro časové řady a GIN pro fulltextové vyhledávání. Jedná se o nástroje, které v kombinaci s porozuměním plánům dotazů umožňují dramatické optimalizace.
Řada: Databázové inženýrství a optimalizace
- Článek 1: PostgreSQL 17/18 – Novinky a výkon
- článek 2 (tento): EXPLAIN ANALYZE - Čtení plánů dotazů
- Článek 3: Pokročilé indexování – částečné, krycí, BRIN, GIN
- Článek 4: Rozdělení tabulek – Správa miliard řádků
- Článek 5: Sdružování připojení – PgBouncer vs Pgpool-II