Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id A30031927EA3 for ; Fri, 13 Apr 2012 22:16:58 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SIrbl-0006t2-A4 for pgsql-performance@postgresql.org; Sat, 14 Apr 2012 01:16:58 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q3E1GhfE000773; Fri, 13 Apr 2012 21:16:44 -0400 (EDT) To: Benoit Delbosc cc: pgsql-performance@postgresql.org Subject: Re: Slow fulltext query plan In-reply-to: <4F875D8B.6020802@nuxeo.com> References: <4F875293.1000100@nuxeo.com> <6273.1334269559@sss.pgh.pa.us> <4F875D8B.6020802@nuxeo.com> Comments: In-reply-to Benoit Delbosc message dated "Fri, 13 Apr 2012 00:56:11 +0200" Date: Fri, 13 Apr 2012 21:16:43 -0400 Message-ID: <772.1334366203@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/109 X-Sequence-Number: 46755 Benoit Delbosc writes: > On 13/04/2012 00:25, Tom Lane wrote: >> Is there a reason why you're writing the query in such a >> non-straightforward way, rather than just >> >> EXPLAIN ANALYZE SELECT hierarchy.id >> FROM hierarchy >> JOIN fulltext ON fulltext.id = hierarchy.id >> WHERE (TO_TSQUERY('whatever') @@ nx_to_tsvector(fulltext.fulltext)) >> OR (TO_TSQUERY('whatever') @@ nx_to_tsvector(fulltext.fulltext_title)); > This query is written by a framework, also I thought that is a common > pattern that can be found in the documentation: > http://www.postgresql.org/docs/9.1/interactive/textsearch-controls.html Well, "common pattern" would be stretching it. Anyway I've concluded that this is in fact a planner bug. There will be a fix in 9.2, but I'm not going to take the risk of back-patching it, so you might want to think about changing that framework. regards, tom lane