Giriş: Neden EXPLAIN ve İndeksler Önemli?

Bir veritabanı sorgusunun yavaş çalışmasının iki yaygın nedeni vardır: sorgunun kendisi (yetersiz filtreleme, gereksiz sütunlar, kötü JOIN sıralaması) ve veritabanı altyapısının sorguyu destekleyecek şekilde düzenlenmemiş olması (eksik veya uygunsuz indeksler, eski istatistikler, tablo bakımının eksikliği). EXPLAIN komutu, sorgu planlarını analiz ederek performans darboğazlarını tespit etmenizi sağlar ve PostgreSQL özelinde bakım görevleri ile birlikte ele alındığında önemli kazanımlar getirebilir (SQL Ekibi, Mor Teknoloji).

EXPLAIN nedir ve nasıl kullanılır?

EXPLAIN, veritabanı sorgusunun nasıl çalıştırılacağına dair planı döndüren bir komuttur. Temel farklar şunlardır:

• EXPLAIN <query> — Sorgunun planını gösterir ama sorguyu çalıştırmaz.
• EXPLAIN ANALYZE <query> — Sorguyu çalıştırır ve gerçek çalışma sürelerini, dönen satır sayılarını verir; bu, gerçek dünya davranışını görmek için kullanışlıdır (test ortamında çalıştırın).
• Ek seçenekler: VERBOSE, BUFFERS, FORMAT JSON gibi parametrelerle daha ayrıntılı çıktı alınabilir.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

EXPLAIN çıktısındaki temel alanlar (özet)

• Seq Scan / Index Scan / Bitmap Heap Scan / Index Only Scan: Hangi erişim metodunun kullanıldığını gösterir. Büyük tablolar üzerinde yapılan Seq Scan genelde yavaşlığa işaret eder.
• Cost: Planlayıcının göreli maliyet tahminleri (başlangıç ve toplam). Gerçek zamanlar için EXPLAIN ANALYZE çıktısındaki actual time bölümüne bakın.
• Rows / Actual Rows: Tahmin edilen ve gerçek dönen satır sayıları arasındaki fark, istatistiklerin güncel olmayabileceğine işaret eder.
• Width: Satır başına ortalama boyut (bayt).

EXPLAIN çıktısında özellikle tahmin edilen (estimates) ve gerçek (actual) değerler arasındaki farkları takip etmek, planlayıcının yanlış varsayımlarda bulunduğu noktaları bulmanızı sağlar; istatistiklerin güncel tutulması bu açıdan önemlidir (Nahrimed).

EXPLAIN ile adım adım sorgu optimizasyonu

1. Yavaş sorguyu üretin ve izole edin: Önce problemli sorguyu belirleyin. Üretimde değişiklik yapmadan önce aynı verinin olduğu bir test ortamında çalışın.
2. EXPLAIN ANALYZE çalıştırın: Çıktıdaki en maliyetli düğümü (node) tespit edin. Gerçek zamanları ve dönen satır sayısını kontrol edin.
3. Seq Scan var mı? Büyük bir tablo üzerinde Seq Scan varsa ve sorgu filtreleri indekslenecek sütunları kullanıyorsa, indeks eksikliği veya indeksin kullanılmaması söz konusu olabilir.
4. Estimates vs Actual: Tahmin ile gerçek arasında büyük fark varsa (estimates çok düşük veya yüksek), istatistikler güncel olmayabilir; bu durumda ANALYZE veya autovacuum ayarlarını gözden geçirin (SQL Ekibi).
5. İndeks oluşturma veya sorguyu yeniden yazma: Bir indeksin sorguyu destekleyip desteklemediğini test etmek için uygun sütunlarda indeks oluşturun (test ortamında) ve EXPLAIN ANALYZE ile farkları gözlemleyin.
6. Tekrar test: İndeks, sorgu planını nasıl değiştirdi? Yeni plan beklediğiniz gibi mi? Yazma maliyetleri ve bakım etkilerini değerlendirin.

İndeksler: Hangi tür ne zaman tercih edilmeli?

İndeks seçimi sorgu deseni ve tablo yapısına bağlıdır. Genel kurallar:

• B-tree: Varsayılan, eşitlik ve aralık sorguları için uygundur; ORDER BY ile iyi çalışır.
• GIN: Full-text arama, jsonb ve dizi tipleri için uygundur; çoklu değerli sütunlarda avantaj sağlar.
• GiST: Coğrafi ve bazı özel türler için kullanışlıdır.
• BRIN: Çok büyük, sıralı veri setleri (zaman serileri gibi) için hafif ve uygun maliyetli bir seçenek olabilir.

İndeksler sorgu hızını artırırken yazma (INSERT/UPDATE/DELETE) maliyetlerini de artırabilir; gereksiz indeksler performansa olumsuz etki eder. Bu dengeyi kurmak için sorgu örüntülerine ve yazma yoğunluğuna bakın (Mor Teknoloji).

Pratik indeks önerileri

• Multicolumn index (sütun sırası önemlidir): WHERE ve ORDER BY aynı sütunları kullanıyorsa, bu sütunları aynı indekste sıralı tutmak faydalıdır.
• Partial index: Sorgularınızın çoğu belirli bir koşulu kullanıyorsa (ör. WHERE status = 'active'), partial index ile daha küçük ve hızlı indeksler oluşturabilirsiniz.
• Index-only scan: Sorgunuz sadece indeks içindeki sütunları döndürüyorsa, index-only scan mümkün olabilir; bunun için visibility map'in güncel olması gerekir—autovacuum ve VACUUM işlemleri bu noktada önemlidir (SQL Ekibi).
• Expression index: Sorguda fonksiyon kullanımı varsa (ör. lower(email) = 'x'), fonksiyon sonucuna göre bir expression index oluşturmak index kullanımını sağlar.

Kısa vaka: "orders" sorgusunu hızlandırma

Problemli sorgu:

SELECT id, total, created_at FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

Sorun analizi: EXPLAIN ANALYZE çıktısında büyük bir Seq Scan tespit edildi. Çözüm adımları:

1. Test ortamında aşağıdaki indeksi oluşturun:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

2. Gerekirse indeks kapsamını artırmak için INCLUDE kullanın (ör. total sütununu dahil ederek index-only scan şansı yükselir):

CREATE INDEX idx_orders_user_created_inc ON orders (user_id, created_at DESC) INCLUDE (id, total);

Ardından EXPLAIN ANALYZE çalıştırarak planın Index Scan veya Index Only Scan'e dönüp dönmediğini kontrol edin. Bu tür değişikliklerin yazma maliyetini artırabileceğini unutmayın; üretime almadan önce test ve ölçüm yapın.

Sık karşılaşılan sorunlar ve çözümleri

• İndeks var ama kullanılmıyor: Tür uyuşmazlığı (örn. integer vs text) veya sorguda sütun üzerine fonksiyon uygulanması olabilir; expression index veya tür dönüşümü çözebilir.
• İstatistikler güncel değil: Planner yanlış seçim yapabilir; ANALYZE komutu ve autovacuum ayarlarını gözden geçirmek faydalıdır (Nahrimed).
• Çok fazla indeks: Yazma performansı düşer; hangi indekslerin gerçekten kullanıldığını pg_stat_statements ve vakitli EXPLAIN örnekleriyle doğrulayın.

İzleme, bakım ve değişiklikleri güvende uygulama

Performans iyileştirmeleri yaparken şu adımları takip edin:

1. Değişiklikleri önce test ortamında uygulayın ve EXPLAIN ANALYZE ile ölçün.
2. İndeks oluştururken bakım penceresi planlayın; büyük indeksler arka planda oluşturulabilir (CREATE INDEX CONCURRENTLY).
3. Otomatik bakım (autovacuum) ve manuel VACUUM/ANALYZE işlemlerini düzenli hale getirin; visibility map ve istatistiklerin güncel olması index-only scan olasılığını artırır (SQL Ekibi).
4. Yedekleme ve geri alma planı hazırlayın; özellikle üretimde tablo/indeks değişiklikleri öncesi veri güvenliğini sağlayın.

Bu rehber, EXPLAIN çıktısını nasıl yorumlayacağınız ve hangi indeks stratejilerinin hangi senaryolarda işe yarayacağı konusunda pratik adımlar sundu. Aşağıdaki SSS bölümünde sık karşılaşılan sorulara kısa yanıtlar bulabilirsiniz.