Veritabanı Optimizasyonu: PostgreSQL Extended Statistics ile Plan Hataları

Veritabanı optimizasyonu için kardinalite hatasını teşhis etme

İleri seviye bir veritabanı eğitimi içinde kritik ayrım, yavaş sorgunun indeks eksikliğinden mi yoksa optimizerin yanlış satır tahmininden mi kaynaklandığını ayırabilmektir. PostgreSQL, varsayılan kolon istatistiklerinde bağımsızlık varsayımı yapar: region_id='TR' satırlarının oranı ile status='PENDING' satırlarının oranını çarpar. Oysa belirli bölgelerde bekleyen sipariş oranı sistematik olarak daha yüksekse bu çarpım gerçek seçiciliği vermez. EXPLAIN çıktısındaki Plan Rows ile Actual Rows arasında 10x ve üzeri fark, özellikle iç içe döngü join'lerinde yanlış plan seçiminin güçlü sinyalidir.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, created_at, total
FROM orders
WHERE region_id = 34
  AND status = 'PENDING'
  AND created_at > now() - interval '7 days';

Bu çıktıda her düğüm için Actual Rows / Plan Rows oranını hesaplayın. Örneğin Bitmap Heap Scan düğümünde Plan Rows=120, Actual Rows=48_000 ise hata 400 kattır; üstteki Hash Join yerine Nested Loop seçimi bu hatanın doğrudan sonucu olabilir. BUFFERS alanındaki shared read ve temp read blokları da plan hatasının I/O maliyetini somutlaştırır. Aynı sorguyu yalnızca status ve yalnızca region_id filtresiyle çalıştırmak, hatanın kolon tekilliklerinden değil kolon kombinasyonundan geldiğini doğrular.

Sorgu adaylarını üretimde elle tahmin etmek yerine pg_stat_statements ile çağrı sayısı yüksek ve ortalama yürütme süresi pahalı sorguları seçin. Bu yaklaşım, sql eğitimi örneklerindeki tekil demo sorgularından farklı olarak gerçek iş yüküne odaklanır. pg_stat_statements sorgu metnini normalize ettiği için uygulamanın farklı parametrelerle gönderdiği aynı sorguyu tek queryid altında toplar.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       rows,
       shared_blks_read,
       temp_blks_written,
       query
FROM pg_stat_statements
WHERE query ILIKE '%FROM orders%'
ORDER BY total_exec_time DESC
LIMIT 20;

Bu sorgudan seçtiğiniz queryid için uygulamanın en sık kullandığı parametre çiftlerini ayrıca kayıt altına alın. Sadece nadir bir region_id ile EXPLAIN almak yanıltıcıdır; MCV istatistikleri en sık değer kombinasyonlarında en fazla farkı yaratır.

Extended Statistics ile korelasyonlu filtreleri modele ekleme

Korelasyonlu eşitlik filtrelerinde üç Extended Statistics türü farklı problem çözer: dependencies bir kolonun diğerini fonksiyonel olarak belirlediği durumları, mcv sık görülen çok kolonlu değer kombinasyonlarını, ndistinct ise GROUP BY ve çok kolonlu DISTINCT kardinalitesini hedefler. Sipariş örneğinde region_id ve status için hem dependencies hem mcv genellikle birlikte anlamlıdır; created_at gibi aralık koşulu ise dependencies tarafından eşitlik koşulları kadar iyi modellenmez.

CREATE STATISTICS orders_region_status_stats
    (dependencies, mcv, ndistinct)
ON region_id, status
FROM orders;

ANALYZE VERBOSE orders;

SELECT stxname,
       stxkeys,
       stxkind
FROM pg_statistic_ext
WHERE stxrelid = 'orders'::regclass;

CREATE STATISTICS yalnızca katalog tanımı oluşturur; plannerın yeni veriyi kullanması için ardından ANALYZE zorunludur. ANALYZE VERBOSE çıktısındaki taranan sayfa ve örneklenen satır bilgilerini dağıtım ekibinin loguna alın. Çok büyük tabloda bu işlem I/O üretir; bakım penceresi ve replica gecikmesi gözlenmeden körlemesine çalıştırılmamalıdır.

MCV listesinin gerçekten beklediğiniz kombinasyonu içerip içermediğini, yeterli yetkiniz varsa pg_statistic_ext_data üzerinden inceleyin. Bu kontrol önemlidir: çok seyrek ama uygulama açısından kritik bir kombinasyon örnekleme sonucu MCV listesine girmeyebilir. Böyle bir durumda istatistik hedefini sadece problemli kolonlarda yükseltmek, tüm tablo için rastgele yükseltmekten daha kontrollüdür.

ALTER TABLE orders
  ALTER COLUMN region_id SET STATISTICS 1000;
ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 1000;

ANALYZE orders;

SELECT s.stxname,
       i.index AS item_no,
       i.values,
       i.frequency,
       i.base_frequency
FROM pg_statistic_ext s
JOIN pg_statistic_ext_data d ON d.stxoid = s.oid
CROSS JOIN LATERAL pg_mcv_list_items(d.stxdmcv) AS i
WHERE s.stxname = 'orders_region_status_stats';

frequency ile base_frequency arasındaki büyük fark, bağımsızlık varsayımının ne kadar yanlış olduğunu gösterir. İstatistik hedefini 1000 yapmak ANALYZE örneklemesini ve pg_statistic katalog boyutunu artırır; bu nedenle bunu tüm kolonlara uygulamak yerine EXPLAIN ile kanıtlanmış eğri dağılımlı kolonlarla sınırlayın.

Veritabanı performans ayarlama: önce-sonra ölçüm protokolü

Veritabanı performans ayarlama çalışmasında yalnızca yeni planın daha kısa görünmesi yeterli değildir. Aynı SQL metni, aynı parametreler, aynı veri hacmi ve mümkün olduğunca benzer cache koşullarıyla ölçüm yapın. Üretim ortamında işletim sistemi page cache temizlemek yerine, iki dönemde de en az 5 tekrar çalıştırıp medyan süreyi kullanın. İlk çalıştırmayı ayrı kaydedin; bu çalıştırma soğuk okuma maliyetini içerirken sonraki tekrarlar shared buffer ve OS cache etkisini taşır.

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON)
SELECT id, created_at, total
FROM orders
WHERE region_id = 34
  AND status = 'PENDING'
  AND created_at > now() - interval '7 days';

İstatistik oluşturulmadan önce ve ANALYZE sonrasında bu JSON planını dosyaya kaydedin. Karşılaştırmada Total Cost yerine Actual Total Time, Plan Rows ile Actual Rows farkı, shared hit/read blokları, temp read/write blokları ve seçilen join algoritmasına bakın. Cost değeri yapılandırma parametrelerine bağlı bir model çıktısıdır; gerçek gecikme yerine tek başına başarı metriği değildir.

pg_stat_statements ile değişiklikten önceki ve sonraki zaman pencerelerini karşılaştırırken queryid, calls ve toplam süreyi birlikte değerlendirin. Tek başına mean_exec_time düşüşü, çağrı parametre dağılımı değiştiyse yanlış sonuç verir. Aynı queryid için p95 gecikmesini uygulama APM aracından veya PostgreSQL loglarında log_min_duration_statement eşiğiyle alın; pg_stat_statements ortalama değer verir, kuyruk gecikmesini vermez.

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_read,
       temp_blks_written
FROM pg_stat_statements
WHERE queryid = 1234567890123456789;

Ölçümden önce pg_stat_statements_reset() çağırmak paylaşımlı üretim metriklerini siler. Bunun yerine değişiklik anının zaman damgasını kaydedin veya staging ortamında reset kullanın. Bu ayrıntı, veritabanı optimizasyonu denemelerinin diğer ekiplerin performans incelemesini bozmasını engeller.

Database indexleme ile Extended Statistics arasındaki sınır

Database indexleme ve Extended Statistics birbirinin alternatifi değildir. İndeks erişim yolunu belirler, Extended Statistics ise optimizerin bu erişim yolunun kaç satır döndüreceğini tahmin etmesine yardım eder. region_id, status ve created_at ile filtrelenen bir sorguda doğru tahmin olsa bile uygun indeks yoksa planner sıralı tarama seçebilir. Tersine, uygun B-tree indeks mevcutken yanlış tahmin plannerı pahalı bir Bitmap Heap Scan veya Nested Loop yoluna sokabilir.

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_region_status_created_idx
ON orders (region_id, status, created_at DESC)
INCLUDE (total);

ANALYZE orders;

Bu indeks, ilk iki eşitlik koşulundan sonra created_at aralığını kullanır; INCLUDE(total), sorgunun sadece id, created_at ve total döndürdüğü senaryoda index-only scan olasılığını artırır. Ancak index-only scan için görünürlük haritasındaki all-visible bitlerinin de uygun olması gerekir. Yoğun güncellenen tabloda VACUUM gecikiyorsa INCLUDE eklenmiş olsa bile heap fetch sayısı yüksek kalabilir.

CREATE INDEX CONCURRENTLY transaction block içinde çalışmaz ve başarısız bir concurrent build geçersiz indeks bırakabilir. Dağıtım sonrası bunu doğrulamak için pg_index.indisvalid kontrolü yapın. Ayrıca yeni kompozit indeksin yazma maliyetini pg_stat_user_indexes ile kullanım sıklığı, pg_stat_all_tables ile insert/update hacmi üzerinden izleyin; yalnızca planın değiştiğini görmek indeksin sürekli bakım maliyetini haklı çıkarmaz.

SELECT c.relname AS index_name,
       s.idx_scan,
       s.idx_tup_read,
       s.idx_tup_fetch,
       i.indisvalid
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
JOIN pg_class c ON c.oid = s.indexrelid
WHERE s.relname = 'orders';

Bir sql server eğitimi bağlamında composite statistics ve histogram davranışları ayrı araçlarla incelense de prensip aynıdır: erişim yapısı ile kardinalite modeli farklı katmanlardır. PostgreSQL'de bu ayrımı EXPLAIN (ANALYZE, BUFFERS) ile görünür kılmak, indeks ekleme kararını tahminden çıkarır.

İstatistiklerin yaşlanması, partition edge case'i ve bakım eşiği

Extended Statistics bir kez oluşturulup unutulacak nesneler değildir. Otomatik ANALYZE eşiği yaklaşık olarak autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples formülüyle belirlenir. 100 milyon satırlı bir tabloda varsayılan scale factor, milyonlarca değişiklikten sonra analiz çalışmasına yol açabilir; status dağılımı kampanya veya batch işlemle bir günde değişiyorsa planlar bu sürede eski MCV listesiyle çalışır.

SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       last_analyze,
       last_autoanalyze,
       round(
         current_setting('autovacuum_analyze_threshold')::numeric +
         current_setting('autovacuum_analyze_scale_factor')::numeric * n_live_tup
       ) AS global_analyze_threshold
FROM pg_stat_all_tables
WHERE relname = 'orders';

n_mod_since_analyze değerini global eşikle değil, tablonun kendi reloptions ayarlarıyla birlikte yorumlayın. Hızla değişen bir fact tablo için aşağıdaki ayar, global parametreyi değiştirmeden analiz sıklığını düşürür. Etkiyi ANALYZE süresi, I/O ve replika gecikmesi ile ölçmeden daha agresif değer kullanmayın.

ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 5000
);

Partitioned tablolarda sık kaçırılan nokta, parent tablo istatistiklerinin otomatik analiz tarafından güncel tutulmamasıdır. Uygulama parent üzerinden sorgu çalıştırıyor, partition pruning sonrası çok sayıda partition birleştiriliyorsa parent için zamanlanmış ANALYZE komutu ekleyin. Bu, nosql eğitimi sırasında sık karşılaşılan dağılım değişimi probleminden farklı olarak PostgreSQL planner kataloglarının ayrıca bakıma ihtiyaç duymasından kaynaklanır.

Sık Sorulan Sorular

Veritabanı optimizasyonu için Extended Statistics mi yoksa composite index mi kullanmalıyım?

Önce EXPLAIN (ANALYZE, BUFFERS) ile sorunu ayırın. Plan Rows ve Actual Rows çok farklıysa Extended Statistics adaydır. Actual Rows doğru olduğu halde Seq Scan maliyeti yüksekse erişim yolu için composite index değerlendirin. Çoğu çok kolonlu filtrede ikisi birlikte gerekir: CREATE STATISTICS tahmini, CREATE INDEX erişimi düzeltir.

SQL eğitimi kapsamında PostgreSQL cardinality estimate hatasını nasıl ölçerim?

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) çalıştırın ve her plan düğümünde Actual Rows / Plan Rows oranını kaydedin. Aynı parametre setini Extended Statistics öncesi ve sonrası en az 5 kez çalıştırıp medyan Actual Total Time, shared_blks_read ve temp_blks_written değerlerini karşılaştırın.

Database indexleme sonrası neden sorgu hala Seq Scan kullanıyor?

Planner, indeks erişiminin rastgele heap okumasını Seq Scan'den pahalı tahmin edebilir. Önce Plan Rows ile Actual Rows farkını kontrol edin, ardından indeks kolon sırasının eşitlik filtreleri ve aralık filtresiyle uyumunu inceleyin. orders(region_id, status, created_at) örneğinde created_at'i başa almak, region_id ve status eşitlik seçiciliğini indeks aramasında kullanmayı engelleyebilir.

SQL Server eğitimi alan biri PostgreSQL Extended Statistics kullanırken hangi farkı bilmelidir?

PostgreSQL'de CREATE STATISTICS tanımı tek başına veri toplamaz; ANALYZE zorunludur. Ayrıca dependencies, mcv ve ndistinct türlerini kullanım biçimine göre seçmeniz gerekir. Tanım sonrası pg_statistic_ext ve gerekirse pg_statistic_ext_data üzerinden oluşan istatistiği, EXPLAIN ile de plan etkisini doğrulayın.