Sorgu Planları: PostgreSQL EXPLAIN ANALYZE Kullanımı

yazar

Bir PostgreSQL veritabanında sorgu yavaşlamaya başladığında, çoğu sysadmin’in ilk refleksi index eklemek ya da sunucuya RAM takmak olur. Oysa sorunun nerede olduğunu bilmeden yapılan optimizasyon, karanlıkta ok atmaktan farksız. İşte tam bu noktada EXPLAIN ANALYZE devreye giriyor. Sorgunun perde arkasında neler yaptığını, hangi adımların ne kadar sürdüğünü ve verinin nasıl dolaştığını size gösteriyor. Bu yazıda gerçek dünya senaryoları üzerinden EXPLAIN ANALYZE kullanımını derinlemesine ele alacağız.

EXPLAIN ve EXPLAIN ANALYZE Arasındaki Fark

Önce temel ayrımı netleştirelim. EXPLAIN, sorguyu çalıştırmadan sadece planlayıcının ürettiği tahmini planı gösterir. EXPLAIN ANALYZE ise sorguyu gerçekten çalıştırır ve her adım için gerçek süre, satır sayısı ve döngü bilgisi verir.

-- Sadece tahmin, sorgu çalıştırılmaz
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Sorgu çalıştırılır, gerçek ölçümler gösterilir
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Dikkat etmeniz gereken önemli bir nokta: EXPLAIN ANALYZE ile DELETE, UPDATE veya INSERT sorgularını çalıştırırsanız bu işlemler gerçekten veritabanına uygulanır. Bu yüzden bu tür sorgularda transaction kullanmak şart.

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < NOW() - INTERVAL '7 days';
ROLLBACK;

Bu yöntemle sorgunun planını güvenle inceleyebilir, ardından değişikliği geri alabilirsiniz.

EXPLAIN Çıktısını Okumak

İlk kez EXPLAIN ANALYZE çıktısıyla karşılaşanlar için bu çıktı biraz korkutucu görünebilir. Ama birkaç temel kavramı kavradıktan sonra her şey yerine oturuyor.

EXPLAIN ANALYZE SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.total DESC
LIMIT 100;

Tipik bir çıktı şöyle görünür:

Limit  (cost=1523.45..1523.70 rows=100 width=48) (actual time=45.123..45.187 rows=100 loops=1)
  ->  Sort  (cost=1523.45..1548.45 rows=10000 width=48) (actual time=45.120..45.150 rows=100 loops=1)
        Sort Key: o.total DESC
        Sort Method: top-N heapsort  Memory: 36kB
        ->  Hash Join  (cost=250.00..1100.00 rows=10000 width=48) (actual time=12.345..38.900 rows=9823 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..750.00 rows=10000 width=32) (actual time=0.023..25.600 rows=9823 loops=1)
                    Filter: (created_at > '2024-01-01'::date)
                    Rows Removed by Filter: 40177
              ->  Hash  (cost=150.00..150.00 rows=8000 width=20) (actual time=8.234..8.234 rows=8000 loops=1)
                    Buckets: 8192  Batches: 1  Memory Usage: 512kB
                    ->  Seq Scan on customers c  (cost=0.00..150.00 rows=8000 width=20) (actual time=0.015..4.100 rows=8000 loops=1)
Planning Time: 2.345 ms
Execution Time: 45.567 ms

Bu çıktıdaki her satırı tek tek ele alalım:

cost=1523.45..1525.70: İlk sayı başlangıç maliyeti, ikincisi toplam maliyet. Bu değerler PostgreSQL’in kendi iç biriminde ifade edilen tahminlerdir, milisaniye değil.

actual time=45.123..45.187: Gerçek elapsed time (ms cinsinden). İlk sayı ilk satırın gelmesi için geçen süre, ikincisi tüm işlemin bitmesi için geçen süre.

rows=100: O node’dan dönen gerçek satır sayısı.

loops=1: O node’un kaç kez çalıştırıldığı. Nested loop join’lerde bu sayı yükselebilir ve toplam süreyi loops ile çarpmak gerekir.

Rows Removed by Filter: 40177: Bu kritik bir bilgi. 40 bin satır tarandı ama sadece ~10 bin işe yaradı. Bu bir index ihtiyacına işaret ediyor.

Gerçek Senaryo 1: Yavaş Raporlama Sorgusu

Bir e-ticaret platformunda her gece çalışan bir raporlama sorgusu 20 dakikada tamamlanıyor. Önce mevcut durumu inceleyelim:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    p.category,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.completed_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY p.category
ORDER BY revenue DESC;

Burada BUFFERS seçeneğini kullandım. Bu parametre buffer hit/miss bilgisini gösterir ve disk I/O’nun ne kadar etkili olduğunu anlamak için çok değerlidir.

-- Çıktıdan kritik kısım:
Seq Scan on orders o  (cost=0.00..85000.00 rows=125000 width=16) 
  (actual time=0.050..8500.234 rows=125000 loops=1)
  Filter: ((status = 'completed') AND (completed_at BETWEEN ...))
  Rows Removed by Filter: 875000
  Buffers: shared hit=2340 read=45670

Buffers: shared read=45670 görüyorsunuz. Bu 45 bin sayfalık disk okuma demek. Ciddi bir problem. Çözüm:

-- Composite index oluşturuyoruz
CREATE INDEX CONCURRENTLY idx_orders_status_completed_at 
ON orders (status, completed_at) 
WHERE status = 'completed';

-- Partial index daha da verimli olur:
CREATE INDEX CONCURRENTLY idx_orders_completed_range
ON orders (completed_at)
WHERE status = 'completed';

Index oluşturduktan sonra tekrar EXPLAIN ANALYZE çalıştırıldığında:

Index Scan using idx_orders_completed_range on orders o
  (cost=0.43..2340.00 rows=125000 width=16)
  (actual time=0.089..450.123 rows=125000 loops=1)
  Buffers: shared hit=2890 read=120

Disk okuma 45670’ten 120’ye düştü. Sorgu süresi 8.5 saniyeden 450ms’ye geriledi.

Gerçek Senaryo 2: N+1 Problemi ve Nested Loop

Bir uygulama logunda binlerce benzer sorgu görüyorsunuz. Her biri hızlı ama toplam yük çok fazla. Bu genellikle N+1 probleminin işaretidir ama bazen tek bir sorgu da benzer bir nested loop tuzağına düşebilir.

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, u.email, 
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count,
       (SELECT SUM(total) FROM orders WHERE user_id = u.id) as total_spent
FROM users u
WHERE u.created_at > NOW() - INTERVAL '30 days';
-- Çıktıdan:
Seq Scan on users u (actual time=0.045..12340.456 rows=15000 loops=1)
  SubPlan 1
    ->  Aggregate (actual time=0.412..0.413 rows=1 loops=15000)
          ->  Index Scan on orders (actual time=0.050..0.380 rows=8 loops=15000)
  SubPlan 2  
    ->  Aggregate (actual time=0.398..0.399 rows=1 loops=15000)
          ->  Index Scan on orders (actual time=0.048..0.365 rows=8 loops=15000)

loops=15000 görüyorsunuz. Orders tablosuna 30 bin kez gidiliyor (15000 kullanıcı x 2 subquery). Bu sorguyu tek bir JOIN ile yazalım:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    u.id, 
    u.email,
    COALESCE(o.order_count, 0) as order_count,
    COALESCE(o.total_spent, 0) as total_spent
FROM users u
LEFT JOIN (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(total) as total_spent
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';
-- Yeni çıktı:
Hash Left Join (actual time=890.234..1234.567 rows=15000 loops=1)
  ->  Index Scan on users u (actual time=0.045..45.678 rows=15000 loops=1)
  ->  Hash (actual time=870.123..870.123 rows=89000 loops=1)
        ->  HashAggregate (actual time=650.234..780.456 rows=89000 loops=1)
              ->  Seq Scan on orders (actual time=0.023..350.123 rows=890000 loops=1)
Planning Time: 3.456 ms
Execution Time: 1245.678 ms

12 saniyeden 1.2 saniyeye düştü.

EXPLAIN Seçenekleri ve Detay Seviyesi

PostgreSQL’in farklı EXPLAIN seçenekleri var ve bunları birlikte kullanabilirsiniz:

EXPLAIN (
    ANALYZE true,      -- Gerçek çalıştırma
    BUFFERS true,      -- Buffer istatistikleri
    TIMING true,       -- Her node için timing (false yaparsanız overhead azalır)
    VERBOSE true,      -- Ekstra bilgi (output columns vs)
    FORMAT JSON        -- JSON çıktı (pgAdmin, explain.depesz.com için ideal)
) 
SELECT * FROM large_table WHERE indexed_column = 'value';

JSON formatı özellikle görsel araçlarla kullanım için çok kullanışlı:

-- JSON çıktıyı explain.depesz.com veya explain.tensor.ru sitelerine yapıştırabilirsiniz
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM your_query;

TIMING false seçeneği çok satır döndüren ve her satır için timing overhead’i olan sorgularda işe yarar:

-- Milyonlarca satır dönen aggregate sorgularda timing overhead olabilir
EXPLAIN (ANALYZE, BUFFERS, TIMING false)
SELECT category, COUNT(*) FROM huge_table GROUP BY category;

Gerçek Senaryo 3: Planlayıcı Tahmin Hataları

Bazen PostgreSQL’in planlayıcısı yanlış bir plan seçer çünkü istatistikler güncel değildir. Bu durumu tespit etmek için estimated rows ile actual rows farkına bakıyoruz:

EXPLAIN ANALYZE
SELECT * FROM events 
WHERE event_type = 'login' 
  AND user_id = 12345
  AND created_at > NOW() - INTERVAL '1 day';
-- Kötü bir tahmin örneği:
Seq Scan on events (cost=0.00..450000.00 rows=3 width=128) 
                   (actual time=0.234..8900.456 rows=89234 loops=1)
  Filter: (event_type = 'login' AND user_id = 12345 AND ...)
  Rows Removed by Filter: 8910766

Planlayıcı 3 satır beklerken 89234 satır geldi. Bu büyük bir tahmin hatası. Sebebi genellikle korelasyon veya eski istatistikler. Çözüm:

-- İstatistikleri güncelleyelim
ANALYZE events;

-- Eğer hala yanlışsa, istatistik detay seviyesini artıralım
ALTER TABLE events ALTER COLUMN event_type SET STATISTICS 500;
ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE events;

-- Korelasyonu kontrol edelim
SELECT tablename, attname, correlation 
FROM pg_stats 
WHERE tablename = 'events' 
  AND attname IN ('event_type', 'user_id', 'created_at');

Correlation değeri -1 ile 1 arasında. 1’e yakınsa fiziksel sıra ile mantıksal sıra uyumlu, index scan çok verimli. 0’a yakınsa korelasyon yok, index scan overhead getirebilir.

Gerçek Senaryo 4: Sort ve Bellek Kullanımı

Büyük sorgularda sort operasyonları disk’e dökülüyorsa (spill to disk) performans ciddi şekilde düşer:

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, created_at, payload
FROM audit_logs
WHERE organization_id = 99
ORDER BY created_at DESC;
-- Tehlikeli çıktı:
Sort (cost=125000.00..127500.00 rows=1000000 width=512) 
     (actual time=23450.234..28900.123 rows=1000000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 512000kB   -- DİKKAT! Disk'e döküldü
  ->  Seq Scan on audit_logs ...

Sort Method: external merge Disk: 512000kB görüyorsunuz. Bu 512MB’lık disk sort demek. Önce work_mem artırarak hafızada sort yapmayı deneyelim:

-- Sadece bu session için geçici olarak artır
SET work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, created_at, payload
FROM audit_logs
WHERE organization_id = 99
ORDER BY created_at DESC;
-- İyileşmiş çıktı:
Sort Method: quicksort  Memory: 245678kB   -- Hafızada sort

Eğer bu sık karşılaşılan bir durumsa, postgresql.conf‘ta global work_mem artırmak yerine, belirli roller veya sorgular için optimize etmek daha güvenli:

-- Sadece reporting rolü için artır
ALTER ROLE reporting_user SET work_mem = '128MB';

-- Ya da query-level olarak uygulama tarafında ayarla

Senaryo 5: Hashaggregate vs Sort+GroupAggregate

Büyük GROUP BY sorgularında PostgreSQL iki farklı strateji kullanabilir. Hangisinin seçildiğini anlamak önemli:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    DATE_TRUNC('hour', created_at) as hour,
    status,
    COUNT(*) as cnt,
    AVG(response_time_ms) as avg_response
FROM api_requests
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY 1, 2;
-- HashAggregate (hafıza yeterli olduğunda hızlı):
HashAggregate (actual time=12345.678..12456.789 rows=5040 loops=1)
  Group Key: date_trunc('hour', created_at), status
  Batches: 1  Memory Usage: 4096kB
  ->  Index Scan on api_requests ...

-- Sort + GroupAggregate (hafıza yetersizse veya çok fazla distinct group varsa):
GroupAggregate (actual time=45678.901..56789.012 rows=5040 loops=1)
  Group Key: date_trunc('hour', created_at), status
  ->  Sort ... Sort Method: external merge Disk: 89000kB

HashAggregate Batches değeri 1’den büyük ise hash aggregate disk’e dökülüyor demektir ve work_mem artırılması gerekiyor.

Auto-Explain ile Yavaş Sorguları Otomatik Yakalama

Üretim ortamında her sorguyu manuel test etmek mümkün değil. auto_explain extension’ı yavaş sorguları otomatik olarak log’a yazar:

-- postgresql.conf veya session bazında:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1000';  -- 1 saniyeden uzun sorguları logla
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
SET auto_explain.log_nested_statements = true;
SET auto_explain.log_format = 'json';

postgresql.conf‘a kalıcı olarak eklemek için:

# /etc/postgresql/16/main/postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 2000    # 2 saniye
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 0.1          # %10 örnekleme (yoğun sistemler için)

sample_rate parametresi özellikle çok yoğun sistemlerde tüm sorguları değil sadece bir kısmını loglamak için ideal.

pg_stat_statements ile Birlikte Kullanım

Hangi sorgunun problematik olduğunu bulmak için pg_stat_statements extension’ı kullanabilirsiniz:

-- En çok toplam süre harcayan sorgular
SELECT 
    substring(query, 1, 80) as short_query,
    calls,
    round(total_exec_time::numeric, 2) as total_ms,
    round(mean_exec_time::numeric, 2) as avg_ms,
    round(stddev_exec_time::numeric, 2) as stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Bu çıktıda stddev_ms yüksek olan sorgular dikkat çekicidir. Bazen hızlı çalışan bir sorgu zaman zaman çok yavaşlayabilir. Bu durum genellikle parametre bağlı plan değişikliğine ya da table bloat’a işaret eder.

-- Belirli bir sorguyu normalleştirip EXPLAIN ANALYZE ile test edelim
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE customer_id = $1  -- pg_stat_statements'tan gelen query formatı
AND status = $2;

-- Gerçek değerlerle:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE customer_id = 99999
AND status = 'pending';

Dikkat Edilmesi Gereken Yaygın Tuzaklar

Cache etkisi: EXPLAIN ANALYZE‘ı ilk çalıştırdığınızda veriler disk’te, ikinci çalıştırmada cache’te olabilir. Gerçekçi bir ölçüm için birkaç kez çalıştırıp ortalamasına bakın ya da pg_prewarm extension ile cache durumunu kontrol altına alın.

Parametre bağlı planlar: PostgreSQL bazı sorgular için generic plan, bazıları için custom plan kullanır. Bunu görmek için:

-- Generic plan nedir diye bakmak için:
PREPARE test_stmt AS SELECT * FROM orders WHERE customer_id = $1;
EXPLAIN EXECUTE test_stmt(42);
-- İlk 5 çalıştırmada custom plan, sonra generic plan denenebilir

-- Planlamayı zorlamak için:
SET plan_cache_mode = 'force_custom_plan';   -- ya da force_generic_plan

Paralel plan: PostgreSQL parallel query kullanıyorsa, her worker ayrı çalışır ve çıktıda bu görünür:

-- Parallel plan örneği:
Gather (actual time=123.456..890.123 rows=1000000 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Seq Scan on large_table (actual time=0.045..234.567 rows=250000 loops=5)

loops=5 burada 1 leader + 4 worker demek. Gerçek satır sayısı 250000 x 5 = 1.25 milyon.

Explain Çıktısını Görselleştirmek

Karmaşık sorgular için metin çıktısını okumak zor olabilir. Birkaç faydalı araç:

  • explain.depesz.com: Hubert Lubaczewski’nin klasik aracı, color-coded çıktı
  • explain.tensor.ru: Daha modern UI, JSON formatında çıktı alıp yapıştırın
  • pgAdmin 4: Sorguyu çalıştırın, “Explain” sekmesinde grafiksel görünüm
-- Bu JSON çıktıyı araçlara yapıştırın:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

Sonuç

EXPLAIN ANALYZE bir PostgreSQL sysadmin’inin en güçlü silahıdır. Ancak sadece aracı bilmek yetmiyor, çıktıyı doğru okumak ve doğru soruları sormak gerekiyor. Bir sorguya bakarken şu sırayı takip etmenizi öneririm:

  • Execution Time‘a bakın. Gerçek süre ne kadar?
  • Estimated vs actual rows farkına bakın. Büyük fark varsa istatistik sorunu var.
  • Seq Scan‘lere bakın. Büyük tablolarda seq scan varsa index gerekebilir.
  • loops değerlerine bakın. Yüksek loops nested loop sorunu işareti.
  • Sort Method‘a bakın. external merge varsa work_mem artırın.
  • Buffers: read değerine bakın. Yüksekse disk I/O sorunu var.
  • Rows Removed by Filter değerine bakın. Çok satır filtre ediyorsa index fırsatı var.

Üretim ortamında her değişikliği BEGIN...ROLLBACK bloğu içinde test edin, auto_explain ile sorunlu sorguları otomatik yakalayın ve pg_stat_statements ile sistematik bir şekilde takip edin. Performans optimizasyonu tek seferlik bir iş değil, sürekli bir süreç. Bu araçları düzenli kullanmak, sorunlar büyümeden önce onları yakalamanızı sağlar.

Yorum yapın