Craig Ringer <craig@xxxxxxxxxxxxxxx> writes: > On 11/21/2012 12:06 AM, Claudio Freire wrote: >> I meant for postgres to do automatically. Rewriting as a join wouldn't >> work as an optimization fence the way we're used to, but pushing >> constraints upwards can only help (especially if highly selective). > Because people are now used to using CTEs as query hints, it'd probably > cause performance regressions in working queries. Perhaps more > importantly, Pg would have to prove that doing so didn't change queries > that invoked functions with side-effects to avoid changing the results > of currently valid queries. We could trivially arrange to keep the current semantics if the CTE query contains any volatile functions (or of course if it's INSERT/UPDATE/DELETE). I think we'd also need to not optimize if it's invoked from more than one place in the outer query. I think the more interesting question is what cases wouldn't be covered by such a rule. Typically you need to use OFFSET 0 in situations where the planner has guessed wrong about costs or rowcounts, and I think people are likely using WITH for that as well. Should we be telling people that they ought to insert OFFSET 0 in WITH queries if they want to be sure there's an optimization fence? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
