From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Craig Ringer <craig@2ndQuadrant.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Poor performance using CTE
Date: Thu, 22 Nov 2012 08:30:25 +1300
Message-ID: <50AD2BD1.8070206@archidevsys.co.nz> (raw)
In-Reply-To: <50ACF991.3020603@vmware.com>
References: <CAHyXU0xeEVX2P=HaNxPiKayEhkWp3GobgAXCQPrfsqbawXVV-w@mail.gmail.com>
<CAGTBQpZ_Za-XSXvmPUx3F7wgeX=HB1xUpDsn+1DXY3a87YJLBQ@mail.gmail.com>
<CAHyXU0z4oUss50vhH_wGZ-HERQe9wCMSf1nMTGNddXeCoKvVVA@mail.gmail.com>
<CAGTBQpbf9bdJ3gjcYdw2FuPmN=dqx+wE7opsCp515cEndpGDYw@mail.gmail.com>
<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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, hlinnakangas@vmware.com, andres@anarazel.de, andrew@dunslane.net, tgl@sss.pgh.pa.us, craig@2ndQuadrant.com, klaussfreire@gmail.com, mmoncure@gmail.com, David_Greco@harte-hanks.com
Subject: Re: Poor performance using CTE
In-Reply-To: <50AD2BD1.8070206@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox