Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbKMC-0000Po-Qo for pgsql-performance@postgresql.org; Thu, 22 Nov 2012 00:09:29 +0000 Received: from outmail149101.authsmtp.com ([62.13.149.101]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbKMA-0001Nq-Ay for pgsql-performance@postgresql.org; Thu, 22 Nov 2012 00:09:27 +0000 Received: from mail-c233.authsmtp.com (mail-c233.authsmtp.com [62.13.128.233]) by punt5.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id qAM095Dv064080; Thu, 22 Nov 2012 00:09:05 GMT Received: from ayaki.localdomain (124-148-90-153.dyn.iinet.net.au [124.148.90.153]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/) with ESMTP id qAM08ufF092834 (version=TLSv1/SSLv3 cipher=DHE-RSA-CAMELLIA256-SHA bits=256 verify=NO); Thu, 22 Nov 2012 00:08:59 GMT Message-ID: <50AD6D16.9040608@2ndQuadrant.com> Date: Thu, 22 Nov 2012 08:08:54 +0800 From: Craig Ringer Organization: 2nd Quadrant User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121029 Thunderbird/16.0.2 MIME-Version: 1.0 To: Gavin Flower 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> In-Reply-To: <50AD2BD1.8070206@archidevsys.co.nz> X-Enigmail-Version: 1.4.5 Content-Type: multipart/alternative; boundary="------------070107090800070607090503" X-Server-Quench: d2704035-3438-11e2-a49c-0025907707a1 X-AuthReport-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-AuthRoute: OCdxZQATClZeVg0b BQteCiN5VAwpPBRK HVkIKg5MJUcNSQVJ NksafxtFagdbY1ZH HGQLWlZEUF17WWp/ awgfbAdZfEtKVxtj UFZIR1FNFgBqBAMA SF9vLxl6MlMweHdy bUVmEHZcWEd6OxJ/ RU5QFW4DeGRka2gC UUENdR4FdAoZYx9F aFV2VHQKYWJWZzQC El17DAgcBxt4EgFg CjkKKkhaWUsREDkm QhkEFjhnAFYEQSp7 BCALQmv9 X-Authentic-SMTP: 61633235383639.1021:706 X-AuthFastPath: 0 (Was 255) X-AuthSMTP-Origin: 124.148.90.153/2525 X-AuthVirus-Status: No virus detected - but ensure you scan with your own anti-virus system. X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201211/247 X-Sequence-Number: 48613 This is a multi-part message in MIME format. --------------070107090800070607090503 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 --------------070107090800070607090503 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
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
--------------070107090800070607090503--