Re: CTE optimization fence on the todo list?

On 5/1/15 5:39 PM, Tom Lane wrote:
> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
>> On 4/30/15 6:35 AM, Robert Haas wrote:
>>> On Thu, Apr 30, 2015 at 12:44 AM, Chris Rogers <teukros(at)gmail(dot)com> wrote:
>>>> I could really use the ability to optimize across CTE boundaries, and it
>>>> seems like a lot of other people could too.
>
>>> I'm not aware that anyone is working on it.
>
>> ISTR a comment to the effect of the SQL standard effectively requires
>> current behavior.
>
> I doubt that the spec says anything about it one way or another.
> However, there are a lot of cases where we definitely can't push
> constraints into a WITH:
> * Data-modifying query in the WITH, eg UPDATE RETURNING --- pushing
> outer constraints into it would change the set of rows updated.
> * Multiply-referenced WITH item (unless the outer query applies
> identical constraints to each reference, which seems silly and not
> worth the cycles to check for).
> * Recursive WITH item (well, maybe in some cases you could push down a
> clause and not change the results, but it seems very hard to analyze).
>
> So initially we just punted and didn't consider flattening WITHs at
> all. I'm not sure to what extent people are now expecting that behavior
> and would be annoyed if we changed it.

I use CTEs for both organizational purposes and as optimization barriers
(in preference to using temp tables, when possible).

I'd definitely prefer to keep the barriers in place by default, perhaps
with a keyword to allow optimization across boundaries when appropriate.
However, when I really need optimization across boundaries I just use a
subquery.

It doesn't seem like there's much to be gained in terms of net
functionality.

--
- David Steele
david(at)pgmasters(dot)net