PostgreSQL'de composite index kolon sırasını nasıl seçerim?

Kısa cevap: Bu sorgu için tek bir composite index kur — (user_id, status, created_at DESC). Eşitlik kolonları başa, ORDER BY kolonu en sona; gerisi kendiliğinden çözülür.

Kısa cevap

Yaşadığın yavaşlık index eksikliği değil, yanlış index biçimi: üç ayrı tekli indeksi DB Bitmap Index Scan ile birleştirip sonra ayrı bir Sort adımı çalıştırmak zorunda kalıyor, ikisi de pahalı. Aynı “sıcak tabloyu hangi kolona göre düzenlemeli” sorusunun kiracı bazlı hâlini multi-tenant izolasyon kaydında ele almıştım.

Neden

1. Eşitlik kolonları önde, sıralama kolonu sonda olmalı. B-tree, eşitlik filtresini uyguladıktan sonra satırları zaten sıralı verir; ayrı bir Sort adımına gerek kalmaz. Sıra bozulursa planlayıcı sıralamayı kendisi yapmak zorunda kalır.

2. Cardinality burada ikincil. Her iki kolon da eşitlik predicate’i olduğu için asıl kazanç sıralamayı index’e taşımakta; yine de yüksek seçicilikli kolonu (genelde user_id) öne koymak ilk taramayı daraltır.

3. Fazlalık index bedava değil. Composite’in kapsadığı tekli indeksler artık okumaya katkı vermez ama her yazmada güncellenir.

Ne yapmalı

1. (user_id, status, created_at DESC) composite index’ini kur. Eşitlik kolonları başta, ORDER BY kolonu sonda.

2. DESC’i index tanımına yaz. Tek kolonda PostgreSQL index’i geriye de tarayabilir, ama composite’te yönü sabitlemek planlayıcının işini garantiler ve sıralamayı bedavaya getirir.

3. EXPLAIN (ANALYZE, BUFFERS) ile doğrula. İstediğin plan tek bir Index Scan — Bitmap Index Scan + Sort değil. Planda hâlâ Sort görüyorsan kolon sırası ya da DESC yönü hatalıdır.

4. Kapsanan tekli indeksleri sil. Composite user_id ve (user_id, status) öneklerini zaten karşılıyor; ayrı user_id/status indeksleri yalnızca yazmayı yavaşlatır.

5. completed baskınsa partial index düşün. Sorgularının çoğu status = 'completed' ise WHERE status = 'completed' koşullu partial index kullan: index boyutunu küçültür, RAM’de daha çok tutabilir, yazma maliyetini düşürür.

Sonuç: Ben olsam (user_id, status, created_at DESC) composite index’ini kurar, EXPLAIN (ANALYZE, BUFFERS) ile Index Scan’e düştüğünü doğrular ve bu index’in zaten kapsadığı tekli indeksleri silerdim. Trafiğin tek bir status’e yığılıyorsa partial index’le bir tur daha sık. Indeksleme ile native SQL’in dengesi üzerine daha derin bir tartışma için sade.dev’deki yazıya bak.