PostgreSQL EXPLAIN ANALYZE Çıktısını Okuma

Bir sorgu üretimde iki saniye sürüyor, aynı sorgu geliştirme makinenizde otuz milisaniyede bitiyor. İnternette bulduğunuz "şu indeksi ekle" tavsiyelerini denediniz, hiçbiri işe yaramadı. Bu noktada tahmin etmeyi bırakıp PostgreSQL'e doğrudan sormanın vakti gelmiştir: EXPLAIN ANALYZE komutu, sorgunuzun hangi adımlardan geçtiğini, her adımın kaç satır ürettiğini ve gerçekte ne kadar sürdüğünü satır satır önünüze koyar. Bu rehber, o çıktıyı korkutucu bir duvar olmaktan çıkarıp okunabilir bir teşhis raporuna dönüştürmeyi amaçlıyor.

Aşağıda önce EXPLAIN ile EXPLAIN ANALYZE arasındaki kritik farkı, sonra plan ağacının hangi yönde okunduğunu, cost ile actual time değerlerinin ne anlattığını, rows tahminindeki sapmanın neden en değerli ipucu olduğunu ve BUFFERS çıktısının disk mi bellek mi okuduğunuzu nasıl gösterdiğini anlatacağım. Ardından Seq Scan, Index Scan, Bitmap Heap Scan, Nested Loop ve Hash Join gibi düğüm tiplerini ne zaman görmek istediğinizi, en sonda da gerçek bir yavaş sorguyu baştan sona teşhis eden bir örneği bulacaksınız.

EXPLAIN, EXPLAIN ANALYZE ve Aradaki Fark#

EXPLAIN, sorguyu çalıştırmadan planlayıcının niyetini gösterir. Size maliyet tahminleri ve beklenen satır sayıları verir ama gerçek süreyi vermez. EXPLAIN ANALYZE ise sorguyu gerçekten çalıştırır, her düğümün ne kadar sürdüğünü ölçer ve tahmin ile gerçeği yan yana koyar. Teşhis için neredeyse her zaman ikincisini istersiniz.

Buradaki en önemli uyarı şudur: EXPLAIN ANALYZE sorguyu çalıştırır. UPDATE, DELETE veya INSERT üzerinde çalıştırdığınızda veriniz gerçekten değişir. Yazma sorgularını incelerken mutlaka bir işlem içine alın ve geri sarın:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE siparisler SET durum = 'iptal' WHERE olusturuldu < '2026-01-01';
ROLLBACK;   -- plan elimizde, veri değişmedi

Pratikte kullandığım standart çağrı biçimi şudur; BUFFERS olmadan yapılan analizin yarısı eksiktir:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT)
SELECT ...;

Plan Ağacı Hangi Yönde Okunur#

Çıktı yukarıdan aşağı yazılır ama içten dışa çalışır. En çok girintili satır ilk çalışan adımdır; sonuç yukarı doğru akar ve en üstteki satır sorgunun nihai çıktısını üretir. Bu yüzden bir plana bakarken gözünüzü önce en derine indirin, oradan yukarı tırmanın.

Sort  (cost=8421.10..8433.60 rows=5000 width=48) (actual time=812.4..813.1 rows=4812 loops=1)
  Sort Key: s.olusturuldu DESC
  Sort Method: quicksort  Memory: 612kB
  ->  Hash Join  (cost=210.50..8104.22 rows=5000 width=48) (actual time=4.1..798.2 rows=4812 loops=1)
        Hash Cond: (s.musteri_id = m.id)
        ->  Seq Scan on siparisler s  (cost=0.00..7650.00 rows=5000 width=32) (actual time=0.3..770.5 rows=4812 loops=1)
              Filter: (durum = 'bekliyor'::text)
              Rows Removed by Filter: 495188
        ->  Hash  (cost=148.00..148.00 rows=5000 width=24) (actual time=3.6..3.6 rows=5000 loops=1)
              ->  Seq Scan on musteriler m  (cost=0.00..148.00 rows=5000 width=24) (actual time=0.0..1.8 rows=5000 loops=1)
Planning Time: 0.412 ms
Execution Time: 814.9 ms

Bu planı okurken sıra şudur: en içteki Seq Scan on musteriler çalışır (1,8 ms), sonucu Hash düğümünde bir hash tablosuna dönüşür, Seq Scan on siparisler yürür (770 ms), ikisi Hash Join ile birleşir, en sonda Sort sıralar. Toplam 814,9 ms'nin 770'i tek bir düğümde harcanmış: siparisler tablosunun tam taraması. Suçlu bulundu.

Bir düğümün kendi maliyetini bulmak için o düğümün actual time değerinden çocuklarının toplamını çıkarın. Yukarıdaki Hash Join 798 ms görünüyor ama bunun 770'i çocuğundan geliyor; birleştirmenin kendisi yalnızca 24 ms sürmüş. Bu çıkarma işlemini yapmadan "join yavaş" gibi yanlış bir sonuca varmak çok kolaydır.

cost, rows, actual time ve loops Ne Anlatır#

cost=8421.10..8433.60 iki sayı içerir: ilki ilk satırın üretilmesine kadar geçen tahmini maliyet, ikincisi son satıra kadar olan tahmini toplam maliyet. Bu değerler milisaniye değildir; planlayıcının kendi iç birimidir ve seq_page_cost = 1.0 referans alınarak hesaplanır. Farklı planları kıyaslamak için anlamlıdır, mutlak bir süre olarak okunmaz.

rows=5000 ... rows=4812 çifti ise altın değerindedir. Soldaki planlayıcının tahmini, sağdaki gerçekleşen sayıdır. Bu ikisi birbirine yakınsa planlayıcı doğru bilgiyle karar vermiştir. Arada 10 kat, 100 kat fark varsa planlayıcı kör uçmuş demektir ve seçtiği plan büyük ihtimalle yanlıştır. Yanlış tahminin en yaygın üç nedeni şunlardır: istatistikler bayattır (ANALYZE çalıştırın), sütunlar arasında planlayıcının bilmediği bir korelasyon vardır (CREATE STATISTICS ile çoklu sütun istatistiği tanımlayın) veya filtre bir fonksiyonun içine gömülmüştür.

loops=1 değerine mutlaka dikkat edin. loops birden büyükse, gördüğünüz actual time ve rows değerleri döngü başına ortalamadır, toplam değil. Yani actual time=0.05..0.09 rows=1 loops=48000 yazan bir düğüm toplamda 48.000 × 0,09 ≈ 4,3 saniye harcamıştır. Nested Loop planlarında saklanan sürenin çoğu tam olarak buradadır ve toplamı elle çarpmadan görülmez.

-- İstatistikler bayatsa planlayıcı yanlış tahmin eder; önce bunu deneyin
ANALYZE siparisler;

-- Sütunlar arası korelasyon varsa (örn. il ve ilçe) çoklu istatistik tanımlayın
CREATE STATISTICS siparis_bolge (dependencies)
    ON il, ilce FROM siparisler;
ANALYZE siparisler;

BUFFERS Çıktısı: Disk mi Bellek mi#

BUFFERS seçeneği, her düğümün kaç 8 KB'lık sayfaya dokunduğunu gösterir ve yavaşlığın kaynağını ayırt etmenin en hızlı yoludur. Üç anahtar terimi ezberlemeniz yeterli:

->  Seq Scan on siparisler s  (actual time=0.3..770.5 rows=4812 loops=1)
      Buffers: shared hit=124 read=61350

Bu satır çok şey söylüyor: 61.350 sayfa, yani yaklaşık 480 MB veri diskten okunmuş. Aynı sorguyu ikinci kez çalıştırdığınızda read düşüp hit yükseliyorsa tablo önbelleğe girmiş demektir ve ilk ölçümünüz soğuk önbellek ölçümüdür. Bu yüzden karşılaştırma yaparken sorguyu her zaman iki üç kez çalıştırıp ikinci ve sonraki ölçümleri kıyaslayın.

temp read veya temp written satırları görüyorsanız, bir sıralama ya da hash işlemi work_mem sınırını aşıp geçici dosyaya taşmıştır. Bu durumda plan içinde Sort Method: external merge Disk: 92160kB gibi bir satır da bulursunuz. Çözüm ya oturum bazında work_mem yükseltmek ya da sıralanan satır sayısını azaltacak bir indeks eklemektir:

-- Yalnızca bu oturum için; sistem geneline uygulamadan önce ölçün
SET work_mem = '64MB';

Düğüm Tiplerini Tanımak#

Plan içinde göreceğiniz düğümlerin çoğu birkaç aileye ayrılır ve hangisini görmek istediğiniz duruma bağlıdır. Seq Scan her zaman kötü değildir: tablonun büyük bir kısmını okuyacaksanız, sıralı disk okuması rastgele indeks erişiminden hızlıdır. Küçük bir tabloda Seq Scan görmek de tamamen normaldir. Sorun, milyonlarca satırlık bir tabloda birkaç yüz satır arayan bir sorguda Seq Scan görmektir.

Index Only Scan gördüğünüzde hemen altındaki Heap Fetches satırına bakın. Bu sayı yüksekse indeks "only" olmaktan çıkmış, görünürlük haritası güncel olmadığı için tabloya inmeye devam etmiştir; çözüm genelde VACUUM çalıştırmaktır. Nested Loop gördüğünüzde ise loops değerini kontrol edin: dış küme tahminden çok daha büyük çıktıysa döngü sayısı patlar ve sorgu saniyelerce sürer. Bu tam olarak yanlış rows tahmininin en pahalı sonucudur.

Gerçek Bir Yavaş Sorguyu Baştan Sona Teşhis Etmek#

Yukarıdaki örnekteki 815 ms'lik sorguyu adım adım düzeltelim. Plandan üç bilgi çıkarmıştık: siparisler üzerinde Seq Scan var, Rows Removed by Filter: 495188 satırı yarım milyon satırın okunup atıldığını söylüyor ve Buffers: read=61350 bu işin diskten yapıldığını gösteriyor. Yani sorgunun tamamı, 500 bin satır arasından 4.812 tanesini bulmak için harcanıyor.

1. Filtre sütununa indeks ekleyin. durum sütunu az sayıda farklı değer aldığı için tam bir B-tree indeksi yerine kısmi indeks daha küçük ve daha etkilidir.
2. Sıralama sütununu indekse dahil ederek Sort düğümünü de ortadan kaldırın.
3. İndeksi CONCURRENTLY ile kurun; üretimde tabloyu yazmaya kapatmamak için bu şarttır.
4. ANALYZE çalıştırıp planı yeniden ölçün.

-- Yalnızca 'bekliyor' durumundakileri kapsayan kısmi indeks
CREATE INDEX CONCURRENTLY idx_siparisler_bekleyen
    ON siparisler (olusturuldu DESC)
    WHERE durum = 'bekliyor';

ANALYZE siparisler;

Yeni plan şuna benzer ve fark çarpıcıdır:

Nested Loop  (actual time=0.08..12.4 rows=4812 loops=1)
  ->  Index Scan using idx_siparisler_bekleyen on siparisler s  (actual time=0.05..3.2 rows=4812 loops=1)
        Buffers: shared hit=142
  ->  Index Scan using musteriler_pkey on musteriler m  (actual time=0.001..0.001 rows=1 loops=4812)
Execution Time: 13.1 ms

Seq Scan gitti, Sort gitti (indeks zaten sıralı geldiği için), read sıfırlandı ve süre 815 ms'den 13 ms'ye indi. Aynı yaklaşımı MySQL tarafında uygularken mantık benzerdir; oradaki yöntemi MySQL yavaş sorgu bulma yazısında bulabilirsiniz. Tablonuz partitioning kullanıyorsa planda ayrıca Subplans Removed satırını arayın — nasıl okunacağını PostgreSQL tablo partitioning rehberinde anlatmıştım.

Sık Yapılan Hatalar#

Soğuk önbellekle ölçüp karar vermek. İlk çalıştırmada tablo diskten okunur ve süre şişer. Değişikliğin etkisini ölçerken hem önceki hem sonraki halini birkaç kez çalıştırıp kararlı değeri alın; aksi halde eklediğiniz indeksin sağladığı kazancı olduğundan büyük görürsünüz.

Planning Time'ı gözden kaçırmak. Çok sayıda partition'ı veya çok sayıda indeksi olan tablolarda planlama süresi tek başına onlarca milisaniye olabilir. Execution Time 2 ms iken Planning Time 40 ms ise problem sorgunun kendisinde değil, şema karmaşıklığındadır.

Fonksiyonla sarmalanmış sütunlar. WHERE lower(eposta) = '[email protected]' yazdığınızda eposta üzerindeki normal indeks kullanılamaz. Ya ifade indeksi kurun (CREATE INDEX ON musteriler (lower(eposta))) ya da sorguyu sütunu çıplak bırakacak şekilde yeniden yazın. Aynı tuzak date_trunc, ::date ve substring için de geçerlidir.

SELECT * ile Index Only Scan'i kaybetmek. İndekste bulunmayan tek bir sütun istediğinizde PostgreSQL tabloya inmek zorunda kalır ve Index Only Scan fırsatı kaybolur. Gerçekten ihtiyaç duyduğunuz sütunları listeleyin ya da INCLUDE ile kapsayıcı indeks kurun.

Üretimde SET enable_seqscan = off bırakmak. Bu ayar bir teşhis aracıdır; planlayıcıyı alternatif plana zorlayıp "indeks kullansaydı ne olurdu" sorusunu cevaplar. Ayarı kalıcı hale getirmek, planlayıcının doğru karar verdiği durumlarda sizi çok daha kötü planlara mahkûm eder.

Sıkça Sorulan Sorular#

EXPLAIN ANALYZE sorguyu gerçekten çalıştırır mı#

Evet, çalıştırır ve bu yüzden yazma sorgularında dikkatli olmanız gerekir. UPDATE, DELETE veya INSERT üzerinde çalıştırdığınızda veriniz kalıcı olarak değişir. Güvenli yöntem sorguyu BEGIN ile başlayan bir işlemin içine alıp sonunda ROLLBACK çalıştırmaktır; planı görürsünüz, veriye hiçbir şey olmaz. Sadece planı merak ediyorsanız ANALYZE olmadan düz EXPLAIN kullanın.

cost değeri milisaniye midir#

Hayır. cost planlayıcının kendi soyut birimidir ve seq_page_cost sabiti 1.0 kabul edilerek hesaplanır. Bir sayfayı sıralı okumanın maliyeti 1 birim, rastgele okumanın random_page_cost (varsayılan 4.0) birimdir. Bu değerler farklı planları birbiriyle kıyaslamak içindir; 8000 maliyetli bir planın 8 saniye süreceği anlamına gelmez. Gerçek süreyi yalnızca actual time verir.

Tahmin edilen ve gerçekleşen satır sayısı neden bu kadar farklı çıkıyor#

En sık neden bayat istatistiklerdir; tabloya toplu veri yüklendikten sonra ANALYZE çalıştırılmamıştır. İkinci sık neden sütunlar arası korelasyondur: planlayıcı il = 'Ankara' ve ilce = 'Çankaya' koşullarını bağımsız varsayıp olasılıkları çarpar, oysa bu iki sütun birbirine bağlıdır. Bu durumda CREATE STATISTICS ... (dependencies) ile çoklu sütun istatistiği tanımlamak tahmini ciddi biçimde düzeltir.

Seq Scan görmek her zaman kötü müdür#

Hayır. Tablonun büyük bir bölümünü okuyacak bir sorguda sıralı tarama, indeksten rastgele sayfa erişimine göre çok daha hızlıdır ve planlayıcı bunu bilerek seçer. Birkaç bin satırlık küçük tablolarda da indeks kurmanın anlamı yoktur. Endişelenmeniz gereken durum, milyonlarca satırlık bir tabloda yüzlerce satır arayan bir sorguda Seq Scan ve yüksek Rows Removed by Filter değeri görmektir.

Buffers çıktısındaki hit ve read arasındaki fark ne#

shared hit, istenen sayfanın zaten PostgreSQL'in shared_buffers alanında bulunduğu, yani RAM'den okunduğu anlamına gelir ve neredeyse bedavadır. shared read, sayfanın bu alanda olmadığı ve dışarıdan istendiği anlamına gelir; bu okuma işletim sistemi önbelleğinden gelebileceği gibi doğrudan diskten de gelebilir. Yüksek read değerleri ya shared_buffers küçüklüğüne ya da gereğinden fazla veri okuyan bir plana işaret eder.

EXPLAIN çıktısını daha okunabilir hale nasıl getirebilirim#

Metin çıktısı derin planlarda okunması güç olduğu için EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) kullanıp sonucu görsel bir plan görüntüleyicisine verebilirsiniz. JSON çıktısını düzenlemek için JSON formatlayıcı aracımızı kullanabilirsiniz. psql içinde kalmak isterseniz \x ile genişletilmiş görünüme geçmek ve çıktıyı bir dosyaya alıp editörde incelemek de pratik bir yoldur.

Kapanış#

EXPLAIN ANALYZE okumayı öğrenmek, veritabanı tarafında yapabileceğiniz en yüksek getirili yatırımlardan biri. Aklınızda tutmanız gereken dört şey var: planı içten dışa okuyun, bir düğümün kendi maliyetini bulmak için çocuklarının süresini çıkarın, loops birden büyükse süreyi çarpmayı unutmayın ve tahmin ile gerçek satır sayısı arasındaki sapmayı her zaman ilk baktığınız yer yapın. BUFFERS olmadan yapılan analiz eksiktir; ölçümlerinizi de soğuk önbellekle değil, ısınmış durumda alın.

Sorgularınızı optimize ettiğiniz halde hâlâ IO sınırına takılıyorsanız sorun donanımda olabilir. NVMe diskli VDS ve bellek miktarını rahatça büyütebileceğiniz bulut sunucu paketlerimiz, shared_buffers alanını genişletip disk okumasını en aza indirmenize imkân verir. Planlayıcı ayarları, indeks bakımı ve düzenli ANALYZE gibi işleri devretmek isterseniz sunucu yönetimi hizmetimiz veritabanı tarafını da kapsıyor.