DEV Community

Sihar Simbolon
Sihar Simbolon

Posted on

3 3

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/

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Retry later
Retry later