Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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 can be 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 could contain tsvectors that were created with different text search configurations, and there would be no way to guess which was 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@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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux