Veritabanı Optimizasyonu: PostgreSQL Index-Only Scan Gerçekleri

Veritabanı optimizasyonu için index-only scan ön koşulları

Index-only scan, sorgunun ihtiyaç duyduğu sütunlar indekste bulunsa bile her zaman heap erişimini kaldırmaz. PostgreSQL, her heap sayfası için visibility map içindeki all-visible bitini kontrol eder. Bit kapalıysa MVCC görünürlüğünü doğrulamak için heap fetch yapar. Bu nedenle database indexleme çalışmasında sadece indeksin kolon listesini değil, EXPLAIN (ANALYZE, BUFFERS) çıktısındaki Heap Fetches değerini izleyin. Bir sorgunun 2 ms sürmesi tek başına yeterli teşhis değildir: Heap Fetches 0 iken 10.000 shared hit, indeksin gerçekten heap'e uğramadan cevap verdiğini gösterir.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 4812
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

-- Hedefe yakın bir çıktı:
-- Index Only Scan using orders_customer_created_cover_idx on public.orders
-- Heap Fetches: 0
-- Buffers: shared hit=12

Bu ölçümü üretim benzeri veri dağılımında en az iki kez alın: değişiklikten önce mevcut planı kaydedin, sonra cover indeks ve vacuum ayarını uygulayıp aynı parametrelerle tekrar çalıştırın. PostgreSQL 13 ve sonrası sürümlerde EXPLAIN'in WAL seçeneği yazan sorgular için ek sinyal verir, ancak salt SELECT karşılaştırmasında BUFFERS ve Heap Fetches daha doğrudan göstergelerdir. Uygulama parametrelerini gerçekçi tutmak için psql'de prepared statement kullanın; sabit literal ile yapılan tek seferlik test, uygulamanın parametreli planından farklı davranabilir.

PREPARE recent_orders(bigint) AS
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = $1
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS) EXECUTE recent_orders(4812);

Database indexleme: INCLUDE ile dar anahtar, geniş kapsama

Sıralama ve filtreleme kolonlarını B-tree anahtarında, yalnızca SELECT için gereken kolonları INCLUDE bölümünde tutun. Örneğin customer_id ve created_at erişim yolunu belirler; id ve total_amount ise index-only scan'i mümkün kılan payload'dır. INCLUDE kolonları B-tree sıralamasına katılmaz; bu, gereksiz anahtar genişlemesini ve karşılaştırma maliyetini önler. Ancak INCLUDE edilen her byte yine indeks yaprak sayfalarını büyütür, dolayısıyla text, jsonb veya nadiren okunan büyük kolonları buraya koymayın.

CREATE INDEX CONCURRENTLY orders_customer_created_cover_idx
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_amount);

-- İndeks boyutunu değişiklik öncesi ve sonrası karşılaştırın.
SELECT pg_size_pretty(pg_relation_size('orders_customer_created_cover_idx')) AS index_size,
       pg_size_pretty(pg_relation_size('orders')) AS table_size;

CREATE INDEX CONCURRENTLY iki ayrı tablo taraması yapar ve normal CREATE INDEX'e göre daha uzun sürer; ayrıca işlem bloğu içinde çalıştırılamaz. Migration aracınız tüm migration'ı otomatik transaction'a sarıyorsa bu komut başarısız olur. Flyway kullanılıyorsa bu migration'ı non-transactional olarak işaretleyin, Liquibase kullanılıyorsa runInTransaction=false seçeneğini inceleyin. Başarısız concurrent build sonrasında geçersiz indeks kalabilir; yeni build denemeden önce pg_index.indisvalid değerini kontrol edin.

SELECT c.relname,
       i.indisvalid,
       i.indisready,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'orders_customer_created_cover_idx';

Veritabanı performans ayarlama: visibility map neden bozulur?

INSERT edilen yeni sayfalar ve UPDATE veya DELETE ile değişen satırların bulunduğu sayfalar all-visible durumundan çıkar. Autovacuum bu sayfayı tarayıp artık hiçbir aktif transaction tarafından görülemeyen tuple kalmadığını doğruladığında biti yeniden kurabilir. Yüksek yazma hacimli orders tablosunda indeks doğru olsa bile Heap Fetches değerinin gün içinde artmasının mekanizması budur. Bu durumu pg_visibility eklentisiyle doğrudan ölçün; eklenti superuser veya uygun yetki gerektirebilir.

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT count(*) FILTER (WHERE all_visible) AS all_visible_pages,
       count(*) AS total_pages,
       round(100.0 * count(*) FILTER (WHERE all_visible) / count(*), 2) AS visible_pct
FROM pg_visibility_map('orders'::regclass);

Tabloya global autovacuum değerini körlemesine düşürmek yerine, yazma yoğunluğu yüksek tabloya eşik tanımlayın. Varsayılan eşik, tablo büyüdükçe vacuum tetiklemesini geciktirebilir; aşağıdaki ayar 2 milyon satırlık ve dakikada on binlerce update alan tablo için başlangıç noktasıdır, kesin reçete değildir. Değişiklikten önce ve sonra pg_stat_user_tables içindeki n_dead_tup, last_autovacuum ve autovacuum_count değerlerini, ayrıca aynı sorgudaki Heap Fetches değerini zaman damgasıyla kaydedin.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_analyze_threshold = 1000
);

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

Yaygın hata VACUUM FULL çalıştırarak index-only scan sorununu çözmeye çalışmaktır. VACUUM FULL tabloyu yeniden yazar ve uzun süreli ACCESS EXCLUSIVE kilidi ister; çevrimiçi iş yükünde çoğu zaman kabul edilemez. Önce normal VACUUM (ANALYZE, VERBOSE) ile görünürlük ve ölü tuple durumunu gözleyin. Eğer vacuum ilerlemiyorsa pg_stat_activity içinde uzun transaction'ları arayın; eski bir xmin, temizlenebilir tuple'ları tutarak visibility map bitlerinin yeniden kurulmasını geciktirebilir.

SQL eğitimi ve nosql eğitimi perspektifinden doğru test tasarımı

Bir sql eğitimi laboratuvarında index-only scan örneğini yalnızca boşta duran bir veritabanında göstermek yanıltıcıdır. Eşzamanlı UPDATE yükü altında görünürlük oranı değişir. pgbench ile ayrı bir oturumda update yükü üretip, diğer oturumda sorgu planını periyodik ölçün. Böylece indeks eklemenin etkisi ile vacuum gecikmesinin etkisini ayırabilirsiniz. pgbench'in varsayılan tabloları yerine kendi orders sorgunuzu -f dosyasına koyarak gerçek erişim desenini test edin.

cat > workload.sql <<'SQL'
\set customer_id random(1, 100000)
UPDATE orders
SET updated_at = clock_timestamp()
WHERE customer_id = :customer_id
  AND id = (
    SELECT id FROM orders
    WHERE customer_id = :customer_id
    LIMIT 1
  );
SQL

pgbench -n -c 16 -j 4 -T 300 -f workload.sql appdb

Nosql eğitimi tarafında sık yapılan yanlış analoji, MongoDB gibi bir belge deposundaki covering query ile PostgreSQL index-only scan'i aynı varsaymaktır. PostgreSQL'in MVCC modelinde indeks kaydı tek başına satır görünürlüğünü garanti etmez; visibility map bu yüzden gereklidir. MongoDB'de de projection ve index coverage ayrı kurallara bağlıdır, ancak PostgreSQL'deki Heap Fetches metriğinin doğrudan karşılığı değildir. Sistemler arası karşılaştırmada aynı veri boyutu, aynı sıcak cache durumu ve aynı p95 gecikme yüzdesini ölçün; sadece ortalama süreyi kıyaslamayın.

Karşılaştırmayı pg_stat_statements ile kalıcı hale getirin. extension yüklendikten sonra plans, calls, mean_exec_time ve shared_blks_hit alanlarını deploy öncesi ve sonrası pencere için dışa aktarın. PostgreSQL yeniden başlatıldıktan sonra pg_stat_statements sıfırlanabilir; bu nedenle ölçüm döneminde reset zamanını kayda alın. Bu disiplin, veritabanı eğitimi kapsamında bir plan değişikliğinin gerçek iş yükünde kaç çağrıyı etkilediğini göstermenin pratik yoludur.

SELECT queryid, calls, plans,
       round(mean_exec_time::numeric, 3) AS mean_ms,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%FROM orders%'
ORDER BY total_exec_time DESC
LIMIT 10;

Sık Sorulan Sorular

Database indexleme sonrası PostgreSQL neden Heap Fetches yapmaya devam eder?

İndeks SELECT kolonlarını kapsasa bile ilgili heap sayfasının visibility map all-visible biti kapalı olabilir. EXPLAIN (ANALYZE, BUFFERS) ile Heap Fetches değerini alın, ardından pg_visibility_map('tablo'::regclass) ile görünür sayfa oranını ölçün. UPDATE ve DELETE yoğunluğu varsa tablo bazında autovacuum eşiklerini ayarlayıp aynı planı tekrar karşılaştırın.

Veritabanı performans ayarlama için INCLUDE indeksi ne zaman kullanılmalı?

WHERE ve ORDER BY kolonları B-tree anahtarında zaten seçiciyse, sadece çıktı için gereken küçük kolonları INCLUDE edin. CREATE INDEX CONCURRENTLY ile indeksi kurun; öncesinde ve sonrasında pg_relation_size ile boyutu, EXPLAIN (ANALYZE, BUFFERS) ile Heap Fetches ve shared buffer kullanımını ölçün. Geniş text veya jsonb alanlarını INCLUDE etmek indeks yapraklarını büyütür ve cache etkinliğini düşürür.

SQL Server eğitimi alan biri için PostgreSQL index-only scan ile covering index farkı nedir?

SQL Server covering index yaklaşımında INCLUDE kolonları benzer rol oynar, ancak PostgreSQL index-only scan ayrıca MVCC görünürlüğü için visibility map'e bağlıdır. Bu yüzden PostgreSQL'de plan Index Only Scan gösterse bile Heap Fetches sıfır olmayabilir. EXPLAIN (ANALYZE, BUFFERS) çıktısındaki Heap Fetches ve pg_stat_user_tables içindeki n_dead_tup değerlerini birlikte inceleyin.

Nosql eğitimi sırasında PostgreSQL index-only scan performansı nasıl yük altında test edilir?

Tek seferlik EXPLAIN yerine pgbench ile eşzamanlı UPDATE yükü üretin ve ölçüm sorgusunu prepared statement olarak çalıştırın. Testin başında ve sonunda EXPLAIN (ANALYZE, BUFFERS) çıktısını, pg_visibility_map görünürlük yüzdesini ve pg_stat_statements mean_exec_time değerini kaydedin. Bu üç veri, cache etkisini, heap doğrulamasını ve gerçek çağrı maliyetini ayırmanıza yardım eder.