Slow fulltext query plan

Поиск
Список
Период
Сортировка
От Benoit Delbosc
Тема Slow fulltext query plan
Дата
Msg-id 4F875293.1000100@nuxeo.com
обсуждение исходный текст
Ответы Re: Slow fulltext query plan
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Random performance hit, unknown cause.
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Slow fulltext query plan