Re: CTE optimization fence on the todo list?

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.

regards, tom lane