DEV Community

Sihar Simbolon
Sihar Simbolon

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Create extension di database yang diinginkan

create extension pg_trgm;
Enter fullscreen mode Exit fullscreen mode

Buat index di tabela

CREATE INDEX CONCURRENTLY idx_tabela1 ON tabela USING gin (nama gin_trgm_ops);
Enter fullscreen mode Exit fullscreen mode

Explain analyze query sebelumnya

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

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

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

Top comments (0)