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.

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ı.

1. Önce eşitlik kolonları, sonra sıralama kolonu. Kural net: WHERE’de eşitlikle (=) süzdüğün kolonlar index’in başında olur — burada user_id ve status. ORDER BY’a giren kolon ise en sona gelir: (user_id, status, created_at DESC). Böylece B-tree, eşitlik filtresini uyguladıktan sonra satırları zaten created_at DESC sırasında verir; ayrı Sort adımına gerek kalmaz.
2. DESC’i index’e yaz. Sıralaman tek yönlüyse (created_at DESC) bunu index tanımına koy. 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. Cardinality eşit eşitliklerde ikincil. Cardinality, hangi eşitlik kolonunun önde olacağını etkiler; ama burada ikisi de eşitlik predicate’i, yani 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.
4. 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.
5. completed baskınsa partial index düşün. Sorgularının çoğu status = 'completed' ise WHERE status = 'completed' koşullu partial index index’i küçültür, RAM’de daha çok tutar, 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 user_id/status indekslerini silerdim — fazlalık index sadece yazmayı yavaşlatır. 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.