Partial index kuyruk tablosunu kırk bir kat küçültüyor — planner onu seçtiği sürece

Ağustos başında bir soru geldi: milyonlarca completed satırın arasında birkaç bin pending satırı sorgulayan bir kuyruk tablosunda partial index mi, düz index mi? “Evet, bu partial index’in ders kitabı örneğidir” diye cevapladım ve altı madde saydım. Hiçbirini ölçmemiştim.

Bu kayıt o cevabı sınıyor.

Üç kademe, dört strateji

Strateji	100k ölü satır	1M	10M	Index boyutu (10M)
index yok	2.001 tps	247 tps	7 tps	—
(status)	6.499 tps	6.417 tps	6.426 tps	66,1 MB
(status, created_at)	12.414 tps	11.202 tps	10.795 tps	310,4 MB
partial (created_at) WHERE pending en hızlı ve en küçük	13.041 tps	11.707 tps	11.537 tps	7,6 MB

İlk satır cevabın gerekçesini doğruluyor: index’siz bir kuyruk tablosu ölü satırla birlikte çökmüyor, yok oluyor. 2.001’den 7 tps’ye. 10 milyon satırın arasından 5.000 satır bulmak sequential scan ile saniyede yedi kez yapılabilir.

Düz (status) index’i de iddia edildiği gibi: kardinalitesi iki olan bir kolona index koymak yardım ediyor ama tavanı düşük — üç kademede de 6.400 civarında takılıyor.

Asıl fark hızda değil, boyutta

Partial ile composite arasında throughput farkı %6,9. Küçük. Ama boyut farkı tabloyla birlikte açılıyor:

Etkileşimli grafiği aç

Tooltip ve imleç okuması için ~60 kB JavaScript iner. Aşağıdaki veri tablosu her hâlde açık.

Veri tablosu

pg_relation_size, bench/run.sh

Seri	100k ölü satır	1M	10M
(status, created_at)	11,2 MB	40,3 MB	310,4 MB
partial	6,5 MB	7,6 MB	7,6 MB

Grafik tarayıcıda çizilir; aşağıdaki tablo aynı veriyi taşır.

Composite index tabloyla büyüyor: 11,2 → 40,3 → 310,4 MB. Partial 7,6 MB’da duruyor, çünkü indekslediği şey tablo değil kuyruk — ve kuyruk sabit. On milyon satırlık tabloda kırk bir kat fark.

Bu, cevabın “küçük olur, belleğe sığar, güncellemesi ucuzdur” cümlesinin sayısal karşılığı. 310 MB’lık bir index’i shared_buffers’da tutmak 7,6 MB’lık birini tutmakla aynı şey değil, ve her insert/update’te güncellenen ağacın boyutu da öyle.

Ve sonra planner fikrini değiştiriyor

Cevabın ikinci maddesi şuydu: “status = $1 gibi parametreyle sorarsanız planner index’i seçemeyebilir; koşulu literal 'pending' olarak yazın.” Uyarıyı yazarken ne kadar ciddi olduğunu bilmiyordum.

10M ölü satır, parametreyle	tps	Ortalama gecikme	Plan
partial · custom plan	11.752	0,68 ms	Limit → LockRows → Index Scan
partial · generic plan index hiç taranmadı	7	1.113 ms	Limit → LockRows → Sort → Seq Scan
composite · custom plan	11.105	0,72 ms	Index Scan
composite · generic plan	11.416	0,70 ms	Index Scan

Bin altı yüz yetmiş üç kat. Partial index generic planda taranmıyor — index tarama sayacı sıfır — ve sorgu index’i hiç olmayan tablonun sayısına düşüyor: 7 tps. Gecikme 0,68 ms’den 1,1 saniyeye çıkıyor.

Composite index aynı koşulda hiç etkilenmiyor.

Mekanizma plan ağacında görünüyor. Generic plan, $1’in ne olduğunu bilmeden kurulur. Partial index’in kullanılabilmesi için planner’ın $1 = 'pending' olduğunu kanıtlaması gerekir — çünkü index yalnız o satırları içeriyor. Kanıtlayamaz, dolayısıyla index’i eleyip sequential scan’e düşer. Composite index’in kanıtlanacak bir predicate’i yoktur; status kolonu index’in içindedir ve $1 ne olursa olsun index taranabilir.

Bunun nasıl production’da patladığı

auto modunda Postgres ilk beş çalıştırmada custom plan kullanır, sonra generic planın maliyetini karşılaştırıp ona geçebilir. Yani bu profil ısındıkça bozulur: uygulama açılır, ilk istekler hızlıdır, worker’lar birkaç dakika çalıştıktan sonra aynı sorgu bin kat yavaşlar.

Staging’de görünmez, çünkü staging’de o deyim beş kez koşmadan test biter.

Sor Bakalım’da buna çok benzeyen başka bir soru vardı: staging’de Index Scan alan sorgu production’da neden Seq Scan’e düşüyor? Orada teşhisi bayat istatistik ve tablo bloat’u diye koymuştum. Bu ölçüm üçüncü bir sebep gösteriyor, ve bu sebebin ANALYZE ile ilgisi yok.

Cevabın karnesi

Sor Bakalım cevabındaki iddia	Sonuç
Partial index küçük olur, belleğe sığar	doğru — 7,6 MB, tabloyla büyümüyor
Düz (status) index'inden net üstün	doğru — 11.537 vs 6.426 tps
ORDER BY kolonunu index'e koyun	doğru — Index Scan sıralamayı bedavaya alıyor
Parametreyle sorarsanız planner seçemeyebilir	doğru ama dar: sorun predicate'li index
Churn ve bloat'a dikkat	ölçülmedi — 30 sn autovacuum için kısa

Bir tavsiyeyi ölçmenin değeri onu doğrulamak değil — dördü zaten doğruydu. Değeri, doğru olan bir cümlenin hangi koşulda tersine döndüğünü bulmakta.