Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I wonder if we need to rethink, though.  We've gotten a number of
> reports of problems that were caused by single-use CTEs not being
> equivalent - in terms of performance - to a non-CTE formulation of the
> same idea.  It seems necessary for CTEs to behave this way when the
> subquery modifies data, and there are certainly situations where it
> could be desirable otherwise, but I'm starting to think that we
> shouldn't do it that way by default.  Perhaps we could let people say
> something like WITH x AS FENCE (...) when they want the fencing
> behavior, and otherwise assume they don't (but give it to them anyway
> if there's a data-modifying operation in there).

Well, in my case, I got performance thanks to CTEs *being*
optimization fences, letting me fiddle with query execution.

And I mean, going from half-hour queries to 1-minute queries.

It is certainly desirable to maintain the possibility to use fences when needed.