Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Taxcu-0005Dn-V5 for pgsql-performance@postgresql.org; Tue, 20 Nov 2012 23:53:13 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Taxcr-0000ik-EY for pgsql-performance@postgresql.org; Tue, 20 Nov 2012 23:53:12 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id qAKNr5YS018124; Tue, 20 Nov 2012 18:53:05 -0500 (EST) From: Tom Lane To: Craig Ringer cc: Claudio Freire , Merlin Moncure , David Greco , "pgsql-performance@postgresql.org" Subject: Re: Poor performance using CTE In-reply-to: <50AC1472.3050607@2ndQuadrant.com> References: <187F6C10D2931A4386EE8E58E13857F612917154@BLUPRD0811MB413.namprd08.prod.outlook.com> <50AC1472.3050607@2ndQuadrant.com> Comments: In-reply-to Craig Ringer message dated "Wed, 21 Nov 2012 07:38:26 +0800" Date: Tue, 20 Nov 2012 18:53:05 -0500 Message-ID: <18123.1353455585@sss.pgh.pa.us> X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201211/218 X-Sequence-Number: 48584 Craig Ringer writes: > On 11/21/2012 12:06 AM, Claudio Freire wrote: >> I meant for postgres to do automatically. Rewriting as a join wouldn't >> work as an optimization fence the way we're used to, but pushing >> constraints upwards can only help (especially if highly selective). > Because people are now used to using CTEs as query hints, it'd probably > cause performance regressions in working queries. Perhaps more > importantly, Pg would have to prove that doing so didn't change queries > that invoked functions with side-effects to avoid changing the results > of currently valid queries. We could trivially arrange to keep the current semantics if the CTE query contains any volatile functions (or of course if it's INSERT/UPDATE/DELETE). I think we'd also need to not optimize if it's invoked from more than one place in the outer query. I think the more interesting question is what cases wouldn't be covered by such a rule. Typically you need to use OFFSET 0 in situations where the planner has guessed wrong about costs or rowcounts, and I think people are likely using WITH for that as well. Should we be telling people that they ought to insert OFFSET 0 in WITH queries if they want to be sure there's an optimization fence? regards, tom lane