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

On 11/2/11 10:22 AM, Claudio Freire wrote:
> 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.
Same here. It was a case where I asked this group and was told that putting an "offset 0" fence in was probably the only way to solve it (once again reminding us that Postgres actually does have hints ... they're just called other things).
> It is certainly desirable to maintain the possibility to use fences when needed.
Indeed. Optimizer problems are usually fixed in due course, but these "fences" are invaluable when you have a dead web site that has to be fixed right now.

Craig