EXPLICAȚI ANALIZA: Citiți și optimizați planurile de interogări PostgreSQL
O interogare care durează 8 secunde în producție și poate fi adesea optimizată la 50 ms cu indexul corect sau
o mică rescriere. EXPLAIN ANALYZE și instrumentul care dezvăluie exact de ce
PostgreSQL rulează o interogare într-un anumit mod: câte rânduri scanează, ce indici folosește, unde cheltuiește
90% din timp. A învăța să citești planurile de interogare este cea mai importantă abilitate pentru un dezvoltator PostgreSQL.
Ce vei învăța
- Citiți rezultatul EXPLAIN ANALYZE și identificați nodurile scumpe
- Diferența dintre scanarea secvențială, scanarea indexului și scanarea numai cu index
- Când PostgreSQL alege bucla imbricată, îmbinare hash sau îmbinare îmbinare
- Cum să remediați estimările incorecte ale planificatorului cu statistici actualizate
- Utilizare
auto_explainpentru a captura automat interogări lente
Structura unui plan de interogare
Un plan de interogare și un arbore de operații: fiecare nod reprezintă o operație (scanare, unire, sortare) iar nodurile copil furnizează date nodului părinte. PostgreSQL rulează arborele de jos în sus.
-- 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;
Ieșire tipică (adnotate):
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
Citiți valorile cheie
Cost: estimare vs realitate
Fiecare nod prezintă două perechi de valori: (cost=startup..total rows=stima width=N) eu sunt
le estimările planificatorului, În timp ce (actual time=startup..total rows=reali loops=N)
ele sunt valorile măsoară-te cu adevărat. Când există o discrepanță mare între estimare și realitate,
planificatorul are informații incorecte despre statistici.
-- 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
Tipul de scanare are un impact enorm asupra performanței. PostgreSQL alege bazat pe tip la selectivitatea estimată și dimensiunea tabelului.
-- 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
Identificarea nodurilor costisitoare
Cel mai rapid mod de a găsi blocajul este utilizarea FORMAT JSON cu unelte
vizual sau căutați manual nodul cu actual time mai înalt.
-- 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!
Tipuri de îmbinări și când să le folosiți
Alăturare buclă imbricată
Iterați peste fiecare rând din tabelul „exterior” și pentru fiecare uitați-vă în tabelul „interior”. Excelent când interiorul este mic și indexat. Dezastruos când ambele mese sunt mari.
-- 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 Join
Construiește un tabel hash din tabelul mai mic, apoi îl scanează pe cel mai mare căutând corespondențe. Eficient pentru îmbinări pe tabele medii/mari fără indici utilizabili.
-- 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';
Merge Join
Necesită ca ambele intrări să fie sortate pe cheia de unire. PostgreSQL îl alege când ambele tabele au indici care garantează ordinea.
Corectarea estimărilor planificatorului
Estimările incorecte sunt cea mai frecventă cauză a planurilor de interogare suboptime. Iată tehnicile pentru a le corecta.
-- 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: Captură automat interogări lente
În producție, nu puteți rula manual EXPLAIN pentru fiecare interogare. auto_explain
înregistrează automat planul de interogări care depășesc un prag de timp.
-- 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)...
Flux de lucru de optimizare practică
Iată procesul sistematic de optimizare a unei interogări lente identificate în producție.
-- 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: nu schimbați planificatorul în producție
Evita SET enable_seqscan = off o SET enable_hashjoin = off in productie.
Aceste setări dezactivează strategiile care sunt optime în alte contexte. Soluția corectă
și actualizați întotdeauna statisticile, creați indecși corespunzători sau rescrieți interogarea.
Instrumente vizuale pentru planuri de interogare
Pentru planurile de interogare complexe, instrumentele vizuale accelerează analiza. Cele mai bune sunt gratuite.
- explică.dalibo.com: inserați rezultatul JSON din EXPLAIN, obțineți un grafic interactiv. Cel mai lent nod este evidențiat cu roșu.
- pgMuştar: analiză ghidată cu sugestii specifice („luați în considerare un index pe coloana X”).
- pspg: pager pentru psql care colorează output-ul EXPLICA lizibil.
-- 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
Concluzii
EXPLAIN ANALYZE și obiectivul care dezvăluie funcționarea interioară a PostgreSQL.
Învățați să citiți Scanarea secvențială vs Scanarea indexului, înțelegeți când este de preferat Hash Join
Bucla imbricată și identificarea discrepanțelor dintre estimări și realitate transformă un dezvoltator
de la „scrierea SQL” la „optimizarea bazei de date”.
În următorul articol vom aprofunda mai mult în indexarea avansată: index parțial, index de acoperire, BRIN pentru serii cronologice și GIN pentru căutarea textului integral. Acestea sunt instrumentele care, combinate cu o înțelegere a planurilor de interogare, acestea permit optimizări dramatice.
Seria: Inginerie și optimizare a bazelor de date
- Articolul 1: PostgreSQL 17/18 - Știri și performanță
- Articolul 2 (acest): EXPLICAȚI ANALIZA - Citiți planurile de interogare
- Articolul 3: Indexare avansată - parțială, acoperire, BRIN, GIN
- Articolul 4: Partiționarea tabelelor - Gestionarea miliardelor de rânduri
- Articolul 5: Pooling de conexiuni - PgBouncer vs Pgpool-II