Indeks PostgreSQL: Mempercepat Kueri Tanpa Berlebihan

Index membantu basis data menemukan data lebih cepat, seperti daftar isi membantu pembaca mencari halaman. Namun, setiap indeks juga menambah ruang penyimpanan dan pekerjaan saat data berubah. Untuk aplikasi PostgreSQL yang melambat, mulai dari kueri yang terbukti lambat, ukur cara basis data menjalankannya, lalu uji satu perubahan sebelum menambah indeks berikutnya.

Pahami manfaat dan biaya setiap indeks, lalu uji satu perubahan pada satu waktu. Dengan begitu, hasilnya dapat dihubungkan dengan perubahan yang dilakukan.

Biaya pemeliharaan indeks saat data ditulis

Indeks bekerja seperti daftar isi yang perlu disesuaikan ketika isi buku berubah. Pada basis data, indeks adalah struktur tambahan yang harus dipelihara bersama tabel.

Secara konseptual, INSERT menambah baris tabel beserta entri pada indeksnya. Perubahan kolom terindeks melalui UPDATE juga memengaruhi entri terkait, sedangkan DELETE membuat baris dan entri perlu dibersihkan. Dengan lima indeks, satu INSERT memengaruhi enam struktur. Jumlah ini bukan enam operasi tulis disk fisik; pembersihan indeks dapat menunggu vacuum.

Ongkos ini terus bertambah. Benchmark Percona menaikkan jumlah indeks pada skema PostgreSQL dari 7 ke 39, di bawah beban kerja campuran insert/update/select. Jumlah transaksi yang diproses per detik (throughput) turun dari sekitar 1.400 transaksi per detik menjadi sekitar 600 — kira-kira 43% dari titik awal. Rata-rata waktu transaksi naik dari 11ms ke 26ms.

Dalam pengujian itu, beban kueri tetap sementara jumlah indeks berubah. Hasilnya menunjukkan perlunya menilai biaya pemeliharaan setiap indeks. Tambahkan indeks untuk kueri yang jelas, lalu ukur dampaknya terhadap pembacaan dan penulisan.

Urutan kolom indeks mengikuti kondisi pencarian

Saran "kolom paling selektif ditaruh duluan" sering diulang tetapi jarang benar-benar berlaku. Aturan sebenarnya, menurut dokumentasi resmi PostgreSQL: kondisi kesetaraan (equality) di kolom-kolom terdepan, ditambah satu kondisi rentang (range) atau ketidaksamaan (inequality) tepat setelahnya — itulah yang membatasi seberapa besar bagian indeks yang benar-benar dipindai. Kolom di posisi lebih kanan tetap diperiksa di dalam indeks, sehingga menghemat kunjungan ke tabel, tetapi belum tentu mempersempit rentang pemindaian. PostgreSQL saat ini juga dapat memakai skip scan pada distribusi yang sesuai; periksa rencana kueri yang sebenarnya.

Jadi pertanyaan desainnya bukan "kolom mana yang punya lebih banyak nilai unik." Pertanyaannya "kolom mana yang kueri saya filter dengan =, dan kolom mana yang difilter dengan range." Index pada (status, created_at) melayani WHERE status = 'active' AND created_at > $1 dengan baik terlepas dari mana yang lebih selektif, karena status adalah predikat equality dan wajib di depan.

Urutan kolom harus mengikuti kueri yang Anda jalankan, bukan perkiraan jumlah nilai berbeda (cardinality). Alasan yang sama mengapa dasbor yang dikelompokkan per zona waktu butuh bentuk indeks sendiri, yang saya bahas di pengelompokan data beberapa zona waktu di PostgreSQL. Selektivitas memang jadi penentu saat dua urutan sama-sama melayani kueri yang sama, tetapi itu faktor sekunder — bukan aturan utama seperti yang diklaim mitos "paling selektif duluan". Jangan menumpuk kolom tanpa batas juga: dokumentasi PostgreSQL sendiri bilang indeks satu kolom biasanya sudah cukup, dan indeks lebih dari tiga kolom jarang membantu kecuali pola pemakaian tabelnya sangat khusus.

Syarat pembacaan langsung dari indeks dengan INCLUDE

Indeks B-tree bisa menjawab kueri sepenuhnya dari dirinya sendiri, tanpa menyentuh tabel, kalau semua kolom yang dibutuhkan kueri ada di dalam indeks. Cara ini disebut index-only scan, yaitu pembacaan yang dapat mengambil data dari indeks tanpa membaca baris tabel. Kalau kueri butuh satu kolom tambahan hanya untuk keluaran, bukan untuk filter, CREATE INDEX ON orders(customer_id) INCLUDE (total_amount) menempelkan kolom itu sebagai data tambahan tanpa memperlebar kunci pencarian.

Ada jebakan yang diam-diam membatalkan manfaatnya. Index-only scan hanya melewati tabel kalau halaman penyimpanan tabel (heap) ditandai all-visible dalam peta visibilitas (visibility map) PostgreSQL. Kalau belum, mesin tetap harus mengunjungi baris itu untuk memastikan visibilitasnya. Pembacaan tabel tambahan dapat mengurangi manfaat yang diharapkan.

Tabel yang mayoritas dibaca akan tetap all-visible dan indeks yang mencakup kolom kebutuhan kueri (covering indeks) memberi manfaat. Tabel dengan penulisan terus-menerus di antara proses vacuum jarang sempat mengejar, dan kolom INCLUDE yang Anda tambahkan menambah beban tanpa manfaat pembacaan yang diharapkan. Cek jadwal autovacuum dulu sebelum mengandalkan ini.

Bandingkan estimasi dan hasil nyata pada EXPLAIN

Keluaran EXPLAIN ANALYZE memuat simpul proses, perkiraan biaya, penggunaan penyangga data, dan waktu eksekusi. Pada pembacaan pertama, bandingkan estimasi jumlah baris dengan hasil nyata pada bagian yang paling dekat dengan masalah.

Menurut dokumentasi PostgreSQL, angka rows pada sebuah node adalah perkiraan yang dikeluarkan node itu setelah penyaringan pada simpul tersebut, bukan jumlah yang dipindai. Angka ini rutin lebih rendah dari jumlah baris mentah. Estimasi dan aktual yang berdekatan berarti perencana kueri memahami data Anda dengan baik. Selisih 10x atau lebih dapat menandakan statistik yang belum mutakhir, statistik yang kurang mewakili hubungan antarkolom, atau nilai parameter dengan pola berbeda, dan indeks baru tidak akan memperbaikinya — ANALYZE pada tabel sering lebih membantu daripada indeks baru.

Ada satu jebakan operasional sebelum menjalankan ini pada apa pun selain SELECT: EXPLAIN ANALYZE bukan sekadar merencanakan perintah, ia benar-benar menjalankannya. Jalankan pada UPDATE dan penulisan data benar-benar terjadi. Bungkus dalam transaksi lalu rollback:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

Dampak indeks berlebihan pada ruang dan pemeliharaan

Selain ongkos per-penulisan di atas, tumpukan indeks yang terus bertambah punya dua konsekuensi lagi. Penyimpanan: indeks sebuah tabel yang digabung sering kali ukurannya melebihi tabelnya sendiri. Biaya perencanaan kueri: planner harus mempertimbangkan setiap kandidat indeks yang menyentuh kolom terkait, dan indeks yang mirip-mirip satu sama lain memberinya lebih banyak peluang memilih yang justru kurang cocok.

Penambahan indeks juga dapat memperbesar pekerjaan pemeliharaan. Semakin banyak indeks, semakin banyak kerja autovacuum di setiap putaran. Vacuum makin tertinggal di tabel yang sibuk. Ruang yang belum dibersihkan (bloat) menumpuk. Query melambat — dan reaksi umumnya adalah menambah indeks lagi untuk mengompensasi. Setiap indeks yang dikirim tanpa kueri spesifik di baliknya membuat kueri lambat berikutnya sedikit lebih sulit diperbaiki.

Uji satu kueri lambat dan satu perubahan

Pada basis data produksi klien, saya membatasi perubahan agar dampak dan cara pemulihannya dapat diperiksa. Alurnya sebagai berikut:

Satu kueri, satu hipotesis, satu perubahan, satu pengukuran. Beberapa indeks yang ditambahkan sekaligus mungkin mempercepat kueri, tetapi sulit diketahui mana yang memberi manfaat. Biaya penulisan indeks lainnya tetap perlu ditanggung. Ubah satu variabel dalam satu waktu. Setiap indeks yang tersisa punya kueri yang menjadi dasar kebutuhannya.

Pemeriksaan sebelum menambahkan indeks

• Apakah Anda punya kueri lambat yang sebenarnya beserta keluaran EXPLAIN ANALYZE-nya, bukan sekadar tebakan?
• Apakah estimasi jumlah baris kurang lebih cocok dengan aktualnya, atau Anda perlu ANALYZE dulu?
• Apakah urutan kolom mengikuti cara kueri memfilter — equality dulu, baru kolom range — bukan cardinality mentah?
• Apakah INCLUDE bisa dipakai di sini, dan apakah tabel ini cukup terjaga vacuum-nya untuk mendapat index-only scan?
• Apakah Anda mengirim indeks ini sendirian, sehingga efeknya bisa diukur secara terisolasi?
• Apakah sudah ada indeks dengan kolom terdepan yang sama yang mencakup kasus ini?

Gunakan indeks untuk pola pencarian yang terbukti membutuhkannya. Catat kueri yang dilayani setiap indeks agar tim berikutnya dapat menilai apakah indeks tersebut masih diperlukan.

Apa bukti perbaikan yang perlu diterima pemilik sistem?

Minta waktu respons sebelum dan sesudah untuk kueri serta beban kerja yang sama. Periksa juga apakah penambahan indeks memperlambat penyimpanan transaksi atau memperbesar biaya disk. Laporan yang berguna menyebut indeks yang berubah, alasan perubahan, dan cara mengembalikannya jika hasil di produksi berbeda.

Hitungan jumlah struktur yang berubah bukan jumlah operasi tulis disk fisik. PostgreSQL memakai buffering, WAL, MVCC, dan dapat memakai HOT update pada kondisi tertentu. Penghapusan entri indeks juga dapat menunggu vacuum. Gunakan pengukuran beban kerja untuk menilai biaya sebenarnya.