Sorunun cevabı
PostgreSQL, pg_statistic içinde sütun başına histogramlar tutar ve çok sütunlu filtreler için seçilebilirlik tahmin ederken sütunlar arasında istatistiksel bağımsızlık varsayar. city ve state_code veya bir araç veritabanında make ve model gibi yüksek korelasyonlu sütunlara filtre uygulandığında, planlayıcı bireysel seçilebilirlikleri çarparak gerçek satır sayısını önemli ölçüde yanlış tahmin eder. Bu kardinalite hatası genellikle optimizatörü Nested Loop arasındaki birleşimleri tercih etmeye zorlar ve büyük tablolarda felaket düzeyinde performansa yol açar.
Bunu çözmek için CREATE STATISTICS kullanarak genişletilmiş bir istatistik nesnesi oluşturursunuz; bu, çok değişkenli korelasyon verisi oluşturur. Özellikle, dependencies türü sütunlar arasındaki işlevsel bağımlılıkları izler, planlayıcının state_code = 'CA' üzerinde filtre uygulamanın, city'yi California değerleri ile sınırladığını tanımasına olanak tanır ve çarpma hatasını önler.
-- Korelasyonlu sütunlar için genişletilmiş istatistikleri oluştur CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- İstatistikleri doldur ANALYZE vehicles;
Hayattan bir durum
Bir lojistik platformu, 50 milyon satırlık shipments tablosunu customers ile birleştiren bir gönderi izleme panosunda mücadele ediyordu. Sorgu origin_state ve origin_city üzerinden filtre uyguladı; burada 'Springfield' olarak şehir için satırların %95'i aslında 'IL'deydi, ancak planlayıcı her iki kriterin bağımsız olarak karşılaştığına yalnızca %2’sinin eşleştiğini varsayıyordu. 500 satır tahmin etti ve bir Nested Loop birleşimi seçti; milyonlarca müşteri kaydı arasında yineleme yaparak 90 saniye sonra zaman aşımına uğradı.
Bir dikkate alınan düzeltme, oturumda SET enable_nestloop = off komutunu kullanarak Nested Loop birleşimlerini tamamen devre dışı bırakmaktı. Bu, bir Hash Join zorladı ve bu spesifik sorguda 3 saniyede tamamlandı, ancak ciddi riskler taşıyordu: global yapılandırma değişiklikleri bağlantı havuzları arasında yayılır ve küçük tablolarda iyi çalışan diğer meşru Nested Loop planlarını gerileyebilirdi. Ayrıca, bu geçici çözüm, sorgulama öncesinde parametreyi ayarlamak için uygulama düzeyinde kod değişiklikleri gerektiriyordu.
Başka bir seçenek, (origin_city, origin_state) üzerinde bileşik bir indeks oluşturmaktı. Bu, indeks seçiminde iyileşme sağlasa da, kardinalite yanlış tahminini çözmedi; planlayıcı hâlâ indeks taramasından birkaç satır çıkacağını düşündü ve Nested Loop stratejisini sürdürdü, yalnızca kaplama indeksinde daha hızlı bir şekilde yürüttü. Ayrıca, geniş bileşik indeks 4GB ek disk alanı tüketti ve yüksek hızdaki shipments tablosundaki yazma işlemlerini yavaşlattı.
Ekip nihayet CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments komutunu çalıştırarak genişletilmiş istatistikleri uyguladı, ardından ANALYZE yaptı. Bu yaklaşım sorgu yeniden yazımları gerektirmedi ve önemsiz bir depolama yükü ekledi. Kullanımdan sonra, planlayıcı 45,000 satırı doğru bir şekilde tahmin etti ve bir Hash Join seçti, sorgu gecikmesini 400 milisaniyeye düşürdü ve alakasız iş yükleri için optimal planları korudu.
Adayların sıkça atladığı noktalar
Genişletilmiş istatistikleri yenileme işlevine sahip ANALYZE komutu nasıl çalışır ve istatistik nesnesi oluşturulduktan hemen sonra neden kullanılmamış görünebilir?
ANALYZE, genişletilmiş istatistikleri yalnızca hedef tablo üzerinde açıkça çağrıldığında veya istatistik nesnesi mevcut olduktan sonra autovacuum işlemleri tabloyu işlediğinde hesaplar. Birçok aday, CREATE STATISTICS işleminin planlamayı anında etkilediğini varsayar, ancak katalog tabloları pg_statistic_ext ve pg_statistic_ext_data bir sonraki analiz döngüsüne kadar boş kalır. Sonuç olarak, planlayıcı, ANALYZE shipments; çok değişkenli verileri doldurana kadar tek sütunlu histogramlar ve bağımsızlık varsayımlarını kullanmaya devam eder. Kullanım doğrulaması, boş olmayan dependencies veya ndistinct değerleri için pg_stats_ext görünümünü kontrol ederek yapılabilir.
CREATE STATISTICS içinde dependencies ile ndistinct arasındaki işlevsel farklılık nedir ve her biri hangi sorgu kalıplarından fayda sağlar?
Dependencies, bir sütunun diğerini belirlediği işlevsel ilişkileri yakalar (örneğin, zip_code city'yi belirler), WHERE koşulu seçilebilirlik tahminlerini doğrudan doğruya düzeltir. Ndistinct sütun gruplarındaki benzersiz kombinasyonların kesin sayısını hesaplar, bu da GROUP BY ve DISTINCT tahminlerini geliştirir, filtre seçilebilirliğinden ziyade. Adaylar genellikle bunları karıştırır, korelasyonlu sütunlar üzerinde GROUP BY içeren yavaş sorgularında dependencies oluşturur veya bunun tersini yapar. Optimal sonuçlar için her iki türü de belirtin: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
Neden genişletilmiş istatistikler, korelasyonlu sütunlar arasında OR koşulları kullanan sorgulara yardımcı olmayabilir?
Genişletilmiş istatistikler şu anda yalnızca seçilebilirlik çarpılmasının gerçekleştiği AND koşullarıyla yardımcı olmaktadır. OR ile filtre uyguladığınızda (örneğin, city = 'Springfield' OR state = 'IL'), PostgreSQL seçilebilirliği P(A) + P(B) - P(A ∩ B) formülü kullanarak hesaplar ve istatistikler birleşimler için ortak seçilebilirliği takip ettiğinden, kesişim terimine bağımlılık katsayılarını uygulayamaz. Adaylar bu sınırlamayı sıklıkla gözden kaçırır ve OR tabanlı kardinalite hatalarını düzeltmek için CREATE STATISTICS kullanmaya çalışır, bu da sorgu yazımını gerektirir (örneğin, UNION ALL dallarına ayırma) veya kısmi indeksler gerektirir.