Sürekli güncellenen bir sessions tablosunda staging'de Index Scan alan sorgu production'da neden Seq Scan'e düşüyor, EXPLAIN ile nasıl teşhis ederim?

Kısa cevap: Aynı sorgunun farklı plan alması, planner’ın maliyet tahmininin ortamlar arasında farklı olması demektir.

Kısa cevap

Çok yazılan bir sessions tablosunda bunun nedeni neredeyse her zaman bayat istatistik ve tablo bloat’udur — “eksik index” değil. Bir index eklemeden veya sorguyu yeniden yazmadan önce şunu içselleştirin: plan, maliyet modelinin bir çıktısıdır ve maliyet modeli, kendisini besleyen istatistikler kadar iyidir. Girdileri düzeltirseniz plan çoğu zaman kendini toparlar. İstatistik tazelendikten sonra hâlâ index’ten şüphe ediyorsanız, kolon sırasının plana etkisini PostgreSQL composite index kaydında ayrıntılandırmıştım.

Neden

1. Yüksek devir hızlı tabloda istatistik bayatlar. sessions sürekli INSERT/UPDATE/DELETE alır; autovacuum/autoanalyze buna yetişemeyince n_distinct ve histogramlar yanlış kalır. Planner “10 satır” derken gerçekte 2 milyon geliyorsa, planner yanıltılıyordur ve Seq Scan’i bu yüzden seçer.
2. Tablo ve index bloat’u planı çevirir. Sürekli update’lerden kalan dead tuple’lar heap’i ve index’leri şişirir; index canlı satırlara oranla o kadar büyür ki planner (haklı olarak) Seq Scan’i daha ucuz bulur.
3. Veri dağılımı gerçekten farklı olabilir. Staging’de küçük/tekdüze veri vardır; staging’de seçici olan bir predicate production’da satırların çoğuyla eşleşiyorsa Seq Scan orada gerçekten daha ucuzdur. Bu bir bug değildir — plan veriye göre doğrudur; planner’ı değil, sorguyu/index’i düzeltin.
4. PostgreSQL’de native query hint yoktur. Yani planı zorla çevirecek bir kaçış kapısı aramanın anlamı yok; asıl çözüm istatistik/bloat/index’i düzeltmektir.

Ne yapmalı

1. EXPLAIN değil, EXPLAIN (ANALYZE, BUFFERS) çalıştırın. Düz EXPLAIN yalnızca tahmini gösterir; production’da EXPLAIN (ANALYZE, BUFFERS) ile tahmini ve gerçek satır sayısını yan yana görün.

2. İstatistiği tazeleyin, ölü satırı ölçün. ANALYZE sessions; çalıştırın; pg_stat_user_tables’ta last_autoanalyze ve n_dead_tup’a bakın ve bu tabloya özel daha agresif autovacuum düşünün.

   -- Teşhis: tahmin vs gerçek
   EXPLAIN (ANALYZE, BUFFERS) SELECT ... FROM sessions WHERE ...;
   
   -- Taze istatistik ve ölü satır kontrolü
   ANALYZE sessions;
   SELECT n_live_tup, n_dead_tup, last_autoanalyze
   FROM pg_stat_user_tables WHERE relname = 'sessions';
   
   -- Bu tabloya özel daha agresif autovacuum
   ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.02,
                             autovacuum_analyze_scale_factor = 0.01);

3. Aurora’ya özgü noktaları hesaba katın. Aurora reader/writer ayrımı ve staging’i production istatistikleriyle birebir taklit edememe gerçeği vardır. sessions için autovacuum_vacuum_scale_factor/analyze_scale_factor’ı düşürün; hep aktif oturumları filtreliyorsanız partial index düşünün.

4. İlk refleks hint olmasın. Önce istatistiği ve bloat’ı düzeltin; SSD için random_page_cost’u 1.1’e doğru düşürmek de index kullanımını teşvik edebilir.

Sonuç: Ben olsam önce production’da EXPLAIN (ANALYZE, BUFFERS) çekerdim, sonra tabloyu ANALYZE edip n_dead_tup’a bakardım; 10 vakanın 9’unda taze istatistikle plan geri döner. sessions doğası gereği çok yazılan bir tabloysa, autovacuum’unu tabloya özel ayarlar ve aktif-oturum predicate’i için partial/covering index eklerdim.