EXPLAIN QUERY PLAN di SQLite: Membaca Output dan Menemukan Query Lambat

EXPLAIN QUERY PLAN Memberi Tahu Cara Sebuah Query Akan Berjalan

Sebelum menyetel query yang lambat, kamu perlu tahu apa yang sebenarnya dilakukan SQLite. EXPLAIN QUERY PLAN mencetak ringkasan singkat strategi yang dipilih planner: tabel mana yang disentuhnya, dalam urutan apa, dan index mana (kalau ada) yang dipakainya. Query-nya sendiri tidak dijalankan; kamu hanya mendapat rencananya.

Taruh kata kunci itu di depan pernyataan apa pun:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

Output-nya kurang lebih seperti ini:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Satu baris itu sudah memberi tahu banyak hal: SQLite melakukan SEARCH (bukan pemindaian), pada tabel users, memakai index unik yang dibuat otomatis untuk email, dengan email sebagai key pencarian. Persis seperti yang kamu harapkan.

SCAN vs SEARCH: Hal Pertama yang Perlu Dibaca

Setiap baris di rencana diawali dengan SCAN atau SEARCH. Perbedaan ini adalah sinyal terpenting di seluruh output.

• SCAN <table>: SQLite membaca setiap baris tabel (atau setiap entri index). Biayanya bertambah seiring ukuran tabel.
• SEARCH <table> USING ...: SQLite langsung melompat ke baris yang cocok lewat index atau primary key. Biayanya bertambah seiring ukuran hasil, bukan ukuran tabel.

Berikut perbandingan berdampingan. Satu kolom punya index, yang lain tidak:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Rencana pertama melaporkan SEARCH orders USING INDEX idx_orders_customer. Rencana kedua melaporkan SCAN orders: tidak ada index pada status, jadi SQLite membaca setiap baris. Pada tabel kecil, ini tidak terasa; pada tabel berisi sejuta baris, inilah perbedaan antara milidetik dan detik.

SCAN tidak selalu salah. Untuk tabel lookup yang kecil, atau query yang memang mengembalikan sebagian besar baris, memindai adalah rencana yang tepat. Tapi pada tabel besar dengan filter yang selektif, SCAN adalah tanda untuk menambahkan index.

Memastikan Index Sedang Dipakai

Frasa yang perlu dicari adalah USING INDEX <name> (atau USING COVERING INDEX <name>, dibahas di bawah). Kalau kamu membuat index dengan harapan planner akan memakainya, beginilah cara memeriksanya:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Kamu seharusnya melihat SEARCH events USING INDEX idx_events_user (user_id=?). Kalau rencananya malah bilang SCAN events, ada sesuatu yang menghalangi planner memakai index. Penyebab umumnya adalah membungkus kolom dalam fungsi (WHERE lower(user_id) = ...), membandingkan tipe yang berbeda, atau memakai LIKE '%foo%' dengan wildcard di awal.

Uji cepatnya:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

+ 0 itu menggagalkan index, sehingga rencananya kembali ke SCAN events. Ekspresi apa pun pada kolom yang di-index berakibat sama.

Covering Index Tampil Berbeda

Kalau sebuah index berisi setiap kolom yang dibutuhkan query, SQLite bisa menjawab query hanya dari index itu, tanpa menyentuh tabel. Rencananya melaporkan USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

Rencananya: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). Query meminta price, dan index sudah menyimpan sku serta price, jadi SQLite tidak pernah membaca tabel di baliknya. Covering index adalah rencana tercepat yang bisa kamu dapat untuk sebuah pencarian, jadi layak diingat saat memilih kolom mana yang di-index bersama.

Membaca Rencana Join

Join adalah tempat rencana query mulai menarik. Setiap baris di rencana sesuai dengan satu tabel dalam join, dan urutan barisnya adalah urutan SQLite mengunjungi tabel-tabel itu. Tabel pertama adalah tabel luar; tabel berikutnya dicari sekali untuk setiap baris dari tabel luar.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Rencana yang umum:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Baca dari atas ke bawah: SQLite menemukan satu customer berdasarkan primary key, lalu untuk customer itu mencari pesanan yang cocok lewat index pada customer_id. Kedua baris adalah SEARCH, tanpa pemindaian penuh, dan itulah yang kamu inginkan.

Kalau kamu melihat SCAN o di baris kedua, setiap pencarian customer akan memicu penelusuran penuh pada orders. Pada tabel besar, itu bencana. Perbaikannya hampir selalu berupa index pada kolom join.

Query Gabungan dan Subquery

Rencana untuk UNION, EXCEPT, dan subquery tersusun bertingkat. Setiap cabang muncul menjorok di bawah induknya:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

Kamu akan melihat dua baris anak di bawah judul COMPOUND QUERY, satu untuk setiap cabang. Subquery dan CTE bekerja dengan cara serupa: masing-masing mendapat node rencana sendiri yang menjorok, dan kamu membaca masing-masing dengan kacamata SCAN vs SEARCH yang sama.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

Subquery menjadi node rencana terpisah ("LIST SUBQUERY" atau sejenisnya), dengan strategi aksesnya sendiri. Terapkan pemeriksaan yang sama di setiap tingkat.

EXPLAIN vs EXPLAIN QUERY PLAN

Keduanya hal yang berbeda, dan orang sering tertukar.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (tanpa QUERY PLAN) menampilkan bytecode yang akan dijalankan virtual machine SQLite: puluhan opcode tingkat rendah seperti OpenRead, SeekRowid, Column, ResultRow. Berguna kalau kamu sedang men-debug engine-nya sendiri. Hampir tidak pernah berguna untuk menyetel query.

EXPLAIN QUERY PLAN adalah ringkasan yang mudah dibaca manusia, dan inilah yang sebenarnya kamu butuhkan. Kalau ragu, selalu pakai EXPLAIN QUERY PLAN.

Alur Kerja untuk Query Lambat

Saat sebuah query lambat, putarannya seperti ini:

1. Jalankan EXPLAIN QUERY PLAN pada query itu.
2. Untuk setiap baris tabel, tanyakan: apakah ini SCAN atau SEARCH? Pada tabel besar, SCAN adalah tersangkanya.
3. Kalau sebuah SCAN memfilter kolom tertentu, pertimbangkan index pada kolom itu.
4. Untuk join, pastikan tabel di perulangan dalam memakai SEARCH USING INDEX pada kolom join.
5. Jalankan ulang EXPLAIN QUERY PLAN setelah menambahkan index. Rencananya seharusnya berubah. Kalau tidak, planner memutuskan index-mu tidak layak dipakai, biasanya karena tabelnya kecil atau filternya kurang selektif.

Contoh lengkap untuk langkah 5:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

Rencananya berubah dari SCAN menjadi SEARCH. Itulah tanda bahwa index-nya bekerja. (Pada tabel yang baru dan hampir kosong, planner mungkin tetap memindai karena datanya belum cukup untuk repot memakai index. Isi tabelnya atau jalankan ANALYZE, dan pilihannya sering berbalik.)

Yang Tidak Diberitahukan oleh Rencana Query

EXPLAIN QUERY PLAN menjelaskan strategi, bukan biaya. Ia tidak akan memberi tahu bahwa query butuh 800 ms atau mengembalikan 50.000 baris. Untuk itu, kamu butuh pengukuran waktu (.timer on di CLI) dan jumlah baris. Rencana dan pengukuran waktu saling melengkapi: rencana memberi tahu kenapa sebuah query lambat, pengukur waktu memberi tahu apakah query itu lambat.

Dua batasan lain yang perlu diketahui:

• Rencananya bisa berubah seiring data bertambah. Query yang dengan tenang memindai tabel berisi 100 baris akan butuh index saat tabelnya mencapai sejuta baris. Periksa ulang rencana pada data seukuran production, bukan fixture pengembanganmu.
• Planner memakai statistik yang dikumpulkan oleh ANALYZE. Tanpa statistik itu, ia kembali ke nilai bawaan yang tidak selalu bagus. Statistik yang usang atau tidak ada adalah alasan umum munculnya rencana yang mengejutkan.

Berikutnya: ANALYZE dan VACUUM

Query planner mengambil keputusan berdasarkan statistik tentang tabel dan index-mu. Kalau statistik itu tidak ada atau sudah usang, bahkan skema dengan index yang sempurna bisa menghasilkan rencana yang buruk. ANALYZE adalah cara menjaga statistik tetap segar, dan VACUUM adalah perintah pendampingnya untuk mengambil kembali ruang dan merapikan fragmentasi file database. Itu yang berikutnya.