From: Benoit Delbosc <bdelbosc@nuxeo.com>
To: pgsql-performance@postgresql.org
Subject: Slow fulltext query plan
Date: Fri, 13 Apr 2012 00:09:23 +0200
Message-ID: <4F875293.1000100@nuxeo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bdelbosc@nuxeo.com
Subject: Re: Slow fulltext query plan
In-Reply-To: <4F875293.1000100@nuxeo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox