Index PostgreSQL: Cara Memilih, Menguji, dan Menghindari Index Sia-sia

Index PostgreSQL: Cara Memilih, Menguji, dan Menghindari Index yang Sia-sia

Index bukan tombol “buat query cepat”. PostgreSQL akan memilih rencana dengan biaya perkiraan paling rendah, dan kadang sequential scan memang lebih masuk akal daripada membaca index lalu mengambil banyak baris dari tabel.

Artikel ini memakai satu prinsip: ukur rencana query yang nyata, lalu buat index untuk pola akses yang nyata.

---

Mulai dari Query, Bukan dari Nama Kolom

Misalkan halaman riwayat pesanan menjalankan query:

SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Index satu kolom pada created_at belum tentu membantu. Filter utama memakai user_id dan status, lalu hasilnya diurutkan. Kandidat yang lebih sesuai:

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

Urutan itu penting. B-tree paling efektif ketika query membatasi kolom-kolom paling kiri. Index di atas cocok untuk user_id saja atau kombinasi user_id dan status, tetapi bukan pilihan umum untuk query yang hanya memfilter status.

Jangan langsung membuatnya di production. Periksa dahulu distribusi data, ukuran tabel, frekuensi query, dan index yang sudah ada.

---

Baca EXPLAIN dengan Benar

Gunakan EXPLAIN untuk melihat rencana tanpa menjalankan query:

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Gunakan EXPLAIN (ANALYZE, BUFFERS) di lingkungan aman ketika Anda perlu waktu aktual dan aktivitas buffer:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE benar-benar menjalankan statement. Untuk UPDATE, DELETE, atau INSERT, pakai transaksi yang dapat dibatalkan atau uji pada salinan data:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'archived'
WHERE created_at < now() - interval '2 years';
ROLLBACK;

Hal yang perlu dibandingkan:

• actual rows versus rows: selisih besar dapat menandakan statistik tidak representatif;
• loops: node murah yang berulang sangat banyak bisa tetap mahal;
• Buffers: shared hit/read: membedakan data dari cache dan pembacaan storage;
• operasi Sort dan apakah memakai memory atau disk;
• total execution time pada beberapa sampel yang representatif.

Seq Scan bukan otomatis bug. Jika query mengambil sebagian besar tabel, membaca tabel berurutan sering lebih murah. Sebaliknya, Index Scan bukan otomatis optimal jika menghasilkan banyak random heap fetch.

---

Expression Index untuk Ekspresi yang Stabil

Query case-insensitive ini tidak identik dengan index biasa pada email:

SELECT id FROM users WHERE lower(email) = lower($1);

Buat expression index yang sesuai:

CREATE INDEX idx_users_email_lower
ON users (lower(email));

Untuk mencegah email duplikat tanpa membedakan kapitalisasi, pertimbangkan unique expression index:

CREATE UNIQUE INDEX users_email_lower_unique
ON users (lower(email));

Pastikan semantik tersebut memang aturan bisnis. Jangan menambahkan unique index sebelum membersihkan data duplikat.

---

Pencarian Substring Membutuhkan Jenis Index yang Tepat

B-tree biasa umumnya tidak membantu pola yang diawali wildcard:

SELECT id, title
FROM articles
WHERE title ILIKE '%postgres%';

Untuk pencarian substring, extension pg_trgm dan GIN/GiST dapat menjadi pilihan:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_articles_title_trgm
ON articles USING gin (title gin_trgm_ops);

Untuk pencarian dokumen berdasarkan bahasa dan peringkat relevansi, full-text search lebih cocok daripada sekadar ILIKE. Keduanya punya biaya penyimpanan dan write, jadi ukur berdasarkan workload.

---

Partial Index untuk Subset yang Sering Diakses

Jika hampir semua query hanya membaca baris aktif:

SELECT id, email
FROM users
WHERE deleted_at IS NULL
AND last_login_at < now() - interval '90 days';

Partial index dapat menyimpan subset itu saja:

CREATE INDEX idx_users_active_last_login
ON users (last_login_at)
WHERE deleted_at IS NULL;

Predicate pada query harus dapat dikenali sebagai cocok dengan predicate index. Query yang tidak menyertakan deleted_at IS NULL tidak dapat mengandalkan index tersebut.

---

Covering Index dan Batas Index Only Scan

Kolom non-key dapat disertakan dengan INCLUDE:

CREATE INDEX idx_orders_user_created_cover
ON orders (user_id, created_at DESC)
INCLUDE (status, total_amount);

Ini membuat status dan total_amount tersedia dari index tanpa menjadikannya bagian pencarian. Namun Index Only Scan tetap bergantung pada visibility map PostgreSQL; heap fetch masih mungkin diperlukan untuk halaman yang belum ditandai all-visible.

Kolom tambahan juga memperbesar index. Jangan memasukkan kolom besar atau banyak kolom hanya untuk mengejar label Index Only Scan.

---

Index Memiliki Biaya

Setiap INSERT, DELETE, dan perubahan nilai yang terindeks harus memelihara struktur index. Index juga memakai storage, cache, waktu vacuum, dan waktu backup.

Lihat statistik penggunaan:

SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 belum cukup untuk langsung menghapus index. Statistik bisa baru di-reset; index mungkin melindungi constraint, dipakai oleh job bulanan, atau diperlukan saat insiden. Periksa definisi, dependensi, log query, dan periode observasi.

Untuk tabel aktif, pembuatan index biasa dapat memblokir write. PostgreSQL menyediakan CREATE INDEX CONCURRENTLY, tetapi cara ini memerlukan lebih banyak pekerjaan, berjalan lebih lama, dan tidak boleh dijalankan di dalam transaction block:

CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

Sesudah membuat index, jalankan kembali query dengan parameter yang mewakili kasus umum dan kasus ekstrem. Satu user mungkin memiliki 10 pesanan, sedangkan user lain memiliki satu juta; planner dapat menghasilkan keputusan berbeda ketika selectivity berubah. Pastikan statistik tabel mutakhir, tetapi jangan menjalankan maintenance berat secara sembarangan pada jam sibuk.

Perhatikan pula index yang tumpang tindih. Index (user_id, status) mungkin menjadi duplikat praktis setelah ada (user_id, status, created_at), tetapi keputusan menghapusnya tetap bergantung pada ukuran, pola ordering, constraint, dan workload write. Bandingkan pg_get_indexdef(), statistik penggunaan, serta rencana query sebelum melakukan drop.

---

Checklist Sebelum Menambah Index

1. Ambil query lambat beserta parameter yang representatif.
2. Catat rencana dan baseline dengan EXPLAIN; gunakan ANALYZE secara aman.
3. Periksa estimasi baris, selectivity, sort, loops, dan buffer.
4. Cari index yang sudah ada dan hindari duplikasi prefix.
5. Buat kandidat index berdasarkan filter, join, dan ordering.
6. Uji efek pada read dan write.
7. Pantau setelah deploy; jangan menyimpulkan dari satu eksekusi dengan cache hangat.

Index yang baik bukan index terbanyak, melainkan index paling kecil yang mendukung workload penting tanpa membuat write dan operasional menjadi tidak proporsional.

Referensi

• PostgreSQL: Using EXPLAIN
• PostgreSQL: Indexes
• PostgreSQL: Index-Only Scans and Covering Indexes
• PostgreSQL: CREATE INDEX