Re: CTE optimization fence on the todo list?

David Steele <david(at)pgmasters(dot)net> writes:
> On 5/1/15 5:39 PM, Tom Lane wrote:
>> 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.

FWIW, a bit of thought suggests that it would not take a lot of code to
handle this: you'd just have to check the conditions mentioned above
and then convert the RTE_CTE item into an RTE_SUBQUERY, much like
inline_set_returning_functions does with RTE_FUNCTION items.

Assuming that that sketch is accurate, it would take more code to provide
a new user-visible knob to enable/disable the behavior than it would to
implement the optimization, which makes me pretty much -1 on providing
such a knob. We should either do it or not. If we do, people who want
optimization fences should use the traditional "OFFSET 0" hack.

(A possible compromise position would be to offer a new GUC to
enable/disable the optimization globally; that would add only a reasonably
small amount of control code, and people who were afraid of the change
breaking their apps would probably want a global disable anyway.)

regards, tom lane