Memilih Jenis Index

Lima belas chapter sebelumnya membedah setiap jenis index satu per satu: B-tree untuk urutan, GIN untuk nilai jamak, GiST untuk jarak dan tumpang tindih, BRIN untuk tabel raksasa terurut, SP-GiST untuk partisi ruang. Pengetahuan yang tersebar baru berguna ketika bisa dipanggil dalam hitungan detik di depan sebuah query nyata. Chapter ini merangkum semuanya menjadi peta keputusan yang ringkas, lalu mengujinya pada lima pola query yang paling sering muncul di aplikasi sehari-hari.

Peta Keputusan Cepat #

Tabel berikut adalah inti dari seluruh Section Index. Cari operator atau bentuk query yang paling mirip dengan masalah Anda, lalu baca kolom jenis index-nya:

Tiga modifier bisa menempel di jenis index mana pun dan sering menentukan keberhasilannya: composite (kolom pertama menentukan kegunaan), partial (hanya baris yang memenuhi predikat), dan expression (mengindeks hasil ekspresi seperti lower(email)). Pemilihan jenis index tanpa memikirkan ketiganya sering berujung pada index yang tidak pernah terpakai.

Pola 1: Kesetaraan pada Kolom Identitas #

Pola paling umum: mencari baris berdasarkan satu nilai — email pengguna, kode produk, nomor order. Tabel latihan berikut berisi 20.000 sesi login dan mencari satu email:

-- Pola 1: kesetaraan. Tabel sesi login dengan 20.000 baris.
CREATE TABLE sesi_login (
    id          bigint NOT NULL,
    email       text NOT NULL,
    waktu_login timestamp NOT NULL,
    perangkat   text NOT NULL
);

INSERT INTO sesi_login (id, email, waktu_login, perangkat)
SELECT g,
       'pengguna' || lpad(((g * 13) % 2000)::text, 4, '0') || '@example.com',
       TIMESTAMP '2026-02-01 05:00:00' + (g * interval '30 seconds'),
       (ARRAY['android','ios','web'])[1 + (g % 3)]
FROM generate_series(1, 20000) AS g;

-- B-tree: pilihan default untuk kesetaraan.
CREATE INDEX idx_email ON sesi_login (email);
ANALYZE sesi_login;

SELECT count(*) AS sesi_email_tersebut
FROM sesi_login
WHERE email = '[email protected]';

SELECT id, email, waktu_login, perangkat
FROM sesi_login
WHERE email = '[email protected]'
ORDER BY id
LIMIT 3;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 20000
CREATE INDEX
ANALYZE
 sesi_email_tersebut 
---------------------
                  10
(1 row)

  id  |          email           |     waktu_login     | perangkat 
------+--------------------------+---------------------+-----------
 1077 | [email protected] | 2026-02-01 13:58:30 | android
 3077 | [email protected] | 2026-02-02 06:38:30 | web
 5077 | [email protected] | 2026-02-02 23:18:30 | ios
(3 rows)

B-tree menang pola ini karena tiga alasan sekaligus: melayani = dengan kompleksitas logaritmik, ikut melayani rentang dan ORDER BY email jika kelak dibutuhkan, dan tidak memerlukan operator class khusus. Hash hanya layak dipertimbangkan bila key-nya panjang dan query selalu kesetaraan murni — penghematan ukuranya jarang sepadan dengan hilangnya fleksibilitas.

Pola 2: Rentang Waktu dan Sepuluh Baris Terbaru #

Query berbasis waktu: “tampilkan transaksi hari kemarin” dan “sepuluh sesi terbaru”. Keduanya dilayani B-tree pada kolom waktu:

-- Pola 2: rentang dan urutan pada kolom waktu yang sama.
CREATE INDEX idx_waktu_login ON sesi_login (waktu_login);

-- Seluruh sesi sepanjang 3 Februari 2026.
SELECT count(*) AS sesi_sehari,
       min(waktu_login) AS pertama,
       max(waktu_login) AS terakhir
FROM sesi_login
WHERE waktu_login BETWEEN TIMESTAMP '2026-02-03 00:00:00'
                      AND TIMESTAMP '2026-02-03 23:59:59';

-- Report sepuluh sesi terbaru: B-tree juga melayani ORDER BY DESC.
SELECT id, email, waktu_login
FROM sesi_login
ORDER BY waktu_login DESC
LIMIT 5;

Output yang Dihasilkan:

CREATE INDEX
 sesi_sehari |       pertama       |      terakhir       
-------------+---------------------+---------------------
        2880 | 2026-02-03 00:00:00 | 2026-02-03 23:59:30
(1 row)

  id   |          email           |     waktu_login     
-------+--------------------------+---------------------
 20000 | [email protected] | 2026-02-08 03:40:00
 19999 | [email protected] | 2026-02-08 03:39:30
 19998 | [email protected] | 2026-02-08 03:39:00
 19997 | [email protected] | 2026-02-08 03:38:30
 19996 | [email protected] | 2026-02-08 03:38:00
(5 rows)

Dua query pada satu file itu sengaja memamerkan dua kemampuan B-tree yang berasal dari sifat terurutnya: BETWEEN memanfaatkan pencarian rentang, dan ORDER BY ... DESC LIMIT 5 memanfaatkan kemampuan index menyajikan baris sudah terurut sehingga tidak ada pengurutan penuh. Aturan komposit yang relevan di sini: bila query memfilter dua kolom, kolom kesetaraan diletakkan lebih dulu dan kolom rentang di belakangnya.

Pola 3: Tag dalam Array #

Satu produk membawa banyak tag; query bertanya “siapa yang punya tag diskon?”. Nilai jamak dalam satu kolom adalah wilayah GIN:

-- Pola 3: nilai jamak. Satu baris memuat banyak tag dalam array text[].
CREATE TABLE produk_tag (
    id   integer PRIMARY KEY,
    nama text NOT NULL,
    tag  text[] NOT NULL
);

INSERT INTO produk_tag (id, nama, tag)
SELECT g,
       'Produk ' || lpad(g::text, 4, '0'),
       ARRAY[(ARRAY['diskon','baru','bestseller'])[1 + (g % 3)], 'gudang-jakarta']
FROM generate_series(1, 20000) AS g;

-- GIN: inverted index untuk keanggotaan elemen array.
CREATE INDEX idx_tag ON produk_tag USING gin (tag);
ANALYZE produk_tag;

SELECT count(*) AS produk_diskon
FROM produk_tag
WHERE tag @> ARRAY['diskon'];

SELECT id, nama, tag
FROM produk_tag
WHERE tag @> ARRAY['diskon']
ORDER BY id
LIMIT 3;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 20000
CREATE INDEX
ANALYZE
 produk_diskon 
---------------
          6666
(1 row)

 id |    nama     |           tag           
----+-------------+-------------------------
  3 | Produk 0003 | {diskon,gudang-jakarta}
  6 | Produk 0006 | {diskon,gudang-jakarta}
  9 | Produk 0009 | {diskon,gudang-jakarta}
(3 rows)

Operator @> pada array membutuhkan pemeriksaan isi kolom baris demi baris tanpa index — 20.000 pemeriksaan array. GIN membalik strukturnya: setiap nilai tag unik menjadi entri index yang menunjuk semua baris pemiliknya, sehingga “semua pemilik tag diskon” terjawab dari satu entri. Pola yang sama berlaku untuk JSONB (@> pada dokumen) dan full-text search (@@ pada tsvector).

Pola 4: Tetangga Terdekat #

“Agen terdekat dari titik pelanggan” — pola lokasi yang tidak bisa dilayani B-tree karena tidak ada pengurutan linear yang alami untuk titik dua dimensi:

-- Pola 4: tetangga terdekat. Posisi 2.000 gerai sebagai titik geometri.
CREATE TABLE gerai (
    id      integer PRIMARY KEY,
    nama    text NOT NULL,
    posisi  point NOT NULL
);

INSERT INTO gerai (id, nama, posisi)
SELECT g,
       'Gerai ' || lpad(g::text, 4, '0'),
       point((g % 50) * 3.0, ((g / 50) % 40) * 2.5)
FROM generate_series(0, 1999) AS g;

-- GiST: satu-satunya access method yang mendukung ORDER BY jarak <->.
CREATE INDEX idx_posisi ON gerai USING gist (posisi);
ANALYZE gerai;

-- Tiga gerai terdekat dari titik kantor pusat (1.7, 2.3).
SELECT nama, posisi,
       round((posisi <-> point(1.7, 2.3))::numeric, 2) AS jarak
FROM gerai
ORDER BY posisi <-> point(1.7, 2.3)
LIMIT 3;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 2000
CREATE INDEX
ANALYZE
    nama    | posisi  | jarak 
------------+---------+-------
 Gerai 0051 | (3,2.5) |  1.32
 Gerai 0050 | (0,2.5) |  1.71
 Gerai 0001 | (3,0)   |  2.64
(3 rows)

GiST (dengan operator class point_ops) menang karena memahami jarak: ORDER BY posisi <-> titik dilayani dari index yang menyusun ruang, dan LIMIT 3 menghentikan pencarian begitu tiga tetangga terkumpul. Untuk kebutuhan geospasial sungguhan — poligon, garis, sistem koordinat — operator class bawaan digantikan PostGIS, tetapi keputusan jenis index-nya tetap GiST.

Pola 5: Log Raksasa Terurut Alami #

Tabel log yang hanya bertambah dan kolom waktunya mengikuti urutan penyimpanan fisik — pola BRIN:

-- Pola 5: tabel raksasa tambah-only yang terurut alami.
CREATE TABLE log_aktivitas (
    id    bigint NOT NULL,
    waktu timestamp NOT NULL,
    aksi  text NOT NULL
);

INSERT INTO log_aktivitas (id, waktu, aksi)
SELECT g,
       TIMESTAMP '2026-01-01 00:00:00' + (g * interval '30 seconds'),
       (ARRAY['login','logout','checkout'])[1 + (g % 3)]
FROM generate_series(1, 100000) AS g;

-- BRIN: ringkasan min/max per rentang blok, ukuran mini.
CREATE INDEX idx_brin_waktu ON log_aktivitas USING brin (waktu);

SELECT count(*) AS aktivitas_sehari
FROM log_aktivitas
WHERE waktu BETWEEN TIMESTAMP '2026-01-15 00:00:00'
                AND TIMESTAMP '2026-01-15 23:59:59';

SELECT pg_size_pretty(pg_relation_size('idx_brin_waktu')) AS ukuran_index_brin;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 100000
CREATE INDEX
 aktivitas_sehari 
------------------
             2880
(1 row)

 ukuran_index_brin 
-------------------
 24 kB
(1 row)

Perhatikan baris terakhir output: index BRIN pada 100.000 baris hanya berukuran puluhan kilobyte — B-tree pada data yang sama akan ratusan kali lebih besar. BRIN murah karena tidak mengindeks tiap baris, melainkan meringkas rentang nilai per blok fisik; query rentang lalu membuang blok yang mustahil mengandung hasil. Ketika data datanya acak (tidak berkorelasi dengan urutan fisik), ringkasan per blok menjadi tidak informatif dan BRIN kehilangan dayanya — saat itulah kembali ke B-tree.

Diagram Keputusan #

Untuk query yang belum jelas polanya, urutan pertanyaan berikut memotong pilihan dengan cepat:

Diagram ini dimulai dari kasus khusus ke umum: GIN dan GiST dicek lebih dulu karena kebutuhannya paling mudah dikenali dari operator query, sedangkan B-tree berada di ujung sebagai default yang hampir selalu benar untuk sisanya. Pertanyaan partial index ditanyakan terakhir karena berlaku untuk semua jenis.

Rangkaian Uji Sebelum Produksi #

Memilih jenis index baru setengah jalan; meyakinkan bahwa index benar-benar terpakai adalah setengah sisanya. Kabar baiknya, seluruh pola pada chapter ini bisa diverifikasi berulang dengan cara yang sama:

1. Buat tabel dan data uji yang bentuknya menyerupai produksi (ukuran minimal ribuan baris agar planner serius).
2. ANALYZE tabelnya supaya statistik mengikuti data.
3. EXPLAIN query-nya dan pastikan nama index muncul di rencana (teknik SET enable_seqscan = off untuk tabel latihan kecil).
4. Ukur dampak nyata dengan EXPLAIN ANALYZE bila perlu.

Setelah index terpasang dan terbukti terpakai, pekerjaan belum selesai — setiap index menagih harga pada setiap INSERT, UPDATE, dan DELETE. Cara menghitung tagihan itu dan menjaga agar index tidak menumpuk tanpa guna adalah topik chapter penutup section ini: Biaya Pemeliharaan Index.

Referensi #

• Indexes Overview
• Index Types
• EXPLAIN Documentation

-- Pola 5: tabel raksasa tambah-only yang terurut alami.
CREATE TABLE log_aktivitas (
    id    bigint NOT NULL,
    waktu timestamp NOT NULL,
    aksi  text NOT NULL
);

INSERT INTO log_aktivitas (id, waktu, aksi)
SELECT g,
       TIMESTAMP '2026-01-01 00:00:00' + (g * interval '30 seconds'),
       (ARRAY['login','logout','checkout'])[1 + (g % 3)]
FROM generate_series(1, 100000) AS g;

-- BRIN: ringkasan min/max per rentang blok, ukuran mini.
CREATE INDEX idx_brin_waktu ON log_aktivitas USING brin (waktu);

SELECT count(*) AS aktivitas_sehari
FROM log_aktivitas
WHERE waktu BETWEEN TIMESTAMP '2026-01-15 00:00:00'
                AND TIMESTAMP '2026-01-15 23:59:59';

SELECT pg_size_pretty(pg_relation_size('idx_brin_waktu')) AS ukuran_index_brin;

-- Pola 1: kesetaraan. Tabel sesi login dengan 20.000 baris.
CREATE TABLE sesi_login (
    id          bigint NOT NULL,
    email       text NOT NULL,
    waktu_login timestamp NOT NULL,
    perangkat   text NOT NULL
);

INSERT INTO sesi_login (id, email, waktu_login, perangkat)
SELECT g,
       'pengguna' || lpad(((g * 13) % 2000)::text, 4, '0') || '@example.com',
       TIMESTAMP '2026-02-01 05:00:00' + (g * interval '30 seconds'),
       (ARRAY['android','ios','web'])[1 + (g % 3)]
FROM generate_series(1, 20000) AS g;

-- B-tree: pilihan default untuk kesetaraan.
CREATE INDEX idx_email ON sesi_login (email);
ANALYZE sesi_login;

SELECT count(*) AS sesi_email_tersebut
FROM sesi_login
WHERE email = '[email protected]';

SELECT id, email, waktu_login, perangkat
FROM sesi_login
WHERE email = '[email protected]'
ORDER BY id
LIMIT 3;

-- Pola 3: nilai jamak. Satu baris memuat banyak tag dalam array text[].
CREATE TABLE produk_tag (
    id   integer PRIMARY KEY,
    nama text NOT NULL,
    tag  text[] NOT NULL
);

INSERT INTO produk_tag (id, nama, tag)
SELECT g,
       'Produk ' || lpad(g::text, 4, '0'),
       ARRAY[(ARRAY['diskon','baru','bestseller'])[1 + (g % 3)], 'gudang-jakarta']
FROM generate_series(1, 20000) AS g;

-- GIN: inverted index untuk keanggotaan elemen array.
CREATE INDEX idx_tag ON produk_tag USING gin (tag);
ANALYZE produk_tag;

SELECT count(*) AS produk_diskon
FROM produk_tag
WHERE tag @> ARRAY['diskon'];

SELECT id, nama, tag
FROM produk_tag
WHERE tag @> ARRAY['diskon']
ORDER BY id
LIMIT 3;

-- Pola 2: rentang dan urutan pada kolom waktu yang sama.
CREATE INDEX idx_waktu_login ON sesi_login (waktu_login);

-- Seluruh sesi sepanjang 3 Februari 2026.
SELECT count(*) AS sesi_sehari,
       min(waktu_login) AS pertama,
       max(waktu_login) AS terakhir
FROM sesi_login
WHERE waktu_login BETWEEN TIMESTAMP '2026-02-03 00:00:00'
                      AND TIMESTAMP '2026-02-03 23:59:59';

-- Report sepuluh sesi terbaru: B-tree juga melayani ORDER BY DESC.
SELECT id, email, waktu_login
FROM sesi_login
ORDER BY waktu_login DESC
LIMIT 5;

-- Pola 4: tetangga terdekat. Posisi 2.000 gerai sebagai titik geometri.
CREATE TABLE gerai (
    id      integer PRIMARY KEY,
    nama    text NOT NULL,
    posisi  point NOT NULL
);

INSERT INTO gerai (id, nama, posisi)
SELECT g,
       'Gerai ' || lpad(g::text, 4, '0'),
       point((g % 50) * 3.0, ((g / 50) % 40) * 2.5)
FROM generate_series(0, 1999) AS g;

-- GiST: satu-satunya access method yang mendukung ORDER BY jarak <->.
CREATE INDEX idx_posisi ON gerai USING gist (posisi);
ANALYZE gerai;

-- Tiga gerai terdekat dari titik kantor pusat (1.7, 2.3).
SELECT nama, posisi,
       round((posisi <-> point(1.7, 2.3))::numeric, 2) AS jarak
FROM gerai
ORDER BY posisi <-> point(1.7, 2.3)
LIMIT 3;