join_collapse_limit'in 32'den 34'e çıkarılması bir Postgres sorgusunu 6.000 kat hızlandırdı

Tek bir PostgreSQL planner ayarı, bir rapor sorgusunu 140.611 ms'den 23,9 ms'ye indirdi — neredeyse 6.000 katlık bir hızlanma — ve bunun nedeni eksik bir index ya da eski istatistikler değil, planner'ın iki iç adımı hangi sırayla gerçekleştirdiğiydi. Payyavula Balanagaraju'nun detaylı dev.to yazısına göre bu davranış Postgres 15'te ve şimdiye kadar yayınlanan tüm sürümlerde mevcut; Postgres 16 ve sonrasında sorunu tamamen ortadan kaldırmayan ama hafifleten bir değişiklik var.

Tek satırlık bir sorgunun ardından iki dakika

Rapor bir view'dan seçim yapıyordu ve o view da başka view'ların üzerine kurulu bir yığının tepesindeydi. Postgres, planlamadan önce bir view'ın kayıtlı SQL'ini sorguya özyinelemeli olarak yapıştırır; böylece tek satırlık SELECT yaklaşık 435 tablo referansına genişledi; tek bir iç view başına 121 referans katkıda bulundu ve üç kez geçti.

EXPLAIN ANALYZE, bir join'in iki tarafını da inşa eden ve sonra join filtresi üzerinden 8.944.739 satırı atan bir nested loop gösterdi. Course tablosundaki bitmap heap scan bir satır tahmin edilmişti ama 181 satır döndürdü; bu yüzden yaklaşık 538,8 ms maliyetindeki bir iç hash adımı her course için bir kez çalıştı — toplamda kabaca 97,5 saniye. Hızlı alternatif, her course için enrollment'ların bir index üzerinden aranması olurdu; optimizer böyle bir plan şeklini hiç üretmedi.

İşe yarayan ama boşluklu bir ayar

İlk düzeltme, bir yapay zeka asistanının önerisiyle join_collapse_limit'i yükseltmekti. İşe yaradı, ama nedenini açıklamıyordu; bu yüzden yazar, gerçek verinin bir kopyası üzerinde 8'den 140'a kadar olan değerleri, her biri için üç koşuyla benchmark'tan geçirdi. Sonuçlar bir eşik değildi. 8, 10, 14, 26, 28 ve 32 değerleri yavaş planı üretirken, 12, 16 ile 24 arası, 30 ve 34'ten yukarısı hızlı planı üretti. 32'de sorgu 140.611 ms sürdü; 34'te 23,9 ms sürdü. Ayar basitçe "büyük olan iyidir" olsaydı, 12 hızlıyken 14 yavaş olamazdı.

Gruplama, budamadan önce yapılıyor

Burada iki planner mekanizması etkileşiyor. Birincisi, Postgres bir LEFT JOIN'i tamamen kaldırabilir; bunun için join'in sağ tarafı tek bir tablo olmalı, join sütunlarında birincil anahtar veya unique index aracılığıyla benzersizlik kanıtlanmış olmalı ve o tablonun hiçbir sütunu join'in üzerinde hiçbir yerde kullanılmamış olmalı — 15.8 kaynak kodunda analyzejoins.c içindeki join_is_removable kontrolü. Bu sorguda kaldırma işlemi ~435 referansı yaklaşık 11'e indirdi. İkincisi, olası join sıraları faktöriyel biçimde büyüdüğünden (11 tablo yaklaşık 40 milyon sıra demektir) planner aramasını sınırlar: join_collapse_limit, varsayılan 8, tek bir grupta kaç öğe bulunabileceğini sınırlar; geqo_threshold ise, varsayılan 12, daha büyük grupları rastgele seçim yapan GEQO optimizer'ına devreder.

Sorun sıralamada. Postgres 15.8'deki query_planner() fonksiyonunda deconstruct_jointree (planmain.c, satır 184) join listesini inşa eder ve hâlâ ~435 tablo sayılırken join_collapse_limit'i uygular; remove_useless_joins (satır 221) sonradan budar ve liste bir daha asla yeniden inşa edilmez. Kurucu, iki tarafı birlikte limiti aşan bir JOIN ile karşılaştığında bunları iki ayrı alt liste olarak döndürür ve her alt liste daha sonra izole biçimde planlanır — üst seviye yalnızca bitmiş bir sonuç görür. Parametreli bir nested loop, yani "her course için enrollment'ları ara" şekli, yalnızca iki tablo da aynı listede olduğunda değerlendirilebilir. Biri bir alt listenin içinde, diğeri dışında kalırsa hızlı plan reddedilmiş bir seçenek değildir; aday olarak hiç var olmamıştır. Planner maliyetleri yanlış değerlendirmiyor.

SELECT listesi neden önemli

Join'in hayatta kalması sütun kullanımına bağlıdır, dolayısıyla sonucu raporun sütun listesi belirledi. İki sütun seçildiğinde budamadan sonra yalnızca üç tablo kaldı ve sorgu varsayılan ayarlarla hızlıydı. Raporun 27 sütununun seçilmesi ise 10–11 tabloyu oyunda tuttu ve yavaş planı tetikledi. Fazladan tek sütun, aksi halde atılacak bir tabloyu sorguya geri çekebilir.

Neden önemli

Yazı, EXPLAIN erişimi olan herkesin uygulayabileceği tanılama yöntemleri sunuyor. "Rows Removed by Join Filter" içindeki milyonlarca satır, tahmini ve gerçek satırlar arasındaki büyük farkla birlikte görüldüğünde, bu bir index sorunu değil plan şekli sorununa işaret eder. Katmanlı view'lar tek satırlık bir sorguyu sessizce yüzlerce tablo referansına genişletebilir ve sütun listesi kaçının budamadan kurtulacağını belirler. Ayrıca join_collapse_limit monotonik değildir — güvenli değerlerin boşlukları vardır — dolayısıyla bugün çalışan bir ayar, biri bir rapora sütun eklediğinde gerileyebilir; çok yükseltmek ise planlama maliyetini artırır ve GEQO'nun rastgele aramasını tetikleyebilir. Yazarın yaptığı gibi, aday değerleri gerçek verinin bir kopyası üzerinde test etmek tek güvenilir kontrol olmaya devam ediyor.