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/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With