Re: [PERFORM] Poor performance using CTE

[Gavin Flower]

On 22/11/12 04:32, Andres Freund wrote:

On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:

On 11/21/2012 09:59 AM, Tom Lane wrote:

Andrew Dunstan  writes:

If we're going to do it can we please come up with something more
intuitive and much, much more documented than "OFFSET 0"? And if/when we
do this we'll need to have big red warnings all over then release notes,
since a lot of people I know will need to do some extensive remediation
before moving to such a release.

The probability that we would actually *remove* that behavior of OFFSET
0 is not distinguishable from zero.  I'm not terribly excited about
having an alternate syntax to specify an optimization fence, but even
if we do create such a thing, there's no need to break the historical
usage.


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

WITH foo AS (SELECT ...) (barrier=on|off)?

9.3 introduces the syntax, defaulting to on
9.4 switches the default to off.

Greetings,

Andres Freund




WITH foo AS (SELECT ...) (fence=on|off)?


WITH foo AS (SELECT ...) (optimisation_fence=on|off)?




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Poor performance using CTE

[Gavin Flower]

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

Re: [PERFORM] Poor performance using CTE

[Gavin Flower]

On 22/11/12 08:42, Andrew Dunstan wrote:


On 11/21/2012 02:30 PM, Gavin Flower wrote:

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!



As Tom (I think) pointed out, we should not have a syntax tied to CTEs.

cheers

andrew

If other SQL constructs have a optimisation fence, then the FENCE & NOT 
FENCE syntax can be used theire as well.


So what am I missing? (obviously WITHOUT FENCE would not make sense 
elsewhere, but I wasn't really being serious when I suggested it!)




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hints (was Poor performance using CTE)

[Cédric Villemain]

Le mercredi 21 novembre 2012 17:34:02, Craig James a écrit :
> On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner  wrote:
> > It's a tough problem. Disguising and not documenting the available
> > optimizer hints leads to more reports on where the optimizer should
> > be smarter, and has spurred optimizer improvements. ...
> > Regarding the above-mentioned benefits we would stand to lose by
> > having clear and documented hints, perhaps we could occasionally
> > solicit input on where people are finding hints useful to get ideas
> > on where we might want to improve the optimizer. As far as worrying
> > about people using hints to force a plan which is sub-optimal --
> > isn't that getting into nanny mode a bit too much?
> 
> Toward that end, the hint documentation (which is almost always viewed as
> HTML) could be prefaced by a strong suggestion to post performance
> questions in this group first, with links to the "subscribe" page and the
> "how to report performance problems" FAQ. The hint documentation could even
> be minimalistic; suggest to developers that they should post their
> problematic queries here before resorting to hints.  That would give the
> experts an opportunity to provide the normal advice.  The correct hint
> syntax would be suggested only when all other avenues failed.

We have hooks in PostgreSQL. We already have at least one extension which is 
using that to change the planner behavior.

We can have a bit more hooks and try to improve the cost estimate, this part 
of the code is known to be built by reports and human estimations, also the 
9.2 version got heavy modifications in this area. 

Let the 'Hints' be inside an extension thus we are able to track them and fix 
the planner/costestimate issues.

I don't see why PostgreSQL needs 'Hints' *in-core*.
-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation


signature.asc
Description: This is a digitally signed message part.

Re: [PERFORM] Hints (was Poor performance using CTE)

[Gavin Flower]

On 22/11/12 06:28, Craig James wrote:



On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway > wrote:


On 11/21/2012 08:05 AM, Heikki Linnakangas wrote:
> Rather than telling the planner what to do or not to do, I'd
much rather
> have hints that give the planner more information about the
tables and
> quals involved in the query. A typical source of bad plans is
when the
> planner gets its cost estimates wrong. So rather than telling the
> planner to use a nested loop join for "a INNER JOIN b ON a.id
 = b.id ",
> the user could tell the planner that there are only 10 rows that
match
> the "a.id  = b.id " qual. That gives
the planner the information it needs
> to choose the right plan on its own. That kind of hints would be
much
> less implementation specific and much more likely to still be
useful, or
> at least not outright counter-productive, in a future version with a
> smarter planner.
>
> You could also attach that kind of hints to tables and columns,
which
> would be more portable and nicer than decorating all queries.

I like this idea, but also think that if we have a syntax to allow
hints, it would be nice to have a simple way to ignore all hints
(yes, I
suppose I'm suggesting yet another GUC). That way after sprinkling
your
SQL with hints, you could easily periodically (e.g. after a Postgres
upgrade) test what would happen if the hints were removed.


Or a three-way choice: Allow, ignore, or generate an error. That would 
allow developers to identify where hints are being used.


Craig


Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support



Or perhaps hints should have the pg version attached, so that they are 
automatically ignored when the pg version changed?  Problem may then 
become people reluctant to upgrade because their hints relate to a 
previous version!  Sigh...


Even requiring registration of hints and expiring them after a limited 
time period would not work - as people would simply automate the process 
of registration & application...



Cheers,
Gavin

Re: [PERFORM] Poor performance using CTE

[Gavin Flower]

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

Re: SOLVED - RE: [PERFORM] Poor performance using CTE

[Bruce Momjian]

On Tue, Nov 20, 2012 at 02:24:01PM -0600, Merlin Moncure wrote:
> On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson  wrote:
> > As can be seen by the current conversation, not everyone is convinced
> that CTEs ought to be an explicit optimization barrier
> 
> On Tue, Nov 20, 2012 at 1:26 PM, Claudio Freire  
> wrote:
> > It *could* just be a lack of imagination on my part. But if it were
> > not, then it'd be nice for it to be done automatically (since this
> > particular CTE behavior bites enough people already).
> 
> Sure.  I just find it personally hard to find a good demarcation line
> between A: "queries where pushing quals through are universally
> beneficial and wanted" and B: "queries where we are inserting an
> explicit materialization step to avoid planner issues", particularly
> where there is substantial overlap with between A and C: "queries that
> are written with a CTE and arguably shouldn't be".
> 
> Put another way, I find CTE to express: 'this then that' where joins
> express 'this with that'.  So current behavior is not surprising at
> all. All that said, there could be a narrow class of low hanging cases
> (such as the OP's) that could be sniped...I'm just skeptical.

Is thi
-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance