Indeks PostgreSQL: B-tree, GIN, dan Cara Membaca EXPLAIN ANALYZE

Indeks membuat query SELECT jauh lebih cepat, tetapi bukan tanpa biaya. Setiap CREATE INDEX menambah pekerjaan pada INSERT dan UPDATE karena struktur indeks ikut diperbarui. Artikel ini membahas kapan memakai B-tree atau GIN, serta cara membaca output EXPLAIN ANALYZE untuk membuktikan apakah sebuah indeks benar-benar membantu.

Ringkasan

• B-tree menjadi indeks default untuk operator perbandingan.
• GIN cocok untuk array, jsonb, dan teks bertingkat.
• Planner memakai indeks hanya bila biayanya lebih murah dari full scan.
• EXPLAIN ANALYZE menampilkan baris aktual, bukan sekadar perkiraan.
• Analisis beban kerja dulu sebelum membuat indeks baru.

Cara Kerja Indeks dan Biayanya

Indeks adalah struktur bantu yang membuat server bisa menemukan baris tertentu lebih cepat daripada memindai seluruh tabel. Analoginya seperti daftar isi buku: Anda tidak perlu membaca semua halaman untuk menemukan satu bab. Dokumentasi PostgreSQL menyebut bahwa indeks tetap menambah overhead bagi database secara keseluruhan, sehingga harus digunakan secara bijak.

Biaya utama ada pada operasi menulis. Saat Anda menjalankan INSERT, UPDATE, atau DELETE, setiap indeks pada tabel ikut diperbarui. Tabel dengan banyak indeks menjadi lambat di jalur tulis, meski cepat dibaca. Artinya, keputusan membuat indeks harus didasarkan pada pola query, bukan kelengkapan kolom.

B-tree: Indeks Bawaan untuk Operator Perbandingan

Perintah CREATE INDEX tanpa klausa USING membuat indeks B-tree. Jenis ini menangani operator =, <, <=, >, >=, bentuk gabungan seperti BETWEEN dan IN, serta IS NULL/IS NOT NULL seperti dijelaskan pada bagian tipe indeks. B-tree juga mendukung pola LIKE 'foo%' selama awalan pola berupa konstanta.

Berikut contoh yang dijalankan pada PostgreSQL 18 dengan 100 ribu baris data dummy:

CREATE TABLE produk (
    id       serial PRIMARY KEY,
    nama     text NOT NULL,
    kategori int  NOT NULL,
    harga    numeric(12,2) NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO produk (nama, kategori, harga, aktif)
SELECT
    'produk-' || g,
    g % 100,
    (random() * 100000)::numeric(12,2) / 100,
    g % 100 <> 7
FROM generate_series(1, 100000) g;

ANALYZE produk;

Sebelum ada indeks, query SELECT * FROM produk WHERE kategori = 7 dijalankan sebagai pemindaian penuh:

Seq Scan on produk  (cost=0.00..2528.00 rows=970 width=66)
  Filter: (kategori = 7)

Setelah CREATE INDEX produk_kategori_idx ON produk (kategori) diikuti ANALYZE, plan berubah menjadi dua tahap bitmap:

Bitmap Heap Scan on produk  (cost=11.94..1278.60 rows=987 width=66)
  Recheck Cond: (kategori = 7)
  ->  Bitmap Index Scan on produk_kategori_idx  (cost=0.00..11.70 rows=987 width=0)
        Index Cond: (kategori = 7)

Biaya turun drastis karena membaca 987 baris lebih murah daripada 100 ribu baris. Perhatikan juga ANALYZE wajib dijalankan setelah membuat indeks agar planner memiliki statistik terbaru.

Kapan Planner Menolak B-tree

Planner membandingkan biaya plan secara keseluruhan. Query yang mengambil nilai umum, misalnya lebih dari beberapa persen isi tabel, sering kali lebih murah dipindai penuh daripada membaca indeks. Pada data di atas, query kategori = 7 menyentuh sekitar 1% baris dan tetap memakai indeks. Pola yang memfilter hampir semua baris akan kembali ke Seq Scan.

GIN: Inverted Index untuk Array dan Jenis Bertingkat

GIN (Generalized Inverted Index) adalah kebalikan dari B-tree: alih-alih menyimpan satu entri per baris, GIN menyimpan satu entri untuk setiap komponen nilai dalam baris. Itulah sebabnya GIN sesuai untuk kolom yang berisi banyak nilai sekaligus, seperti array, jsonb, atau dokumen teks. Untuk array, operator yang terbantu adalah @>, <@, =, dan &&.

Lanjutkan contoh dengan kolom tags:

ALTER TABLE produk ADD COLUMN tags text[] NOT NULL DEFAULT '{}';

UPDATE produk SET tags = '{flash-sale}'
WHERE id % 1000 = 0;

UPDATE produk SET tags = '{vip}'
WHERE id % 1000 <> 0;

CREATE INDEX produk_tags_idx ON produk USING gin (tags);
ANALYZE produk;

Query untuk tag langka flash-sale (hanya 100 baris) memakai indeks:

Bitmap Heap Scan on produk  (cost=29.61..310.64 rows=90 width=16)
  Recheck Cond: (tags @> '{flash-sale}'::text[])
  ->  Bitmap Index Scan on produk_tags_idx  (cost=0.00..29.59 rows=90 width=0)
        Index Cond: (tags @> '{flash-sale}'::text[])

Sebaliknya, tag vip yang dimiliki separuh tabel membuat biaya plan naik sekitar tujuh kali lipat. GIN menguntungkan untuk nilai yang jarang dicari; nilai yang sangat umum justru membuat biaya indeks mendekati biaya pemindaian penuh.

Jenis Indeks Lainnya Sekilas

Beberapa tipe indeks lain tersedia dan digunakan dengan notasi USING nama_tipe:

Hash amat hemat namun hanya menangani =. GiST dan SP-GiST menjadi dasar berbagai strategi dari ekstensi. BRIN menyimpan ringkasan per blok, sehingga sangat kecil tetapi efisien untuk data seperti timestamp yang tersusun berurutan. Keterangan lebih lengkap ada di dokumentasi tipe indeks.

Partial Index: Indeks untuk Baris yang Relevan Saja

Partial index dibangun di atas subset tabel melalui predikat di akhir perintah. Manfaat utamanya mengecilkan ukuran indeks dan mempercepat operasi tulis, seperti dijelaskan pada dokumentasi partial index.

Contoh dari dokumentasi: tabel order berisi baris yang sudah dibayar dan belum dibayar, tetapi yang paling sering diakses adalah yang belum dibayar. Indeks cukup dibuat untuk baris itu:

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

Pada data dummy, indeks WHERE NOT aktif dijadikan partial index untuk kategori:

CREATE INDEX produk_inactive_idx ON produk (kategori) WHERE NOT aktif;

Query SELECT id FROM produk WHERE NOT aktif AND kategori = 7 memakai indeks tersebut. Syaratnya, predikat indeks harus diimplikasikan oleh kondisi WHERE, dan pencocokan terjadi saat planning. Predikat parameter, misalnya kategori < $1, tidak akan memicu partial index.

Membaca Output EXPLAIN

Baris pertama plan menunjukkan cost=biaya_awal..biaya_total baris width. Biaya total pada node teratas adalah perkiraan keseluruhan; biaya node di atasnya mencakup semua anaknya. Angka rows adalah perkiraan baris yang dipancarkan node, bukan yang dipindai. Node anak paling bawah adalah scan: Seq Scan, Index Scan, atau Bitmap Index Scan yang diikuti Bitmap Heap Scan.

Perbedaan Index Cond dan Filter menentukan apa yang bisa diteruskan ke indeks:

Bitmap Heap Scan on produk  (cost=11.71..1280.83 rows=50 width=66)
  Recheck Cond: (kategori = 7)
  Filter: (harga < '50'::numeric)
  ->  Bitmap Index Scan on produk_kategori_idx  (cost=0.00..11.70 rows=987 width=0)
        Index Cond: (kategori = 7)

Kondisi kategori = 7 menjadi Index Cond karena ada di indeks. Kondisi harga < 50 menjadi Filter: dicek per baris setelah baris diambil dari tabel. Query seperti ini tidak akan lebih cepat hanya karena indeks kategori ada, selama filter kedua tidak pernah jadi bagian indeks.

EXPLAIN ANALYZE Menampilkan Realita

EXPLAIN ANALYZE benar-benar mengeksekusi query dan menampilkan waktu serta baris aktual di samping perkiraan. Waktu aktual dalam milidetik, sedangkan cost dalam satuan arbitrer, sehingga tidak bisa dibandingkan langsung:

Bitmap Index Scan on produk_kategori_idx  (cost=0.00..11.70 rows=987 width=0)
  (actual time=0.224..0.225 rows=1000.00 loops=1)
Planning Time: 0.036 ms
Execution Time: 0.980 ms

Perkiraan planner 987, padahal aktual 1.000 baris. Perbedaan kecil seperti ini normal. Yang patut dicurigai adalah perbedaan besar antara rows estimasi dan aktual, yang umumnya berarti statistik tabel basi. Info tambahan lain termasuk Rows Removed by Filter dan Buffers. Penjelasan lebih dalam tersedia di panduan EXPLAIN.

Hindari mengukur pada tabel kecil lalu menyimpulkan perilaku tabel besar: planner memilih plan berbeda karena cost tidak linear. Bahkan pada tabel berukuran satu halaman, Seq Scan hampir selalu menang.

Panduan Praktis Membuat Indeks

Dokumentasi menyarankan beberapa langkah untuk menentukan indeks yang layak, dirangkum dari bagian examining index usage:

• Selalu jalankan ANALYZE untuk memperbarui statistik sebelum menguji.
• Gunakan data nyata, bukan sampel kecil yang sengaja dibuat.
• Jika indeks tidak dipakai, uji dengan SET enable_seqscan = off untuk melihat apakah plan berubah.
• Bandingkan EXPLAIN ANALYZE dengan dan tanpa indeks untuk memastikan perbaikannya nyata.
• Untuk gambaran pemakaian menyeluruh, lihat statistik indeks per tabel pada struktur pg_stat_user_indexes.

Satu indeks berguna pada sebuah sistem belum tentu berguna di sistem lain. Ide memberi indeks untuk semua kolom lebih sering menjadi masalah daripada solusi. Mulailah dari query paling lambat, perbaiki dengan indeks yang paling sesuai, lalu buktikan lewat EXPLAIN ANALYZE.