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?

score 16 · Accepted Answer · answered Aug 26 '12 at 07:16

16

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.

answered Aug 26 '12 at 07:16

Richard Huxton

21,516
3
39
51

pg_trgm is very helpful. For Django users: index on `upper(your_text_field)` because Django issues `upper(x) like upper(y)` queries instead of `ilike`. If the field is indexed without `upper` the index won't be used in those queries. – Risadinha Oct 31 '14 at 12:52

score 5 · Answer 2 · answered Aug 26 '12 at 00:05

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.

You are right that no B-Tree will improve that search, but PostgreSQL can use other index types to support a `%foobar%` condition (see Richard's answer) — , Aug 26 '12 at 07:44

score 3 · Answer 3 · edited Aug 27 '12 at 08:46

Adding a bit to what the others have said.

First you can't really use an index based on a value in the middle of the string. Indexes are tree searches generally, and you have no way to know if your search will be faster than just scanning the table, so PostgreSQL will default to a seq scan. Indexes will only be used if they match the first part of the string. So:

SELECT * FROM invoice
  WHERE invoice_number like 'INV-2012-435%'

may use an index but like '%44354456%' cannot.

In general in LedgerSMB we use both, depending on what kind of search we are doing. You might see a search like:

select * from parts
  WHERE partnumber ilike ?  || '%'
    and plainto_tsquery(get_default_language(), ?) @@ description;

So these are very different. Use each one where it makes the most sense.

I agree that there's not enough information to be sure whether trigram or tsearch is more appropriate, but one or the other (or possibly a combination) seems indicated. — kgrittn, Aug 27 '12 at 08:49

Postgresql ILIKE versus TSEARCH

3 Answers3

Linked