Tarihçe: PostgreSQL, I/O işlemlerine soyut para birimleri atayan maliyet temelli bir optimizatör kullanır. İlk veritabanı sistemleri esas olarak döner diskleri hedefliyordu; burada arama cezası rastgele I/O'yu sıralı okumalardan yaklaşık 40 kat daha pahalı hale getiriyordu. Bu asimetriyi azaltmak için, Bitmap Index Scans rastgele sayfa alımlarını amorti etmek üzere eşleşen tuple konumlarının bellek içindeki bir bitmap'ini oluşturarak yığın verilerine yaklaşık fiziksel sırayla erişmek amacıyla tanıtıldı.
Problemler: Temel ikilem, birçok veri sayfasına dağılmış binlerce satırı eşleşen orta derecede seçici predikatları filtrelerken ortaya çıkar. Bir Index Scan, her eşleşen tuple göstericisi için bir rastgele I/O gerçekleştirir ve mekanik disklerde aşınma ya da SSD'lerde aşırı I/O taleplerine sebep olur. Aksine, bir Bitmap Index Scan, bitmap yapısını inşa etmek için ek maliyet getirir ve bitmap work_mem kısıtlamaları nedeniyle kaybı olduğunda alakasız satırları işleyebilir.
Çözüm: Karar eşiği cost_index() ve cost_bitmap_heap_scan() fonksiyonları aracılığıyla belirlenir. Planlayıcı, sorguyu tatmin etmek için gerekli olan farklı yığın sayfa sayısını (pages_fetched) tahmin eder. pages_fetched değeri, random_page_cost / seq_page_cost oranını aştığında, optimizatör bitmap yaklaşımını tercih eder çünkü sıralı sayfa alım maliyeti, rastgele erişim cezasından daha yüksektir. random_page_cost'u (örneğin, SSD depolaması için 4.0'dan 1.1'e) azaltmak, rastgele I/O'nun algılanan cezasını düşürür ve planlayıcıyı daha önce bitmap oluşturmayı tetikleyen seçimlere geri yönlendirir.
Bir finansal raporlama platformu, mevcut mali çeyrek için account_id'ye göre transactions'ı toplayan bir gösterge tablolarında ciddi gecikmeler yaşadı. Tablo, döner disklerde 500 milyon satır içeriyordu. account_id = 12345 predikatı, yığın içerisinde dağılmış yaklaşık %12'lik bir satır eşleşti. İcra planı, rastgele I/O fırtınası nedeniyle 14 saniye süren standart bir Index Scan olduğunu gösteriyordu.
random_page_cost'u 4.0'dan 8.0'a artırmak, optimizatöre rastgele disk aramalarının aşırı pahalı olduğunu açıkça bildirmiştir. Bu ani değişiklik, planlayıcının bir Bitmap Index Scan seçmesini zorladı ve sayfa taleplerini sıralı aralıklara toplarken 1.8 saniyeye kadar süreyi azalttı. Ancak bu global ayar, uygulamadaki diğer OLTP nokta-arama sorgularını cezalandırarak, yoğun ticaret saatleri sırasında kilit içermeyi artıran daha az verimli sıralı taramalara geçmelerine neden oldu.
(account_id, transaction_date, amount) üzerinde bir kapsayıcı indeks oluşturmak, yığın üzerini tamamen atlayarak 80 ms yanıt süreleri veren bir Index Only Scan'i mümkün kıldı. Okumalar için optimal olmasına rağmen, bileşik indeks tablo boyutunu %35 oranında artırdı ve her ekleme şimdi iki büyük B-ağaç yapısını korumayı gerektirdiğinden, gerçek zamanlı ticaret kaydı için katı SLA'yı ihlal etti.
created_at üzerinden aralıklara göre tablo parçalama uygulamak ve random_page_cost'u 6.0 olarak ayarlamak durumunda kaldık. Bu hibrit yaklaşım, sorguyu mevcut çeyreğin parçasıyla kısıtlayarak, bitmap eşiğinin altına düşen toplam sayfa sayısını azaltırken, artırılan maliyet parametresi, geçmiş sorguların tarihsel sorgularının hala bitmap kullanarak rastgele I/O doygunluğunu önlemesini sağladı. Bu çözüm, ticaret sisteminin yazma performansı kısıtlamalarını dikkate alarak okuma ağırlıklı raporlama yolunu optimize etti.
Sonuç: Gösterge tablosu sorgusu, OLTP ekleme performansını düşürmeden 400 ms'de istikrara kavuştu ve raporlama düğümündeki disk I/O kullanımı iş saatleri boyunca %95'ten %30'a düştü.
Planlayıcının maliyet modelindeki effective_cache_size ile random_page_cost nasıl etkileşimde bulunur ve büyük bir önbelleğe sahip bir sistemde random_page_cost'u düşürmek neden belirli bir join türü için performansı kötüleştirebilir?
effective_cache_size, disk önbellekleme için mevcut olan belleği ölçer. Yüksek ayarlandığında, planlayıcı birçok sayfanın RAM'de bulunduğunu varsayar ve random_page_cost ayarına bakılmaksızın I/O maliyetlerini etkili bir şekilde göz ardı eder. Eğer random_page_cost'u agresif bir şekilde 1.1'e (NVMe SSD'ler için tipik) düşürürken büyük bir effective_cache_size tutarsanız, optimizatör mantıksal olarak Index Scans kullanan Nested Loop join'lar üzerinde Hash Joins'a kıyasla tercih edebilir. Model, iç ilişki indeks sorgularının neredeyse bedava olduğunu varsayıyor çünkü rastgele I/O ucuz ve önbellekte, büyük iç döngülerin yine de tuple işleme ile CPU'u doygun hale getirdiğini ve önbellek yerinden kaldırmayı tetiklediğini göz ardı ediyor, tek bir toplu hash işlemiyle iç tabloyu bir kez taramaktan daha kötü duvar zamanı ile sonuçlanıyor.
PostgreSQL'in Bitmap Index Scan'i, Bitmap Heap Scan'den nasıl farklıdır ve planlayıcı neden tek bir bileşik indeks kullanmak yerine birden fazla indeks üzerinde BitmapOr işlemleri tercih eder? Bitmap Index Scan indeks yapısını tarar ve eşleşen tuple göstericilerinin (veya kayıplıysa sayfa aralıklarının) bir bitmap'ini oluşturur. Bitmap Heap Scan sonrasında o bitmap'i kullanarak tabloyu sayfalar halinde sıralı olarak erişerek gerçek satır verilerini geri alır. BitmapOr (veya BitmapAnd), WHERE status = 'active' OR priority = 'high' gibi koşullarda sorgu filtrelendiğinde, ayrı indeksleri eşleştiren durumlarda gerçekleşir. PostgreSQL, iki B-ağacını tek bir geçişte verimli bir şekilde aynı anda tarayamadığından, her bir indeksten bağımsız bitmalar oluşturur ve bunları bit düzeyinde işlemlerle birleştirir. Bu teknik, sorgular status veya priority'yi tek başına veya her ikisini değişken olarak filtrelerken (status, priority) bileşik indeksine göre tercih edilir, çünkü iki ayrı indeksin sürdürülmesi, çok sayıda kapsayıcı bileşik varyantlara kıyasla önemli derecede daha düşük yazma artırımı sağlar.
Bir sorgu LIMIT ifadesini kullandığında, PostgreSQL neden standart bir Index Scan'i destekleyen erken sonlandırmanın avantajına rağmen yine de bir Bitmap Index Scan tercih edebilir ve eski istatistiklerin bu yanlış hesaplamayı nasıl etkilediği nedir?
Bir standart Index Scan, gerekli sıralamayı destekleyen bir indeks varsa LIMIT N satırını aldıktan hemen sonra sona erebilir ve I/O'yu minimize eder. Ancak, planlayıcı, eşleşen satırların sayısını eski ANALYZE istatistikleri veya ilişkili sütunlar nedeniyle azımsarsa, Index Scan'in eşleşmeleri bulmadan önce aşırı sayıda yaprak sayfasını tarayacağını varsayar. Bu nedenle I/O maliyetlerini azaltmak için Bitmap Index Scan'i seçer. Bitmalar tamamen malzeme edilmeden yığına erişilmediğinden, yürütücü erken duramaz; ilk onu hariç tutmak için binlerce satırı atmak amacıyla bir bitmap oluşturur ve bu da planlayıcının iyimser tahminine kıyasla felaket gecikmelere neden olur.