Faster ILIKE queries

How to speed-up the ILIKE queries

• Add module CREATE EXTENSION pg_trgm;
• Create GIN index CREATE INDEX edge_idx_head_id_tail_id ON edge USING gin("head_node_id", "tail_node_id" gin_trgm_ops);
• Query <Any ILIKE query on head_node_id and tail_node_id>

Speed comparison

Test query

edge.head_node_id ILIKE 'JAK2' AND edge.head_node_id ILIKE 'JAK2' AND edge.tail_node_id ILIKE 'STAT4' AND edge.tail_node_id ILIKE 'STAT4' OR edge.tail_node_id ILIKE 'STAT4' AND edge.tail_node_id ILIKE 'STAT4' AND edge.head_node_id ILIKE 'JAK2' AND edge.head_node_id ILIKE 'JAK2'

Results

• 1.4851 sec- Like query for JAK2:STAT4 edge with BTREE indices
• 0.024 sec - Equal query for JAK2:STAT4 edge with BTREE indices
• 7.34786 sec - iLike query for JAK2:STAT4 edge with BTREE indices
• 0.214 sec - iLike query for JAK2:STAT4 edge with GIN indices and pg_trgm module.
• 0.019 sec - Equal query for JAK2:STAT4 edge with BTREE indices

When should we use GIN indices ?

Generalized Inverted Indexes (GIN) are useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values as well as for implementing full-text search.

What is pg_trgm module?

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings.

For more info, Refer to the PostgreSQL documentation on pg_trgm

Relevant Links

• PostgreSQL LIKE query performance variations
• Difference between gist and gin index
• pg_trgm module