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
