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

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. 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. Yüksek devir hızlı tabloda bayat istatistik. sessions sürekli INSERT/UPDATE/DELETE alır; autovacuum/autoanalyze buna yetişemeyince n_distinct ve histogramlar yanlış kalır. ANALYZE sessions; çalıştırın ve pg_stat_user_tables’ta last_autoanalyze’a bakın.
3. Tablo ve index bloat’u. 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. n_dead_tup’a bakın ve bu tabloya özel daha agresif autovacuum düşünün.
4. 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.
5. Aurora’ya özgü noktalar. 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.
6. İlk refleks hint olmasın. PostgreSQL’de native query hint yoktur; asıl çözüm istatistik/bloat/index’i düzeltmektir. SSD için random_page_cost’u 1.1’e doğru düşürmek de index kullanımını teşvik edebilir.

-- 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);

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.