David Steele <da...@pgmasters.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers