Differences between to_tsquery() and plainto_tsquery() when it comes to applying an index?
От | Denis Papathanasiou |
---|---|
Тема | Differences between to_tsquery() and plainto_tsquery() when it comes to applying an index? |
Дата | |
Msg-id | 4C753C9B.5010507@gmail.com обсуждение исходный текст |
Ответы |
Re: Differences between to_tsquery() and plainto_tsquery() when it comes to applying an index?
Re: Differences between to_tsquery() and plainto_tsquery() when it comes to applying an index? |
Список | pgsql-general |
As a follow-up to my question from this past Saturday (http://archives.postgresql.org/pgsql-general/2010-08/msg00624.php), I experimented with adding two types of indices to the same text column: CREATE INDEX item_eng_searchable_text_idx ON item USING gin(to_tsvector('english', searchable_text)); and CREATE INDEX item_searchable_text_idx ON item (searchable_text); Running my queries under explain, I noticed that queries of the form: select pk from item where searchable_text @@ plainto_tsquery('search phrase'); Actually run *slower* with the item_eng_searchable_text_idx index applied. But when I switch the query to use to_tsquery() instead, e.g. something like this: select pk from item where searchable_text @@ to_tsquery('phrase'); The performance is better. Is this because the gin/to_tsvector() index works differently for to_tsquery() compared to plainto_ts_query() ? If so, how can I create an effective index for queries that will use plainto_tsquery() ? Note that I need the plainto_tsquery() form b/c my search phrases will correspond to exact blocks of text, and therefore they will contain spaces and punctuation, etc.
В списке pgsql-general по дате отправления: