Postgresql ILIKE versus TSEARCH

Question

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?

Answer 1

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.

• http://www.postgresql.org/docs/9.1/static/pgtrgm.html
• http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

Answer 2

One thing that is very important: NO B-TREE INDEX will ever improve this kind of search:

where field ilike '%SOMETHING%'

What I am saying is that if you do a:

create index idx_name on some_table(field);

The only access you will improve is where field like 'something%'. (when you search for values starting with some literal). So, you will get no benefit by adding a regular index to field column in this case.

If you need to improve your search response time, definitely consider using FULL TEXT SEARCH.