Sorgu yavaşladığında sorgu planını okumak

Yavaş bir sorgu karşısında refleks genellikle aynı: WHERE'deki kolona indeks atmak. Bazen işe yarar. İşe yaramadığında ikinci indeks atılır, sonra üçüncü, ve tabloya kimsenin niye orada olduğunu bilmediği beş indeks birikir. Bunların her biri yazma işlemini yavaşlatır ve hiçbiri asıl sorunu çözmez.

Planı okumak bu döngüyü kırar. Veritabanı sorguyu nasıl çalıştıracağını zaten size söylüyor; sorun okumayı bilmemekte.

ANALYZE olmadan EXPLAIN yarım bilgidir

EXPLAIN planlayıcının niyetini gösterir. EXPLAIN ANALYZE sorguyu gerçekten çalıştırır ve ne olduğunu gösterir. Aralarındaki fark, teşhisin tamamıdır.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT s.id, s.olusturma, m.ad
FROM siparis s
JOIN musteri m ON m.id = s.musteri_id
WHERE s.durum = 'bekliyor'
  AND s.olusturma >= now() - interval '30 days'
ORDER BY s.olusturma DESC
LIMIT 50;

ANALYZE yazma yapan bir sorguda çalıştırılırsa gerçekten yazar. UPDATE ya da DELETE planı bakacaksanız işlemi açıp geri alın:

BEGIN;
EXPLAIN (ANALYZE) UPDATE siparis SET durum = 'iptal' WHERE id = 42;
ROLLBACK;

BUFFERS çoğu kılavuzda es geçilir, oysa en bilgilendirici parametredir. Okunan blokların kaçının önbellekten kaçının diskten geldiğini söyler.

Bakılacak ilk şey: rows tahmini ile gerçeğin oranı

Plandaki her düğüm iki sayı taşır:

Seq Scan on siparis  (cost=0.00..18423.00 rows=112 width=48)
                     (actual time=0.031..214.882 rows=48210 loops=1)

rows=112 planlayıcının tahmini, actual ... rows=48210 gerçekleşen satır sayısı. Arada 400 kat fark var. Bu satır, sorgunun neden yavaş olduğunu söylemese de planın neden yanlış seçildiğini söyler: planlayıcı 112 satır bekleyip ona göre bir birleştirme stratejisi seçmiş, 48 bin satır gelince strateji çökmüş.

Planda gezinirken tek soru şu: tahmin ile gerçeğin ilk ciddi ayrıştığı düğüm hangisi. Ağacın yapraklarından köke doğru bakın. En derindeki sapma, üstündeki her şeyi bozar; üstteki düğümleri düzeltmeye çalışmak boşa emektir.

Sapmanın olağan sebepleri sırayla: istatistikler bayat (ANALYZE tablo_adi çalıştırın), kolonlar arasında planlayıcının bilmediği bir bağıntı var, ya da WHERE koşulu planlayıcının kestiremeyeceği bir ifade içeriyor — WHERE lower(email) = $1 gibi bir fonksiyon çağrısı veya WHERE tarih::text LIKE '2026%' gibi bir tür dönüşümü.

Seq Scan her zaman kötü değildir

Planda Seq Scan görüp panikleyen çok. Tablo küçükse ya da sorgu satırların büyük bölümünü döndürüyorsa, sıralı tarama indeks taramasından hızlıdır; indeks üzerinden gidip her satır için tabloya dönmek daha pahalıdır.

Seq Scan gerçekten sorunsa iki iz bırakır: actual rows büyüktür ama Filter sonrası Rows Removed by Filter daha da büyüktür.

Seq Scan on siparis  (actual time=0.028..431.203 rows=240 loops=1)
  Filter: (durum = 'bekliyor'::text)
  Rows Removed by Filter: 1841306

Bir milyon sekiz yüz bin satır okunup atılıyor, geriye 240 tanesi kalıyor. Burada indeks işe yarar.

loops sayısını çarpmayı unutmayın

En sık kaçırılan detay bu. Bir iç düğümün actual time değeri tek çalışma başınadır:

->  Nested Loop  (actual time=0.019..2891.442 rows=48210 loops=1)
      ->  Seq Scan on siparis  (actual rows=48210 loops=1)
      ->  Index Scan using musteri_pkey on musteri
            (actual time=0.041..0.052 rows=1 loops=48210)

İçerideki index scan satır başına 0.05 ms görünüyor, zararsız duruyor. Ama loops=48210. Gerçek maliyet yaklaşık 2.4 saniye ve üstteki Nested Loop'un süresinin neredeyse tamamı budur. Toplam süreyi hesaplarken actual time ile loops çarpılır.

Nested Loop yerine Hash Join seçilmesi çoğu zaman bu tablonun çözümüdür ve planlayıcı doğru satır tahminine sahip olsaydı zaten onu seçerdi. Yani çözüm yine bir üst maddeye, tahmin sapmasına döner.

Sıralama ve bellek

Sort  (actual time=1204.331..1288.104 rows=48210 loops=1)
  Sort Key: s.olusturma DESC
  Sort Method: external merge  Disk: 24816kB

external merge ve Disk: satırı, sıralamanın belleğe sığmayıp diske taştığını söyler. quicksort Memory: görüyorsanız sorun yok. Diske taşma varsa iki yol var: work_mem değerini o oturum için yükseltmek, ya da sıralamayı tamamen ortadan kaldıran bir indeks vermek. İkincisi kalıcı çözümdür — (durum, olusturma DESC) üzerinde bir bileşik indeks hem filtreyi hem sıralamayı karşılar ve planda Sort düğümü tamamen kaybolur.

Bileşik indekste kolon sırası keyfi değildir: eşitlik koşulundaki kolonlar önce, aralık ve sıralama kolonları sonra gelir.

Planı okumanın sırası

Pratikte şu sırayı izlemek işi kısaltıyor:

1. En içteki düğümden başlayıp tahmin/gerçek oranının ilk bozulduğu yeri bul.
2. O düğümde loops kaç, süreyi çarp.
3. Rows Removed by Filter büyükse indeks adayını not et.
4. Sort Method diske taşıyor mu, bak.
5. BUFFERS çıktısında read sayısı hit sayısına göre yüksekse veri önbellekte değil demektir; bu bazen sorgunun değil, tablo boyutunun ya da makinenin sorunudur.

Bu beş adım bitmeden indeks atmayın. Planı okumadan atılan indeks, en iyi ihtimalle işe yarar ve nedenini bilmezsiniz; bir sonraki yavaşlamada aynı körlükle başlarsınız.