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 May 5 '18 at 18:56

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, privacy policy and cookie policy

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