Strategi CREATE INDEX

Merancang index composite, partial, dan covering sesuai pola query.

Daftar isi

Topik ini membahas tiga bentuk index yang melayani pola query yang lebih spesifik, yaitu composite untuk beberapa kolom, partial untuk sebagian baris, dan covering dengan INCLUDE untuk query yang dilayani tanpa menyentuh tabel. Keluaran EXPLAIN pada topik ini direkam sebagai jenis simpul rencananya, karena angka biaya lengkapnya mengikuti statistik dan konfigurasi mesin, sedangkan jenis simpulnya tetap di lingkungan mana pun.

Blok penyiapan dataset#

bash

# dataset besar deterministik untuk pengukuran index
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS sirkulasi CASCADE;
CREATE TABLE sirkulasi (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    anggota_id INTEGER NOT NULL,
    buku_id  INTEGER NOT NULL,
    status   TEXT NOT NULL,
    tahun    INTEGER NOT NULL
);
INSERT INTO sirkulasi (anggota_id, buku_id, status, tahun)
SELECT g % 500 + 1, g % 400 + 1,
       CASE WHEN g % 3 = 0 THEN 'kembali' ELSE 'dipinjam' END,
       2018 + g % 8
FROM generate_series(1, 50000) g;
ANALYZE sirkulasi;
SQL

Lima puluh ribu baris cukup bagi perencana untuk memilih index secara berarti, dan datanya ditentukan rumus sehingga setiap eksekusi menghasilkan isi yang persis sama.

Composite dan urutan kolomnya#

Index composite mengindeks beberapa kolom sekaligus dengan urutan yang menentukan kegunaannya, karena btree hanya melayani pencarian yang menyertakan awalannya. Blok berikut membuat index composite, lalu membaca jenis simpul rencana untuk dua pola query.

bash

# dua pola query di bawah index composite yang sama
psql -U postgres -d perpustakaan -c "CREATE INDEX idx_sirkulasi_anggota_status ON sirkulasi (anggota_id, status);"
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE anggota_id = 42 AND status = 'dipinjam';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE status = 'dipinjam';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_sirkulasi_anggota_status;"

text Keluaran

CREATE INDEX
      1 Bitmap Heap Scan
      1 Bitmap Index Scan
      1 Seq Scan
DROP INDEX

Query pertama dilayani index melalui sepasang simpul bitmap, yaitu Bitmap Index Scan yang menyusuri index dan menandai alamat baris, lalu Bitmap Heap Scan yang membaca tabel sesuai tandanya. Query kedua memilih pemindaian penuh karena kolom yang disaring adalah kolom kedua tanpa awalannya, dan kedua keputusan itu terbaca dari jenis simpulnya. Pada keluaran EXPLAIN penuh, setiap simpul menyertakan estimasi baris, lebar baris, dan biaya, yang dibaca tuntas pada topik EXPLAIN dan EXPLAIN ANALYZE.

Aturan menyusun composite sederhana, yaitu kolom penyaring yang selalu ada diletakkan di awal, sedangkan pembeda yang bervariasi menyusul, dan urutan yang terbalik membuat query yang hanya menyaring kolom belakang kehilangan layanan index.

Partial untuk sebagian baris#

Index partial mengindeks baris yang lolos syaratnya saja, sehingga ukurannya kecil dan tulisannya ringan. Pola yang paling sering adalah status yang tidak seimbang, misalnya baris aktif yang minoritas dibanding arsip yang mayoritas.

bash

# index partial pada baris yang masih dipinjam
psql -U postgres -d perpustakaan -c "CREATE INDEX idx_sirkulasi_aktif ON sirkulasi (anggota_id) WHERE status = 'dipinjam';"
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE status = 'dipinjam' AND anggota_id = 42;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_sirkulasi_aktif;"

text Keluaran

CREATE INDEX
      1 Bitmap Heap Scan
      1 Bitmap Index Scan
DROP INDEX

Syarat pada index harus cocok dengan syarat query agar dikenali, dan jenis simpul pada keluaran menunjukkan rencana yang menyusuri index partial itu. Dua pertiga baris yang berstatus kembali tidak terindeks sama sekali, penghematan yang tumbuh seiring ketidakseimbangan datanya.

Covering dengan INCLUDE#

Index covering membawa kolom tambahan pada daunnya melalui INCLUDE, sehingga query yang hanya membaca kolom tersebut dilayani index-only scan tanpa menyentuh tabel. Kolom INCLUDE tidak menjadi kunci pencarian, sehingga urutan pencarian tetap milik kolom utamanya.

bash

# index covering yang dilayani tanpa menyentuh tabel
psql -U postgres -d perpustakaan <<'SQL'
CREATE INDEX idx_sirkulasi_cover ON sirkulasi (tahun) INCLUDE (anggota_id);
VACUUM sirkulasi;
SQL
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE tahun = 2021;" | grep -oE "Seq Scan|Index Scan|Index Only Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP INDEX idx_sirkulasi_cover;"

text Keluaran

CREATE INDEX
VACUUM
      1 Index Only Scan
DROP INDEX

VACUUM sebelum pengukuran menandai halaman sebagai dapat dilihat pada peta visibilitas, syarat index-only scan, dan jenis simpul pada keluaran menegaskan query dilayani index semata. Index-only scan juga rentan pada tabel yang ramai ditulis, karena peta visibilitasnya cepat basi, dan VACUUM rutin menjaganya.

Index pada ekspresi#

Index tidak terbatas pada kolom, karena ekspresi pun dapat diindeks, dan query yang menyaring dengan ekspresi yang sama menjadi dilayani. Pola yang paling terkenal adalah pencarian tanpa peduli kapital pada kolom teks.

bash

# index ekspresi untuk pencarian lower()
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE judul_uji (id SERIAL PRIMARY KEY, judul TEXT NOT NULL);
INSERT INTO judul_uji (judul)
SELECT 'Koleksi nomor ' || g FROM generate_series(1, 20000) g;
ANALYZE judul_uji;
SQL
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM judul_uji WHERE lower(judul) = 'koleksi nomor 19999';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "CREATE INDEX idx_judul_lower ON judul_uji (lower(judul));"
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM judul_uji WHERE lower(judul) = 'koleksi nomor 19999';" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan" | sort | uniq -c
psql -U postgres -d perpustakaan -c "DROP TABLE judul_uji;"

text Keluaran

CREATE TABLE
INSERT 0 20000
ANALYZE
      1 Seq Scan
CREATE INDEX
      1 Bitmap Heap Scan
      1 Index Scan
DROP TABLE

Sebelum index ekspresi ada, rencananya pemindaian penuh, dan sesudahnya rencana menyusuri index lower(judul), sementara index pada judul polos tidak akan melayani bentuk penyaringan ini. Konsistensi antara bentuk query dan bentuk index menjadi kuncinya, pemisahan yang dibedah lagi sebagai anti-pola pada topik penutup modul ini.

Kesalahan umum#

Kesalahan pertama adalah menyusun composite dengan urutan yang terbalik, sehingga query yang menyaring hanya kolom belakang tidak dilayani, dan pemeriksaan jenis simpul rencana mengungkapnya seketika. Kesalahan kedua adalah index partial yang syaratnya berbeda dari query, misalnya beda kapitalisasi atau beda bentuk literal, sehingga tidak pernah dikenali.

Kesalahan ketiga adalah membungkus semua kolom dengan INCLUDE, karena setiap kolom tambahan tetap memperbesar index dan memperlambat tulis, sedangkan INCLUDE hanya untuk kolom yang dibaca tanpa disaring. Kesalahan keempat adalah membuat tiga index tunggal untuk tiga kolom yang selalu disaring bersama, padahal satu composite melayani pola itu lebih baik daripada gabungan tiga index terpisah.

Ringkasan#

Composite melayani query yang menyertakan kolom awalannya, sehingga urutan kolomnya adalah keputusan desain.
Partial mengindeks sebagian baris sesuai syarat, kecil dan ringan, dengan syaratnya yang harus cocok pada query.
INCLUDE membawa kolom pembaca pada daun index dan membuka index-only scan yang ditopang VACUUM rutin.
Ekspresi dapat diindeks, dan query yang menyaring dengan ekspresi yang sama menjadi dilayani index tersebut.

Referensi#

Multicolumn Indexes — PostgreSQL Documentation — Aturan urutan kolom pada index composite.
Partial Indexes — PostgreSQL Documentation — Referensi index partial beserta contohnya.
Index-Only Scans and Covering Indexes — PostgreSQL Documentation — Mekanisme index-only scan dan INCLUDE.