CODEWITHSUNDEEP
postgresqlindexing
Paweł
Paweł

Reputation: 11

Efficient postgres index type for LIKE operator (fixed ending)

There is a postgres query using like statement: email LIKE '%@%domain.com' What is the most appropriate index type that I can use? I've found pg_trgm module which must be enabled:

CREATE EXTENSION pg_trgm;

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

And then you can

CREATE INDEX <index name> ON <table name> USING gin (<column> gin_trgm_ops);

Is there a better option?

gin_trgm_ops is described here: https://niallburkley.com/blog/index-columns-for-like-in-postgres/

Upvotes: 1

Views: 610

Answers (1)

jjanes
jjanes

Reputation: 44137

The trigram index might be fine, and allows you write the query naturally. But more efficient would be a reversed string index:

create index on foobar (reverse(email) text_pattern_ops);
select * from foobar where reverse(email) LIKE reverse('%@%domain.com');

If your default collation is "C", then you don't need to specify text_pattern_ops.

If the search parameter contains any escaped (literal) characters, then you will have to do something more complicated than simply reversing it.

Upvotes: 4

Related Questions