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 по дате отправления: