Veli Keçeci
Tüm yazılara dön
·5 dk okuma

PostgreSQL sorgu planını okumak: yavaş sorguları indeksle doğru çözmek

Bir sorgu yavaşladığında ilk refleks genellikle "bir indeks ekleyelim" olur. Bazen işe yarar, bazen de yazma maliyetini artırıp hiçbir şeyi düzeltmez. Aradaki fark, veri tabanının sorguyu nasıl çalıştırdığını gerçekten görmekten geçer. PostgreSQL bunu sorgu planı ile açıkça gösterir; yeter ki okumayı bilelim.

Bu yazıda EXPLAIN ANALYZE çıktısını nasıl okuyacağınızı, hangi durumlarda hangi indeks türünün mantıklı olduğunu ve yaygın tuzakları anlatıyorum. Örnekler PostgreSQL üzerinden, ama plan okuma alışkanlığı diğer motorlara da taşınır.

Tahmin etmeyin, planı isteyin

Planı görmek için sorgunun önüne EXPLAIN yazılır. Sadece EXPLAIN sorguyu çalıştırmaz, planlayıcının tahminini gösterir. EXPLAIN (ANALYZE, BUFFERS) ise sorguyu gerçekten çalıştırır ve gerçek süreleri ile okunan sayfa sayılarını ekler.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Dikkat: ANALYZE sorguyu çalıştırdığı için UPDATE veya DELETE ile kullanırken bir işlem (transaction) içinde deneyip geri almak gerekir. Okuma sorgularında bu risk yoktur.

Plan çıktısında nelere bakılır?

Plan, iç içe geçmiş bir ağaçtır; en iç düğümler önce çalışır. Her düğümde iki sayı grubu önemlidir: planlayıcının tahmin ettiği satır sayısı (rows) ve gerçekte dönen satır sayısı (actual ... rows). İkisi arasında büyük fark varsa istatistikler eskimiş olabilir. Bu durumda ANALYZE tablo_adi çalıştırmak çoğu zaman planı değiştirir.

Düğüm türleri de ipucu verir:

  • Seq Scan: Tablonun tamamı okunuyor. Küçük tablolarda normaldir, büyük tabloda ve az satır dönen bir sorguda şüphelidir.
  • Index Scan / Index Only Scan: İndeks kullanılıyor. Index Only Scan, tabloya hiç gitmeden yanıt verebildiği için genellikle en ucuz yoldur.
  • Bitmap Heap Scan: Orta büyüklükte sonuç kümelerinde indeksten sayfa listesi çıkarılıp tablo toplu okunur.
  • Sort ve Hash: Bellek yetmezse diske taşar. Sort Method: external merge gibi bir satır, work_mem yetersizliğini ya da eksik bir sıralı indeksi işaret eder.

Buffers satırları da değerlidir: shared read yüksekse veri diskten okunuyor, shared hit yüksekse önbellekten geliyor demektir. Süre dalgalanıyorsa çoğu zaman bu fark yüzünden dalgalanır.

Bileşik indekslerde sıra önemlidir

Yukarıdaki sorgu customer_id ile eşitlik, status ile eşitlik ve created_at ile sıralama yapıyor. Bunu tek bir bileşik indeks karşılayabilir:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at DESC);

Kural şudur: eşitlik koşullarındaki sütunlar önce, sıralama ya da aralık sütunu sonra gelir. Böylece PostgreSQL ilgili satırlara doğrudan iner ve veriyi zaten sıralı bulduğu için ayrı bir Sort adımına gerek kalmaz; LIMIT 20 de ilk yirmi kayıtta durabilir.

Sıranın neden önemli olduğunu şöyle düşünün: (customer_id, status, created_at) indeksi telefon rehberi gibidir. Soyada göre bakabilirsiniz ama yalnızca ada göre bakmak için işe yaramaz. Sorgunuz status olmadan yalnızca created_at ile arama yapıyorsa bu indeks verimli çalışmaz.

Kısmi ve kapsayan indeksler

Verinin yalnızca bir bölümünü sorguluyorsanız tamamını indekslemek gereksizdir. Kısmi indeks, koşula uyan satırları içerir ve daha küçük, dolayısıyla daha hızlıdır:

CREATE INDEX idx_orders_open
    ON orders (customer_id, created_at DESC)
    WHERE status = 'open';

Bu, "açık siparişler" gibi küçük ve sık sorgulanan bir alt küme için idealdir. Kapsayan indeks ise INCLUDE ile sorgunun ihtiyaç duyduğu ek sütunları taşır; böylece Index Only Scan mümkün olur:

CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id, created_at DESC)
    INCLUDE (total);

Index Only Scan'in tabloya hiç uğramaması, tablonun görünürlük haritasının güncel olmasına bağlıdır; bunu düzenli VACUUM sağlar. Yoğun güncellenen tablolarda beklediğiniz kazancı görmeyebilirsiniz.

İndeksi kullanılmaz kılan yaygın tuzaklar

İndeks var ama plan yine Seq Scan gösteriyorsa sebep çoğu zaman şunlardan biridir:

  1. Sütun üzerinde fonksiyon: WHERE lower(email) = '[email protected]' ifadesi, email üzerindeki düz indeksi kullanamaz. Çözüm ya ifade indeksi (CREATE INDEX ON users (lower(email))) ya da veriyi baştan normalize etmektir.
  2. Tür uyuşmazlığı: Sütun text iken farklı bir türle karşılaştırmak örtük dönüşüm yaptırabilir ve indeksi devre dışı bırakabilir.
  3. Başa joker karakter: LIKE '%abc' sıradan B-tree indeksini kullanamaz. Bu tür aramalar için pg_trgm eklentisi ve GIN indeksi gibi farklı bir yaklaşım gerekir.
  4. Düşük seçicilik: Koşul tablonun büyük kısmını döndürüyorsa Seq Scan gerçekten daha ucuz olabilir. Planlayıcı bu kararı çoğu zaman doğru verir.
  5. Eskimiş istatistikler: Büyük toplu yüklemeden sonra planlayıcı yanlış tahmin yapar.

İndeksin bir bedeli vardır

Her indeks, her INSERT, UPDATE ve DELETE işleminde güncellenmek zorundadır. Fazla indeks yazma hızını düşürür, disk ve önbellek tüketir. Bu yüzden indeks eklemek kadar kullanılmayanları temizlemek de gerekir. pg_stat_user_indexes görünümü, bir indeksin kaç kez tarandığını (idx_scan) gösterir; uzun süredir sıfır kalan indeksler aday olabilir. Silmeden önce istatistiklerin ne kadar süredir biriktiğine ve nadir çalışan raporlara bakın.

Canlı sistemde indeks oluştururken kayıtları kilitlememek için CREATE INDEX CONCURRENTLY kullanın. Daha uzun sürer ve bir işlem bloğu içinde çalışmaz, ama yazmaları engellemez.

Pratik bir çalışma akışı

Yavaş bir sorguyla karşılaştığınızda şu sırayı izlemek zaman kazandırır:

  1. Sorguyu gerçek parametrelerle ve gerçeğe yakın veriyle EXPLAIN (ANALYZE, BUFFERS) ile çalıştırın.
  2. Tahmin ile gerçek satır sayısını karşılaştırın; fark büyükse önce ANALYZE çalıştırın.
  3. En pahalı düğümü bulun: çoğu zaman bir Seq Scan, bir Sort ya da çok tekrarlanan bir iç döngüdür.
  4. Sorgunun koşul ve sıralamasına göre en küçük işe yarar indeksi tasarlayın.
  5. Planı yeniden alın ve iyileşmeyi sayılarla doğrulayın. Düzelmediyse indeksi geri alın.

Uygulama tarafında da bir kontrol noktası ekleyin: yavaş sorgu günlüğünü (log_min_duration_statement) açın ya da pg_stat_statements ile en çok zaman harcayan sorguları izleyin. Böylece optimizasyon, hissedilen yavaşlığa değil ölçülmüş yüke yönelir.

Kapanış

Sorgu planı, veri tabanının "bu işi nasıl yapacağım" cevabıdır; indeks ise ona verdiğiniz bir kısayol. Kısayolun işe yarayıp yaramadığını yalnızca plan söyler. Planı okumayı alışkanlık hâline getirdiğinizde indeksleri tahminle değil kanıtla eklersiniz, gereksiz olanları fark edip kaldırırsınız ve yazma performansından ödün vermeden okuma hızını korursunuz. Bir sonraki yavaş sorguda önce EXPLAIN (ANALYZE, BUFFERS) çalıştırmanızı öneririm.