Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Will adding a full text index in PostgreSQL speed up my regular queries which use LIKE?

Tags:

sql

postgresql

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;
like image 211
Krystian Cybulski Avatar asked Mar 23 '12 09:03

Krystian Cybulski


1 Answers

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/

like image 86
a_horse_with_no_name Avatar answered Oct 05 '22 18:10

a_horse_with_no_name