Message-ID: <4F875293.1000100@nuxeo.com>
Date: Fri, 13 Apr 2012 00:09:23 +0200
From: Benoit Delbosc <bdelbosc@nuxeo.com>
Organization: Nuxeo
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.28) Gecko/20120313 Lightning/1.0b2 Thunderbird/3.1.20
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Slow fulltext query plan
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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