Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbG0h-0004BM-3N for pgsql-performance@postgresql.org; Wed, 21 Nov 2012 19:30:59 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbG0c-0001UA-95 for pgsql-performance@postgresql.org; Wed, 21 Nov 2012 19:30:58 +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 qALJUPHB032175 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 22 Nov 2012 08:30:26 +1300 Message-ID: <50AD2BD1.8070206@archidevsys.co.nz> Date: Thu, 22 Nov 2012 08:30:25 +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: Heikki Linnakangas CC: Andres Freund , Andrew Dunstan , 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> In-Reply-To: <50ACF991.3020603@vmware.com> Content-Type: multipart/alternative; boundary="------------090703030604050409020607" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/259 X-Sequence-Number: 48625 This is a multi-part message in MIME format. --------------090703030604050409020607 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 22/11/12 04:56, Heikki Linnakangas wrote: > On 21.11.2012 17:42, Gavin Flower wrote: >> On 22/11/12 04:32, Andres Freund wrote: >>> On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: >>>> I wasn't talking about removing it. My point was that if the >>>> optimization >>>> fence around CTEs is removed a lot of people will need to rework apps >>>> where >>>> they have used them for that purpose. And I continue to think that >>>> spelling >>>> it "OFFSET 0" is horribly obscure. >>> +1 > > FWIW, I'm happy with "OFFSET 0". Granted, it's pretty obscure, but > that's what we've historically recommended, and it's pretty ugly to > have to specify a fence like that in the first place. Whenever you > have to resort to it, you ought have a comment in the query explaining > why you need to force the planner like that, anyway. > >>> WITH foo AS (SELECT ...) (barrier=on|off)? >>> >>> 9.3 introduces the syntax, defaulting to on >>> 9.4 switches the default to off. >> >> WITH foo AS (SELECT ...) (fence=on|off)? >> >> WITH foo AS (SELECT ...) (optimisation_fence=on|off)? > > If we are to invent a new syntax for this, can we please come up with > something that's more widely applicable than just the WITH syntax. > Something that you could use to replace OFFSET 0 in a subquery, too. > > - Heikki 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! Cheers, Gavin --------------090703030604050409020607 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 22/11/12 04:56, Heikki Linnakangas wrote:
On 21.11.2012 17:42, Gavin Flower wrote:
On 22/11/12 04:32, Andres Freund wrote:
On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:
I wasn't talking about removing it. My point was that if the
optimization
fence around CTEs is removed a lot of people will need to rework apps
where
they have used them for that purpose. And I continue to think that
spelling
it "OFFSET 0" is horribly obscure.
+1

FWIW, I'm happy with "OFFSET 0". Granted, it's pretty obscure, but that's what we've historically recommended, and it's pretty ugly to have to specify a fence like that in the first place. Whenever you have to resort to it, you ought have a comment in the query explaining why you need to force the planner like that, anyway.

WITH foo AS (SELECT ...) (barrier=on|off)?

9.3 introduces the syntax, defaulting to on
9.4 switches the default to off.

WITH foo AS (SELECT ...) (fence=on|off)?

WITH foo AS (SELECT ...) (optimisation_fence=on|off)?

If we are to invent a new syntax for this, can we please come up with something that's more widely applicable than just the WITH syntax. Something that you could use to replace OFFSET 0 in a subquery, too.

- Heikki
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!

Cheers,
Gavin --------------090703030604050409020607--