Search code examples
postgresqlfull-text-searchfull-text-indexing

Postgresql ILIKE versus TSEARCH

I have a query with a number of test fields something like this:

SELECT * FROM some-table
  WHERE field1 ILIKE "%thing%"
     OR field2 ILIKE "%thing"
     OR field3 ILIKE "%thing";

The columns are pretty much all varchar(50) or thereabouts. Now I understand to improve performance I should index the fields upon which the search operates. Should I be considering replacing ILIKE with TSEARCH completely?

Solution

  • A full text search setup is not identical to a "contains" like query. It stems words etc so you can match "cars" against "car".

    If you really want a fast ILIKE then no standard database index or FTS will help. Fortunately, the pg_trgm module can do that.