Slow fulltext query plan

[Benoit Delbosc <bdelbosc@xxxxxxxxx>]

Hi,

I would like to understand why the following query execution don't use 
any fulltext indexes
and takes more than 300s (using lot of temporary files):

  EXPLAIN ANALYZE SELECT hierarchy.id
  FROM hierarchy
  JOIN fulltext ON fulltext.id = hierarchy.id,
  TO_TSQUERY('whatever') query1,
  TO_TSQUERY('whatever') query2
  WHERE (query1 @@ nx_to_tsvector(fulltext.fulltext)) OR (query2 @@ 
nx_to_tsvector(fulltext.fulltext_title));

The query plan is here:
  http://explain.depesz.com/s/YgP

While if I replace the query2 by query1 in the second clause:

  EXPLAIN ANALYZE SELECT hierarchy.id
  FROM hierarchy
  JOIN fulltext ON fulltext.id = hierarchy.id,
  TO_TSQUERY('whatever') query1,
  TO_TSQUERY('whatever') query2
  WHERE (query1 @@ nx_to_tsvector(fulltext.fulltext)) OR (query1 @@ 
nx_to_tsvector(fulltext.fulltext_title));

It is 5 order of magniude faster (15ms) using the gin indexes:
  http://explain.depesz.com/s/RLa

The nx_to_tsvector is an immutable function with the following code:
  SELECT TO_TSVECTOR('english', SUBSTR($1, 1, 250000))

Here is the list of indexes:
  hierarchy: "hierarchy_pk" PRIMARY KEY, btree (id)
  fulltext: "fulltext_fulltext_idx" gin 
(nx_to_tsvector(fulltext::character varying))
  fulltext: "fulltext_fulltext_title_idx" gin 
(nx_to_tsvector(fulltext_title::character varying))

fulltext and fulltext_title are text type.

And some PostgreSQL configuration:
  PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu
  shared_buffers: 4GB
  effective_cache_size: 10GB
  work_mem: 20MB

Thanks for your work and enlightenment

ben

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance