On 11/2/11 10:22 AM, Claudio Freire wrote:
On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas<robertmhaas@xxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
