Menganalisis Query dengan EXPLAIN QUERY PLAN

Query yang lambat punya banyak tersangka: index yang hilang, pola pencarian yang tidak bisa memakai index, atau pengurutan yang dilakukan berulang kali. Menebak penyebab tanpa alat berarti mencoba-coba perbaikan sambil berharap salah satunya berhasil. EXPLAIN QUERY PLAN mengubah menebak menjadi diagnosis: perintah ini memperlihatkan strategi yang dipilih query planner SQLite untuk menjalankan query — sebelum query benar-benar dijalankan. Chapter Index dan Pencarian Cepat telah memakainya untuk membuktikan efek index; sekarang kita pelajari cara membacanya secara menyeluruh.

Apa yang Diberikan EXPLAIN QUERY PLAN #

Prefiks EXPLAIN QUERY PLAN ditempelkan di depan sebuah SELECT, lalu query planner menganalisis query tersebut dan melaporkan rencananya dalam bentuk baris-baris teks. Query-nya sendiri tidak dieksekusi, sehingga perintah ini aman dijalankan kapan pun tanpa menyentuh data. Di level paling dasar, hasilnya terdiri dari empat kolom: id, parent, notused, dan detail. sqlite3 CLI merangkai kolom detail menjadi diagram pohon, dengan id dan parent yang menentukan indentasi tiap cabang.

Untuk latihan membaca rencana, kita pakai skema toko sederhana:

CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  kota TEXT NOT NULL
);
CREATE TABLE pesanan (
  id INTEGER PRIMARY KEY,
  pelanggan_id INTEGER NOT NULL REFERENCES pelanggan(id),
  total INTEGER NOT NULL,
  tanggal TEXT NOT NULL
);
CREATE INDEX idx_pesanan_pelanggan ON pesanan(pelanggan_id);

INSERT INTO pelanggan (nama, kota) VALUES
  ('Budi', 'Jakarta'),
  ('Siti', 'Bandung'),
  ('Rina', 'Jakarta'),
  ('Anton', 'Surabaya');
INSERT INTO pesanan (pelanggan_id, total, tanggal) VALUES
  (1, 150000, '2025-06-01'),
  (1, 275000, '2025-06-03'),
  (2, 99000, '2025-06-05'),
  (3, 310000, '2025-06-07'),
  (3, 84000, '2025-06-10');

Rencana paling sederhana terlihat pada pencarian tanpa index:

EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE nama = 'Budi';

Output yang Dihasilkan:

QUERY PLAN
`--SCAN pelanggan

SCAN, SEARCH, dan Urutan Loop #

Dua kata pertama yang perlu dikenali adalah SCAN dan SEARCH. SCAN berarti seluruh tabel dipindai baris demi baris; SEARCH berarti query planner menyusuri struktur B-tree untuk melompat hanya ke baris yang relevan. Tiga variasi SEARCH yang umum:

EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE id = 3;

Output yang Dihasilkan:

QUERY PLAN
`--SEARCH pelanggan USING INTEGER PRIMARY KEY (rowid=?)

EXPLAIN QUERY PLAN SELECT * FROM pesanan WHERE pelanggan_id = 3;

Output yang Dihasilkan:

QUERY PLAN
`--SEARCH pesanan USING INDEX idx_pesanan_pelanggan (pelanggan_id=?)

USING INTEGER PRIMARY KEY (rowid=?) artinya pencarian memakai B-tree rowid tabel — index internal yang selalu ada. USING INDEX idx_pesanan_pelanggan artinya query planner memakai index buatan kita. Pada query multi-tabel, urutan baris rencana adalah urutan loop:

EXPLAIN QUERY PLAN
SELECT p.nama, s.total
FROM pelanggan p JOIN pesanan s ON s.pelanggan_id = p.id
WHERE p.kota = 'Jakarta';

Output yang Dihasilkan:

QUERY PLAN
|--SCAN p
`--SEARCH s USING INDEX idx_pesanan_pelanggan (pelanggan_id=?)

Mari kita bedah rencana di atas:

1. Baris pertama (SCAN p): loop luar memindai seluruh tabel pelanggan karena kolom kota tidak memiliki index.
2. Baris kedua (SEARCH s USING INDEX ...): untuk setiap pelanggan dari loop luar, loop dalam mencari pesanan lewat idx_pesanan_pelanggan — bukan scan penuh.

Loop luar selalu berada di baris paling atas. Jika loop luar menghasilkan sedikit baris, loop dalam yang efisien menjadi sangat penting; sebaliknya loop luar yang menghasilkan jutaan baris membuat loop dalam dieksekusi jutaan kali.

Penyortiran: USE TEMP B-TREE #

Kata kunci berikutnya menyangkut pengurutan hasil. Tanpa index yang cocok, engine SQLite harus mengumpulkan semua baris ke struktur sementara sebelum bisa mengembalikannya:

EXPLAIN QUERY PLAN SELECT * FROM pesanan ORDER BY tanggal;

Output yang Dihasilkan:

QUERY PLAN
|--SCAN pesanan
`--USE TEMP B-TREE FOR ORDER BY

USE TEMP B-TREE FOR ORDER BY adalah alarm kecil: setiap eksekusi query ini membangun pohon pengurutan sementara. Jika kolom tanggal diindex, baris sudah tersimpan berurut sehingga alarm itu hilang:

CREATE INDEX idx_pesanan_tanggal ON pesanan(tanggal);
EXPLAIN QUERY PLAN SELECT * FROM pesanan ORDER BY tanggal;

Output yang Dihasilkan:

QUERY PLAN
`--SCAN pesanan USING INDEX idx_pesanan_tanggal

Subquery Berkorelasi dan Co-routine #

Pada query bersarang, EXPLAIN QUERY PLAN memperlihatkan label CORRELATED SCALAR SUBQUERY — subquery skalar yang dieksekusi berulang kali karena merujuk kolom dari query luar:

EXPLAIN QUERY PLAN
SELECT p.nama,
       (SELECT count(*) FROM pesanan s WHERE s.pelanggan_id = p.id) AS jumlah_pesanan
FROM pelanggan p;

Output yang Dihasilkan:

QUERY PLAN
|--SCAN p
`--CORRELATED SCALAR SUBQUERY 1
   `--SEARCH s USING COVERING INDEX idx_pesanan_pelanggan (pelanggan_id=?)

Label COVERING INDEX menandakan subquery cukup membaca index saja tanpa membuka tabel. Query ini tetap perlu menjalankan subquery untuk setiap pelanggan — pada 4 baris tidak terasa, pada 5.000 pelanggan barulah terasa, seperti akan kita buktikan di chapter Mengoptimalkan Query Lambat:

SELECT p.nama,
       (SELECT count(*) FROM pesanan s WHERE s.pelanggan_id = p.id) AS jumlah_pesanan
FROM pelanggan p
ORDER BY p.nama;

Output yang Dihasilkan:

Anton|0
Budi|2
Rina|2
Siti|1

Terakhir, label CO-ROUTINE muncul ketika query planner menjalankan subquery secara mengalir (streaming) tanpa menyimpan hasilnya seluruhnya di memori:

EXPLAIN QUERY PLAN
WITH statistik AS (
  SELECT kota, count(*) AS jumlah
  FROM pelanggan
  GROUP BY kota
)
SELECT * FROM statistik WHERE jumlah > 1;

Output yang Dihasilkan:

QUERY PLAN
|--CO-ROUTINE statistik
|  |--SCAN pelanggan
|  `--USE TEMP B-TREE FOR GROUP BY
`--SCAN statistik

Alih-alih menghitung seluruh statistik lebih dulu, engine SQLite mengalirkan baris dari CTE ke query luar begitu baris tersebut siap.

Dari Bukti ke Perbaikan #

Sebagai penutup, mari lihat EQP memandu satu perbaikan nyata. Tabel berikut diisi 100.000 baris produk dengan pola deterministik:

CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  harga INTEGER NOT NULL
);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO produk (kode, harga)
SELECT 'BRG-' || printf('%05d', x), (x * 37) % 250000 + 1000
FROM angka;

Filter pada harga — kolom tanpa index — menghasilkan SCAN produk. Di mesin penulis, SELECT count(*) FROM produk WHERE harga = 38000; memakan waktu sekitar 3 milidetik dengan rencana berikut:

EXPLAIN QUERY PLAN SELECT count(*) FROM produk WHERE harga = 38000;

Output yang Dihasilkan:

QUERY PLAN
`--SCAN produk

Setelah CREATE INDEX idx_produk_harga ON produk(harga);, rencana berubah menjadi SEARCH produk USING COVERING INDEX idx_produk_harga (harga=?) dan di mesin penulis turun ke bawah 0,1 milidetik. Angka waktunya berbeda-beda di setiap mesin, tetapi pasangan rencana SCAN menjadi SEARCH adalah bukti yang deterministik. Bab berikutnya membahas jalan menuju rencana terbaik tersebut: strategi merancang index.