On 11/15/2012 12:29 AM, Tom Lane wrote: > David Greco <david_gr...@harte-hanks.com> writes: >> Thanks, that did the trick. Though I'm still not clear as to why. > PG treats WITH as an optimization fence --- the WITH query will be > executed pretty much as-is. It may be that Oracle flattens the query > somehow; though if you're using black-box functions in both cases, > it's not obvious where the optimizer could get any purchase that way. >
I was looking through the latest spec drafts I have access to and couldn't find any reference to Pg's optimisation-fence-for-CTEs behaviour being required by the standard, though I've repeatedly seen it said that there is such a requirement. Do you know where it's specified? All I can see is that the optimised result must have the same effect as the original. That'd mean that wCTEs and CTE terms that use VOLATILE functions or functions with side-effects couldn't be optimised into other queries. Simple CTEs could be, though, and there are times I've really wished I could use a CTE but I've had to use a set-returning subquery to get reasonable plans. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
