Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbKpC-0008RQ-82 for pgsql-performance@postgresql.org; Thu, 22 Nov 2012 00:39:26 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbKp8-0001v9-FU for pgsql-performance@postgresql.org; Thu, 22 Nov 2012 00:39: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 qAM0cs5Z008391 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 22 Nov 2012 13:38:55 +1300 Message-ID: <50AD741E.1060100@archidevsys.co.nz> Date: Thu, 22 Nov 2012 13:38:54 +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: Craig Ringer CC: Heikki Linnakangas , Andres Freund , Andrew Dunstan , Tom Lane , 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> <50AD6D16.9040608@2ndQuadrant.com> In-Reply-To: <50AD6D16.9040608@2ndQuadrant.com> Content-Type: multipart/alternative; boundary="------------000901020402040504000709" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/262 X-Sequence-Number: 48628 This is a multi-part message in MIME format. --------------000901020402040504000709 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 22/11/12 13:08, Craig Ringer wrote: > On 11/22/2012 03:30 AM, Gavin Flower wrote: >> 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? > That doesn't bind tightly enough to a specific CTE term. Consider: > > WITH > FENCE foo AS (SELECT ...), > bar AS (SELECT ...) > SELECT * FROM bar; > > Are we fencing just foo? Or all expressions? > > > -- > Craig Ringerhttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services Are we fencing or fooing??? :-) Hmm... How about: (a) If we have lots of WITH SELECTS which mostly have one specific type of fencing, then we could specify the common fence value after the WITH and the exceptions after the AS: WITH FENCE foo AS (SELECT ...), bar AS NOT FENCE (SELECT ...). baz AS (SELECT ...) SELECT * FROM bar; alternatively: WITH NOT FENCE foo AS FENCE (SELECT ...), bar AS (SELECT ...). baz AS FENCE (SELECT ...) SELECT * FROM bar; (b) If we retain that FENCE is the default, then it would be simpler just to just allow a FENCE clause after the AS keyword. WITH foo AS (SELECT ...), bar AS NOT FENCE (SELECT ...). baz AS (SELECT ...) SELECT * FROM bar; Obviously even for (a), we have to have one value of the FENCE clause as the default. Either make the default FENCE, as now - or NOT FENCE if that is seen to be a better default, especially if that is easier for people coming from Oracle. I suspect most people are blissfully unaware of CTE's being fenced, or at least not really sure what it means. So I suspect NOT FENCE would be the better default. Alternative spellings might be better such as: FENCED / NOT FENCED or FENCED / UNFENCED Cheers, Gavin --------------000901020402040504000709 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 22/11/12 13:08, Craig Ringer wrote:
On 11/22/2012 03:30 AM, Gavin Flower wrote:
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?
That doesn't bind tightly enough to a specific CTE term. Consider:

WITH
  FENCE foo AS (SELECT ...),
  bar AS (SELECT ...)
SELECT * FROM bar;

Are we fencing just foo? Or all expressions? 


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
Are we fencing or fooing??? :-)

Hmm...
How about:

(a) If we have lots of WITH SELECTS which mostly have one specific type of fencing, then we could specify the common fence value after the WITH and the exceptions after the AS:

WITH FENCE
  foo AS (SELECT ...),
  bar AS NOT FENCE (SELECT ...).
  baz AS (SELECT ...)
SELECT * FROM bar;

alternatively:
WITH NOT FENCE
  foo AS FENCE (SELECT ...),
  bar AS (SELECT ...).
  baz AS FENCE (SELECT ...)
SELECT * FROM bar;

(b) If we retain that FENCE is the default, then it would be simpler just to just allow a FENCE clause after the AS keyword.

WITH
  foo AS (SELECT ...),
  bar AS NOT FENCE (SELECT ...).
  baz AS (SELECT ...)
SELECT * FROM bar;

Obviously even for (a), we have to have one value of the FENCE clause as the default.  Either make the default FENCE, as now - or NOT FENCE if that is seen to be a better default, especially if that is easier for people coming from Oracle.

I suspect most people are blissfully unaware of CTE's being fenced, or at least not really sure what it means. So I suspect NOT FENCE would be the better default.

Alternative spellings might be better such as:
FENCED / NOT FENCED
or
FENCED / UNFENCED


Cheers,
Gavin


--------------000901020402040504000709--