Create kolom index untuk like di Postgresql

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/