Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZ49E-0005T2-Cr for pgsql-performance@postgresql.org; Thu, 15 Nov 2012 18:26:44 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZ49A-0000EE-76 for pgsql-performance@postgresql.org; Thu, 15 Nov 2012 18:26:43 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id qAFIQHgp026902 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Fri, 16 Nov 2012 07:26:18 +1300 Message-ID: <50A533C9.9000701@archidevsys.co.nz> Date: Fri, 16 Nov 2012 07:26:17 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2 MIME-Version: 1.0 To: Peter Geoghegan CC: Andrew Dunstan , Craig Ringer , Tom Lane , David Greco , "pgsql-performance@postgresql.org" Subject: Re: SOLVED - RE: Poor performance using CTE References: <187F6C10D2931A4386EE8E58E13857F61291D897@BY2PRD0811MB415.namprd08.prod.outlook.com> <20462.1352910543@sss.pgh.pa.us> <50A442A8.10803@2ndQuadrant.com> <50A4497D.80902@dunslane.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/200 X-Sequence-Number: 48566 On 15/11/12 15:03, Peter Geoghegan wrote: > On 15 November 2012 01:46, Andrew Dunstan wrote: >> It cuts both ways. I have used CTEs a LOT precisely because this behaviour >> lets me get better plans. Without that I'll be back to using the "offset 0" >> hack. > Is the "OFFSET 0" hack really so bad? We've been telling people to do > that for years, so it's already something that we've effectively > committed to. > How about adding the keywords FENCED and NOT FENCED to the SQL definition of CTE's - with FENCED being the default? Cheers, Gavin