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; eşitlik kolonları arasında sıra, index’in ne kadarının taranacağını değiştirmez (PostgreSQL kılavuzu: baştaki kolonlardaki eşitlik koşulları taranan bölümü sınırlar); user_id başa gidiyor çünkü yalnızca user_id ile filtreleyen sorgulara da hizmet ediyor.

3. Fazlalık index bedava değil. Tekli user_id indeksi artık composite’in gereksiz bir öneki, ama her yazmada hâlâ güncelleniyor.

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 yazmak burada isteğe bağlı. PostgreSQL bir B-tree’yi geriye doğru tarayabilir; baştaki iki kolon eşitlikle sabitlendiğinde (user_id, status, created_at) de ORDER BY created_at DESC’i karşılar. Index’in sorguyu belgelemesini istiyorsan yönü yaz; ORDER BY yönleri karışıksa (ör. x ASC, y DESC) gerçekten gerekir.

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ına bak: sıralama kolonu eşitlik kolonlarından sonra gelmeli.

4. Tekli user_id indeksini sil. Composite, baştaki kolon üzerindeki her aramayı zaten karşılıyor. status ve created_at indeksleri composite’in öneki değil; başka sorgular kullanıyorsa tut, silmeden önce pg_stat_user_indexes’e bak.

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 gereksiz kalan user_id indeksini 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.