WYJAŚNIJ ANALIZĘ: Czytaj i optymalizuj plany zapytań PostgreSQL
Zapytanie, które zajmuje 8 sekund w środowisku produkcyjnym i często można je zoptymalizować do 50 ms przy odpowiednim indeksie lub
trochę przepisywania. EXPLAIN ANALYZE i narzędzie, które dokładnie pokazuje, dlaczego
PostgreSQL uruchamia zapytanie w określony sposób: ile wierszy skanuje, jakich używa indeksów, gdzie spędza
W 90% przypadków. Nauka czytania planów zapytań jest najbardziej wpływową umiejętnością dla programisty PostgreSQL.
Czego się nauczysz
- Przeczytaj wynik EXPLAIN ANALYZE i zidentyfikuj drogie węzły
- Różnica między skanowaniem sekwencyjnym, skanowaniem indeksowym i skanowaniem tylko indeksowym
- Gdy PostgreSQL wybierze Nested Loop, Hash Join lub Merge Join
- Jak naprawić nieprawidłowe szacunki planisty dzięki zaktualizowanym statystykom
- Używać
auto_explainaby automatycznie wychwytywać powolne zapytania
Struktura planu zapytań
Plan zapytań i drzewo operacji: każdy węzeł reprezentuje operację (skanowanie, łączenie, sortowanie) a węzły podrzędne dostarczają dane do węzła nadrzędnego. PostgreSQL uruchamia drzewo od dołu do góry.
-- 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;
Typowe dane wyjściowe (z adnotacjami):
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
Przeczytaj kluczowe wskaźniki
Koszt: szacunki a rzeczywistość
Każdy węzeł przedstawia dwie pary wartości: (cost=startup..total rows=stima width=N) jestem
le szacunki planisty, Chwila (actual time=startup..total rows=reali loops=N)
to są wartości naprawdę zmierz się. W przypadku dużej rozbieżności pomiędzy szacunkami a rzeczywistością,
planista ma błędne informacje na temat statystyk.
-- 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');
Skanowanie sekwencyjne a skanowanie indeksowe a skanowanie tylko indeksowe
Rodzaj skanowania ma ogromny wpływ na wydajność. PostgreSQL wybiera na podstawie typu do szacowanej selektywności i rozmiaru tabeli.
-- 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
Identyfikacja kosztownych węzłów
Najszybszym sposobem na znalezienie wąskiego gardła jest użycie FORMAT JSON z narzędziami
wizualne lub ręcznie wyszukaj węzeł za pomocą actual time wyższy.
-- 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!
Rodzaje połączeń i kiedy ich używać
Zagnieżdżone łączenie pętli
Wykonaj iterację po każdym wierszu tabeli „zewnętrznej” i dla każdego z nich spójrz na tabelę „wewnętrzną”. Doskonała, gdy wewnętrzna część jest mała i indeksowana. Katastrofalne, gdy oba stoły są duże.
-- 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 Dołącz
Tworzy tabelę mieszającą z mniejszej tabeli, a następnie skanuje większą w poszukiwaniu korespondencje. Skuteczne w przypadku złączeń na średnich/dużych tabelach bez użytecznych indeksów.
-- 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';
Połącz Dołącz
Wymaga posortowania obu danych wejściowych według klucza łączenia. PostgreSQL wybiera, kiedy obie tabele posiadają indeksy gwarantujące porządek.
Korygowanie szacunków planisty
Nieprawidłowe szacunki są najczęstszą przyczyną nieoptymalnych planów zapytań. Oto techniki ich korygowania.
-- 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: Automatycznie przechwytuj wolne zapytania
W środowisku produkcyjnym nie można ręcznie uruchomić EXPLAIN dla każdego zapytania. auto_explain
automatycznie rejestruje plan zapytań przekraczających określony próg czasowy.
-- 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)...
Praktyczny proces optymalizacji
Oto systematyczny proces optymalizacji powolnego zapytania zidentyfikowanego w środowisku produkcyjnym.
-- 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: Nie zmieniaj planisty w produkcji
Unikać SET enable_seqscan = off o SET enable_hashjoin = off w produkcji.
Te ustawienia wyłączają strategie, które są optymalne w innych kontekstach. Prawidłowe rozwiązanie
i zawsze aktualizuj statystyki, twórz odpowiednie indeksy lub przepisz zapytanie.
Narzędzia wizualne dla planów zapytań
W przypadku złożonych planów zapytań narzędzia wizualne przyspieszają analizę. Najlepsze są bezpłatne.
- wyjaśnij.dalibo.com: wklej wynik JSON EXPLAIN, aby uzyskać interaktywny wykres. Najwolniejszy węzeł jest podświetlony na czerwono.
- pgMusztarda: analiza kierowana z konkretnymi sugestiami („rozważ indeks w kolumnie X”).
- pspg: pager dla psql, który koloruje dane wyjściowe WYJAŚNIJ w czytelny sposób.
-- 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
Wnioski
EXPLAIN ANALYZE oraz soczewka ukazująca wewnętrzne działanie PostgreSQL.
Naucz się czytać skanowanie sekwencyjne a skanowanie indeksowe, dowiedz się, kiedy lepsze jest łączenie Hash
Nested Loop i identyfikacja rozbieżności pomiędzy szacunkami a rzeczywistością zmienia programistę
od „napisz SQL” do „optymalizuj bazę danych”.
W kolejnym artykule zagłębimy się w indeksowanie zaawansowane: indeks częściowy, indeks pokrywający, BRIN dla szeregów czasowych i GIN dla wyszukiwania pełnotekstowego. Są to narzędzia, które w połączeniu dzięki zrozumieniu planów zapytań umożliwiają radykalne optymalizacje.
Seria: Inżynieria i optymalizacja baz danych
- Artykuł 1: PostgreSQL 17/18 – Nowości i wydajność
- Artykuł 2 (ten): WYJAŚNIJ ANALIZUJ - Przeczytaj plany zapytań
- Artykuł 3: Indeksowanie zaawansowane – częściowe, obejmujące, BRIN, GIN
- Artykuł 4: Partycjonowanie tabeli — zarządzanie miliardami wierszy
- Artykuł 5: Pula połączeń – PgBouncer kontra Pgpool-II