Re: Poor performance using CTE

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