Hints (was Poor performance using CTE)

On Tue, Nov 20, 2012 at 3:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Craig Ringer <craig(at)2ndQuadrant(dot)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.

Craig James

>
> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>