Query Optimization

SQL bersifat deklaratif: kita menulis apa yang ingin dijawab, bukan bagaimana menjawabnya. Dua penulis bisa menghasilkan SQL yang setara namun berbeda gaya penulisan, dan tugas optimizer adalah memilih jalur eksekusi tercepat untuk keduanya. Ketika query analitik tiba-tiba lambat, alat diagnosis pertama bukan menebak — melainkan membaca rencana eksekusi. Chapter Query Planning pada course sebelumnya memperkenalkan konsepnya di SQLite; sekarang kita membedahnya di engine analitik dengan bukti-bukti nyata dari DuckDB: predicate pushdown, row group skipping, dan normalisasi rencana.

Apa yang Dioptimasi Sebuah Engine #

Optimizer menerima query yang sudah diurai, lalu menerapkan serangkaian penulisan ulang sebelum eksekusi dimulai:

Teknik	Yang dilakukan	Bukti di chapter ini
Column pruning	hanya baca kolom yang dirujuk query	`Projections` pada TABLE_SCAN
Predicate pushdown	dorong filter sedekat mungkin ke sumber data	`Filters` di dalam TABLE_SCAN
Row group skipping	lewati blok Parquet yang statistiknya tidak cocok	statistik min-maks per row group
Join ordering	pilih urutan join termurah lebih dulu	rencana join yang dinormalisasi

Dua baris pertama sudah kita saksikan bekerja di chapter Columnar Storage; sekarang buktinya dibedah tuntas. Semua penulisan ulang itu digerakkan optimizer berbasis biaya (cost-based): engine menyimpan statistik jumlah baris dan sebaran nilai per kolom, memperkirakan biaya tiap kandidat rencana, lalu memilih yang termurah. Statistik inilah yang membuat pilihan seperti urutan join bisa diambil tanpa menjalankan query lebih dulu.

Bukti Predicate Pushdown dan Row Group Skipping #

Skrip berikut menyiapkan satu juta pesanan TokoKita yang diurutkan per kota ke dalam berkas Parquet berisi empat row group, lalu membandingkan agregasi dengan dan tanpa filter. Statistik per row group dibaca dari metadata berkas — tanpa memindai datanya. Pengurutan sebelum penulisan adalah keputusan model data yang disengaja: seperti akan kita lihat, statistik row group yang rapat membuka pintu bagi optimizer untuk melompati data:

python
"""Ch7: Query optimization — bukti nyata kerja optimizer DuckDB.

Tiga bukti dieksekusi nyata:
1. Filter didorong ke dalam TABLE_SCAN (predicate pushdown).
2. Statistik Parquet menyingkap row group yang tidak perlu dibaca.
3. Penulisan filter berbeda menghasilkan rencana eksekusi yang sama.
"""
import statistics
import time

import duckdb

con = duckdb.connect()

# --- dataset 1 juta baris, diurutkan per kota, 4 row group ---
con.execute("""
CREATE OR REPLACE TABLE pesanan AS
SELECT
    i AS id,
    CASE (i - 1) // 250000
        WHEN 0 THEN 'Bandung' WHEN 1 THEN 'Denpasar'
        WHEN 2 THEN 'Jakarta' ELSE 'Surabaya' END AS kota,
    'pelanggan-' || (i % 10000)::VARCHAR AS pelanggan,
    15000.0 + (i % 500) * 250.0 AS nilai,
    'catatan pengiriman untuk pesanan nomor ' || i::VARCHAR AS catatan
FROM range(1, 1000001) AS t(i)
""")
con.execute("""
COPY (SELECT * FROM pesanan ORDER BY kota)
TO 'opt_big.parquet' (FORMAT parquet, ROW_GROUP_SIZE 250000)
""")

# --- bukti 1: statistik kolom kota per row group ---
print("== statistik kolom kota per row group ==")
for baris in con.execute("""
    SELECT row_group_id, row_group_num_rows, stats_min_value, stats_max_value
    FROM parquet_metadata('opt_big.parquet')
    WHERE path_in_schema = 'kota' ORDER BY row_group_id
""").fetchall():
    print(baris)

# --- bukti 2: waktu eksekusi dengan dan tanpa filter ---
q_semua = ("SELECT kota, count(*), round(avg(nilai), 2) AS rata "
           "FROM read_parquet('opt_big.parquet') GROUP BY kota ORDER BY kota")
q_filter = ("SELECT kota, count(*), round(avg(nilai), 2) AS rata "
            "FROM read_parquet('opt_big.parquet') "
            "WHERE kota = 'Bandung' GROUP BY kota")


def ukur(query, ulang=5):
    con.execute(query).fetchall()  # pemanasan
    durasi = []
    for _ in range(ulang):
        t0 = time.perf_counter()
        con.execute(query).fetchall()
        durasi.append(time.perf_counter() - t0)
    return statistics.median(durasi)


print()
print("== waktu agregasi 1 juta baris (median 5 run) ==")
print(f"tanpa filter (4 row group) : {ukur(q_semua):.3f} detik")
print(f"filter kota='Bandung'      : {ukur(q_filter):.3f} detik")
print("hasil:", con.execute(q_filter).fetchall())

plan = con.execute("EXPLAIN ANALYZE " + q_filter).fetchall()[0][1]
lines = [l.replace("│", "").strip() for l in plan.splitlines()]
mulai = next(i for i, l in enumerate(lines) if "TABLE_SCAN" in l)
print()
print("== kutipan TABLE_SCAN dari EXPLAIN ANALYZE ==")
for l in lines[mulai:mulai + 20]:
    if l:
        print(l)

# --- bukti 3: posisi filter di SQL tidak mengubah rencana ---
con.execute("""
CREATE OR REPLACE TABLE pelanggan_master AS
SELECT DISTINCT 'pelanggan-' || (i % 10000)::VARCHAR AS nama, i % 5 AS segmen
FROM range(1, 1000001) AS t(i)
""")
q_luar = """
SELECT p.pelanggan, count(*) AS jumlah, sum(p.nilai) AS total
FROM pesanan p JOIN pelanggan_master m ON p.pelanggan = m.nama
WHERE p.kota = 'Bandung'
GROUP BY p.pelanggan
"""
q_dalam = """
SELECT p.pelanggan, count(*) AS jumlah, sum(p.nilai) AS total
FROM (SELECT * FROM pesanan WHERE kota = 'Bandung') p
JOIN pelanggan_master m ON p.pelanggan = m.nama
GROUP BY p.pelanggan
"""
rencana_luar = con.execute("EXPLAIN " + q_luar).fetchall()[0][1]
rencana_dalam = con.execute("EXPLAIN " + q_dalam).fetchall()[0][1]
print()
print("== filter di WHERE luar vs di dalam subquery ==")
print(f"rencana eksekusi identik : {rencana_luar == rencana_dalam}")
print("operator + filter yang sama di kedua rencana:")
terlihat = [l.replace("│", "").strip() for l in rencana_luar.splitlines()
            if any(k in l for k in ("TABLE_SCAN", "HASH_JOIN", "HASH_GROUP_BY",
                                    "Filters:", "kota"))]
for l in terlihat[:8]:
    print(l)

Output yang Dihasilkan:

text
== statistik kolom kota per row group ==
(0, 251904, 'Bandung', 'Denpasar')
(1, 251904, 'Denpasar', 'Jakarta')
(2, 251904, 'Jakarta', 'Surabaya')
(3, 244288, 'Surabaya', 'Surabaya')

== waktu agregasi 1 juta baris (median 5 run) ==
tanpa filter (4 row group) : 0.013 detik
filter kota='Bandung'      : 0.007 detik
hasil: [('Bandung', 250000, 77375.0)]

== kutipan TABLE_SCAN dari EXPLAIN ANALYZE ==
TABLE_SCAN
────────────────────
Function:
READ_PARQUET
Projections:
kota
nilai
Filters:
kota='Bandung'
Total Files Read: 1
Filename(s):
opt_big.parquet
250,000 rows

== filter di WHERE luar vs di dalam subquery ==
rencana eksekusi identik : True
operator + filter yang sama di kedua rencana:
HASH_GROUP_BY
HASH_JOIN
Filters:
kota='Bandung'

Mari kita telaah tiga bukti tersebut:

Statistik row group: karena data diurutkan per kota sebelum ditulis, setiap row group memuat rentang kota yang sempit. Row group 0 berstatistik [Bandung..Denpasar], row group 2 [Jakarta..Surabaya]. Untuk filter kota='Bandung', engine cukup membandingkan string dengan statistik ini — row group 2 dan 3 langsung dilewati tanpa dibaca.
Waktu eksekusi: query berfilter selesai dua kali lebih cepat (0,007 vs 0,013 detik) karena hanya sekitar seperempat data yang dibuka. Pada berkas beberapa GB, selisih ini berlipat menjadi menit. Perhatikan pula bahwa Projections: kota, nilai tetap hadir — column pruning bekerja bersamaan dengan filter, dua teknik saling memperkuat pada scan yang sama.
Rencana TABLE_SCAN: bagian Filters: kota='Bandung' muncul di dalam kartu TABLE_SCAN — bukan sebagai operator terpisah di atasnya. Itulah tanda predicate pushdown: filter dieksekusi bersamaan dengan pembacaan, sehingga baris non-Bandung tidak pernah mengalir ke operator berikutnya.

Penulisan Berbeda, Rencana Sama #

Bukti ketiga menjawab pertanyaan yang sering muncul: apakah filter lebih cepat bila ditulis di dalam subquery sebelum join? Jawaban DuckDB: tidak ada bedanya. q_luar menempatkan WHERE p.kota = 'Bandung' di level teratas; q_dalam menyaringnya dulu di subquery. Kedua query menghasilkan rencana eksekusi yang identik — optimizer menuliskan ulang keduanya ke bentuk kanonik yang sama, lalu memilih urutan join terbaik dari sana. Penulisan ulang semacam ini disebut normalisasi: subquery ditumpahkan (subquery flattening), filter diangkat dan didorong ke bawah, baru biaya dihitung.

Perbedaan EXPLAIN dan EXPLAIN ANALYZE juga terlihat di skrip ini. EXPLAIN hanya menampilkan rencana yang disusun optimizer — cukup untuk membandingkan dua bentuk penulisan. EXPLAIN ANALYZE menjalankan query sungguhan sambil mencatat waktu dan jumlah baris nyata di tiap operator; angka 250,000 rows pada TABLE_SCAN adalah hasil pengukuran, bukan perkiraan, sehingga cocok untuk memburu operator yang menjadi titik lambat.

Rencana eksekusi yang kita baca di chapter ini masih berupa pipeline satu mesin: satu proses DuckDB menjalankan seluruh operator dari scan sampai agregasi. Ketika operator TABLE_SCAN dan HASH_JOIN itu sendiri tersebar ke banyak mesin, muncul lapisan koordinasi baru — pembagian tahapan, exchange data antar worker, dan penyeimbangan beban — yang menjadi topik chapter Distributed Query Processing.