Re: Index Scans become Seq Scans after VACUUM ANALYSE

On Wed, Apr 17, 2002 at 04:28:03PM -0400, mlw wrote:

> Oracle has a cost based optimizer, and they allow you to override
> it, offer hints as to what it should do, or use the rules based
> optimizer. They know that a cost based optimizer can not generate
> the best query all the time.

Oracle's the wrong competition to cite here. IBM's optimiser and
planner in DB2 is rather difficult to override; IBM actively
discourages doing so. That's because it's the best there is. It's
_far_ better than Oracle's, and has ever been so. It just about
_always_ gets it right. Without presuming to speak for him, I'd
suggest that Tom probably wants to get the planner to that level,
rather than adding band-aids.

> I say it is obvious it can never know enough, since statistics are

Enough for what? The idea is that the statistics will get you the
best-bet plan. You're trying to redefine what the best bet is; and
Tom and others have suggested that a simple rule of thumb, "All else
being more or less equal, prefer an index," is not a good one.

> Now, given the choice of the two strategies on a table, both pretty
> close to one another, the risk of poor performance for using the
> index scan is minimal based on the statistics, but the risk of poor
> performance for using the sequential scan is quite high on a large
> table.

I thought that's what the various cost estimates were there to cover.
If this is all you're saying, then the feature is already there.

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110