> > Systems which have optimizing planners can *never* be guaranteed to
> > generate the actual lowest-cost query plan. Any impression that Oracle,
> > for example, actually does do that may come from a lack of visibility
> > into the process, and a lack of forum for discussing these edge cases.
> And here in lies the crux of the problem. It isn't a purely logical/numerical
> formula. It is a probability estimate, nothing more. Currently, the statistics
> are used to calculate a probable best query, not a guaranteed best query. The
> presence of an index should be factored into the probability of a best query,
> should it not?
Well, it is already. I *think* what you are saying is that the numbers
should be adjusted to bias the choice toward an index; that *choosing*
the index even if the statistics (and hence the average result) will
produce a slower query is preferred to trying to choose the lowest cost
plan.
afaict we could benefit from more test cases run on more machines.
Perhaps we could also benefit from being able to (easily) run multiple
versions of plans, so folks can see whether the system is actually
choosing the correct one. But until we get better coverage of more test
cases on more platforms, adjusting the planner based on a small number
of "problem queries" is likely to lead to "problem queries" which
weren't problems before!
That is why Tom gets excited about "factor of 10 problems", but not
about factors of two. Because he knows that there are lots of queries
which happen to fall on the other side of the fence, misestimating the
costs by a factor of two *in the other direction*, which you will not
notice because that happens to choose the correct plan anyway.
- Thomas
