Index: Membuat Query Jadi Ngebut (dan Membaca EXPLAIN)
Apa itu index, kapan membuatnya, jenis index di PostgreSQL (B-tree, GIN, partial), biaya yang harus dibayar, dan membaca EXPLAIN untuk melihat rencana eksekusi.
Analogi: Index itu seperti daftar isi dan indeks di belakang buku. Tanpa index, mencari kata "transaksi" berarti membaca 500 halaman satu per satu (sequential scan). Dengan index, kamu langsung lompat ke halaman 212. Harganya: indeks memakan tempat dan harus diperbarui setiap buku diedit.
Masalah: tabel jutaan baris
Mencari buku berjudul "Dilan 1990" di tabel 10 baris itu instan. Di tabel 10 juta baris tanpa persiapan, PostgreSQL harus membaca semua baris satu per satu (sequential scan). Lambat.
Analogi: index itu seperti daftar isi di belakang buku. Tanpa daftar isi, mencari kata "Postgres" berarti membuka halaman demi halaman. Dengan daftar isi, langsung loncat ke halaman 142.
Membuat index
CREATE INDEX idx_buku_judul ON buku (judul); -- index biasa
CREATE UNIQUE INDEX idx_pelanggan_email ON pelanggan (email); -- sekaligus menjamin unik
CREATE INDEX idx_pesanan_pelanggan_tgl ON pesanan (pelanggan_id, tanggal); -- gabungan 2 kolom
DROP INDEX idx_buku_judul; -- menghapus indexJenis default adalah B-tree, cocok untuk =, <, >, BETWEEN, dan ORDER BY. PostgreSQL otomatis membuat index untuk kolom PRIMARY KEY dan UNIQUE.
Kapan index berguna?
- Kolom yang sering dipakai di
WHERE. - Kolom foreign key yang sering di-JOIN (
pesanan.pelanggan_id). Postgres tidak membuat index FK otomatis. - Kolom yang sering dipakai di
ORDER BY.
Harga yang dibayar
- Index memakan ruang disk.
- Setiap
INSERT/UPDATE/DELETEjadi sedikit lebih lambat karena index ikut diperbarui.
Jangan membuat index untuk semua kolom. Buat untuk kolom yang terbukti sering dicari, dan ukur manfaatnya dengan EXPLAIN.
Jenis index lain di PostgreSQL
| Jenis | Untuk apa |
|---|---|
| B-tree (default) | Pencarian umum, rentang, pengurutan |
| Hash | Hanya kesamaan (=) |
| GIN | JSONB, array, full-text search |
| GiST / SP-GiST | Data geometri, rentang |
| BRIN | Tabel raksasa yang datanya berurutan alami (log, time-series) |
Dua varian praktis:
CREATE INDEX idx_pesanan_menunggu ON pesanan (tanggal) WHERE status = 'menunggu'; -- partial index: hanya sebagian baris
CREATE INDEX idx_buku_judul_lower ON buku (LOWER(judul)); -- index pada ekspresiEXPLAIN: mengintip rencana eksekusi
EXPLAIN SELECT * FROM buku WHERE judul = 'Dilan 1990';Hasilnya menunjukkan cara Postgres mengerjakan query:
Seq Scan: baca seluruh tabel (lambat di data besar).Index Scan/Index Only Scan: memakai index (cepat).
Versi lengkap, EXPLAIN ANALYZE, benar-benar menjalankan query dan melaporkan waktu nyata. Pakai di data uji, hati-hati dengan UPDATE/DELETE.
Catatan simulator: editor latihan memeriksa apakah index yang kamu buat ada. Perbedaan kecepatan baru terasa di data besar pada PostgreSQL asli.
Kasus nyata: login yang lambat karena mencari email di jutaan baris
Halaman login makin lambat saat pelanggan mencapai 2 juta. Penyebabnya, setiap login memindai seluruh tabel untuk menemukan satu email.
EXPLAIN SELECT * FROM pelanggan WHERE email = 'dewi@email.com';
-- Seq Scan on pelanggan (lambat)
CREATE INDEX idx_pelanggan_email ON pelanggan (email);
-- Index Scan using idx_pelanggan_email (cepat)Kesalahan yang sering terjadi
- Membuat index di setiap kolom. Index memperlambat INSERT/UPDATE dan memakan ruang; buat hanya untuk kolom yang sering dipakai di WHERE/JOIN/ORDER BY.
- Mengira index selalu dipakai. Pada tabel kecil, PostgreSQL justru memilih membaca seluruh tabel karena lebih murah.
- Menyaring dengan
WHERE LOWER(email) = ...padahal index dibuat padaemail. Fungsi pada kolom membuat index biasa tidak terpakai.