Saat Indeks Merugikan: Penulisan dan Selektivitas

Setelah tiga pelajaran tentang alasan indeks membantu, pewawancara membalik pertanyaannya: 'Mengapa tidak mengindeks saja setiap kolom?' Kandidat yang kuat menjelaskan bahwa indeks memiliki biaya nyata, pada penulisan serta pada cache dan penyimpanan, dan bahwa beberapa indeks bahkan tidak akan pernah digunakan oleh perencana.

Pelajaran ini membahas dua alasan utama indeks dapat merugikan: amplifikasi penulisan dan selektivitas rendah.

Indeks harus tetap sinkron dengan tabel. Setiap INSERT, setiap DELETE, dan setiap UPDATE pada kolom yang diindeks juga harus memperbarui struktur indeks. Inilah amplifikasi penulisan: satu perubahan baris menjadi satu penulisan tabel ditambah satu penulisan untuk setiap indeks yang terdampak.

Tabel dengan delapan indeks membutuhkan kira-kira sembilan kali pekerjaan penulisan dibandingkan tabel tanpa indeks. Pada tabel yang didominasi penulisan atau memiliki throughput tinggi, ini merupakan beban yang serius.

Bayangkan tabel peristiwa yang menerima ribuan baris per detik. Setiap indeks tambahan membuat setiap penyisipan membutuhkan lebih banyak pekerjaan, memecah halaman indeks, memperbarui daun, dan bersaing memperebutkan cache.

Untuk tabel yang hanya menerima penambahan dan didominasi penulisan, jawaban yang tepat sering kali adalah sedikit atau tanpa indeks di luar kunci utama, lalu melakukan pembacaan berat pada replika atau gudang data.

Selektivitas menunjukkan seberapa baik suatu kolom membedakan baris, yaitu proporsi baris yang cocok dengan nilai tipikal. Selektivitas tinggi berarti hanya sedikit baris untuk setiap nilai, seperti alamat surel atau UUID. Selektivitas rendah berarti banyak baris untuk setiap nilai, seperti nilai boolean atau status dengan tiga pilihan.

Indeks memberikan manfaat pada kolom dengan selektivitas tinggi, ketika suatu pencarian menyingkirkan hampir semua baris. Pada kolom dengan selektivitas rendah, indeks sering kali tidak memberikan manfaat.

Misalkan is_active bernilai benar untuk 90% pengguna. Pencarian melalui indeks akan mengembalikan 90% isi tabel, dan untuk sebanyak itu baris mesin harus melakukan pengambilan dari heap untuk setiap baris, yang lebih lambat daripada memindai tabel secara berurutan dalam satu lintasan.

Jadi, perencana dengan tepat mengabaikan indeks tersebut dan melakukan pemindaian berurutan. Indeks itu hanya menambah beban penulisan dan penggunaan penyimpanan tanpa memberikan manfaat pembacaan sama sekali.

Aturan praktis yang berguna untuk disampaikan: ketika sebuah predikat cocok dengan kira-kira lebih dari 5 hingga 20% isi tabel, pemindaian berurutan biasanya lebih unggul daripada pemindaian indeks, karena pengambilan acak dari heap lebih mahal daripada mengalirkan halaman secara berurutan.

Titik peralihan yang tepat bergantung pada ukuran baris, penyimpanan dalam cache, dan kecepatan penyimpanan, sehingga perencana menggunakan statistik, bukan angka tetap, untuk mengambil keputusan.

Jika Anda hanya pernah mengkueri nilai-nilai langka dari kolom yang distribusinya tidak merata, indeks parsial (PostgreSQL) hanya mengindeks baris-baris tersebut—kecil, selektif, dan murah dipelihara.

Jika 1% pesanan berstatus pending dan itulah yang terus-menerus Anda kueri, indekskan hanya pesanan tersebut. Indeks tetap kecil dan perencana akan dengan senang hati menggunakannya.

Pengoptimal menentukan pilihan antara indeks dan pemindaian berdasarkan statistik kolom. Jika statistik tersebut kedaluwarsa, misalnya setelah pemuatan massal atau pembaruan besar, pengoptimal dapat salah menilai selektivitas dan memilih rencana yang keliru.

Ketika pewawancara berkata 'indeksnya ada tetapi tidak digunakan', jawaban yang sangat baik mencakup penyegaran statistik dengan ANALYZE sebelum menyalahkan indeks itu sendiri.

Lengkapi jawaban dengan biaya-biaya yang kurang diketahui:

• Penyimpanan dan cache: indeks menggunakan ruang disk dan bersaing memperebutkan memori, sehingga mengeluarkan halaman data yang berguna.
• Indeks redundan/tumpang tindih: tetap dipelihara tetapi tidak pernah dipilih.
• Penggelembungan: akibat pembaruan berat, B-Tree mengalami fragmentasi dan memerlukan REINDEX.
• Kebingungan pengoptimal: terlalu banyak indeks serupa membuat perencanaan lebih lambat dan kurang dapat diprediksi.

Untuk mendukung pembersihan di dunia nyata, sebutkan bahwa PostgreSQL melacak penggunaan indeks. Indeks dengan idx_scan = 0 merupakan kandidat untuk dihapus karena menghabiskan biaya penulisan dan ruang tanpa pernah melayani pembacaan.

Ringkasan yang lengkap dan seimbang:

'Indeks menimbulkan amplifikasi penulisan; setiap INSERT/UPDATE/DELETE memeliharanya, ditambah tekanan pada penyimpanan dan cache. Indeks hanya bermanfaat pada predikat dengan selektivitas tinggi; pada kolom yang sebagian besar barisnya cocok, perencana dengan tepat memilih pemindaian berurutan, sehingga indeks itu hanya menjadi beban. Untuk kolom yang distribusinya tidak merata, saya memilih indeks parsial, menjaga statistik tetap mutakhir dengan ANALYZE, dan menghapus indeks yang tidak digunakan.'

Tentukan indeks mana yang paling kecil kemungkinannya sepadan dengan biayanya.

Pokok-pokok penting:

• Setiap indeks menambah amplifikasi penulisan serta biaya penyimpanan dan cache.
• Indeks membantu pada kolom dengan selektivitas tinggi; pada kolom dengan selektivitas rendah, perencana memilih pemindaian berurutan.
• Jika baris yang cocok melebihi kira-kira 5 hingga 20%, pemindaian biasanya lebih unggul.
• Gunakan indeks parsial untuk kolom yang distribusinya tidak merata dan hanya Anda kueri pada nilai-nilai langka.
• Jaga statistik tetap mutakhir dengan ANALYZE dan hapus indeks yang tidak digunakan (idx_scan = 0).

Dengan demikian, kursus strategi pengindeksan selesai: buat indeks di tempat yang memberikan manfaat, lalu buktikan dengan rencana.