Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tc3VQ-0003AJ-G5 for pgsql-performance@postgresql.org; Sat, 24 Nov 2012 00:22:00 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tc3VM-00017a-EZ for pgsql-performance@postgresql.org; Sat, 24 Nov 2012 00:21:59 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1Tc3VF-0006HN-LY; Fri, 23 Nov 2012 19:21:49 -0500 Date: Fri, 23 Nov 2012 19:21:49 -0500 From: Bruce Momjian To: Merlin Moncure Cc: Claudio Freire , Jon Nelson , Peter Geoghegan , Andrew Dunstan , Craig Ringer , Tom Lane , David Greco , "pgsql-performance@postgresql.org" Subject: Re: SOLVED - RE: Poor performance using CTE Message-ID: <20121124002149.GA1594@momjian.us> References: <187F6C10D2931A4386EE8E58E13857F61291D897@BY2PRD0811MB415.namprd08.prod.outlook.com> <20462.1352910543@sss.pgh.pa.us> <50A442A8.10803@2ndQuadrant.com> <50A4497D.80902@dunslane.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201211/264 X-Sequence-Number: 48630 On Tue, Nov 20, 2012 at 02:24:01PM -0600, Merlin Moncure wrote: > On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson wrote: > > As can be seen by the current conversation, not everyone is convinced > that CTEs ought to be an explicit optimization barrier > > On Tue, Nov 20, 2012 at 1:26 PM, Claudio Freire wrote: > > It *could* just be a lack of imagination on my part. But if it were > > not, then it'd be nice for it to be done automatically (since this > > particular CTE behavior bites enough people already). > > Sure. I just find it personally hard to find a good demarcation line > between A: "queries where pushing quals through are universally > beneficial and wanted" and B: "queries where we are inserting an > explicit materialization step to avoid planner issues", particularly > where there is substantial overlap with between A and C: "queries that > are written with a CTE and arguably shouldn't be". > > Put another way, I find CTE to express: 'this then that' where joins > express 'this with that'. So current behavior is not surprising at > all. All that said, there could be a narrow class of low hanging cases > (such as the OP's) that could be sniped...I'm just skeptical. Is thi -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +