EXPLAIN & Query Plans — Database

Apa itu EXPLAIN? EXPLAIN menunjukkan bagaimana database akan mengeksekusi query — disebut query plan. Ini adalah tool utama untuk memahami dan mengoptimasi…

EXPLAIN meminta database menceritakan rencana (query plan) yang akan dia pakai untuk sebuah query: tabel mana yang dibaca, lewat index atau tidak, dan apakah hasilnya masih harus diurutkan sendiri. Bayangin minta rute di aplikasi peta sebelum berangkat: kamu lihat dulu "lewat tol, keluar di gerbang kedua", padahal belum jalan sama sekali. Di pelajaran Index & Performa kamu sudah kenal SCAN dan SEARCH. Sekarang kita baca plan yang lebih lengkap, lalu terjemahkan ke bahasa PostgreSQL.

🧪 Tebakan cepat: tabel orders sudah punya index di customer_id. Halaman riwayat mencari 100 pesanan milik Siti, lalu ORDER BY created_at DESC. Apakah database masih harus mengurutkan 100 pesanan itu sendiri? Tebak dulu, jawabannya ada di bagian "USE TEMP B-TREE: mengurutkan sendiri".

Tabel dan query yang kita pakai

Tabel orders di Lab sama dengan pelajaran Index & Performa: 20.000 pesanan Kedai Senja dari 200 pelanggan, dengan kolom id, customer_id, status, created_at dan total. Siti (customer_id 2) punya 100 pesanan. Halaman "Riwayat pesanan" menjalankan:

SELECT * FROM orders WHERE customer_id = 2 ORDER BY created_at DESC;

Taruh EXPLAIN QUERY PLAN di depannya. Query-nya gak dijalankan, yang keluar cuma rencananya. Tanpa index apa pun, sqlite3 di terminal menggambarnya begini:

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

Tiap baris adalah satu langkah. Langkah yang menjorok ke kanan adalah bagian dari langkah di atasnya.
SCAN orders: baca seluruh tabel, 20.000 baris, untuk menemukan 100 yang cocok.
USE TEMP B-TREE FOR ORDER BY: hasilnya dimasukkan ke struktur sementara (temp B-tree) supaya bisa diurutkan, karena gak ada yang sudah urut.

Di Lab dan Playground, plan yang sama keluar sebagai tabel dengan kolom id, parent, notused dan detail. Teks di kolom detail itulah baris-baris di atas.

Kata-kata di plan SQLite

Baris plan	Artinya
`SCAN orders`	Baca seluruh tabel dari awal sampai akhir
`SEARCH orders USING INDEX idx (customer_id=?)`	Lompat lewat index ke baris yang cocok, lalu ambil barisnya di tabel
`SEARCH orders USING COVERING INDEX idx (customer_id=?)`	Semua kolom yang dibutuhkan sudah ada di index, tabelnya gak disentuh sama sekali
`SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)`	Cari lewat primary key, misalnya `WHERE id = 42`
`USE TEMP B-TREE FOR ORDER BY`	Urutkan sendiri di struktur sementara
`SCAN orders USING INDEX idx`	Baca seluruh index berurutan, bukan seluruh tabel

Yang ada di dalam kurung, seperti (customer_id=?), adalah kolom yang dipakai untuk mencari di index. Tanda ? artinya "nilai apa pun", karena plan-nya sama untuk Siti maupun Budi.

USE TEMP B-TREE: mengurutkan sendiri

Pasang index di customer_id, lalu minta plan-nya lagi:

CREATE INDEX idx_orders_customer ON orders(customer_id);

QUERY PLAN
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

SEARCH menemukan 100 pesanan Siti dengan cepat. Tapi index itu cuma urut menurut customer_id, gak tahu apa-apa soal created_at, jadi baris kedua masih ada: ya, database masih mengurutkan sendiri. Itu jawaban tebakannya. Di 100 baris gak kerasa, tapi untuk pelanggan dengan 50.000 pesanan, mengurutkan sendiri di setiap buka halaman itu mahal.

Obatnya composite index dari pelajaran Index & Performa, yang urutannya sama dengan query:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_date (customer_id=?)

Di index ini, pesanan Siti sudah berjajar menurut created_at. Database tinggal membacanya dari belakang untuk DESC, dan baris temp B-tree hilang. Kalau query-nya cuma butuh kolom yang ada di index, misalnya SELECT created_at FROM orders WHERE customer_id = 2 ORDER BY created_at DESC, plan-nya jadi SEARCH orders USING COVERING INDEX idx_orders_customer_date (customer_id=?). Merancang index untuk banyak query sekaligus dibahas di pelajaran Index Strategy.

Sama di PostgreSQL: EXPLAIN dan EXPLAIN ANALYZE

PostgreSQL cukup pakai EXPLAIN. Ini jawaban PostgreSQL 18 untuk query yang sama di 20.000 baris yang sama, tanpa index:

 Sort  (cost=381.32..381.57 rows=100 width=24)
   Sort Key: created_at DESC
   ->  Seq Scan on orders  (cost=0.00..378.00 rows=100 width=24)
         Filter: (customer_id = 2)

Dan dengan idx_orders_customer:

 Sort  (cost=135.63..135.88 rows=100 width=24)
   Sort Key: created_at DESC
   ->  Bitmap Heap Scan on orders  (cost=5.06..132.31 rows=100 width=24)
         Recheck Cond: (customer_id = 2)
         ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (customer_id = 2)

Baca dari yang paling menjorok: langkah itu jalan duluan, hasilnya naik ke langkah di atasnya (->).
Seq Scan = SCAN: baca seluruh tabel baris per baris. Sort = USE TEMP B-TREE.
Index Scan = SEARCH ... USING INDEX: lewat index, ambil baris satu per satu.
Index Only Scan = COVERING INDEX: semua yang dibutuhkan ada di index.
Bitmap Index Scan lalu Bitmap Heap Scan: index dipakai untuk mengumpulkan lokasi semua baris yang cocok dulu, baru halaman tabelnya dibaca berurutan. PostgreSQL memilihnya saat baris yang cocok lumayan banyak.
cost=5.06..132.31 adalah perkiraan biaya, angka relatif dan bukan milidetik: biaya sampai baris pertama, lalu biaya total. rows=100 adalah perkiraan jumlah baris.

Untuk mencari sedikit baris di tabel besar, urutan kecepatannya biasanya: Index Only Scan tercepat, Index Scan cepat, Bitmap Index Scan sedang, Seq Scan lambat. Kalau plan tabel besar menunjukkan Seq Scan untuk query yang cuma butuh sedikit baris, solusi utamanya: tambah index di kolom yang ada di WHERE atau JOIN.

EXPLAIN ANALYZE beda: dia benar-benar menjalankan query-nya, lalu menambahkan angka sungguhan seperti actual time=0.027..0.051 rows=100.00 loops=1 dan Execution Time: 0.067 ms. SQLite gak punya EXPLAIN ANALYZE, makanya Lab mengukur waktunya sendiri.

📌 Plan adalah pilihan, bukan aturan

Planner PostgreSQL memilih plan termurah menurut perkiraannya. Di data yang sama, dengan idx_orders_customer_date pun, untuk SELECT * dia tetap memilih Bitmap Heap Scan lalu Sort, karena 100 pesanan Siti tersebar di 100 halaman tabel yang berbeda. Untuk SELECT created_at dia memilih Index Only Scan Backward, dan untuk ... LIMIT 5 dia memilih Index Scan Backward. Jadi selalu cek plan di data yang mirip production, jangan menebak.

⚠️ Jebakan yang sering ketemu

Mengira SCAN selalu buruk. Membaca 12 baris tabel settings sudah instan. Dan kalau query mengambil sebagian besar baris, PostgreSQL sengaja memilih Seq Scan walau ada index.
Membungkus kolom dengan fungsi. Dengan idx_orders_customer_date, WHERE customer_id = 2 AND strftime('%Y-%m', created_at) = '2026-10' cuma memakai bagian customer_id (plan-nya (customer_id=?)), karena index berisi created_at, bukan strftime(...). Tulis sebagai rentang: created_at >= '2026-10-01' AND created_at < '2026-11-01', dan plan-nya jadi (customer_id=? AND created_at>? AND created_at<?).
EXPLAIN ANALYZE pada DELETE atau UPDATE. Datanya benar-benar berubah. Bungkus dengan BEGIN; ... ROLLBACK;.
Di latihan, jawabannya huruf kecil persis: "tercepat", "cepat", "sedang", "lambat".

TL;DR: EXPLAIN QUERY PLAN (SQLite) dan EXPLAIN (PostgreSQL, MySQL) menunjukkan rencana query tanpa menjalankannya; EXPLAIN ANALYZE di PostgreSQL menjalankannya dan menambah waktu sungguhan. SCAN = Seq Scan (baca seluruh tabel), SEARCH ... USING INDEX = Index Scan, COVERING INDEX = Index Only Scan, USE TEMP B-TREE FOR ORDER BY = Sort. Untuk sedikit baris di tabel besar: Index Only Scan tercepat, Index Scan cepat, Bitmap Index Scan sedang, Seq Scan lambat. Seq Scan di tabel besar biasanya diobati dengan index di kolom WHERE atau JOIN, dan Sort yang mahal dengan composite index yang urutannya sama dengan ORDER BY.