Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbHnR-0000zw-V7 for pgsql-performance@postgresql.org; Wed, 21 Nov 2012 21:25:26 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbHnP-0007WS-B2 for pgsql-performance@postgresql.org; Wed, 21 Nov 2012 21:25:24 +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 qALLOt3o002734 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 22 Nov 2012 10:24:56 +1300 Message-ID: <50AD46A7.103@archidevsys.co.nz> Date: Thu, 22 Nov 2012 10:24:55 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/17.0 Thunderbird/17.0 MIME-Version: 1.0 To: Andrew Dunstan CC: Heikki Linnakangas , Andres Freund , Tom Lane , Craig Ringer , Claudio Freire , Merlin Moncure , David Greco , "pgsql-performance@postgresql.org" Subject: Re: Poor performance using CTE References: <50AC1472.3050607@2ndQuadrant.com> <18123.1353455585@sss.pgh.pa.us> <50ACD166.1000507@vmware.com> <50ACE970.7050701@dunslane.net> <3396.1353509947@sss.pgh.pa.us> <50ACF16C.2020902@dunslane.net> <20121121153209.GA1271@awork2.anarazel.de> <50ACF658.10902@archidevsys.co.nz> <50ACF991.3020603@vmware.com> <50AD2BD1.8070206@archidevsys.co.nz> <50AD2E88.4000902@dunslane.net> In-Reply-To: <50AD2E88.4000902@dunslane.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/261 X-Sequence-Number: 48627 On 22/11/12 08:42, Andrew Dunstan wrote: > > On 11/21/2012 02:30 PM, Gavin Flower wrote: >> WITH FENCE foo AS (SELECT ...) >> default? >> >> >> WITHOUT FENCE foo AS (SELECT ...) :-) >> Nah! >> I prefer this, but it is too specific to 'WITH', >> and very unSQL standardish! >> >> Alternatively one of the following >> >> 1. WITH UNFENCED foo AS (SELECT ...) >> 2. WITH NO FENCE foo AS (SELECT ...) >> 3. WITH NOT FENCE foo AS (SELECT ...) >> >> I loke the firsat variant, but the 3rd is >> most SQL standardish! >> > > As Tom (I think) pointed out, we should not have a syntax tied to CTEs. > > cheers > > andrew > If other SQL constructs have a optimisation fence, then the FENCE & NOT FENCE syntax can be used theire as well. So what am I missing? (obviously WITHOUT FENCE would not make sense elsewhere, but I wasn't really being serious when I suggested it!)