CODEWITHSUNDEEP
sqlpostgresql
Krystian Cybulski
Krystian Cybulski

Reputation: 11108

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

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;

Upvotes: 5

Views: 820

Answers (2)

Richard Huxton
Richard Huxton

Reputation: 22893

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

Upvotes: 5

user330315
user330315

Reputation:

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/

Upvotes: 7

Related Questions