Sorgu planı okumak (query plan): tarama, arama, birleştirme, tahmin ve gerçek

SCAN ve SEARCH, Seq Scan ve Index Scan, Nested Loop, Hash Join, Merge Join, tahmini ve gerçek satır, döngü sayısı.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Plan neyi söyler?

Sorgu iyileştirici (optimizer) aynı sonucu verecek birçok yol arasından en ucuz görüneni seçer. Plan bu seçimdir. Sana dört şey söyler: hangi tablo nasıl okunuyor, tablolar hangi yöntemle birleşiyor, motor kaç satır bekliyor, gerçekte kaç satır çıktı.

PostgreSQL çıktılarıBu derslerdeki PostgreSQL planları elle yazılmadı. Aynı veriyle PostgreSQL 16.4 üzerinde (PGlite, tarayıcıda çalışan WASM sürümü) üretildi. VACUUM ANALYZE çalıştırıldı, planlama satırları kısalık için çıkarıldı. Sayılar kendi sunucunda farklı olur; okuma biçimi aynıdır.

SQLite planı

Her satır bir adımdır. Birleştirmede üstteki tablo dış döngü, alttaki iç döngüdür.

EXPLAIN QUERY PLAN
SELECT s.name, COUNT(*) AS yolculuk
FROM bk_trips t
JOIN bk_stations s ON s.station_id = t.start_station
GROUP BY s.name;

Çıktı	Anlamı
`SCAN t`	Tablonun tamamı okunuyor
`SCAN t USING COVERING INDEX ix`	Tablo yerine indeksin tamamı okunuyor. Yine tam tarama, yalnız daha dar
`SEARCH t USING INDEX ix (a=?)`	İndeksle yalnız ilgili satırlara gidiliyor. Parantez hangi sütunların aramada kullanıldığını söyler
`SEARCH t USING COVERING INDEX ix (a=?)`	Aynı arama, üstelik tabloya hiç gidilmiyor
`USE TEMP B-TREE FOR GROUP BY` (ya da ORDER BY, DISTINCT)	Sıra indeksten gelmiyor, geçici yapı kuruluyor
`CORRELATED SCALAR SUBQUERY`	Alt sorgu dış sorgunun her satırı için yeniden çalışıyor
`AUTOMATIC COVERING INDEX`	Uygun indeks yok, motor sorgu boyunca geçici indeks kuruyor. Kalıcı indeks için güçlü bir işaret

SQLite'ın sınırıSQLite planı tahmini ya da gerçek satır sayısı göstermez ve çıktı biçimi sürümden sürüme değişebilir. Tahmin ve gerçek farkını görmek için aşağıdaki PostgreSQL çıktılarına bakacağız.

Aynı kavram, farklı adlar

	SQLite	PostgreSQL	SQL Server	MySQL	Oracle
Tam tarama	SCAN	Seq Scan	Table Scan, Clustered Index Scan	type: ALL	TABLE ACCESS FULL
İndeks araması	SEARCH	Index Scan, Bitmap Index Scan	Index Seek	type: ref, range	INDEX RANGE SCAN
Yalnız indeks	COVERING INDEX	Index Only Scan	Index Seek, Key Lookup olmadan	Using index	INDEX RANGE SCAN, tabloya erişim olmadan

SQL Server, MySQL, Oracle ve BigQuery planlarını bu uygulamada üretemiyoruz, bu yüzden çıktı örneği göstermiyoruz. Adları resmî belgelerde geçtiği gibi veriyoruz. Motor burada SQLite, başka SQL türü seçsen de plan SQLite planıdır.

PostgreSQL planını okumak

En içteki (en sağa girintili) düğüm önce çalışır, sonuç yukarı akar. Her düğümde iki parantez vardır: ilki tahmin, ikincisi gerçek.

CREATE INDEX ix_txn_time ON bank_txns (txn_time);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_id, amount FROM bank_txns WHERE txn_time >= '2024-11-01' AND txn_time < '2024-11-02';

Gerçek çıktı

Index Scan using ix_txn_time on bank_txns  (cost=0.29..12.75 rows=173 width=10) (actual rows=120 loops=1)
  Index Cond: ((txn_time >= '2024-11-01 00:00:00'::timestamp without time zone) AND (txn_time < '2024-11-02 00:00:00'::timestamp without time zone))
  Buffers: shared hit=4

cost=...: maliyet birimi keyfidir, milisaniye değildir. Yalnız planları karşılaştırmak içindir.
rows=173 tahmin, actual rows=120 gerçek.
Index Cond indeksle aranan koşul, Filter okunduktan sonra elenen koşuldur. Filter çok satır eliyorsa (Rows Removed by Filter) boşa okuma vardır.
Buffers: shared hit=4 düğümün ve altındakilerin okuduğu sayfa sayısıdır.
loops değeri 1'den büyükse rows döngü başına ortalamadır. Toplam için çarp.

Dikkat: ANALYZE sorguyu çalıştırırPostgreSQL ve MySQL'de EXPLAIN ANALYZE sorguyu gerçekten yürütür. UPDATE ya da DELETE için PostgreSQL'de BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; yaz.

Birleştirme türleri (join algorithms)

Tür	Nasıl çalışır	İyi olduğu durum	Şüphelen
Nested Loop	Dış tablonun her satırı için iç tabloda arama	Dış taraf az satır, iç tarafta indeks var	Dış taraf beklenenden çok satır çıkıyorsa
Hash Join	Küçük taraftan bellekte karma tablo kurar, büyük tarafı tarar	Büyük ve sırasız girdiler, eşitlik koşulu	Bellek yetmezse diske taşar
Merge Join	İki sıralı girdiyi yan yana yürütür	İki taraf da birleştirme sütununa göre sıralı	Sıralama için ek maliyet gerekiyorsa

SQLite birleştirmeyi iç içe döngüyle yapar. PostgreSQL ise üçünü de seçebilir. Aşağıdaki planların üçü de aynı veride üretildi.

Nested Loop: tek hesabın hareketleri

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT a.customer_id, t.amount FROM bank_accounts a JOIN bank_txns t ON t.account_id = a.account_id WHERE a.account_id = 100019;

Gerçek çıktı

Nested Loop  (cost=4.94..153.18 rows=49 width=10) (actual rows=72 loops=1)
  Buffers: shared hit=71
  ->  Index Scan using bank_accounts_pkey on bank_accounts a  (cost=0.28..8.29 rows=1 width=8) (actual rows=1 loops=1)
        Index Cond: (account_id = 100019)
        Buffers: shared hit=3
  ->  Bitmap Heap Scan on bank_txns t  (cost=4.67..144.40 rows=49 width=10) (actual rows=72 loops=1)
        Recheck Cond: (account_id = 100019)
        Heap Blocks: exact=66
        Buffers: shared hit=68
        ->  Bitmap Index Scan on bank_txns_account_id_idx  (cost=0.00..4.66 rows=49 width=0) (actual rows=72 loops=1)
              Index Cond: (account_id = 100019)
              Buffers: shared hit=2

Hash Join: bütün hareketler ve bütün hesaplar

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT a.customer_id, COUNT(*) FROM bank_txns t JOIN bank_accounts a ON a.account_id = t.account_id GROUP BY a.customer_id;

Gerçek çıktı

HashAggregate  (cost=1139.62..1144.62 rows=500 width=12) (actual rows=484 loops=1)
  Group Key: a.customer_id
  Batches: 1  Memory Usage: 97kB
  Buffers: shared hit=46
  ->  Hash Join  (cost=26.39..928.20 rows=42284 width=4) (actual rows=42284 loops=1)
        Hash Cond: (t.account_id = a.account_id)
        Buffers: shared hit=46
        ->  Index Only Scan using bank_txns_account_id_idx on bank_txns t  (cost=0.29..790.55 rows=42284 width=4) (actual rows=42284 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=39
        ->  Hash  (cost=15.49..15.49 rows=849 width=8) (actual rows=849 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 31kB
              Buffers: shared hit=7
              ->  Seq Scan on bank_accounts a  (cost=0.00..15.49 rows=849 width=8) (actual rows=849 loops=1)
                    Buffers: shared hit=7

Merge Join: iki taraf da sıralı

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT o.order_id, l.item_id FROM lk_orders o JOIN lk_order_lines l ON l.order_id = o.order_id ORDER BY o.order_id;

Gerçek çıktı

Merge Join  (cost=0.57..868.97 rows=14974 width=8) (actual rows=14974 loops=1)
  Merge Cond: (o.order_id = l.order_id)
  Buffers: shared hit=153
  ->  Index Only Scan using lk_orders_pkey on lk_orders o  (cost=0.28..195.95 rows=7178 width=4) (actual rows=7178 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=22
  ->  Index Scan using lk_order_lines_order_id_idx on lk_order_lines l  (cost=0.29..467.90 rows=14974 width=8) (actual rows=14974 loops=1)
        Buffers: shared hit=131

Döngü sayısı

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT a.account_id, t.amount FROM bank_accounts a JOIN bank_txns t ON t.account_id = a.account_id WHERE a.customer_id < 6 AND t.txn_type = 'fatura';

Gerçek çıktı

Nested Loop  (cost=4.71..765.68 rows=57 width=10) (actual rows=84 loops=1)
  Buffers: shared hit=390
  ->  Seq Scan on bank_accounts a  (cost=0.00..17.61 rows=6 width=4) (actual rows=6 loops=1)
        Filter: (customer_id < 6)
        Rows Removed by Filter: 843
        Buffers: shared hit=7
  ->  Bitmap Heap Scan on bank_txns t  (cost=4.71..124.58 rows=10 width=10) (actual rows=14 loops=6)
        Recheck Cond: (a.account_id = account_id)
        Filter: ((txn_type)::text = 'fatura'::text)
        Rows Removed by Filter: 53
        Heap Blocks: exact=371
        Buffers: shared hit=383
        ->  Bitmap Index Scan on bank_txns_account_id_idx  (cost=0.00..4.70 rows=55 width=0) (actual rows=67 loops=6)
              Index Cond: (account_id = a.account_id)
              Buffers: shared hit=12

Dış taraftan gelen her hesap için iç taraf bir kez çalışır: loops değeri bunu gösterir. İç düğümdeki rows döngü başına ortalamadır. Toplam için çarp: 14 × 6 = 84, en üstteki Nested Loop satırıyla aynı sayı.

Tahmin ile gerçek ayrışınca

CREATE INDEX ix_txn_time ON bank_txns (txn_time);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM bank_txns WHERE EXTRACT(YEAR FROM txn_time) = 2024 AND EXTRACT(MONTH FROM txn_time) = 11;

Gerçek çıktı

Aggregate  (cost=1236.68..1236.69 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=391
  ->  Seq Scan on bank_txns  (cost=0.00..1236.68 rows=1 width=0) (actual rows=3590 loops=1)
        Filter: ((EXTRACT(year FROM txn_time) = '2024'::numeric) AND (EXTRACT(month FROM txn_time) = '11'::numeric))
        Rows Removed by Filter: 38694
        Buffers: shared hit=391

Motor 1 satır bekliyor, 3.590 satır çıkıyor. Sütun fonksiyonun içinde olduğu için istatistikler işe yaramıyor ve motor körlemesine tahmin yapıyor. Bu örnekte zarar yok, çünkü tek tablo var. Birleştirmeli bir sorguda bu yanlış tahmin "1 satır için iç içe döngü yeter" kararına yol açar ve binlerce döngüye dönüşür. Planda ilk aranacak şey budur: tahmin ve gerçek arasındaki büyük fark.

SQL Server'da tahmini planı Ctrl+L, gerçek planı Ctrl+M verir. Oracle'da E-Rows ve A-Rows sütunlarını karşılaştırırsın. MySQL'de EXPLAIN ANALYZE ağacında her düğümde tahmini satır ve gerçek satır yan yana yazar.