If I add a full text index in PostgreSQL, will my LIKE and ILIKE queries use it automatically?
Do I need to use the special full text syntax in order to take advantage of a full-text index in PostgreSQL:
SELECT title, ts_headline(body, query) AS snippet, ts_rank_cd(body_tsv, query, 32) AS rank
FROM blog_entry, plainto_tsquery('hello world') AS query
WHERE body_tsv @@ query
ORDER BY rank DESC;
No, a full-text index will not be used by the LIKE operator.
With PostgreSQL 9.1 you can create a new type of index that will speed up LIKE operations even if the wildcard is not only at the end of the expression
http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/
To expand on ahwnn's answer, they are different comparisons and cannot use the same index.
The full-text-search always involves tokenizing the text and typically involves stemming words. This makes it difficult to search for exact prefixes and usually impossible to e.g. match two spaces in a row (they generally just get discarded).
You might want to read up on the various index operator classes too, in particular text_pattern_ops and friends. Without this LIKE 'cab%' can't be optimised to >= 'cab' AND < 'cac'.
http://www.postgresql.org/docs/9.1/static/indexes-opclass.html
