postgresql full text search function syntax

Question

In postgres database we have a table table1 and with column column1 which type is text. And we created an index to that column CREATE INDEX idx_column1 ON table1 USING gin (to_tsvector('english', column1));

question is, why when we execute this query

SELECT *
FROM table1
where to_tsvector('english', column1) @@ to_tsquery('searchedText')

index is used, but by this query index is not used

SELECT *
FROM table1
where ts_match_vq(to_tsvector('english', column1),to_tsquery('searchedText'))

ts_match_vq(to_tsvector('english', column1) @@ to_tsquery('searchedText')) This does not work for me. The function is defined as expecting a ts_vector and a ts_query. In your example, you are passing a boolean to it, my 9.1 complains about this. — DrColossos
Sorry, corrected question. Instead of ts_match_vq(to_tsvector('english', column1) @@ to_tsquery('searchedText')) I wanted ask about to_tsvector('english', column1) @@ to_tsquery('searchedText') — zhmaksat

DrColossos DrColossos · Accepted Answer · 2013-07-25T12:09:31

Wild guess:

vector @@ query is defined as

CREATE OPERATOR @@(
  PROCEDURE = ts_match_vq,
  LEFTARG = tsvector,
  RIGHTARG = tsquery,
  COMMUTATOR = @@,
  RESTRICT = tsmatchsel,
  JOIN = tsmatchjoinsel);

Looking at tsmatchjoinsel there is quite a lot going on (don't ask me what, this kind of C is way beyond me....) But if you go through the different functions, there are some calculations involved. When using ts_match_vq directly, you bypass these calculations. That's why ts_match_vq is never mentioned in the docs and you should always use @@ since it takes care of calling the right functions and all the stuff that goes with it.

postgresql full text search function syntax

1 Answers