Toplama ve pencere fonksiyonu maliyeti: önce topla, sırala, özetle

GROUP BY ve pencere fonksiyonunda sıralama maliyeti, indeksle sıralamayı kaldırmak, önce toplama, COUNT DISTINCT, özet tablo.

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: Pedal İzmir yönetim panosu

Pano her yenilendiğinde 116.000 yolculuk üzerinden istasyon toplamları ve kullanıcı sıralamaları hesaplıyor. Toplama ve pencere fonksiyonları satırları sıralamayı ya da gruplamayı gerektirir. Maliyetin büyük kısmı filtreden sonra burada oluşur.

GROUP BY: sıralı mı, karma mı?

İki yöntem vardır: karma tablo (HashAggregate) ya da sıralı girdiyi yürüyerek gruplama (GroupAggregate). SQLite'ta indeks yoksa USE TEMP B-TREE FOR GROUP BY görürsün. Uygun indeks bu adımı kaldırır:

EXPLAIN QUERY PLAN SELECT start_station, COUNT(*) FROM bk_trips GROUP BY start_station;
CREATE INDEX ix_baslangic ON bk_trips (start_station);
EXPLAIN QUERY PLAN SELECT start_station, COUNT(*) FROM bk_trips GROUP BY start_station;

Önce topla, sonra birleştir: işe yarar mı?

Yaygın öğüt: büyük tabloyu birleştirmeden önce özetle. Ölçelim.

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.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT s.name, COUNT(*) FROM bk_trips t JOIN bk_stations s ON s.station_id = t.start_station GROUP BY s.name;

Gerçek çıktı

HashAggregate  (cost=3075.79..3076.41 rows=62 width=23) (actual rows=62 loops=1)
  Group Key: s.name
  Batches: 1  Memory Usage: 24kB
  Buffers: shared hit=105
  ->  Hash Join  (cost=2.69..2493.20 rows=116517 width=15) (actual rows=116517 loops=1)
        Hash Cond: (t.start_station = s.station_id)
        Buffers: shared hit=105
        ->  Index Only Scan using bk_trips_start_station_idx on bk_trips t  (cost=0.29..2164.05 rows=116517 width=4) (actual rows=116517 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=104
        ->  Hash  (cost=1.62..1.62 rows=62 width=19) (actual rows=62 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 7kB
              Buffers: shared hit=1
              ->  Seq Scan on bk_stations s  (cost=0.00..1.62 rows=62 width=19) (actual rows=62 loops=1)
                    Buffers: shared hit=1

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
WITH g AS (SELECT start_station, COUNT(*) AS n FROM bk_trips GROUP BY start_station) SELECT s.name, g.n FROM g JOIN bk_stations s ON s.station_id = g.start_station;

Gerçek çıktı

Merge Join  (cost=0.43..2761.87 rows=62 width=23) (actual rows=62 loops=1)
  Merge Cond: (bk_trips.start_station = s.station_id)
  Buffers: shared hit=106
  ->  GroupAggregate  (cost=0.29..2747.25 rows=62 width=12) (actual rows=62 loops=1)
        Group Key: bk_trips.start_station
        Buffers: shared hit=104
        ->  Index Only Scan using bk_trips_start_station_idx on bk_trips  (cost=0.29..2164.05 rows=116517 width=4) (actual rows=116517 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=104
  ->  Index Scan using bk_stations_pkey on bk_stations s  (cost=0.14..13.07 rows=62 width=19) (actual rows=62 loops=1)
        Buffers: shared hit=2

Dürüst sonuç: bu veride okunan sayfa neredeyse aynı (105 ve 106), planlar farklı. PostgreSQL iki yazımı da makul biçimde çözüyor. "Önce topla" burada hız kazandırmadı, yine de iki önemli yararı var:

Doğruluk: Birden çok tabloyu birleştirip toplarken satır çoğalması (fan-out) tutarları şişirir. Önce toplamak bunu önler.
Motorun yeteneği: Daha basit iyileştiricilerde ve dağıtık ambarlarda fark büyür. BigQuery belgesi GROUP BY ve JOIN içeren sorgularda erken toplamayı önerir.

Kutuları sırayla çalıştırıp SQLite süresini kendin karşılaştırabilirsin:

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;

WITH g AS (SELECT start_station, COUNT(*) AS yolculuk FROM bk_trips GROUP BY start_station)
SELECT s.name, g.yolculuk FROM g JOIN bk_stations s ON s.station_id = g.start_station;

Pencere fonksiyonları

Bir pencere fonksiyonu PARTITION BY ve ORDER BY sırasına göre satırları dizmek zorundadır. Sıra indeksten gelmezse sıralama yapılır.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_id, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_time DESC) AS sira FROM bank_txns;

Gerçek çıktı

WindowAgg  (cost=4.90..4858.40 rows=42284 width=24) (actual rows=42284 loops=1)
  Buffers: shared hit=39404
  ->  Incremental Sort  (cost=4.90..4118.43 rows=42284 width=16) (actual rows=42284 loops=1)
        Sort Key: account_id, txn_time DESC
        Presorted Key: account_id
        Full-sort Groups: 618  Sort Method: quicksort  Average Memory: 19kB  Peak Memory: 19kB
        Pre-sorted Groups: 525  Sort Method: quicksort  Average Memory: 19kB  Peak Memory: 19kB
        Buffers: shared hit=39404
        ->  Index Scan using bank_txns_account_id_idx on bank_txns  (cost=0.29..2354.53 rows=42284 width=16) (actual rows=42284 loops=1)
              Buffers: shared hit=39404

Aynı sıraya sahip indeksle sıralama adımı kalkar:

CREATE INDEX ix_hesap_zaman ON bank_txns (account_id, txn_time DESC);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_id, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_time DESC) AS sira FROM bank_txns;

Gerçek çıktı

WindowAgg  (cost=0.29..3598.45 rows=42284 width=24) (actual rows=42284 loops=1)
  Buffers: shared hit=39530
  ->  Index Scan using ix_hesap_zaman on bank_txns  (cost=0.29..2858.48 rows=42284 width=16) (actual rows=42284 loops=1)
        Buffers: shared hit=39530

Bu iki çıktıda okunan sayfa neredeyse aynı. Çünkü tablodaki bütün satırlar isteniyor ve indeks üzerinden tablo sayfalarına tek tek gidiliyor. Kazanç yalnız sıralama adımının kalkmasıdır. Ölçmeden "indeks koydum, kesin hızlandı" deme.

Aynı pencereyi tekrarlama. Bir sorguda aynı OVER (...) tanımı aynı sıralamayı paylaşabilir. Farklı sıralamalı her pencere ek bir sıralama demek olabilir.
Grup başına ilk N: ROW_NUMBER() tüm satırları numaralar. Tek hesabın son 20 hareketi gerekiyorsa pencere yerine indeksli ORDER BY ... LIMIT daha ucuzdur.
Satır başına alt sorgu yerine pencere: "Her hesabın son işlemi" için ilişkili alt sorgu yerine pencere çoğunlukla daha iyi plan verir.

COUNT(DISTINCT) ve özet tablo

COUNT(DISTINCT x) değerleri sıralar ya da karma tabloda tutar. Tam sayı gerekmiyorsa yaklaşık fonksiyonlar vardır: BigQuery ve SQL Server'da APPROX_COUNT_DISTINCT.

Aynı ağır toplama günde yüzlerce kez çalışıyorsa sorguyu iyileştirmek yerine sonucu saklamak daha iyi olabilir: özet tablo, ya da PostgreSQL ve Oracle'da materialized view, SQL Server'da indexed view. Bedeli tazeliktir: kim, ne sıklıkla yeniliyor, gecikme kabul edilebilir mi?

CREATE TABLE ozet_gun AS
SELECT date(started_at) AS gun, start_station, COUNT(*) AS yolculuk
FROM bk_trips
GROUP BY date(started_at), start_station;

SELECT gun, SUM(yolculuk) AS yolculuk FROM ozet_gun
WHERE gun >= '2024-07-01' AND gun < '2024-07-08'
GROUP BY gun ORDER BY gun;