On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas <robertmhaas@gmail.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.