JOIN, EXISTS, IN ve alt sorgu maliyeti: erken süzme, gereksiz DISTINCT, SELECT *

Aynı sonucu veren yazımların planı, ilişkili alt sorgu, WHERE ve HAVING, DISTINCT ile örtülen çoğalma, SELECT * bedeli.

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ç.

Senaryo: "Hesabı olan müşteriler"

Bu soruyu üç biçimde yazabilirsin. Hangisi hızlı? İnternette kesin cevaplar göreceksin. Doğrusu: motora ve veriye bağlı, ölç.

-- 1) EXISTS
SELECT c.customer_id FROM bank_customers c
WHERE EXISTS (SELECT 1 FROM bank_accounts a WHERE a.customer_id = c.customer_id);

-- 2) IN
SELECT c.customer_id FROM bank_customers c
WHERE c.customer_id IN (SELECT a.customer_id FROM bank_accounts a);

-- 3) JOIN + DISTINCT
SELECT DISTINCT c.customer_id FROM bank_customers c
JOIN bank_accounts a ON a.customer_id = c.customer_id;

PostgreSQL ne yapıyor?

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.

EXISTS ve IN için plan birebir aynı: motor ikisini de yarı birleştirmeye (semi join) çevirdi.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT c.customer_id FROM bank_customers c WHERE EXISTS (SELECT 1 FROM bank_accounts a WHERE a.customer_id = c.customer_id);

Gerçek çıktı

Hash Semi Join  (cost=26.10..44.60 rows=500 width=4) (actual rows=500 loops=1)
  Hash Cond: (c.customer_id = a.customer_id)
  Buffers: shared hit=13
  ->  Seq Scan on bank_customers c  (cost=0.00..11.00 rows=500 width=4) (actual rows=500 loops=1)
        Buffers: shared hit=6
  ->  Hash  (cost=15.49..15.49 rows=849 width=4) (actual rows=849 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 28kB
        Buffers: shared hit=7
        ->  Seq Scan on bank_accounts a  (cost=0.00..15.49 rows=849 width=4) (actual rows=849 loops=1)
              Buffers: shared hit=7

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT c.customer_id FROM bank_customers c WHERE c.customer_id IN (SELECT a.customer_id FROM bank_accounts a);

Gerçek çıktı

Hash Semi Join  (cost=26.10..44.60 rows=500 width=4) (actual rows=500 loops=1)
  Hash Cond: (c.customer_id = a.customer_id)
  Buffers: shared hit=13
  ->  Seq Scan on bank_customers c  (cost=0.00..11.00 rows=500 width=4) (actual rows=500 loops=1)
        Buffers: shared hit=6
  ->  Hash  (cost=15.49..15.49 rows=849 width=4) (actual rows=849 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 28kB
        Buffers: shared hit=7
        ->  Seq Scan on bank_accounts a  (cost=0.00..15.49 rows=849 width=4) (actual rows=849 loops=1)
              Buffers: shared hit=7

JOIN + DISTINCT ise önce satırları çoğaltıyor (849 eşleşme), sonra tekilleştiriyor (500 müşteri):

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT DISTINCT c.customer_id FROM bank_customers c JOIN bank_accounts a ON a.customer_id = c.customer_id;

Gerçek çıktı

HashAggregate  (cost=37.11..42.11 rows=500 width=4) (actual rows=500 loops=1)
  Group Key: c.customer_id
  Batches: 1  Memory Usage: 65kB
  Buffers: shared hit=13
  ->  Hash Join  (cost=17.25..34.99 rows=849 width=4) (actual rows=849 loops=1)
        Hash Cond: (a.customer_id = c.customer_id)
        Buffers: shared hit=13
        ->  Seq Scan on bank_accounts a  (cost=0.00..15.49 rows=849 width=4) (actual rows=849 loops=1)
              Buffers: shared hit=7
        ->  Hash  (cost=11.00..11.00 rows=500 width=4) (actual rows=500 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 18kB
              Buffers: shared hit=6
              ->  Seq Scan on bank_customers c  (cost=0.00..11.00 rows=500 width=4) (actual rows=500 loops=1)
                    Buffers: shared hit=6

SQLite ne yapıyor?

Aynı üç sorgunun planlarına bak ve süreleri karşılaştır. Burada indeks yokken EXISTS her müşteri için hesap tablosunu ilk eşleşmeye kadar tarar. "EXISTS her zaman hızlıdır" kuralının bu motorda geçerli olmadığını kendin görürsün.

EXPLAIN QUERY PLAN SELECT c.customer_id FROM bank_customers c
WHERE EXISTS (SELECT 1 FROM bank_accounts a WHERE a.customer_id = c.customer_id);
EXPLAIN QUERY PLAN SELECT c.customer_id FROM bank_customers c
WHERE c.customer_id IN (SELECT a.customer_id FROM bank_accounts a);

Çıkarım: kuralı ezberleme. bank_accounts (customer_id) indeksi eklenince SQLite'ta EXISTS de arama yapar. Yöntemi anlama göre seç: varlık soruyorsan EXISTS ya da IN, satırı çoğaltmak gerekmiyorsa JOIN + DISTINCT yazma.

NOT IN başka bir konuNOT IN ile NOT EXISTS farkı hız değil anlamdır. Alt sorguda tek bir NULL varsa NOT IN hiç satır döndürmez. Bu yüzden NOT EXISTS tercih edilir.

İlişkili alt sorgu (correlated subquery)

SELECT listesindeki ilişkili alt sorgu dış sorgunun her satırı için çalışır. İç tarafta indeks yoksa satır × satır iş çıkar.

EXPLAIN QUERY PLAN
SELECT c.customer_id, c.city,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS siparis
FROM customers c;

Plandaki CORRELATED SCALAR SUBQUERY ve altındaki SCAN a satırları bunu söylüyor. Önce toplayıp sonra birleştirince alt sorgu bir kez çalışır:

SELECT c.customer_id, c.city, COALESCE(o.siparis, 0) AS siparis
FROM customers c
LEFT JOIN (SELECT customer_id, COUNT(*) AS siparis FROM orders GROUP BY customer_id) o
  ON o.customer_id = c.customer_id;

LEFT JOIN ve COALESCE önemli: siparişi olmayan müşteride alt sorgu 0 veriyordu, düz JOIN o satırı kaybederdi.

Erken süzme

Mantıksal sırada HAVING gruplamadan sonra çalışır. Grup sütunu üzerindeki koşul için öğüt nettir: WHERE'de yaz. Ama hız farkı beklemeden önce planlara bak:

CREATE INDEX ix_tip ON bank_txns (txn_type);
EXPLAIN QUERY PLAN SELECT txn_type, COUNT(*) FROM bank_txns GROUP BY txn_type HAVING txn_type = 'faiz';
EXPLAIN QUERY PLAN SELECT txn_type, COUNT(*) FROM bank_txns WHERE txn_type = 'faiz' GROUP BY txn_type;

İki kutu da aynı planı veriyor: bu basit durumda motor koşulu kendisi öne alıyor. PostgreSQL de aynı şeyi yapıyor:

CREATE INDEX ix_tip ON bank_txns (txn_type);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_type, COUNT(*) FROM bank_txns GROUP BY txn_type HAVING txn_type = 'faiz';

Gerçek çıktı

GroupAggregate  (cost=0.29..33.98 rows=1 width=18) (actual rows=1 loops=1)
  Buffers: shared hit=4
  ->  Index Only Scan using ix_tip on bank_txns  (cost=0.29..30.76 rows=1284 width=10) (actual rows=1262 loops=1)
        Index Cond: (txn_type = 'faiz'::text)
        Heap Fetches: 0
        Buffers: shared hit=4

Yine de WHERE yaz. Niyetin açık kalır, ayrıca motorun koşulu öne alamadığı durumlar vardır. Birleştirmeli sorgularda da sor: "Bu büyük tablo birleşmeden önce daralabilir mi?" İyileştiriciler WHERE koşulunu çoğunlukla kendileri aşağı iter, ama LEFT JOIN, pencere fonksiyonu ve DISTINCT içeren alt sorgular bunu engelleyebilir. Emin olmanın yolu plandır.

CTE her zaman bedava değildir

PostgreSQL'de CTE (12 ve sonrası) tek kullanılıyorsa sorguya gömülür, MATERIALIZED yazarsan önce hesaplanıp saklanır:

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

Gerçek çıktı

Aggregate  (cost=7.78..7.79 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Only Scan using ix_txn_time on bank_txns  (cost=0.29..7.39 rows=155 width=0) (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))
        Heap Fetches: 0
        Buffers: shared hit=3

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

Gerçek çıktı

Aggregate  (cost=1871.47..1871.48 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=391
  CTE t
    ->  Seq Scan on bank_txns  (cost=0.00..813.84 rows=42284 width=12) (actual rows=42284 loops=1)
          Buffers: shared hit=391
  ->  CTE Scan on t  (cost=0.00..1057.10 rows=211 width=0) (actual rows=120 loops=1)
        Filter: ((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))
        Rows Removed by Filter: 42164
        Buffers: shared hit=391

Birinde 3 sayfa, diğerinde 391 sayfa. MATERIALIZED her zaman kötü de değildir: pahalı bir alt sonuç birkaç kez kullanılıyorsa kazandırabilir. BigQuery belgesi de CTE'nin okunabilirlik için olduğunu, performans için olmadığını ve birden çok kez değerlendirilebileceğini yazar.

Gereksiz DISTINCT ve SELECT *

DISTINCT çoğalmayı gizler. Birleştirme satırları çoğaltıyorsa önce nedenini düzelt. Sonuç doğru görünse bile iş iki kat yapılmıştır.
SELECT * kapsayan indeksi işe yaramaz hâle getirir ve sıralama belleğini büyütür. Aşağıdaki iki planda satır sayısı aynı, sıralama belleği farklı.
Sütun tabanlı ambarda fatura farkı: BigQuery'de okunan sütun kadar ödersin. Belgeye göre SELECT * sorgusuna LIMIT eklemek okunan veri miktarını değiştirmez.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT * FROM bank_txns ORDER BY amount DESC;

Gerçek çıktı

Sort  (cost=4062.91..4168.62 rows=42284 width=46) (actual rows=42284 loops=1)
  Sort Key: amount DESC
  Sort Method: quicksort  Memory: 3688kB
  Buffers: shared hit=391
  ->  Seq Scan on bank_txns  (cost=0.00..813.84 rows=42284 width=46) (actual rows=42284 loops=1)
        Buffers: shared hit=391

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_id, amount FROM bank_txns ORDER BY amount DESC;

Gerçek çıktı

Sort  (cost=4062.91..4168.62 rows=42284 width=10) (actual rows=42284 loops=1)
  Sort Key: amount DESC
  Sort Method: quicksort  Memory: 2676kB
  Buffers: shared hit=391
  ->  Seq Scan on bank_txns  (cost=0.00..813.84 rows=42284 width=10) (actual rows=42284 loops=1)
        Buffers: shared hit=391