Create kolom index untuk like di Postgresql

#postgres #index

Spesifikasi Percobaan:

Centos 7
PostgreSQL 12

Persoalan:
Query pencarian ke sebuah kolom (contoh: nama) memerlukan waktu yang cukup lama meskipun sudah dibuat index untuk kolom tersebut.
Contoh cuplikan query:

EXPLAIN ANALYZE SELECT nama from tabela where nama ilike '%abcdefg%';
...
Planning Time: 0.300 ms
Execution Time: 261.213 ms

Penyebab:
Index btree tidak bekerja untuk pencarian menggunakan like atau ilike (partial match). Index btree bekerja ketika menggunakan pencarian full match. Contoh: "...where nama ='abcdefg'..."

Solusi:
Install software contrib sesuai versi postgresql

yum install postgresql12-contrib

Create extension di database yang diinginkan

create extension pg_trgm;

Buat index di tabela

CREATE INDEX CONCURRENTLY idx_tabela1 ON tabela USING gin (nama gin_trgm_ops);

Explain analyze query sebelumnya

EXPLAIN ANALYZE SELECT nama from tabela where nama ilike '%abcdefg%';
...
Planning Time: 0.588 ms
Execution Time: 1.004 ms

Waktu eksekusi untuk pencarian seperti sebelumnya jauh lebih cepat. Selesai!

Referensi
https://niallburkley.com/blog/index-columns-for-like-in-postgres/

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →