Query dunia nyata jarang menyaring satu kolom. Laporan penjualan paling sering berbunyi ganda: WHERE kota = 'Bandung' AND kategori = 'gold', WHERE status = 'aktif' AND tanggal BETWEEN .... Untuk pola seperti ini PostgreSQL menyediakan composite index — satu index yang menampung beberapa kolom sekaligus. Membuatnya gampang; yang menentukan index itu berguna atau sia-sia adalah satu keputusan kecil: urutan kolom. Chapter ini membedah aturan prefiks terkiri — konsep paling penting di seluruh topik index — beserta aturan penempatan kesetaraan sebelum rentang, semuanya pada tabel pelanggan 1000 baris yang dibangun ulang di sini:

sql buat-tabel.sql 
-- Tabel contoh chapter ini (domain yang sama dengan chapter Konsep Index).
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;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 1000
ANALYZE

Satu Index untuk Dua Kolom #

Composite index dibuat dengan menuliskan dua kolom atau lebih di dalam tanda kurung, dan urutannya tercatat resmi di catalog:

sql buat-index-composite.sql 
-- Index komposit: satu index B-tree atas dua kolom sekaligus,
-- urutan kolom menentukan urutan penyimpanan entri.
CREATE INDEX idx_pelanggan_kota_kategori ON pelanggan (kota, kategori);

-- pg_indexes mencatat daftar kolom beserta urutannya.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'pelanggan'
ORDER BY indexname;

Output yang Dihasilkan:

text 
CREATE INDEX
          indexname          |                                         indexdef                                          
-----------------------------+-------------------------------------------------------------------------------------------
 idx_pelanggan_kota_kategori | CREATE INDEX idx_pelanggan_kota_kategori ON public.pelanggan USING btree (kota, kategori)
 pelanggan_pkey              | CREATE UNIQUE INDEX pelanggan_pkey ON public.pelanggan USING btree (id)
(2 rows)

Definisi USING btree (kota, kategori) menyatakan bahwa entri-entri index adalah pasangan nilai (kota, kategori) yang diurutkan kota lebih dulu, kategori menyusul. Cara mengurutkan ini persis seperti buku telepon yang mengurutkan berdasarkan nama belakang lalu nama depan: semua entri “Sanjaya” menggerombol di satu tempat, dan di dalam gerombolan itu nama depannya terurut. Ilustrasi urutan entri pada index kita — dengan data 1000 baris, lima kota, tiga kategori:

Posisi entrikotakategori
1-66Bandunggold
67-133Bandungreguler
134-200Bandungsilver
201-267Jakartagold
………
935-1000Surabayasilver

Perhatikan polanya: kolom pertama membentuk gerombolan besar yang berurutan, dan kolom kedua hanya terurut di dalam gerombolan kolom pertama. Seluruh perilaku index komposit — kegunaan maupun batasnya — mengalir dari bentuk urutan ini.

Aturan Prefiks Terkiri #

Karena pengurutan mengikuti kolom pertama, index komposit hanya dapat dicari dari kirinya. Tiga kombinasi kondisi pada tabel pelanggan menghasilkan tiga nasib yang berbeda — hitung dulu berapa baris yang disaring masing-masing:

sql hitung-baris.sql 
-- Seberapa banyak baris disaring oleh tiap kombinasi kondisi?
SELECT count(*) AS kota_dan_kategori
FROM pelanggan
WHERE kota = 'Bandung' AND kategori = 'gold';

SELECT count(*) AS kota_saja
FROM pelanggan
WHERE kota = 'Bandung';

SELECT count(*) AS kategori_saja
FROM pelanggan
WHERE kategori = 'gold';

Output yang Dihasilkan:

text 
 kota_dan_kategori 
-------------------
                66
(1 row)

 kota_saja 
-----------
       200
(1 row)

 kategori_saja 
---------------
           333
(1 row)

Kombinasi lengkap menyaring 66 baris, kota sendirian menyaring 200 baris, dan kategori sendirian menyaring 333 baris. Sekarang kita ajukan ketiga query itu di bawah EXPLAIN:

sql explain-prefix.sql 
-- Prefiks terkiri terpenuhi penuh: kedua kolom masuk kondisi index.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE kota = 'Bandung' AND kategori = 'gold';

-- Prefiks terkiri terpenuhi sebagian: hanya kolom pertama yang terpakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE kota = 'Bandung';

-- Prefiks terkiri tidak terpenuhi: index komposit tidak bisa dipakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE kategori = 'gold';

Output yang Dihasilkan:

text 
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on pelanggan  (cost=4.84..18.84 rows=67 width=19)
   Recheck Cond: ((kota = 'Bandung'::text) AND (kategori = 'gold'::text))
   ->  Bitmap Index Scan on idx_pelanggan_kota_kategori  (cost=0.00..4.82 rows=67 width=0)
         Index Cond: ((kota = 'Bandung'::text) AND (kategori = 'gold'::text))
(4 rows)

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Bitmap Heap Scan on pelanggan  (cost=5.70..21.20 rows=200 width=19)
   Recheck Cond: (kota = 'Bandung'::text)
   ->  Bitmap Index Scan on idx_pelanggan_kota_kategori  (cost=0.00..5.65 rows=200 width=0)
         Index Cond: (kota = 'Bandung'::text)
(4 rows)

                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on pelanggan  (cost=0.00..25.50 rows=333 width=19)
   Filter: (kategori = 'gold'::text)
(2 rows)

Mari kita baca ketiga rencana itu satu per satu:

  1. Rencana pertama — Bitmap Index Scan on idx_pelanggan_kota_kategori dengan Index Cond yang memuat kedua kondisi: prefiks terkiri terpenuhi penuh, planner mendarat di gerombolan Bandung lalu melompat ke bagian gold di dalamnya. Dua kondisi menjadi dua penyaring di dalam index.
  2. Rencana kedua — index yang sama terpakai, tetapi Index Cond hanya memuat kota = 'Bandung': prefiks terkiri terpenuhi sebagian. Index tetap berharga karena gerombolan Bandung yang berurutan ditemukan sekali jalan, lalu 200 barisnya diambil; kondisi kategori tidak ada sehingga tidak ada penyaringan kedua.
  3. Rencana ketiga — Seq Scan on pelanggan: prefiks terkiri tidak terpenuhi sama sekali. Mencari kategori = 'gold' pada index yang terurut (kota, kategori) sama mustahilnya dengan mencari semua orang bernama depan “Ahmad” di buku telepon yang diurutkan berdasarkan nama belakang — entri gold terpencar di lima gerombolan kota, dan satu-satunya cara menemukannya adalah memeriksa seluruh index. Planner memilih langsung memindai tabelnya.

Urutan Kolom: Kesetaraan Dulu, Rentang Belakangan #

Aturan kedua composite index menjawab pertanyaan: bila satu kolom dicari dengan kesetaraan dan kolom lain dengan rentang, mana yang ditaruh depan? Jawabnya: kolom kesetaraan di depan, kolom rentang di belakang. Alasannya kembali ke bentuk pengurutan: kesetaraan pada kolom pertama mengunci satu gerombolan, dan rentang pada kolom kedua menjadi jalan berjalan di dalam gerombolan itu. Kalau urutannya dibalik, rentang pada kolom pertama memaksa planner membuka banyak gerombolan, dan kondisi kesetaraan kolom kedua hanya menjadi penyaring tambahan.

Index (kota, saldo) dibuat mengikuti aturan itu:

sql buat-index-urutan.sql 
-- Index komposit kedua, mengikuti aturan "kesetaraan di depan,
-- rentang di belakang": kota dicari dengan =, saldo dengan rentang.
CREATE INDEX idx_pelanggan_kota_saldo ON pelanggan (kota, saldo);

Output yang Dihasilkan:

text 
CREATE INDEX

Query berikut mencari pelanggan Bandung dengan saldo di jendela tertentu:

sql explain-urutan.sql 
-- Kesetaraan pada kolom pertama, rentang pada kolom kedua:
-- keduanya masuk Index Cond karena urutan kolom mengikuti aturan
-- "kesetaraan di depan, rentang di belakang".
EXPLAIN
SELECT id, nama, saldo
FROM pelanggan
WHERE kota = 'Bandung' AND saldo BETWEEN 250000 AND 260000;

Output yang Dihasilkan:

text 
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on pelanggan  (cost=4.30..9.98 rows=2 width=25)
   Recheck Cond: ((kota = 'Bandung'::text) AND (saldo >= '250000'::numeric) AND (saldo <= '260000'::numeric))
   ->  Bitmap Index Scan on idx_pelanggan_kota_saldo  (cost=0.00..4.30 rows=2 width=0)
         Index Cond: ((kota = 'Bandung'::text) AND (saldo >= '250000'::numeric) AND (saldo <= '260000'::numeric))
(4 rows)

Index Cond pada rencana itu memuat ketiga kondisi sekaligus: kota = 'Bandung', saldo >= 250000, dan saldo <= 260000 — bukti bahwa kedua kolom benar-benar bekerja sebagai kondisi pencarian, bukan sekadar filter. Sebagai catatan kecil, taksiran rows=2 di rencana itu lebih kecil daripada jumlah baris sebenarnya (10 baris): planner menebak sebaran dua kolom seolah-olah saling bebas, padahal di data kita saldo naik teratur mengikuti id sehingga jendela saldo tertentu hanya menyentuh satu kota. Selisih taksiran seperti ini dibahas lebih jauh di section Query Optimization.

Panduan memilih urutan dapat dirangkum dalam tabel:

Pola query paling sibukUrutan kolom yang tepat
WHERE a = ? AND b = ?(a, b) atau (b, a) — keduanya setara
WHERE a = ? AND b BETWEEN ?(a, b) — kesetaraan dulu
WHERE a = ? sendirian, b jarang dicari sendiri(a, ...) — kolom pencarian mandiri di depan
WHERE b = ? sendirian paling sibukbutuh index terpisah pada b

Biaya Kolom Kedua #

Setiap kolom tambahan pada index menambah lebar entri — tetapi tidak selalu menambah ukuran index. Bandingkan empat index pada tabel kita:

sql ukuran-index.sql 
-- Dua index pembanding tambahan.
CREATE INDEX idx_pelanggan_kota ON pelanggan (kota);

-- Pembanding: index satu kolom yang sama dengan deduplication dimatikan.
CREATE INDEX idx_pelanggan_kota_nodedup ON pelanggan (kota) WITH (deduplicate_items = off);

SELECT pg_size_pretty(pg_relation_size('idx_pelanggan_kota'))          AS satu_kolom,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_kategori')) AS kota_kategori,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_saldo'))    AS kota_saldo,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_nodedup'))  AS tanpa_dedup;

Output yang Dihasilkan:

text 
CREATE INDEX
CREATE INDEX
 satu_kolom | kota_kategori | kota_saldo | tanpa_dedup 
------------+---------------+------------+-------------
 16 kB      | 16 kB         | 48 kB      | 48 kB
(1 row)

Dua temuan menarik dari kolom ukuran itu. Pertama, index (kota, kategori) berukuran sama dengan index (kota) sendirian: 16 kB. Sejak PostgreSQL 13, B-tree melakukan deduplication — nilai-nilai kota yang sama berulang kali dikompresi menjadi satu entri dengan daftar ctid, sehingga duplikasi berat pada kolom pertama nyaris tidak memakan ruang ekstra. Pembanding tanpa_dedup membuktikannya: index satu kolom yang sama dengan deduplication dimatikan membengkak dari 16 kB ke 48 kB. Kedua, menambahkan kolom saldo yang bernilai unik di setiap baris memang menaikkan ukuran menjadi 48 kB — pasangan (kota, saldo) hampir selalu berbeda sehingga tidak ada duplikasi untuk dikompresi. Jadi “kolom kedua gratis saat nilainya banyak duplikat, mahal saat nilainya unik”.

Index komposit memberi kita index yang melayani banyak pola sekaligus. Masih ada satu wajah index yang belum dibahas — wajah yang mengubahnya dari alat percepatan menjadi penjaga integritas data. CREATE UNIQUE INDEX dan seluruh keluarganya menanti di chapter Unique Index.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-index-composite.sql

Unduh berkas
sql 
-- Index komposit: satu index B-tree atas dua kolom sekaligus,
-- urutan kolom menentukan urutan penyimpanan entri.
CREATE INDEX idx_pelanggan_kota_kategori ON pelanggan (kota, kategori);

-- pg_indexes mencatat daftar kolom beserta urutannya.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'pelanggan'
ORDER BY indexname;

buat-index-urutan.sql

Unduh berkas
sql 
-- Index komposit kedua, mengikuti aturan "kesetaraan di depan,
-- rentang di belakang": kota dicari dengan =, saldo dengan rentang.
CREATE INDEX idx_pelanggan_kota_saldo ON pelanggan (kota, saldo);

buat-tabel.sql

Unduh berkas
sql 
-- Tabel contoh chapter ini (domain yang sama dengan chapter Konsep Index).
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;

explain-prefix.sql

Unduh berkas
sql 
-- Prefiks terkiri terpenuhi penuh: kedua kolom masuk kondisi index.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE kota = 'Bandung' AND kategori = 'gold';

-- Prefiks terkiri terpenuhi sebagian: hanya kolom pertama yang terpakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE kota = 'Bandung';

-- Prefiks terkiri tidak terpenuhi: index komposit tidak bisa dipakai.
EXPLAIN
SELECT id, nama
FROM pelanggan
WHERE kategori = 'gold';

explain-urutan.sql

Unduh berkas
sql 
-- Kesetaraan pada kolom pertama, rentang pada kolom kedua:
-- keduanya masuk Index Cond karena urutan kolom mengikuti aturan
-- "kesetaraan di depan, rentang di belakang".
EXPLAIN
SELECT id, nama, saldo
FROM pelanggan
WHERE kota = 'Bandung' AND saldo BETWEEN 250000 AND 260000;

hitung-baris.sql

Unduh berkas
sql 
-- Seberapa banyak baris disaring oleh tiap kombinasi kondisi?
SELECT count(*) AS kota_dan_kategori
FROM pelanggan
WHERE kota = 'Bandung' AND kategori = 'gold';

SELECT count(*) AS kota_saja
FROM pelanggan
WHERE kota = 'Bandung';

SELECT count(*) AS kategori_saja
FROM pelanggan
WHERE kategori = 'gold';

ukuran-index.sql

Unduh berkas
sql 
-- Dua index pembanding tambahan.
CREATE INDEX idx_pelanggan_kota ON pelanggan (kota);

-- Pembanding: index satu kolom yang sama dengan deduplication dimatikan.
CREATE INDEX idx_pelanggan_kota_nodedup ON pelanggan (kota) WITH (deduplicate_items = off);

SELECT pg_size_pretty(pg_relation_size('idx_pelanggan_kota'))          AS satu_kolom,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_kategori')) AS kota_kategori,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_saldo'))    AS kota_saldo,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_nodedup'))  AS tanpa_dedup;