Postgresql ILIKE vs TSEARCH

Question

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

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

Columns are almost all varchar (50) or so. Now I understand that in order to improve performance, I have to index the fields on which the search works. Should I consider replacing ILIKE with TSEARCH completely?

Answer 1

Full-text search is not identical to the query contains. It speaks of words, etc., so you can match “cars” with “cars”.

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

• 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 never improve such a search:

 where field ilike '%SOMETHING%' 

I say that if you do:

 create index idx_name on some_table(field); 

The only access you can improve is where field like 'something%' . (when searching for values ​​starting with some literal). This way you will not get any benefit by adding a regular index to the field column in this case.

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

Answer 3

Adding a bit to what others have said.

At first, you cannot use an index based on a value in the middle of a row. Indexes are usually tree searches, and you cannot find out if your search will be faster than just scanning the table, so PostgreSQL will perform seq scanning by default. 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%' 

can use the index, but like '%44354456%' cannot.

In general, in LedgerSMB we use both options, depending on what kind of search we do. You can see the search, for example:

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

So these are very different things. Use each where it makes sense.