Merhaba arkadaşlar, bugün başımı çok ağrıtan bir konudan bahsedeceğim: Database index'leri. Hepimiz "index ekle performans artsın" diye biliyoruz değil mi? Ama işin aslı öyle değilmiş. Geçenlerde canlı sistemde bir sorgunun dakikalar sürdüğünü görünce kafayı yemiştim. Meğerse yanlış index, işleri daha da kötü yapıyormuş.
Sorunun Ortaya Çıkışı
Sistemdeki kullanıcı aktivite loglarını tarih aralığına ve kullanıcı durumuna göre filtreleyen bir raporlama sorgum vardı. Veri arttıkça sorgu süresi de katlanarak artmaya başladı. İlk refleksim, "WHERE" koşulundaki sütunlara index eklemek oldu. `created_at` ve `is_active` sütunlarının her birine ayrı ayrı B-Tree index'leri oluşturdum. "Şimdi olacak" dedim ama işler daha da beter oldu! Sorgu planı, beklediğim gibi bu index'leri verimli kullanmak yerine, bir sürü "Bitmap Heap Scan" ve "Bitmap Index Scan" işlemi yapmaya başladı. CPU kullanımı fırladı.
EXPLAIN ANALYZE ile Gerçek Yüzü Görmek
İşte bu noktada silahımız devreye giriyor: `EXPLAIN ANALYZE`. Bu komut, sorgunun sadece nasıl çalışacağını (EXPLAIN) değil, gerçekten çalıştırıp ne kadar sürdüğünü (ANALYZE) de gösteriyor. Yanlış index'li sorgumu analiz ettiğimde karşıma şöyle bir çıktı çıktı:
Çıktıda en kritik satır şuydu: `Parallel Seq Scan on user_activities`. Yani PostgreSQL, benim güzelim index'leri es geçip, tüm tabloyu sırayla taramayı tercih etmişti! Sebebi, `created_at` üzerindeki tek başına index'in, `is_active` gibi seçiciliği (selectivity) yüksek olmayan bir filtreyle kombine edildiğinde verimsiz kalmasıydı. Optimizer, index'leri kullanıp sonra heap tabloya gitmektense, direkt full scan yapmayı daha ucuz bulmuştu.
Çözüm: Bileşik (Composite) Index ve Doğru Sıralama
Buradaki çözüm, sorgunun ihtiyaç duyduğu sütunları tek bir index'te doğru sırayla birleştirmekti. `created_at` (tarih aralığı) ve `is_active` (eşitlik filtresi) için bir bileşik index oluşturdum. İPUCU: Bileşik index'lerde, önce eşitlik (=) ile kullanılan sütun, sonra aralık (BETWEEN, >, <) ile kullanılan sütun gelmeli.
Yeni index'i oluşturup `EXPLAIN ANALYZE`'i tekrar çalıştırdığımda, artık `Index Scan using idx_active_created on user_activities` satırını görüyordum. Sorgu süresi 4200 ms'den 45 ms'ye düşmüştü! Aradaki fark inanılmazdı.
Sonuç ve Öneriler
Bu olay bana şunu öğretti: Index eklemek sihirli değnek değil. Her index, yazma (INSERT/UPDATE) performansına bir maliyettir. Bir index eklemeden veya var olan bir sorunu analiz etmeden önce mutlaka `EXPLAIN ANALYZE` ile sorgu planını inceleyin. Hangi index'in kullanıldığına, kaç satırın filtrelendiğine (`rows removed by filter`) ve toplam maliyete (`total cost`) bakın.
Siz de PostgreSQL'de veya kullandığınız başka bir DBMS'de benzer "yanlış index" hikayeleri yaşadınız mı? `EXPLAIN` çıktılarını yorumlarken en çok hangi metriklere dikkat ediyorsunuz? Sizin kullandığınız farklı bir performans analiz yöntemi var mı?
Sorunun Ortaya ÇıkışıSistemdeki kullanıcı aktivite loglarını tarih aralığına ve kullanıcı durumuna göre filtreleyen bir raporlama sorgum vardı. Veri arttıkça sorgu süresi de katlanarak artmaya başladı. İlk refleksim, "WHERE" koşulundaki sütunlara index eklemek oldu. `created_at` ve `is_active` sütunlarının her birine ayrı ayrı B-Tree index'leri oluşturdum. "Şimdi olacak" dedim ama işler daha da beter oldu! Sorgu planı, beklediğim gibi bu index'leri verimli kullanmak yerine, bir sürü "Bitmap Heap Scan" ve "Bitmap Index Scan" işlemi yapmaya başladı. CPU kullanımı fırladı.
EXPLAIN ANALYZE ile Gerçek Yüzü Görmekİşte bu noktada silahımız devreye giriyor: `EXPLAIN ANALYZE`. Bu komut, sorgunun sadece nasıl çalışacağını (EXPLAIN) değil, gerçekten çalıştırıp ne kadar sürdüğünü (ANALYZE) de gösteriyor. Yanlış index'li sorgumu analiz ettiğimde karşıma şöyle bir çıktı çıktı:
SQL:
EXPLAIN ANALYZE
SELECT user_id, action, created_at
FROM user_activities
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'
AND is_active = true
ORDER BY created_at DESC
LIMIT 100;Çıktıda en kritik satır şuydu: `Parallel Seq Scan on user_activities`. Yani PostgreSQL, benim güzelim index'leri es geçip, tüm tabloyu sırayla taramayı tercih etmişti! Sebebi, `created_at` üzerindeki tek başına index'in, `is_active` gibi seçiciliği (selectivity) yüksek olmayan bir filtreyle kombine edildiğinde verimsiz kalmasıydı. Optimizer, index'leri kullanıp sonra heap tabloya gitmektense, direkt full scan yapmayı daha ucuz bulmuştu.
Çözüm: Bileşik (Composite) Index ve Doğru SıralamaBuradaki çözüm, sorgunun ihtiyaç duyduğu sütunları tek bir index'te doğru sırayla birleştirmekti. `created_at` (tarih aralığı) ve `is_active` (eşitlik filtresi) için bir bileşik index oluşturdum. İPUCU: Bileşik index'lerde, önce eşitlik (=) ile kullanılan sütun, sonra aralık (BETWEEN, >, <) ile kullanılan sütun gelmeli.
SQL:
-- YANLIŞ: Sadece created_at üzerinde index
CREATE INDEX idx_created_at ON user_activities(created_at);
-- YANLIŞ: Sütun sırası yanlış
CREATE INDEX idx_wrong_order ON user_activities(is_active, created_at);
-- DOĞRU: Önce eşitlik, sonra aralık sütunu
CREATE INDEX idx_active_created ON user_activities(is_active, created_at);Yeni index'i oluşturup `EXPLAIN ANALYZE`'i tekrar çalıştırdığımda, artık `Index Scan using idx_active_created on user_activities` satırını görüyordum. Sorgu süresi 4200 ms'den 45 ms'ye düşmüştü! Aradaki fark inanılmazdı.
Sonuç ve ÖnerilerBu olay bana şunu öğretti: Index eklemek sihirli değnek değil. Her index, yazma (INSERT/UPDATE) performansına bir maliyettir. Bir index eklemeden veya var olan bir sorunu analiz etmeden önce mutlaka `EXPLAIN ANALYZE` ile sorgu planını inceleyin. Hangi index'in kullanıldığına, kaç satırın filtrelendiğine (`rows removed by filter`) ve toplam maliyete (`total cost`) bakın.
Siz de PostgreSQL'de veya kullandığınız başka bir DBMS'de benzer "yanlış index" hikayeleri yaşadınız mı? `EXPLAIN` çıktılarını yorumlarken en çok hangi metriklere dikkat ediyorsunuz? Sizin kullandığınız farklı bir performans analiz yöntemi var mı?