Efficient postgres index type for LIKE operator (fixed ending)

Question

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/

jjanes · Accepted Answer · 2021-05-31 15:54:52Z

4

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.

answered May 31, 2021 at 15:54

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Efficient postgres index type for LIKE operator (fixed ending)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related