ANALİZİ AÇIKLAYIN: PostgreSQL Sorgu Planlarını Okuyun ve Optimize Edin
Üretimde 8 saniye süren ve genellikle doğru dizinle veya 50 ms'ye optimize edilebilen bir sorgu
biraz yeniden yazıyorum. EXPLAIN ANALYZE ve tam olarak nedenini ortaya çıkaran araç
PostgreSQL bir sorguyu belirli bir şekilde çalıştırır: kaç satır tarar, hangi indeksleri kullanır, nerede harcar
Zamanın %90'ı. Sorgu planlarını okumayı öğrenmek, bir PostgreSQL geliştiricisi için en etkili beceridir.
Ne Öğreneceksiniz
- EXPLAIN ANALYZE çıktısını okuyun ve pahalı düğümleri belirleyin
- Sıralı Tarama, Dizin Taraması ve Yalnızca Dizin Taraması arasındaki fark
- PostgreSQL, İç İçe Döngü, Karma Birleştirme veya Birleştirme Birleştirmeyi seçtiğinde
- Güncellenmiş istatistiklerle yanlış planlayıcı tahminleri nasıl düzeltilir?
- Kullanmak
auto_explainyavaş sorguları otomatik olarak yakalamak için
Sorgu Planının Yapısı
Bir sorgu planı ve bir işlem ağacı: her düğüm bir işlemi temsil eder (tarama, birleştirme, sıralama) ve alt düğümler üst düğüme veri sağlar. PostgreSQL ağacı aşağıdan yukarıya doğru çalıştırır.
-- 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;
Tipik çıktı (açıklamalı):
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
Temel Metrikleri Okuyun
Maliyet: Tahmin ve Gerçeklik
Her düğüm iki değer çiftini gösterir: (cost=startup..total rows=stima width=N) ben
le planlamacı tahminleri, Sırasında (actual time=startup..total rows=reali loops=N)
bunlar değerler gerçekten kendini ölç. Tahmin ile gerçeklik arasında büyük bir tutarsızlık olduğunda,
planlamacının istatistiklerle ilgili yanlış bilgisi var.
-- 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');
Sıralı Tarama vs Dizin Tarama vs Yalnızca Dizin Tarama
Tarama türünün performans üzerinde büyük etkisi vardır. PostgreSQL türe dayalı olarak seçim yapar tahmini seçiciliğe ve tablo boyutuna.
-- 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
Pahalı Düğümlerin Belirlenmesi
Darboğazı bulmanın en hızlı yolu kullanmaktır. FORMAT JSON araçlarla
düğümü görsel olarak veya manuel olarak arayın actual time daha uzun.
-- 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!
Birleştirme Türleri ve Ne Zaman Kullanılacağı
İç İçe Döngü Birleştirme
"Dış" tablonun her satırı üzerinde yineleyin ve her biri için "iç" tabloya bakın. İç kısım küçük ve indekslendiğinde mükemmeldir. Her iki tablo da büyük olduğunda felaket.
-- 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;
Karma Katıl
Küçük tablodan bir karma tablo oluşturur, ardından büyük tabloyu tarar ve yazışmalar. Kullanılabilir dizinler olmayan orta/büyük tablolardaki birleştirmeler için etkilidir.
-- 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';
Birleştir Katıl
Her iki girişin de birleştirme anahtarında sıralanmasını gerektirir. PostgreSQL bunu ne zaman seçer? her iki tablonun da düzeni garanti eden indeksleri vardır.
Planlayıcı Tahminlerini Düzeltme
Yanlış tahminler, optimal olmayan sorgu planlarının en yaygın nedenidir. İşte bunları düzeltmenin teknikleri.
-- 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: Yavaş Sorguları Otomatik Olarak Yakala
Üretimde her sorguda EXPLAIN komutunu manuel olarak çalıştıramazsınız. auto_explain
Belirli bir zaman eşiğini aşan sorguların planını otomatik olarak günlüğe kaydeder.
-- 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)...
Pratik Optimizasyon İş Akışı
Üretimde tanımlanan yavaş bir sorguyu optimize etmek için sistematik süreç aşağıda verilmiştir.
-- 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-Desen: Üretimdeki planlayıcıyı değiştirmeyin
Kaçınmak SET enable_seqscan = off o SET enable_hashjoin = off üretimde.
Bu ayarlar diğer bağlamlarda en uygun olan stratejileri devre dışı bırakır. Doğru çözüm
ve her zaman istatistikleri güncelleyin, uygun dizinler oluşturun veya sorguyu yeniden yazın.
Sorgu Planları için Görsel Araçlar
Karmaşık sorgu planları için görsel araçlar analizi hızlandırır. En iyileri ücretsizdir.
- açıkla.dalibo.com: EXPLAIN'in JSON çıktısını yapıştırın, etkileşimli bir grafik elde edin. En yavaş düğüm kırmızı renkle vurgulanır.
- pgHardal: özel öneriler içeren rehberli analiz ("X sütununda bir dizin düşünün").
- pspg: EXPLAIN çıktısını okunabilir şekilde renklendiren psql için çağrı cihazı.
-- 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
Sonuçlar
EXPLAIN ANALYZE ve PostgreSQL'in iç işleyişini ortaya çıkaran mercek.
Sıralı Tarama ile Dizin Taramayı okumayı öğrenin, Karma Birleştirmenin ne zaman tercih edildiğini anlayın
İç İçe Döngü ve tahminlerle gerçeklik arasındaki tutarsızlıkların belirlenmesi geliştiriciyi dönüştürüyor
"SQL yaz"dan "veritabanını optimize et"e.
Bir sonraki makalede gelişmiş indekslemeyi daha derinlemesine inceleyeceğiz: kısmi indeks, kapsayan indeks, Zaman serileri için BRIN ve tam metin araması için GIN. Bunlar bir araya getirilen araçlardır Sorgu planlarının anlaşılmasıyla dramatik optimizasyonlara olanak tanırlar.
Seri: Veritabanı Mühendisliği ve Optimizasyonu
- Makale 1: PostgreSQL 17/18 - Haberler ve Performans
- Madde 2 (bu): ANALİZİ AÇIKLAYIN - Sorgu Planlarını Okuyun
- Madde 3: Gelişmiş Endeksleme - Kısmi, Kaplama, BRIN, GIN
- Madde 4: Tablo Bölümleme - Milyarlarca Satırın Yönetilmesi
- Madde 5: Bağlantı Havuzu Oluşturma - PgBouncer ve Pgpool-II