How do I index the following statement with an un-anchored search pattern

SELECT somefield
FROM sometable 
WHERE lower(somefield2) like '%foo%';

Some rows have more than 2k bytes.

  • You had a bunch of stuff in the question that was not relevant to what you were looking for, I cropped it out. Sometimes a simple question just needs to be phrased simply, welcome to Database Administrators Commented May 5, 2018 at 18:56

1 Answer 1


The length aside, a btree index would not help that query. You could create a hash index but that would also only help if the query wants an exact match for the whole column. not for a substring pattern. To do what you want first, add the pg_trgm extension:


Then create a trigram index:

CREATE INDEX trgm_idx ON sometable
  USING GIN (somefield2 gin_trgm_ops); -- can also be GIST

A trigram index can help find matches for SQL's LIKE and ILIKE and regex patterns.

For more information see the docs on pg_trgm


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.