Optimizing Like Queries in PostgreSQL

SQL query with like operator can not be optimized using standart btree indexes in PostgreSql. You may use pg_trgm extension to create trgm index which can be used to optimize “like” operator. pg_trgm comes within contrib module.

Lets see in an example

Step 1. create some sample data using postgresql anonymizer

create table persons
as
select t.id, anon.dummy_first_name() first_name, anon.dummy_last_name() last_name,
anon.fake_email() email,anon.fake_country() country,anon.fake_city() city
from
(
select id
from generate_series(1,1000000 ) as id
) t ;

Step 2. create index on email column

demodb=# create index ix_email on persons (email);

CREATE INDEX

Step 3. Analyze plan for a SQL query with like operator

demodb=# explain analyze select * from persons where email like ‘anna37%’;

As shown below the plan is not using index on email column although there is an index

Step 4. Create extension pg_trgm and trgm index on email column

demodb=# create extension pg_trgm ;

demodb=# CREATE INDEX trgm_email_idx ON persons USING gist (email gist_trgm_ops);

Step 5. Explain same query with new plan after creating trgm index

demodb=# explain analyze select * from persons where email like ‘anna37%’;

Now it uses newly created trgm index. And cost has reduced from 24.532 fro 375 and query reduced to 23 ms from 90 ms.