On Tue, Nov 20, 2012 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Craig Ringer <craig@2ndQuadrant.com> 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?
I'm probably beating a dead horse ... but isn't this just a hint? Except that it's worse than a hint, because it's a hint in disguise and is undocumented. As far as I can tell, there's no use for "OFFSET 0" except to act as an optimizer fence.
It's clearly an important need, given the nature of the dialog above (and many others that have passed through this mailing list).
Why not make an explicit hint syntax and document it? I've still don't understand why "hint" is a dirty word in Postgres. There are a half-dozen or so ways in common use to circumvent or correct sub-optimal plans.
