Re: generalizing the planner knobs

On Thursday 2005-12-01 19:01, Gregory Maxwell wrote:
> On 12/1/05, Pollard, Mike <mpollard(at)cincom(dot)com> wrote:
> > Optimizer hints were added because some databases just don't have a very
> > smart optimizer. But you are much better served tracking down cases in
> > which the optimizer makes a bad choice, and teaching the optimizer how
> > to make a better one. That way, all users get the benefit of the fix.
> > Remember, the purpose of SQL is to isolate the end user from having to
> > care about how the data is retrieved; that is the RDBMS' problem. (the
> > other thing forgotten was that it was supposed to be a natural language.
> > NVL. Bah.)
>
> The flipside there is that a good set of hinting options may increase
> the amount of detailed feedback we get from users on improvements
> needed in the optimizer. The current knobs are pretty blunt and don't
> do as much as I'd like when trying to track down exactly where the
> optimiser has gone wrong.
>
> If we'd really like to avoid people using the knobs to rig queries,
> how about making them only work with explain analyze, useful for
> debugging but not so useful for actual queries.

I'm all in favor of sticking to the declarative language ideal.

Also, I'm much in favor of protecting people from themselves.

On the other hand, if folks insist on engaging in extreme sports (like second
guessing the optimizer) I'm against regulating their freedom. I think
exposing planner variables would be a good thing, on net. Naturally, you
would warn everyone not to touch them. (Safety and freedom are both
necessary.)

If you can play with the knobs, you should let them be used to return real
result sets. That way, when you get feedback, you will be able to tell if
the cost estimator is "broken". Just returning a modified plan won't
challenge costing assumptions.