Query planner SQLite selalu berusaha memilih index termurah untuk setiap query. Masalahnya, tanpa informasi tentang isi tabel, query planner hanya bisa menebak: dua index yang sama-sama terlihat menjanjikan dinilai berdasarkan asumsi, bukan kenyataan. Perintah ANALYZE mengisi kekosongan itu — perintah ini memindai index-index yang ada dan menyimpan ringkasan distribusi nilainya ke dalam tabel sqlite_stat1, sehingga keputusan query planner berubah dari tebakan menjadi perkiraan yang berbasis data.

Demo: Planner Memilih Index yang Keliru #

Kita bangun skenario yang mempertemukan dua index. Tabel pelanggan berisi 100.000 baris: kolom kode bernilai unik (satu baris per nilai), sedangkan kolom kota hanya memiliki lima nilai (20.000 baris per nilai):

sql 
CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  kota TEXT NOT NULL
);

WITH RECURSIVE angka(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM angka WHERE x < 100000
)
INSERT INTO pelanggan (kode, kota)
SELECT 'PLG-' || printf('%05d', x),
       CASE x % 5
         WHEN 0 THEN 'Jakarta'
         WHEN 1 THEN 'Bandung'
         WHEN 2 THEN 'Surabaya'
         WHEN 3 THEN 'Medan'
         ELSE 'Denpasar' END
FROM angka;

CREATE INDEX idx_pelanggan_kode ON pelanggan(kode);
CREATE INDEX idx_pelanggan_kota ON pelanggan(kota);

Query berikut mencari satu pelanggan: kota Jakarta dan kode PLG-05000. Karena kode bernilai unik, index pada kode seharusnya jelas lebih unggul:

sql 
SELECT * FROM pelanggan WHERE kota = 'Jakarta' AND kode = 'PLG-05000';

Output yang Dihasilkan:

text 
5000|PLG-05000|Jakarta
sql 
EXPLAIN QUERY PLAN
SELECT * FROM pelanggan WHERE kota = 'Jakarta' AND kode = 'PLG-05000';

Output yang Dihasilkan:

text 
QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_kota (kota=?)

Query planner memilih idx_pelanggan_kota — pilihan yang keliru. Tanpa statistik, kedua index diasumsikan sama selektifnya, dan tebakan jatuh ke kota: filter kota = 'Jakarta' menghasilkan sekitar 20.000 kandidat baris, lalu setiap kandidat diperiksa ulang terhadap kode. Di mesin penulis, query ini memakan waktu sekitar 5 milidetik.

ANALYZE dan Tabel sqlite_stat1 #

Sekarang jalankan ANALYZE, lalu lihat apa yang berubah:

sql 
ANALYZE;
SELECT * FROM sqlite_stat1 ORDER BY idx;

Output yang Dihasilkan:

text 
pelanggan|idx_pelanggan_kode|100000 1
pelanggan|idx_pelanggan_kota|100000 20000

Mari kita bedah isi tabel sqlite_stat1:

  1. Kolom tbl dan idx: nama tabel dan nama index yang dianalisis — sqlite_stat1 memiliki tiga kolom: tbl, idx, dan stat.
  2. Angka pertama pada stat (100000): jumlah entri di dalam index.
  3. Angka kedua (1 versus 20000): perkiraan rata-rata jumlah baris yang memuat nilai yang sama — satu baris per kode, tetapi 20.000 baris per kota.

Dengan angka-angka itu di tangan, query planner mengevaluasi ulang keputusannya:

sql 
EXPLAIN QUERY PLAN
SELECT * FROM pelanggan WHERE kota = 'Jakarta' AND kode = 'PLG-05000';

Output yang Dihasilkan:

text 
QUERY PLAN
`--SEARCH pelanggan USING INDEX idx_pelanggan_kode (kode=?)

Rencana berpindah ke idx_pelanggan_kode. Di mesin penulis, waktu query turun dari sekitar 5 milidetik menjadi di bawah 0,1 milidetik — hasil yang sama, 5000|PLG-05000|Jakarta, dengan biaya ratusan kali lebih kecil.

Kapan Menjalankan ANALYZE #

Statistik adalah potret data pada saat pemindaian. Jalankan ANALYZE pada momen-momen berikut:

  • Setelah memuat data besar: impor awal, migrasi, atau backfill mengubah distribusi nilai drastis.
  • Setelah pertumbuhan signifikan: tabel yang berkembang dari ribuan menjadi jutaan baris membuat statistik lama menyesatkan.
  • Saat skema index berubah: index baru dianalisis, tetapi statistik index lama bisa ikut tergeser.

Statistik yang usang jarang merusak kebenaran hasil — selalu bisa diverifikasi dengan query manual — tetapi bisa membuat query planner memilih rencana yang tidak lagi optimal. ANALYZE tanpa argumen memindai seluruh database; ANALYZE nama_tabel membatasi pada satu tabel sehingga lebih murah pada database besar.

ANALYZE Online dengan analysis_limit #

Pada database produksi yang besar, ANALYZE penuh memindai semua index dan bisa memakan waktu lama. Sejak SQLite 3.32.0, PRAGMA analysis_limit membatasi jumlah baris yang diperiksa per index — statistik menjadi perkiraan dari sampel, tetapi pemindaian selesai jauh lebih cepat:

sql 
PRAGMA analysis_limit = 100;
ANALYZE;
SELECT * FROM sqlite_stat1 ORDER BY idx;

Output yang Dihasilkan:

text 
100
pelanggan|idx_pelanggan_kode|100000 1
pelanggan|idx_pelanggan_kota|100000 101

Baris pertama adalah gema PRAGMA analysis_limit = 100 yang melaporkan nilai barunya. Perhatikan stat untuk idx_pelanggan_kota: sampel 100 baris menghasilkan perkiraan 101 baris per nilai — jauh dari kenyataan 20.000, namun masih cukup untuk membuat query planner memilih index yang benar, karena perbandingannya selalu relatif antar index.