Merancang Kolom tsvector dan Indeks GIN

Pencarian teks lengkap PostgreSQL membandingkan tsvector (dokumen yang dapat dicari) dengan tsquery (istilah pencarian). Pendekatan naif memanggil to_tsvector() pada kolom teks mentah saat kueri dijalankan.

Pendekatan tersebut berhasil, tetapi pada skala besar memiliki dua biaya:

• CPU per baris: mengurai dan melakukan stemming teks pada setiap pemindaian membutuhkan biaya besar.
• Tidak ada indeks yang dapat digunakan kecuali ekspresi indeks sama persis dengan ekspresi kueri.

Solusinya adalah menghitung terlebih dahulu dokumen sekali dan menyimpannya, lalu membuat indeks untuknya. Pelajaran ini menunjukkan cara merancang kolom tersebut dan indeks GIN agar kueri teks lengkap tetap berjalan dalam waktu di bawah satu milidetik, bahkan pada jutaan baris.

Berikut pola yang biasanya digunakan sebagai awal: simpan teks biasa, lalu buat tsvector secara langsung saat dibutuhkan.

Kueri di bawah ini bekerja dengan benar, tetapi pada tabel besar kueri tersebut memicu pemindaian berurutan dan mengurai ulang body untuk setiap baris. Setiap pemanggilan to_tsvector melakukan stemming dan normalisasi terhadap seluruh teks dokumen.

Tujuan pelajaran ini adalah menghilangkan pekerjaan per baris tersebut sepenuhnya.

Desain modern yang paling bersih (PostgreSQL 12+) adalah kolom terbuat yang disimpan. PostgreSQL menghitung tsvector secara otomatis setiap kali baris berubah, sehingga dokumen selalu konsisten dengan teks sumber.

Ingat dua aturan berikut:

• Ekspresi pembentukan harus IMMUTABLE, karena itu regconfig diberikan sebagai literal ('english'), bukan dengan mengandalkan pengaturan sesi.
• Gunakan coalesce() agar kolom NULL tidak membuat seluruh dokumen menjadi NULL.

Tidak semua bidang memiliki tingkat kepentingan yang sama. Kecocokan pada judul biasanya lebih penting daripada kecocokan jauh di dalam isi. setweight() menandai leksem dengan label A, B, C, atau D (A yang tertinggi).

Label ini nantinya memungkinkan ts_rank memberikan skor kecocokan judul lebih tinggi daripada kecocokan isi. Masukkan pembobotan tersebut ke dalam kolom terbuat agar dihitung sekali, bukan saat kueri dijalankan.

tsvector yang disimpan tetap tidak berguna tanpa indeks. Jenis indeks yang tepat untuk pencarian teks lengkap adalah GIN (Generalized Inverted Index). GIN menyimpan satu entri untuk setiap leksem berbeda yang menunjuk ke baris-baris yang memuatnya, persis seperti yang diperlukan oleh pencocokan @@.

Karena kolom tersebut sudah menyimpan tsvector, indeksnya adalah indeks kolom biasa dan tidak memerlukan ekspresi:

Sekarang kueri merujuk langsung ke kolom yang disimpan. Perencana dapat menggunakan indeks GIN karena ekspresi dalam klausa WHERE (search_doc) sama persis dengan ekspresi yang diindeks.

Tidak ada to_tsvector() per baris dan tidak ada pemindaian berurutan. Jalankan EXPLAIN ANALYZE dan Anda seharusnya melihat Bitmap Index Scan pada idx_articles_search_doc.

PostgreSQL mendukung dua jenis indeks untuk tsvector. Pilihlah dengan pertimbangan:

• GIN: pencarian lebih cepat, pilihan bawaan untuk pencarian. Ukurannya sedikit lebih besar dan pembuatannya/pembaruan indeksnya lebih lambat. Terbaik ketika operasi baca lebih dominan.
• GiST: lebih kecil dan biaya pembaruannya lebih rendah, tetapi bersifat lossy, sehingga indeks memeriksa ulang baris kandidat dan lebih lambat untuk kueri. Berguna untuk data yang sangat sering ditulis atau terus berubah.

Untuk sebagian besar beban kerja pencarian, ketika Anda melakukan kueri jauh lebih sering daripada menulis, GIN lebih unggul. Gunakan GiST hanya ketika biaya pembaruan indeks menjadi hambatan utama Anda.

Indeks GIN menggunakan daftar tertunda untuk mengelompokkan penyisipan (fastupdate = on secara bawaan). Hal ini mempercepat penulisan, tetapi daftar tertunda yang besar memperlambat pembacaan karena kueri harus memindai daftar tersebut selain indeks utama.

Untuk tabel pencarian yang lebih banyak dibaca, Anda dapat menyetel atau menonaktifkan perilaku ini. Menonaktifkan fastupdate membuat setiap penyisipan melakukan lebih banyak pekerjaan, tetapi menjaga kueri tetap cepat secara konsisten.

Kolom yang dihasilkan hadir di PostgreSQL 12. Pada versi yang lebih lama, atau ketika Anda memerlukan logika yang bukan IMMUTABLE, Anda memelihara tsvector dengan pemicu.

Pembantu klasiknya adalah tsvector_update_trigger, yang mengisi kolom tujuan dari kolom sumber yang disebutkan. Perhatikan keterbatasannya: pembantu ini menggunakan satu bobot tetap dan satu konfigurasi tetap, sehingga untuk pembobotan per bidang Anda perlu menulis fungsi pemicu BEFORE khusus.

Pemicu hanya berjalan pada penyisipan dan pembaruan berikutnya. Baris yang sudah ada tetap memiliki search_doc bernilai NULL sampai Anda mengisinya.

Untuk kolom tersimpan yang dihasilkan, PostgreSQL mengisi data secara otomatis saat Anda menambahkan kolom tersebut. Untuk pola pemicu, jalankan UPDATE satu kali. Pada tabel yang sangat besar, lakukan dalam beberapa kelompok berdasarkan rentang kunci utama agar Anda tidak mengunci seluruh tabel atau membuat satu transaksi raksasa membengkak.

Selalu pastikan perencana menggunakan indeks GIN Anda, bukan beralih ke pemindaian berurutan. Alasan umum indeks tidak digunakan: ekspresi kueri tidak cocok dengan ekspresi yang diindeks, tabel sangat kecil, atau statistik sudah usang.

Jalankan EXPLAIN (ANALYZE, BUFFERS) dan cari Bitmap Index Scan pada nama indeks Anda. Jika Anda melihat Seq Scan dengan Filter, berarti indeks tidak digunakan; perbaiki kecocokan ekspresinya atau jalankan ANALYZE.

Anda telah merancang kolom pencarian teks lengkap berperforma tinggi dari awal hingga akhir:

• Prakomputasikan dokumen ke dalam kolom tsvector STORED yang dihasilkan agar teks diurai sekali, bukan pada setiap kueri.
• Beri bobot pada bidang dengan setweight() (A untuk judul, B untuk isi) agar ts_rank dapat menilai kecocokan secara bermakna.
• Indeks kolom tersebut dengan GIN, yaitu indeks terbalik yang dioptimalkan untuk pembacaan dan pencocokan @@; pilih GiST hanya untuk data yang sangat sering ditulis dan berubah-ubah.
• Sesuaikan penulisan melalui fastupdate dan gin_pending_list_limit ketika daftar tertunda memperlambat pembacaan.
• Pelihara tabel sebelum versi 12 dengan pemicu dan isi baris yang sudah ada dalam beberapa kelompok.
• Verifikasi dengan EXPLAIN (ANALYZE, BUFFERS) bahwa Anda mendapatkan Bitmap Index Scan, bukan Seq Scan.