EXPLAIN QUERY PLAN

Membaca rencana eksekusi dan memastikan index benar-benar dipakai.

Daftar isi

Topik ini membahas EXPLAIN QUERY PLAN sebagai alat membaca rencana eksekusi SQLite tanpa menjalankan query-nya. Perbedaan SCAN dan SEARCH, ragam keluaran USING INDEX, USING COVERING INDEX, dan pencarian rowid, serta rencana join multi-tabel dibahas satu per satu. Kebiasaan memeriksa rencana sebelum mengklaim kueri cepat menjadi keterampilan utama yang ditutup di bagian akhir.

Membaca rencana eksekusi#

EXPLAIN QUERY PLAN diawali pernyataan SELECT apa pun dan menampilkan rencananya tanpa mengeksekusi kueri. Keluarannya berbentuk pohon teks yang diawali baris QUERY PLAN, dan setiap barisnya mewakili satu putaran (loop) pembacaan baris. Indentasi menunjukkan sarangnya, yaitu putaran yang menjorok ke dalam dieksekusi untuk setiap baris yang dihasilkan putaran di atasnya.

Kueri berikut dijalankan dua kali pada tabel yang sama, yaitu sebelum dan sesudah index dibuat. Perbandingan semacam inilah bentuk paling jujur untuk menunjukkan efek sebuah index.

sql

CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE tahun = 2000;
CREATE INDEX idx_buku_tahun ON buku(tahun);
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE tahun = 2000;

text Keluaran

QUERY PLAN
`--SCAN buku
QUERY PLAN
`--SEARCH buku USING INDEX idx_buku_tahun (tahun=?)

Kueri yang sama pada tabel yang sama berubah rencananya setelah index dibuat. Mula-mula planner memilih SCAN buku, yaitu membaca seluruh 20000 baris lalu menyaring yang cocok. Setelah idx_buku_tahun dibuat, rencananya menjadi SEARCH yang melompat langsung ke kelompok tahun 2000, dan tanda (tahun=?) menegaskan syarat yang dilayani index. Keluaran inilah bukti pemakaian index, bukti yang tidak dapat digantikan oleh kesan cepat atau lambat.

SCAN versus SEARCH#

Seluruh keluaran rencana pada dasarnya memilih di antara dua cara membaca data. SCAN membaca tabel atau index dari ujung ke ujung, sehingga biayanya sebanding dengan jumlah isian yang dibaca. SEARCH menggunakan index untuk melompat ke kelompok baris yang cocok, sehingga biayanya sebanding dengan jumlah baris yang cocok ditambah kedalaman pohon index.

Jalur pencarian tercepat adalah pencarian rowid, yaitu penunjuk internal setiap baris tabel biasa. Kolom INTEGER PRIMARY KEY merupakan alias dari rowid, sehingga syarat pada kolom id dilayani pencarian langsung pada struktur tabel tanpa index tambahan. Dua bentuk berikut menghasilkan rencana yang identik.

sql

CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;

EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE id = 42;
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE rowid = 42;

text Keluaran

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

Teks USING INTEGER PRIMARY KEY menandai pencarian langsung menurut penunjuk baris. Bentuk ini adalah titik jangkar join satu-ke-banyak pada umumnya, karena tabel yang dirujuk cukup dicari menurut rowid untuk setiap baris tabel perujuknya. Apabila kolom kunci referensinya bukan rowid alias, index pada kolom tersebut yang akan menyediakan jalur pencariannya.

USING INDEX dan USING COVERING INDEX#

Ketika pencarian memakai index sekunder, teksnya USING INDEX, dan tambahan kata COVERING muncul bila seluruh kolom yang diminta sudah termuat di dalam index. Perbedaannya menentukan apakah tabel perlu dikunjungi per baris yang cocok. Index komposit (penulis, tahun) memperlihatkan kedua bentuk itu pada tabel yang sama.

sql

CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
CREATE INDEX idx_buku_penulis_tahun ON buku(penulis, tahun);
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE penulis = 'Penulis 7';
EXPLAIN QUERY PLAN SELECT tahun FROM buku WHERE penulis = 'Penulis 7';

text Keluaran

QUERY PLAN
`--SEARCH buku USING INDEX idx_buku_penulis_tahun (penulis=?)
QUERY PLAN
`--SEARCH buku USING COVERING INDEX idx_buku_penulis_tahun (penulis=?)

Kueri yang meminta judul menghasilkan USING INDEX, karena judul tidak termuat sehingga setiap baris yang cocok masih diambil dari tabel melalui rowidnya. Kueri yang hanya meminta tahun menghasilkan USING COVERING INDEX, karena seluruh datanya sudah ada di index dan tabel tidak disentuh sama sekali. Untuk kueri yang sering dijalankan pada tabel besar, perbedaan satu kunjungan tabel per baris inilah yang paling terasa.

Rencana join multi-tabel#

Rencana join berbentuk nested loop, yaitu putaran luar yang membaca satu tabel dan putaran dalam yang mencari pasangannya untuk setiap baris luar. Pemilihan tabel luar dan cara pencarian tabel dalam merupakan keputusan planner yang paling menentukan biaya join. Contoh berikut membaca seluruh peminjaman milik satu anggota, lalu mencari judul bukunya.

sql

CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
CREATE TABLE peminjaman (
  id      INTEGER PRIMARY KEY,
  buku_id INTEGER NOT NULL,
  anggota INTEGER NOT NULL
);
INSERT INTO peminjaman (buku_id, anggota)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 1 + (i % 20000), 1 + (i % 500) FROM deret;
EXPLAIN QUERY PLAN
SELECT peminjaman.id, buku.judul
FROM peminjaman
JOIN buku ON buku.id = peminjaman.buku_id
WHERE peminjaman.anggota = 3;

text Keluaran

QUERY PLAN
|--SCAN peminjaman
`--SEARCH buku USING INTEGER PRIMARY KEY (rowid=?)

Putaran luar memindai peminjaman dan menyaring anggota 3, sedangkan putaran dalam yang menjorok mencari buku melalui USING INTEGER PRIMARY KEY karena kolom id merupakan alias rowid. Untuk setiap baris peminjaman yang lolos, satu pencarian langsung pada tabel buku dijalankan, sehingga biaya totalnya sebanding dengan jumlah peminjaman anggota itu. Apabila putaran dalam berubah menjadi SCAN buku, biayanya meledak menjadi kelipatan ukuran kedua tabel, dan itulah tanda join yang belum diberi index.

Kebiasaan memeriksa rencana#

Rencana eksekusi membuat klaim performa dapat diuji, bukan ditebak. Kebiasaan berikut diterapkan pada setiap kueri yang menjadi beban utama aplikasi.

Tulis kueri yang sering dijalankan, lalu jalankan EXPLAIN QUERY PLAN untuk setiap kueri itu.
Cari teks SCAN pada tabel besar, karena SCAN berarti seluruh isian dibaca setiap kali kueri dijalankan.
Pastikan SEARCH menunjuk index yang memuat kolom syarat, dan gunakan covering index untuk kueri yang membaca sedikit kolom.
Periksa putaran dalam rencana join, dan pastikan putaran dalamnya berupa SEARCH, bukan SCAN.
Ulangi pemeriksaan setiap kali schema, index, atau versi SQLite berubah, karena rencana dapat bergeser.

Satu pemeriksaan rencana lebih berharga daripada kesan cepat yang muncul sekali jalan, karena rencana menunjukkan sebabnya, bukan hanya akibatnya. Rencana juga stabil pada satu versi SQLite, sehingga keluaran sebelum dan sesudah perubahan index dapat dibandingkan langsung. Dengan kebiasaan ini, keputusan penambahan index selalu bertumpu pada bukti yang dapat direproduksi ulang.

Ringkasan#

EXPLAIN QUERY PLAN menampilkan pohon rencana yang setiap barisnya satu putaran pembacaan, dengan indentasi yang menandai putaran dalam.
SCAN membaca seluruh isian, sedangkan SEARCH melompat melalui index, dan USING INTEGER PRIMARY KEY menandai pencarian langsung menurut rowid.
USING INDEX tetap mengunjungi tabel per baris yang cocok, sedangkan USING COVERING INDEX menyelesaikan kueri dari index saja.
Rencana join adalah nested loop, dan putaran dalamnya wajib berupa SEARCH agar biayanya tidak menjadi kelipatan ukuran tabel.

Referensi#

EXPLAIN QUERY PLAN — SQLite — Dokumentasi resmi bentuk keluaran EQP beserta makna setiap barisnya.
Overview Of The Query Planner — SQLite — Cara planner memilih antara SCAN dan SEARCH serta menyusun nested loop.
SELECT — SQLite — Sintaks SELECT yang rencananya dibaca dengan EXPLAIN QUERY PLAN.