Re: Poor performance using CTE - Mailing list pgsql-performance

From Gavin Flower
Subject Re: Poor performance using CTE
Date
Msg-id 50AD2BD1.8070206@archidevsys.co.nz
Whole thread Raw
In response to Re: Poor performance using CTE  (Heikki Linnakangas <hlinnakangas@vmware.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Gavin Flower
Date:
Subject: Re: Poor performance using CTE
Next
From: Gavin Flower
Date:
Subject: Re: Hints (was Poor performance using CTE)