Topik ini membahas keputusan perencana antara pemindaian penuh dan pemindaian index, keputusan yang sering disalahpahami sebagai kesalahan server. Index yang diabaikan hampir selalu memiliki alasan yang terukur, dan membaca alasannya menutup jarak antara mengira lambat dan memahami biaya. Sebagaimana topik sebelumnya, jenis simpul rencana yang direkam karena angka biayanya mengikuti statistik mesin.
Blok penyiapan dataset#
# dua tabel: kecil untuk pola pemindaian penuh, besar untuk index
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS buku_uji, sirkulasi CASCADE;
CREATE TABLE buku_uji (id SERIAL PRIMARY KEY, judul TEXT NOT NULL);
INSERT INTO buku_uji (judul) SELECT 'Buku nomor ' || g FROM generate_series(1, 8) g;
CREATE TABLE sirkulasi (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, anggota_id INTEGER NOT NULL);
INSERT INTO sirkulasi (anggota_id) SELECT g % 500 + 1 FROM generate_series(1, 50000) g;
CREATE INDEX idx_sirkulasi_anggota ON sirkulasi (anggota_id);
ANALYZE buku_uji;
ANALYZE sirkulasi;
SQLPemindaian penuh menang pada tabel kecil#
Pemindaian penuh (sequential scan) membaca seluruh halaman tabel sekali jalan, dan pada tabel kecil biayanya lebih murah daripada lompatan ke index lalu ke barisnya. Query pada tabel delapan baris tetap memilih pemindaian penuh meskipun kolom id-nya berindex.
# tabel kecil: pemindaian penuh meski ada index
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT * FROM buku_uji WHERE id = 5;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c 1 Seq ScanRencananya satu simpul Seq Scan, dan keputusan itu benar, karena menyentuh index dulu berarti dua langkah tambahan untuk delapan baris. Temuan yang sering membuat heran, yaitu index tidak dipakai pada lingkungan uji yang kecil, biasanya hanya peniruan pola ini.
Bitmap menang pada penyaringan sempit di tabel besar#
Pada tabel lima puluh ribu baris, penyaringan satu anggota tidak lagi dilayani pemindaian penuh, dan perencana menyusuri index lalu membaca tabel sesuai hasilnya. Bentuk yang dipilihnya adalah sepasang simpul bitmap, bukan Index Scan tunggal, karena baris hasil tersebar di banyak halaman dan bitmap menghindari pembacaan halaman yang bolak-balik.
# tabel besar: bitmap atas index untuk satu anggota
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE anggota_id = 42;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c 1 Bitmap Heap Scan
1 Bitmap Index ScanBitmap Index Scan menyusuri index dan menandai alamat baris, sedangkan Bitmap Heap Scan membaca tabel sekali jalan sesuai tandanya. Ketika hasilnya menjangkau sebagian besar baris, index kehilangan gunanya karena tetap menyentuh hampir semua halaman, dan perencana kembali ke pemindaian penuh.
Rentang lebar kembali ke pemindaian penuh#
Penyaringan rentang yang mencakup mayoritas tabel membuat seluruh struktur index tidak berguna, dan perencana memilih pemindaian penuh alih-alih menandai hampir setiap halaman lewat bitmap.
# rentang yang menjangkau mayoritas tabel
psql -U postgres -d perpustakaan -c "EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE anggota_id BETWEEN 100 AND 400;" | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c 1 Seq ScanTiga ratus anggota mencakup tiga perlima tabel, dan simpulnya kembali menjadi Seq Scan. Ketiga bentuk keputusan ini saling melengkapi, yaitu pemindaian penuh untuk hasil lebar, bitmap untuk hasil menengah yang menyebar, dan Index Scan tunggal untuk hasil sempit pada tabel yang padat, dan membaca pilihannya adalah keterampilan yang dibangun bersama topik EXPLAIN.
Memaksa jalur index untuk pengujian#
Saat menguji bentuk rencana, pemaksaan dilakukan melalui SET enable_seqscan = off pada sesi uji, dan keputusannya dikembalikan setelahnya. Perintah ini alat laboratorium, bukan resep produksi, karena mematikan satu jalur hanya menyembunyikan alasan perencana memilihnya.
# uji jalur index dengan pemindaian penuh dimatikan pada sesi yang sama
psql -U postgres -d perpustakaan <<'SQL' | grep -oE "Seq Scan|Index Scan|Bitmap Heap Scan|Bitmap Index Scan" | sort | uniq -c
SET enable_seqscan = off;
EXPLAIN SELECT COUNT(*) FROM sirkulasi WHERE anggota_id = 42;
RESET enable_seqscan;
SQL 1 Bitmap Heap Scan
1 Bitmap Index ScanDengan pemindaian penuh dimatikan pada sesi yang sama, rencana berpindah ke sepasang simpul bitmap di atas index, dan keluaran jenis simpulnya memperlihatkan jalur alternatif itu. Nilai dari latihan ini adalah melihat bentuk rencana pemaksaannya, sehingga keputusan bawaan perencana dapat dinilai secara berimbang, dan RESET pada sesi yang sama mengembalikan perilaku bawaan.
Kesalahan umum#
Kesalahan pertama adalah mematikan konfigurasi perencana secara permanen demi memaksa index, yang memindahkan masalah tanpa menyelesaikannya, dan jalur yang benar memeriksa estimasi baris beserta statistiknya. Kesalahan kedua adalah menilai rencana pada tabel latihan kecil, karena pilihannya memang berbeda pada volume yang nyata.
Kesalahan ketiga adalah menyalahkan perencana atas rencana yang benar, yaitu pemindaian penuh untuk query yang memang menjangkau sebagian besar tabel. Kesalahan keempat adalah menganggap satu jenis simpul lebih baik secara mutlak, karena Index Scan, bitmap, dan pemindaian penuh masing-masing menang pada wilayah selektivitasnya, dan wilayah itu digerakkan oleh statistik.
Ringkasan#
- Pemindaian penuh menang pada tabel kecil dan query yang menjangkau sebagian besar baris, dan itu keputusan yang benar.
- Penyaringan sempit pada tabel besar dilayani sepasang simpul bitmap, yaitu Bitmap Index Scan lalu Bitmap Heap Scan.
- Rentang yang menjangkau mayoritas tabel kembali ke pemindaian penuh, sehingga garis pemisahnya biaya, bukan keberadaan index.
- SET enable_seqscan = off adalah alat uji sesi, dan ANALYZE tetap menjadi penjaga akurasi keputusan.
Referensi#
- Examining Index Usage — PostgreSQL Documentation — Panduan resmi menilai pemakaian index.
- Plan Cost Estimation — PostgreSQL Documentation — Konstanta biaya yang melandasi keputusan pemindaian.
- Query Planning — PostgreSQL Documentation — Konfigurasi enable_seqscan dan kawan-kawannya.