Denis, we need examples of your explain analyze. I don't want to waste my time reading theoretical reasoning :) btw, Be sure you use the same search configuration as in create index or index will not be used at all. Oleg On Wed, 25 Aug 2010, Denis Papathanasiou wrote:
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.
Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
