Covering Index

Index memang cepat menemukan baris, tetapi menemukan bukan akhir cerita: setelah posisi baris diketahui, query lazimnya masih membaca kolom-kolom lain dari tabel — nama pelanggan, total belanja, alamat — dan setiap pembacaan itu adalah kunjungan terpisah ke heap, struktur penyimpanan tabel itu sendiri. Untuk query yang menjalani ribuan baris hasil, biaya bolak-balik index-ke-heap bisa melebihi biaya pencariannya. PostgreSQL menawarkan jalan keluar sejak versi 11 lewat klausa INCLUDE: index tetap diurutkan berdasarkan kunci yang ramping, sementara kolom-kolom muatan disimpan sebagai bonus di setiap entri. Query yang semua kolomnya telah tersedia di index bisa dilayani tanpa menyentuh heap sama sekali — rencana eksekusinya punya nama khusus: index-only scan. Chapter ini membangun index seperti itu, membuktikan hilangnya akses heap lewat Heap Fetches di EXPLAIN, lalu membongkar satu ketergantungan halus di baliknya: peta visibilitas yang dirawat oleh VACUUM.

Membawa Muatan dengan INCLUDE #

Tabel pelanggan berikut memakai domain yang sama dengan chapter-chapter sebelumnya: 1000 pelanggan tersebar di lima kota, masing-masing dengan total belanja. Query tipikal pada data seperti ini — “tampilkan nama dan total belanja pelanggan Medan” — hanya menyentuh tiga kolom:

sql
buat-tabel.sql
-- 1000 pelanggan tersebar di 5 kota, lengkap dengan total belanja.
CREATE TABLE pelanggan (
    id           integer PRIMARY KEY,
    nama         text NOT NULL,
    email        text NOT NULL,
    kota         text NOT NULL,
    total_belanja numeric(12,2) NOT NULL
);

INSERT INTO pelanggan
SELECT g,
       (ARRAY['Budi', 'Siti', 'Andi', 'Dewi', 'Rina'])[1 + g % 5]
           || ' '
           || (ARRAY['Santoso', 'Wijaya', 'Kusuma', 'Hartono', 'Pratama'])[1 + (g / 5) % 5],
       'pelanggan' || g || '@example.com',
       (ARRAY['Jakarta', 'Bandung', 'Surabaya', 'Medan', 'Denpasar'])[1 + g % 5],
       ((g * 12345) % 10000000 + 500)::numeric(12, 2)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

SELECT id, nama, kota, total_belanja
FROM pelanggan
WHERE id <= 5
ORDER BY id;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 1000
ANALYZE
 id |     nama     |   kota   | total_belanja 
----+--------------+----------+---------------
  1 | Siti Santoso | Bandung  |      12845.00
  2 | Andi Santoso | Surabaya |      25190.00
  3 | Dewi Santoso | Medan    |      37535.00
  4 | Rina Santoso | Denpasar |      49880.00
  5 | Budi Wijaya  | Jakarta  |      62225.00
(5 rows)

Index covering untuk query kota dibangun dengan memisahkan dua peran kolom: kota sebagai kunci yang menentukan urutan dan pencarian, nama serta total_belanja sebagai muatan yang ikut tersimpan tetapi tidak ikut diurutkan:

sql
index-include.sql
-- kota = kunci; nama dan total_belanja = muatan (INCLUDE).
CREATE INDEX idx_pelanggan_kota
    ON pelanggan (kota) INCLUDE (nama, total_belanja);

-- VACUUM menandai halaman tabel all-visible (dibahas di bawah).
VACUUM pelanggan;

-- Query ini hanya butuh kolom yang ada di dalam index.
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Medan'
ORDER BY id
LIMIT 5;

Output yang Dihasilkan:

text
CREATE INDEX
VACUUM
 kota  |     nama     | total_belanja 
-------+--------------+---------------
 Medan | Dewi Santoso |      37535.00
 Medan | Dewi Wijaya  |      99260.00
 Medan | Dewi Kusuma  |     160985.00
 Medan | Dewi Hartono |     222710.00
 Medan | Dewi Pratama |     284435.00
(5 rows)

Klausa INCLUDE (nama, total_belanja) membuat setiap entri index membawa salinan kedua kolom itu — hasil query Medan di atas keluar dengan data lengkap. Perintah VACUUM yang menyusul bukan hiasan: tanda hubung antara index-only scan dan peta visibilitas dibahas dua bagian di bawah. Aturan mainnya tegas: agar index-only scan mungkin, seluruh kolom di daftar SELECT harus tersedia di index — sebagai kunci, sebagai muatan, atau keduanya. Tidak ada kolom yang gratis; meskipun setiap entri index menyimpan penunjuk fisik ke baris tabel, penunjuk itu bukan nilai kolom id, sehingga SELECT id pun meminta id masuk INCLUDE bila ingin dilayani tanpa heap.

Index Only Scan: Berhenti di Index #

Bukti bahwa query benar-benar berhenti di index terlihat dari EXPLAIN. Dua rencana berikut membandingkan query yang seluruh kolomnya tersedia di index dengan query yang meminta email — satu-satunya kolom penting yang sengaja tidak dibawa:

sql
rencana-index-only.sql
-- Semua kolom ada di index: cukup Index Only Scan.
EXPLAIN (ANALYZE)
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Medan';

-- Kolom email tidak dibawa index: heap harus disentuh kembali.
EXPLAIN
SELECT id, email
FROM pelanggan
WHERE kota = 'Medan';

Output yang Dihasilkan:

text
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_pelanggan_kota on pelanggan  (cost=0.28..11.78 rows=200 width=27) (actual time=0.033..0.055 rows=200 loops=1)
   Index Cond: (kota = 'Medan'::text)
   Heap Fetches: 0
 Planning Time: 0.303 ms
 Execution Time: 0.093 ms
(5 rows)

                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on pelanggan  (cost=0.00..23.50 rows=200 width=28)
   Filter: (kota = 'Medan'::text)
(2 rows)

Baris Index Only Scan using idx_pelanggan_kota adalah nama rencana yang dicari: 200 baris pelanggan Medan dilayani sepenuhnya dari index, dan baris Heap Fetches: 0 di bawahnya adalah bukti matematisnya — nol kunjungan ke heap. Angka actual time dan Execution Time pada output ini bervariasi antar mesin dan antar eksekusi; Heap Fetches: 0 lah yang stabil dan menjadi kesimpulan. Rencana kedua kehilangan hak istimewa itu begitu email masuk daftar SELECT: opsi index-only lenyap karena email tidak tersimpan di index, dan pada tabel sekecil ini planner memilih Seq Scan — membaca seluruh tabel sekali jalan dianggap lebih murah daripada menemukan 200 baris lewat index lalu mengunjungi heap satu per satu untuk email.

Peta Visibilitas: Syarat Tersembunyi Index-Only #

Mengapa PostgreSQL tidak selalu mengambil data langsung dari index? Jawabnya berurat pada MVCC: entri index tidak menyimpan informasi versi baris, sehingga server harus memastikan dulu bahwa baris di heap terlihat oleh semua transaksi — kecuali halaman heap tersebut sudah ditandai all-visible di peta visibilitas (visibility map). Tanda itu hanya dipasang oleh VACUUM. Eksperimen berikut memperlihatkan seluruh siklusnya: perubahan data menurunkan tanda, VACUUM memasangnya kembali:

sql
peta-visibilitas.sql
-- 21 pelanggan Bandung berubah total belanjanya.
UPDATE pelanggan
SET total_belanja = total_belanja + 1
WHERE kota = 'Bandung' AND id <= 101;

-- Halaman yang memuat baris baru itu kehilangan tanda all-visible.
EXPLAIN (ANALYZE)
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Bandung';

-- VACUUM memulihkan tanda all-visible pada halaman.
VACUUM pelanggan;

-- Kembali ke Heap Fetches: 0.
EXPLAIN (ANALYZE)
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Bandung';

Output yang Dihasilkan:

text
UPDATE 21
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_pelanggan_kota on pelanggan  (cost=0.28..19.93 rows=218 width=27) (actual time=0.022..0.040 rows=200 loops=1)
   Index Cond: (kota = 'Bandung'::text)
   Heap Fetches: 76
 Planning Time: 0.030 ms
 Execution Time: 0.052 ms
(5 rows)

VACUUM
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_pelanggan_kota on pelanggan  (cost=0.28..11.78 rows=200 width=27) (actual time=0.010..0.023 rows=200 loops=1)
   Index Cond: (kota = 'Bandung'::text)
   Heap Fetches: 0
 Planning Time: 0.058 ms
 Execution Time: 0.034 ms
(5 rows)

Tiga tahapan dalam output itu layak dibaca pelan-pelan. Setelah UPDATE 21, rencana tetap memilih index-only scan — tetapi Heap Fetches melonjak ke 76: halaman-halaman yang menampung baris hasil UPDATE (dan baris lama yang mati di dalamnya) kehilangan tanda all-visible, sehingga server memeriksa visibilitas 76 entri ke heap. Angka 76 lebih besar dari 21 baris yang diubah karena tanda visibilitas melekat pada halaman, bukan baris — satu halaman bisa memuat puluhan baris pelanggan. Setelah VACUUM membersihkan baris mati, tanda all-visible terpasang kembali dan rencana terakhir kembali ke Heap Fetches: 0. Nilai actual time, jumlah Heap Fetches non-nol, dan rows hasil estimasi berbeda-beda mengikuti tata letak halaman di mesin Anda; polanya — naik setelah tulis, turun setelah VACUUM — yang konsisten.

INCLUDE Lawan Composite Penuh #

Dua cara membuat index “lengkap” kini tersedia: muatan lewat INCLUDE, atau semua kolom sebagai kunci composite. Tiga index berikut dibangun di atas tabel yang sama agar harganya bisa dibandingkan langsung:

sql
ukuran-tradeoff.sql
-- Tiga varian index untuk membandingkan harga ruang.
CREATE INDEX idx_pelanggan_kota_saja
    ON pelanggan (kota);

CREATE INDEX idx_pelanggan_kota_lengkap
    ON pelanggan (kota, nama, total_belanja);

SELECT pg_size_pretty(pg_relation_size('idx_pelanggan_kota_saja'))  AS kunci_saja,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota'))       AS dengan_include,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_lengkap')) AS komposit_penuh;

-- Dua index pembanding dijatuhkan agar demo berikutnya bersih.
DROP INDEX idx_pelanggan_kota_saja;
DROP INDEX idx_pelanggan_kota_lengkap;

Output yang Dihasilkan:

text
CREATE INDEX
CREATE INDEX
 kunci_saja | dengan_include | komposit_penuh 
------------+----------------+----------------
 16 kB      | 72 kB          | 72 kB
(1 row)

DROP INDEX
DROP INDEX

Angkanya jujur dan sedikit kontra-intuitif: versi INCLUDE tidak lebih kecil dari composite penuh — keduanya 72 kB, karena kolom muatan maupun kunci sama-sama menempati ruang di entri index. Yang membedakan keduanya bukan ukuran, melainkan kemampuan:

Aspek	`(kota) INCLUDE (nama, total_belanja)`	`(kota, nama, total_belanja)`
Menyaring `kota = ...`	Bisa	Bisa
Menyaring rentang `nama`	Tidak — muatan tidak diurutkan	Bisa — `nama` adalah kunci kedua
`ORDER BY nama` dalam satu kota	Harus sort terpisah	Langsung dari urutan index
Ukuran entri	Sebanding composite	Sebanding INCLUDE
Peran dalam index unik	Tidak memengaruhi keunikan	Ikut menentukan keunikan

Dua baris terakhir tabel itu adalah alasan sesungguhnya INCLUDE ada. Karena muatan tidak diurutkan, index (kode_voucher) INCLUDE (nama, harga) tetap menjamin keunikan atas kode_voucher semata — kolom muatan bebas dimasukkan tanpa mengubah makna keunikan, kebalikan dari composite yang menjadikan setiap kolom bagian dari kunci unik. Praktik umumnya: jadikan kunci hanya kolom yang benar-benar disaring atau diurutkan, lalu angkat kolom hasil query ke INCLUDE.

Dengan tiga varian index khusus berturut-turut — partial, ekspresi, covering — pola besarnya sudah utuh: satu keluarga B-tree dalam berbagai kostum. Bab berikutnya keluar dari keluarga itu sama sekali: GIN, index terbalik yang mengindeks isi kolom multi-nilai seperti array dan jsonb, tempat satu baris bisa diwakili banyak kunci sekaligus.

Referensi #

Berkas Sumber

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

sql
-- 1000 pelanggan tersebar di 5 kota, lengkap dengan total belanja.
CREATE TABLE pelanggan (
    id           integer PRIMARY KEY,
    nama         text NOT NULL,
    email        text NOT NULL,
    kota         text NOT NULL,
    total_belanja numeric(12,2) NOT NULL
);

INSERT INTO pelanggan
SELECT g,
       (ARRAY['Budi', 'Siti', 'Andi', 'Dewi', 'Rina'])[1 + g % 5]
           || ' '
           || (ARRAY['Santoso', 'Wijaya', 'Kusuma', 'Hartono', 'Pratama'])[1 + (g / 5) % 5],
       'pelanggan' || g || '@example.com',
       (ARRAY['Jakarta', 'Bandung', 'Surabaya', 'Medan', 'Denpasar'])[1 + g % 5],
       ((g * 12345) % 10000000 + 500)::numeric(12, 2)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

SELECT id, nama, kota, total_belanja
FROM pelanggan
WHERE id <= 5
ORDER BY id;

sql
-- kota = kunci; nama dan total_belanja = muatan (INCLUDE).
CREATE INDEX idx_pelanggan_kota
    ON pelanggan (kota) INCLUDE (nama, total_belanja);

-- VACUUM menandai halaman tabel all-visible (dibahas di bawah).
VACUUM pelanggan;

-- Query ini hanya butuh kolom yang ada di dalam index.
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Medan'
ORDER BY id
LIMIT 5;

sql
-- 21 pelanggan Bandung berubah total belanjanya.
UPDATE pelanggan
SET total_belanja = total_belanja + 1
WHERE kota = 'Bandung' AND id <= 101;

-- Halaman yang memuat baris baru itu kehilangan tanda all-visible.
EXPLAIN (ANALYZE)
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Bandung';

-- VACUUM memulihkan tanda all-visible pada halaman.
VACUUM pelanggan;

-- Kembali ke Heap Fetches: 0.
EXPLAIN (ANALYZE)
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Bandung';

sql
-- Semua kolom ada di index: cukup Index Only Scan.
EXPLAIN (ANALYZE)
SELECT kota, nama, total_belanja
FROM pelanggan
WHERE kota = 'Medan';

-- Kolom email tidak dibawa index: heap harus disentuh kembali.
EXPLAIN
SELECT id, email
FROM pelanggan
WHERE kota = 'Medan';

sql
-- Tiga varian index untuk membandingkan harga ruang.
CREATE INDEX idx_pelanggan_kota_saja
    ON pelanggan (kota);

CREATE INDEX idx_pelanggan_kota_lengkap
    ON pelanggan (kota, nama, total_belanja);

SELECT pg_size_pretty(pg_relation_size('idx_pelanggan_kota_saja'))  AS kunci_saja,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota'))       AS dengan_include,
       pg_size_pretty(pg_relation_size('idx_pelanggan_kota_lengkap')) AS komposit_penuh;

-- Dua index pembanding dijatuhkan agar demo berikutnya bersih.
DROP INDEX idx_pelanggan_kota_saja;
DROP INDEX idx_pelanggan_kota_lengkap;