Konsep Index

Chapter penutup section desain database berakhir dengan dua perintah CREATE INDEX untuk mempercepat jalur join yang sibuk — tetapi perintah itu kita jalankan sebagai kotak hitam. Section ini membuka kotak hitam tersebut: apa sebenarnya yang dibuat CREATE INDEX, bagaimana struktur itu mempercepat pencarian, dan harga apa yang kita bayar untuk kecepatan itu. Chapter pertama membangun fondasi konsepnya: index sebagai struktur terpisah yang menyimpan alamat baris, bukan bagian dari tabel itu sendiri. Seluruh demonstrasi memakai satu tabel pelanggan berisi tepat 1000 baris, cukup besar agar perilaku planner terlihat, cukup kecil agar setiap angka bisa diperiksa ulang.

Menyiapkan Tabel Contoh #

Satu domain yang sama akan menemani enam chapter pertama section ini: tabel pelanggan milik Toko Maju Jaya dengan 1000 baris data pelanggan. Baris-barisnya dibuat lewat generate_series dengan ekspresi tetap — bukan random() — sehingga data, statistik planner, dan setiap output chapter ini dapat direproduksi persis:

-- Tabel contoh untuk seluruh pembahasan index di section ini:
-- 1000 pelanggan Toko Maju Jaya, dibuat deterministik lewat generate_series
-- sehingga statistik planner dan perilaku query selalu sama.
CREATE TABLE pelanggan (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    email    text NOT NULL,
    kota     text NOT NULL,
    kategori text NOT NULL,
    saldo    numeric(12,2) NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
SELECT
    g,
    'Pelanggan ' || lpad(g::text, 4, '0'),
    'pelanggan' || lpad(g::text, 4, '0') || '@example.com',
    (ARRAY['Jakarta','Bandung','Surabaya','Medan','Denpasar'])[1 + (g - 1) / 200],
    (ARRAY['reguler','silver','gold'])[1 + g % 3],
    ((g - 1) * 1000 + 500)::numeric(12,2),
    (g % 10 <> 0)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

-- Peta data: lima kota, masing-masing tepat 200 pelanggan.
SELECT kota, count(*) AS jumlah
FROM pelanggan
GROUP BY kota
ORDER BY kota;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 1000
ANALYZE
   kota   | jumlah 
----------+--------
 Bandung  |    200
 Denpasar |    200
 Jakarta  |    200
 Medan    |    200
 Surabaya |    200
(5 rows)

Perintah ANALYZE di akhir file bukan hiasan: perintah itu memerintahkan PostgreSQL mengumpulkan statistik tabel (jumlah baris, sebaran nilai kolom) yang dipakai planner saat memilih rencana eksekusi. Output terakhir memperlihatkan peta datanya — lima kota dengan masing-masing tepat 200 pelanggan, kolom kategori tiga nilai yang bergantian, dan saldo yang nilainya naik teratur mengikuti urutan id. Struktur data yang tertata ini akan membuat perbedaan perilaku index mudah diamati.

Analogi Indeks Buku — dan Batasnya #

Cara tercepat memahami index adalah membayangkan indeks di bagian belakang buku: Anda mencari kata “Denpasar”, membuka indeks, menemukan nomor halaman 412, lalu langsung membuka halaman itu — tanpa membaca buku dari halaman pertama. Tanpa indeks, satu-satunya pilihan adalah sequential scan: membaca setiap halaman, atau dalam istilah database, setiap baris tabel, satu per satu.

Analogi itu membantu sebagai titik awal, tetapi patah di tiga tempat penting:

1. Indeks buku ditulis sekali setelah naskah selesai; index database harus diperbarui pada setiap INSERT, UPDATE, dan DELETE — bukan sekali di akhir.
2. Nomor halaman buku stabil selamanya; alamat baris database dapat berubah ketika baris dipindahkan di dalam file fisik.
3. Indeks buku sekadar daftar kata; index database adalah struktur data terurut (pohon, tabel hash) yang dirancang agar pencarian, rentang, dan urutan bisa dihitung dalam langkah logaritmik, bukan linear.

Dengan tiga batasan itu di kepala, kita bisa membedah mekanisme aslinya di PostgreSQL.

Tabel Heap dan Alamat Baris #

PostgreSQL menyimpan isi tabel dalam struktur bernama heap: kumpulan halaman berukuran 8 kB, dan setiap halaman menampung baris-baris data. Setiap baris memiliki alamat fisik bernama ctid — pasangan (nomor halaman, posisi baris dalam halaman). Mari kita intip alamat tiga baris di tabel pelanggan:

-- ctid adalah alamat fisik sebuah baris di heap:
-- pasangan (nomor halaman, posisi baris dalam halaman).
SELECT ctid, id, nama
FROM pelanggan
WHERE id IN (1, 500, 1000)
ORDER BY id;

Output yang Dihasilkan:

  ctid   |  id  |      nama      
---------+------+----------------
 (0,1)   |    1 | Pelanggan 0001
 (6,14)  |  500 | Pelanggan 0500
 (12,22) | 1000 | Pelanggan 1000
(3 rows)

Baris id = 1 beralamat (0,1) — halaman pertama, baris pertama. Baris id = 500 beralamat (6,14), dan baris id = 1000 beralamat (12,22). Inilah titik kuncinya: index tidak menyimpan baris itu sendiri, melainkan pasangan (nilai kolom, ctid). Saat query mencari nama = 'Pelanggan 0420', index menemukan nilainya dengan cepat, membaca ctid yang menempel pada entri itu, lalu melompat langsung ke alamat baris di heap. Index adalah jalan pintas berisi alamat, bukan salinan data.

Index yang Sudah Anda Miliki #

Sebelum membuat index pertama secara sadar, kita sebenarnya sudah memilikinya: setiap PRIMARY KEY otomatis membuat satu index. View catalog pg_indexes memetakan semuanya:

-- pg_indexes adalah view catalog yang memetakan seluruh index dalam database.
-- PRIMARY KEY pada tabel pelanggan sudah otomatis membuat satu index.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'pelanggan'
ORDER BY indexname;

Output yang Dihasilkan:

   indexname    |                                indexdef                                 
----------------+-------------------------------------------------------------------------
 pelanggan_pkey | CREATE UNIQUE INDEX pelanggan_pkey ON public.pelanggan USING btree (id)
(1 row)

Output menunjukkan index pelanggan_pkey dengan definisi CREATE UNIQUE INDEX ... USING btree (id). Dua fakta terbaca langsung dari satu baris itu: primary key diimplementasikan sebagai index unique bertipe B-tree pada kolom id, dan setiap index menyimpan definisi lengkapnya di catalog sehingga bisa diperiksa kapan saja. Constraint UNIQUE biasa juga bekerja dengan cara yang sama — topik yang akan dibedah tuntas di chapter Unique Index.

Harga yang Dibayar: Ruang dan Waktu Tulis #

Index bukan bagian dari tabel; index adalah struktur fisik terpisah yang menempati ruang penyimpanan sendiri. Ukurannya bisa diukur dengan pg_relation_size:

-- Index bukan bagian dari tabel: ia struktur fisik terpisah dengan ukuran sendiri.
SELECT pg_size_pretty(pg_relation_size('pelanggan'))      AS tabel_pelanggan,
       pg_size_pretty(pg_relation_size('pelanggan_pkey')) AS index_pkey;

Output yang Dihasilkan:

 tabel_pelanggan | index_pkey 
-----------------+------------
 104 kB          | 40 kB
(1 row)

Tabel berisi 1000 baris hanya memakan 104 kB, tetapi index primary key-nya sudah memakan 40 kB — hampir 40% ukuran tabel, padahal index itu hanya menampung satu kolom bertipe integer. Semakin banyak index pada sebuah tabel, semakin besar porsi penyimpanan yang dipakai struktur pelengkap ini.

Ada pula biaya pada jalur tulis. Setiap INSERT baru harus menambahkan entri ke semua index tabel; setiap DELETE harus membersihkan entri dari semua index; dan UPDATE pada kolom yang diindeks harus memindahkan entri index ke nilai yang baru. Tabel dengan lima index berarti lima pemeliharaan tambahan di belakang setiap tulis. Di sisi pembacaan pun index tidak selalu gratis: pencarian lewat index membaca halaman index dan halaman heap, sedangkan sequential scan membaca halaman tabel secara berurutan. Untuk hasil yang hanya menyentuh sebagian kecil baris, index menang telak; untuk hasil yang mencakup hampir seluruh tabel, memindai berurutan justru lebih murah. Efek penggandaan bacaan inilah yang di dunia database disebut read amplification.

Sebelum dan Sesudah Index #

Sekarang momen pembuktian. Perintah EXPLAIN menampilkan rencana eksekusi pilihan planner — tanpa menjalankan query-nya. Berikut query pencarian satu pelanggan berdasarkan nama, sebelum kolom nama memiliki index:

-- Tanpa index pada kolom nama, satu-satunya jalan: periksa seluruh baris
-- satu per satu (sequential scan).
EXPLAIN
SELECT id, kota, saldo
FROM pelanggan
WHERE nama = 'Pelanggan 0420';

Output yang Dihasilkan:

                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on pelanggan  (cost=0.00..25.50 rows=1 width=18)
   Filter: (nama = 'Pelanggan 0420'::text)
(2 rows)

Rencananya Seq Scan on pelanggan: PostgreSQL membaca seluruh 1000 baris dan menguji setiap baris dengan Filter: (nama = 'Pelanggan 0420'). Mari kita buat index-nya, lalu periksa strukturnya masuk daftar milik tabel:

-- Index pertama kita: satu perintah, menghasilkan struktur baru yang
-- terpisah dari tabel dan menyimpan (nama, ctid) dalam keadaan terurut.
CREATE INDEX idx_pelanggan_nama ON pelanggan (nama);

-- Sekarang hubungan tabel dan dua index miliknya terlihat dari ukurannya.
SELECT pg_size_pretty(pg_relation_size('pelanggan'))          AS tabel,
       pg_size_pretty(pg_relation_size('pelanggan_pkey'))     AS index_pkey,
       pg_size_pretty(pg_relation_size('idx_pelanggan_nama')) AS index_nama;

Output yang Dihasilkan:

CREATE INDEX
 tabel  | index_pkey | index_nama 
--------+------------+------------
 104 kB | 40 kB      | 48 kB
(1 row)

Index baru idx_pelanggan_nama berukuran 48 kB — biaya storage yang langsung terlihat. Query yang sama kemudian dijalankan ulang di bawah pengawasan EXPLAIN:

-- Dengan index pada nama, planner menuruni struktur terurut,
-- bukan lagi memindai seluruh tabel.
EXPLAIN
SELECT id, kota, saldo
FROM pelanggan
WHERE nama = 'Pelanggan 0420';

Output yang Dihasilkan:

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Index Scan using idx_pelanggan_nama on pelanggan  (cost=0.28..8.29 rows=1 width=18)
   Index Cond: (nama = 'Pelanggan 0420'::text)
(2 rows)

Bentuk rencananya berubah total. Baris pertama kini Index Scan using idx_pelanggan_nama: planner menuruni struktur index yang terurut untuk menemukan Pelanggan 0420, lalu mengambil baris heap lewat ctid yang tersimpan di entri index. Baris kedua menunjukkan Index Cond: (nama = 'Pelanggan 0420') — kondisi yang tadinya sekadar filter penguji setiap baris kini menjadi kondisi pencarian yang memandu perjalanan langsung ke baris tujuan. Perhatikan juga angka cost: rentang 0.00..25.50 pada sequential scan berubah menjadi 0.28..8.29 pada index scan.

Kapan Index Justru Tidak Perlu #

Dua sisi mata uang sudah terlihat: pembacaan jauh lebih cepat, penulisan dan storage membayar harga. Kesimpulan praktisnya bukan “index semua kolom”, melainkan “index kolom yang benar-benar menjadi jalur pencarian”. Panduan cepatnya:

Dengan konsep, analogi, dan bukti EXPLAIN di tangan, langkah berikutnya adalah menguasai perintah kerjanya sendiri: sintaks CREATE INDEX lengkap dengan variasinya, cara melihat dan menjatuhkan index, serta cara membangun index tanpa mengunci tabel produksi — semuanya di chapter Membuat dan Menghapus Index.

Referensi #

• Indexes Documentation
• Pengantar Index
• Memeriksa Penggunaan Index

-- ctid adalah alamat fisik sebuah baris di heap:
-- pasangan (nomor halaman, posisi baris dalam halaman).
SELECT ctid, id, nama
FROM pelanggan
WHERE id IN (1, 500, 1000)
ORDER BY id;

-- Index pertama kita: satu perintah, menghasilkan struktur baru yang
-- terpisah dari tabel dan menyimpan (nama, ctid) dalam keadaan terurut.
CREATE INDEX idx_pelanggan_nama ON pelanggan (nama);

-- Sekarang hubungan tabel dan dua index miliknya terlihat dari ukurannya.
SELECT pg_size_pretty(pg_relation_size('pelanggan'))          AS tabel,
       pg_size_pretty(pg_relation_size('pelanggan_pkey'))     AS index_pkey,
       pg_size_pretty(pg_relation_size('idx_pelanggan_nama')) AS index_nama;

-- Tabel contoh untuk seluruh pembahasan index di section ini:
-- 1000 pelanggan Toko Maju Jaya, dibuat deterministik lewat generate_series
-- sehingga statistik planner dan perilaku query selalu sama.
CREATE TABLE pelanggan (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    email    text NOT NULL,
    kota     text NOT NULL,
    kategori text NOT NULL,
    saldo    numeric(12,2) NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
SELECT
    g,
    'Pelanggan ' || lpad(g::text, 4, '0'),
    'pelanggan' || lpad(g::text, 4, '0') || '@example.com',
    (ARRAY['Jakarta','Bandung','Surabaya','Medan','Denpasar'])[1 + (g - 1) / 200],
    (ARRAY['reguler','silver','gold'])[1 + g % 3],
    ((g - 1) * 1000 + 500)::numeric(12,2),
    (g % 10 <> 0)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

-- Peta data: lima kota, masing-masing tepat 200 pelanggan.
SELECT kota, count(*) AS jumlah
FROM pelanggan
GROUP BY kota
ORDER BY kota;

-- Dengan index pada nama, planner menuruni struktur terurut,
-- bukan lagi memindai seluruh tabel.
EXPLAIN
SELECT id, kota, saldo
FROM pelanggan
WHERE nama = 'Pelanggan 0420';

-- Tanpa index pada kolom nama, satu-satunya jalan: periksa seluruh baris
-- satu per satu (sequential scan).
EXPLAIN
SELECT id, kota, saldo
FROM pelanggan
WHERE nama = 'Pelanggan 0420';

-- pg_indexes adalah view catalog yang memetakan seluruh index dalam database.
-- PRIMARY KEY pada tabel pelanggan sudah otomatis membuat satu index.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'pelanggan'
ORDER BY indexname;

-- Index bukan bagian dari tabel: ia struktur fisik terpisah dengan ukuran sendiri.
SELECT pg_size_pretty(pg_relation_size('pelanggan'))      AS tabel_pelanggan,
       pg_size_pretty(pg_relation_size('pelanggan_pkey')) AS index_pkey;