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

From Tom Lane
Subject Re: Poor performance using CTE
Date
Msg-id 18123.1353455585@sss.pgh.pa.us
Whole thread Raw
In response to Re: Poor performance using CTE  (Craig Ringer <craig@2ndQuadrant.com>)
Responses Re: Poor performance using CTE
List pgsql-performance
Craig Ringer <craig@2ndQuadrant.com> writes:
> On 11/21/2012 12:06 AM, Claudio Freire wrote:
>> I meant for postgres to do automatically. Rewriting as a join wouldn't
>> work as an optimization fence the way we're used to, but pushing
>> constraints upwards can only help (especially if highly selective).

> Because people are now used to using CTEs as query hints, it'd probably
> cause performance regressions in working queries. Perhaps more
> importantly, Pg would have to prove that doing so didn't change queries
> that invoked functions with side-effects to avoid changing the results
> of currently valid queries.

We could trivially arrange to keep the current semantics if the CTE
query contains any volatile functions (or of course if it's
INSERT/UPDATE/DELETE).  I think we'd also need to not optimize if
it's invoked from more than one place in the outer query.

I think the more interesting question is what cases wouldn't be covered
by such a rule.  Typically you need to use OFFSET 0 in situations where
the planner has guessed wrong about costs or rowcounts, and I think
people are likely using WITH for that as well.  Should we be telling
people that they ought to insert OFFSET 0 in WITH queries if they want
to be sure there's an optimization fence?

            regards, tom lane


pgsql-performance by date:

Previous
From: Claudio Freire
Date:
Subject: Re: Poor performance using CTE
Next
From: Craig James
Date:
Subject: Hints (was Poor performance using CTE)