Query Planning

Query yang benar belum tentu query yang cepat — dua query dengan hasil identik bisa berbeda ribuan kali lipat kecepatannya bergantung pada langkah eksekusi yang dipilih. Keputusan tersebut diambil oleh query planner: komponen database yang memperkirakan biaya tiap rencana eksekusi lalu memilih yang termurah. Chapter Indexing sudah memperlihatkan plan berubah dari SCAN menjadi SEARCH; chapter ini membaca plan secara lebih utuh — urutan join, index yang dibangun otomatis, dan bagaimana statistik mengubah keputusan planner — semuanya dengan eksekusi nyata di SQLite.

EXPLAIN vs EXPLAIN QUERY PLAN #

SQLite menyediakan dua tingkat keterbukaan. EXPLAIN QUERY PLAN menampilkan rencana tingkat tinggi — tabel apa dibaca dan lewat index apa. EXPLAIN membuka lebih dalam: program opcode untuk mesin virtual SQLite. Keduanya dijalankan pada query sederhana:

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.executescript("""
CREATE TABLE pelanggan (id INTEGER PRIMARY KEY, nama TEXT NOT NULL, kota TEXT);
INSERT INTO pelanggan VALUES
    (1, 'Budi Santoso', 'Jakarta'),
    (2, 'Siti Rahayu', 'Bandung');
""")

print("-- EXPLAIN QUERY PLAN --")
for baris in koneksi.execute(
        "EXPLAIN QUERY PLAN SELECT nama FROM pelanggan WHERE id = 1"):
    print(baris[-1])

print("-- EXPLAIN (program opcode) --")
for baris in koneksi.execute("EXPLAIN SELECT nama FROM pelanggan WHERE id = 1"):
    print(f"{baris[0]:>2}  {baris[1]:<12} {baris[2]} {baris[3]} {baris[4]}")

Output yang Dihasilkan:

-- EXPLAIN QUERY PLAN --
SEARCH pelanggan USING INTEGER PRIMARY KEY (rowid=?)
-- EXPLAIN (program opcode) --
 0  Init         0 7 0
 1  OpenRead     0 2 0
 2  Integer      1 1 0
 3  SeekRowid    0 6 1
 4  Column       0 1 2
 5  ResultRow    2 1 0
 6  Halt         0 0 0
 7  Transaction  0 0 1
 8  Goto         0 1 0

Rencana tingkat tinggi cukup untuk diagnosis sehari-hari; program opcode menjelaskan langkah mesinnya: SeekRowid melompat langsung ke baris id 1, Column membaca kolom nama, dan ResultRow mengirimkannya sebagai hasil. Untuk pekerjaan rutin, EXPLAIN QUERY PLAN adalah alat utama kita.

Membaca Urutan Join #

SQLite mengeksekusi join sebagai perulangan bersarang (nested loop join): tabel pertama pada plan menjadi perulangan luar, tabel berikutnya dicari sekali per baris perulangan luar. Urutan baris pada plan adalah urutan bersarangnya. Kita periksa join tiga tabel skema toko online — pesanan dan detail_pesanan disiapkan terlebih dahulu:

koneksi.executescript("""
CREATE TABLE pesanan (
    id INTEGER PRIMARY KEY,
    pelanggan_id INTEGER NOT NULL REFERENCES pelanggan(id),
    tanggal TEXT NOT NULL
);
CREATE TABLE detail_pesanan (
    pesanan_id INTEGER NOT NULL REFERENCES pesanan(id),
    produk_id INTEGER NOT NULL,
    jumlah INTEGER NOT NULL,
    PRIMARY KEY (pesanan_id, produk_id)
);
INSERT INTO pesanan VALUES
    (1, 1, '2024-06-01'), (2, 2, '2024-06-03'), (3, 1, '2024-07-20');
INSERT INTO detail_pesanan VALUES
    (1, 1, 1), (1, 2, 2), (2, 2, 1), (3, 1, 1);
""")

for baris in koneksi.execute("""
    EXPLAIN QUERY PLAN
    SELECT pl.nama, ps.tanggal, d.produk_id
    FROM detail_pesanan d
    JOIN pesanan ps ON ps.id = d.pesanan_id
    JOIN pelanggan pl ON pl.id = ps.pelanggan_id
"""):
    print(baris[-1])

Output yang Dihasilkan:

SCAN d USING COVERING INDEX sqlite_autoindex_detail_pesanan_1
SEARCH ps USING INTEGER PRIMARY KEY (rowid=?)
SEARCH pl USING INTEGER PRIMARY KEY (rowid=?)

Mari kita telaah tiga baris plan tersebut:

1. SCAN d ...: detail_pesanan menjadi perulangan luar dan dibaca penuh — wajar, karena query meminta semua detail tanpa filter; index PK kompositnya bahkan dipakai sebagai covering index sehingga tabel tidak tersentuh.
2. SEARCH ps USING INTEGER PRIMARY KEY (rowid=?): untuk tiap baris detail, SQLite melompat ke baris pesanan lewat primary key — pencarian O(log n), bukan pemindaian ulang.
3. SEARCH pl USING INTEGER PRIMARY KEY (rowid=?): pola yang sama berlanjut ke pelanggan.

Pola “pindai tabel fakti sekali, lompat ke tabel referensi lewat primary key” adalah bentuk ideal join analitik. Plan yang buruk terlihat dari kebalikannya: tabel besar dipindai berulang kali di perulangan dalam.

Automatic Index: Planner Membuat Index Sementara #

Apa yang terjadi bila kolom join tidak punya index sama sekali? Planner punya jurus darurat: membangun index sementara khusus untuk query berjalan. Dua tabel 50.000 baris disiapkan dengan kolom join (k) yang tidak diindeks:

koneksi.executescript("""
CREATE TABLE tabel_a (id INTEGER PRIMARY KEY, k INTEGER NOT NULL, info TEXT);
CREATE TABLE tabel_b (id INTEGER PRIMARY KEY, k INTEGER NOT NULL, info TEXT);
INSERT INTO tabel_a (k, info)
WITH RECURSIVE seq(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM seq WHERE i < 50000)
SELECT i % 500, 'a' || i FROM seq;
INSERT INTO tabel_b (k, info)
WITH RECURSIVE seq(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM seq WHERE i < 50000)
SELECT i % 500, 'b' || i FROM seq;
""")

for baris in koneksi.execute("""
    EXPLAIN QUERY PLAN
    SELECT COUNT(*) FROM tabel_a a JOIN tabel_b b ON a.k = b.k
"""):
    print(baris[-1])

koneksi.execute("CREATE INDEX idx_b_k ON tabel_b(k)")
for baris in koneksi.execute("""
    EXPLAIN QUERY PLAN
    SELECT COUNT(*) FROM tabel_a a JOIN tabel_b b ON a.k = b.k
"""):
    print(baris[-1])

Output yang Dihasilkan:

SCAN a
BLOOM FILTER ON b (k=?)
SEARCH b USING AUTOMATIC COVERING INDEX (k=?)
SCAN a
SEARCH b USING COVERING INDEX idx_b_k (k=?)

Kata AUTOMATIC pada plan pertama adalah tandanya: SQLite membangun index sementara atas tabel_b(k) saat query berjalan, lalu membuangnya setelah selesai. Baris BLOOM FILTER ON b menandai penyaring ringkas yang menolak pencarian yang hampir pasti gagal sebelum masuk ke index. Automatic index menyelamatkan query ad-hoc sekali jalan, tetapi dibangun ulang pada setiap eksekusi — query yang berjalan rutin layak mendapat index permanen.

Statistik dengan ANALYZE #

Planner memilih index berdasarkan perkiraan jumlah baris hasil, dan tanpa data, perkiraannya buruk. Perintah ANALYZE mengumpulkan statistik distribusi nilai ke tabel sqlite_stat1. Manfaatnya terlihat pada data yang miring: 200.000 pesanan dengan 190.000 milik pelanggan 1 (10 pelanggan total), 180 tanggal berbeda, dan dua index tunggal:

koneksi.executescript("""
CREATE TABLE pesanan_besar (
    id INTEGER PRIMARY KEY,
    pelanggan_id INTEGER NOT NULL,
    tanggal TEXT NOT NULL
);
INSERT INTO pesanan_besar (pelanggan_id, tanggal)
WITH RECURSIVE seq(i) AS (
    SELECT 1 UNION ALL SELECT i + 1 FROM seq WHERE i < 200000
)
SELECT CASE WHEN i <= 190000 THEN 1 ELSE 2 + (i % 9) END,
       date('2024-01-01', (i % 180) || ' days')
FROM seq;
CREATE INDEX idx_pb_pelanggan ON pesanan_besar(pelanggan_id);
CREATE INDEX idx_pb_tanggal ON pesanan_besar(tanggal);
""")

query = """
    SELECT COUNT(*) FROM pesanan_besar
    WHERE pelanggan_id = 1 AND tanggal = '2024-06-01'
"""

Plan query di atas diperiksa sebelum dan sesudah ANALYZE berjalan:

for baris in koneksi.execute("EXPLAIN QUERY PLAN " + query):
    print(baris[-1])

koneksi.execute("ANALYZE")
for baris in koneksi.execute("EXPLAIN QUERY PLAN " + query):
    print(baris[-1])

for baris in koneksi.execute(
    "SELECT idx, stat FROM sqlite_stat1 WHERE tbl = 'pesanan_besar'"):
    print(baris)

Output yang Dihasilkan:

SEARCH pesanan_besar USING INDEX idx_pb_pelanggan (pelanggan_id=?)
SEARCH pesanan_besar USING INDEX idx_pb_tanggal (tanggal=?)
('idx_pb_tanggal', '200000 1112')
('idx_pb_pelanggan', '200000 20000')

Sebelum ANALYZE, planner memilih index pelanggan_id; setelah statistik terkumpul, pilihan berpindah ke index tanggal. Statistik menjelaskan alasannya: rata-rata satu nilai pelanggan_id mewakili 20.000 baris, sedangkan satu tanggal hanya 1.112 baris — kondisi tanggal menyaring jauh lebih tajam, apalagi pelanggan_id = 1 yang sebenarnya mencakup 190.000 baris. Tanpa statistik, planner tidak bisa tahu perbedaan tersebut.

Planner, index, dan statistik bersama-sama menjaga kecepatan pembacaan data. Namun pipeline produksi juga menulis data — dan penulisan yang gagal di tengah jalan menuntut jaminan yang berbeda: keutuhan rangkaian perubahan. Jaminan itulah materi chapter Transaksi dan ACID.