Re: Differences between to_tsquery() and plainto_tsquery() when it comes to applying an index?

Try this select

alerts=> explain analyze select item_pk from node where
  tag='primaryIssuer.entityType' and val @@ plainto_tsquery('english','Limited
  Partnership');

Read 12.2.2. Creating Indexes at http://www.postgresql.org/docs/8.4/static/textsearch-tables.html

CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));

Notice that the 2-argument version of to_tsvector is used. Only text search functions that specify a configuration name
canbe used in expression indexes (Section 11.7). This is because the index contents must be unaffected by
default_text_search_config.If they were affected, the index contents might be inconsistent because different entries
couldcontain tsvectors that were created with different text search configurations, and there would be no way to guess
whichwas which. It would be impossible to dump and restore such an index correctly. 



Oleg

On Wed, 25 Aug 2010, Denis Papathanasiou wrote:

>
>> we need examples of your explain analyze. I don't want to waste my time
>> reading theoretical reasoning :)
>
> Here's an actual 'explain analyze' example:
>
> alerts=> CREATE INDEX node_val_tsv_idx ON node USING
> gin(to_tsvector('english', val));
> CREATE INDEX
> alerts=> explain analyze select item_pk from node where
> tag='primaryIssuer.entityType' and val @@ plainto_tsquery('Limited
> Partnership');
>                                                         QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on node  (cost=204.26..5792.92 rows=4 width=16) (actual
> time=2.952..131.868 rows=953 loops=1)
>   Recheck Cond: (tag = 'primaryIssuer.entityType'::text)
>   Filter: (val @@ plainto_tsquery('Limited Partnership'::text))
>   ->  Bitmap Index Scan on node_tag_idx  (cost=0.00..204.26 rows=3712
> width=0) (actual time=1.628..1.628 rows=3631 loops=1)
>         Index Cond: (tag = 'primaryIssuer.entityType'::text)
> Total runtime: 133.345 ms
> (6 rows)
>
> alerts=> DROP INDEX node_val_tsv_idx;
> DROP INDEX
> alerts=> explain analyze select item_pk from node where
> tag='primaryIssuer.entityType' and val @@ plainto_tsquery('Limited
> Partnership');
>                                                         QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on node  (cost=204.26..5792.92 rows=4 width=16) (actual
> time=2.938..93.239 rows=953 loops=1)
>   Recheck Cond: (tag = 'primaryIssuer.entityType'::text)
>   Filter: (val @@ plainto_tsquery('Limited Partnership'::text))
>   ->  Bitmap Index Scan on node_tag_idx  (cost=0.00..204.26 rows=3712
> width=0) (actual time=1.614..1.614 rows=3631 loops=1)
>         Index Cond: (tag = 'primaryIssuer.entityType'::text)
> Total runtime: 94.696 ms
> (6 rows)
>
> The table this is run against is defined like this:
>
> CREATE TABLE node (
>    pk uuid primary key,
>    item_pk uuid not null references item (pk),
>    tag text not null,
>    val text
> );
>
> In addition to the gin/ts_vector index on node.val shown above, there are two
> other explicit indices on this table:
>
> CREATE INDEX node_tag_idx ON node (tag);
> CREATE INDEX node_val_idx ON node (val);
>
> The reason for the node_val_idx index is that there will be cases where the
> query phrase is known exactly, so the where clause in the select statement
> will be just "val = 'Limited Partnership'".
>
>> btw, Be sure you use the same search configuration as in create index or
>> index will not be used at all.
>
> Is this indeed the problem here?
>
> The explain output references "val @@ plainto_tsquery()" but as a filter,
> whereas the tag portion of the statement mentions node_tag_idx as the index
> it used.
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83