Indeks: B-tree, GIN, Partial & Covering — Belajar PostgreSQL

Jenis Indeks dan Kegunaannya

Jenis	Untuk	Operator yang didukung
B-tree	Bawaan; kesetaraan, rentang, pengurutan	`= < > BETWEEN LIKE 'awalan%'`
GIN	JSONB, array, pencarian teks penuh	`@> ? && @@`
GiST	Geometri, rentang, kedekatan	`&& <-> @>`
BRIN	Tabel sangat besar yang terurut alami	Rentang; indeks sangat kecil
Hash	Hanya kesetaraan	`=` — jarang lebih baik dari B-tree

Aturan Urutan Kolom pada Indeks Gabungan

CREATE INDEX idx ON pesanan (pengguna_id, status, dibuat_pada);

Indeks ini bisa dipakai untuk kueri yang menyaring:

✅ pengguna_id
✅ pengguna_id + status
✅ pengguna_id + status + dibuat_pada
❌ status saja                    ← tidak bisa (melompati kolom pertama)
❌ dibuat_pada saja               ← tidak bisa

💡 Aturan yang perlu diingat

Indeks gabungan bisa dipakai untuk awalan kiri dari daftar kolomnya. Urutan yang baik: kolom yang dipakai dengan kesetaraan di depan, kolom rentang di belakang. Menaruh kolom rentang di tengah membuat kolom sesudahnya tidak bisa dimanfaatkan untuk penyaringan.

Indeks Parsial

-- Hanya 2% pesanan berstatus menunggu, tapi itu yang paling sering dikueri
CREATE INDEX idx_menunggu ON pesanan (dibuat_pada)
WHERE status = 'menunggu';

-- Indeksnya berukuran 2% dari indeks penuh,
-- muat sepenuhnya di cache, dan jauh lebih cepat.

-- Sangat berguna untuk penghapusan lunak
CREATE INDEX idx_aktif ON pengguna (email)
WHERE dihapus_pada IS NULL;

📌 Indeks parsial sering merupakan kemenangan terbesar

Bila sebagian besar kueri Anda hanya menyentuh sebagian kecil baris — baris aktif, pesanan belum selesai, catatan yang belum diproses — indeks parsial memberi peningkatan yang dramatis dengan biaya penyimpanan jauh lebih kecil, sekaligus mengurangi beban pemeliharaan saat penulisan.

Covering Index

CREATE INDEX idx_covering ON pesanan (pengguna_id) INCLUDE (total, status);

SELECT total, status FROM pesanan WHERE pengguna_id = 55;
-- PostgreSQL bisa menjawab sepenuhnya dari indeks
-- tanpa menyentuh tabel sama sekali (index-only scan).

⚠️ Index-only scan butuh visibility map yang mutakhir

PostgreSQL tetap harus memastikan baris terlihat oleh transaksi Anda. Ia bisa melewatkan pemeriksaan itu hanya bila halaman ditandai "semua terlihat" pada visibility map — dan penanda itu diperbarui oleh VACUUM. Pada tabel yang sering berubah dan jarang di-vacuum, index-only scan tidak akan terjadi meski indeksnya sudah tepat.

Kapan Indeks Merugikan

🚫 Setiap indeks memperlambat penulisan

Setiap INSERT, UPDATE, dan DELETE harus memperbarui semua indeks pada tabel tersebut. Tabel dengan sepuluh indeks membayar sepuluh kali biaya pemeliharaan pada setiap penulisan. Indeks juga memakan ruang disk dan memori cache yang seharusnya dipakai data panas.

Indeks juga tidak berguna pada tabel yang sangat kecil, pada kolom dengan sedikit nilai unik seperti boolean (kecuali sebagai indeks parsial), dan ketika kueri tetap mengambil sebagian besar baris.

Menemukan Indeks yang Tidak Terpakai

SELECT schemaname, relname, indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS ukuran
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;

Membuat Indeks Tanpa Mengunci

🚫 CREATE INDEX biasa mengunci penulisan

-- ❌ Memblokir SEMUA penulisan sampai selesai.
CREATE INDEX idx ON pesanan (pengguna_id);

-- ✅ Tidak memblokir penulisan
CREATE INDEX CONCURRENTLY idx ON pesanan (pengguna_id);

Versi CONCURRENTLY lebih lambat dan tidak bisa dijalankan di dalam blok transaksi. Bila gagal di tengah jalan, ia meninggalkan indeks yang tidak valid — harus dihapus dan diulang:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx;

Kenapa Indeks Tidak Terpakai

-- ❌ Fungsi pada kolom membuat indeks tidak bisa dipakai
WHERE lower(email) = '[email protected]'
-- ✅ Buat indeks ekspresi yang sesuai
CREATE INDEX ON pengguna (lower(email));

-- ❌ Tipe tidak cocok memicu konversi
WHERE id_teks = 12345          -- kolom text dibandingkan integer
-- ✅ WHERE id_teks = '12345'

-- ❌ LIKE dengan wildcard di depan
WHERE nama LIKE '%budi%'
-- ✅ Gunakan indeks trigram
CREATE EXTENSION pg_trgm;
CREATE INDEX ON pengguna USING gin (nama gin_trgm_ops);