Re: Unable to use index?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> > Unfortunately there's no easy way to fix this, because the statistics
> > information does not have information about the physical position of
> > tuples with certain vaules.
>
> Yeah, I think the real problem is that the desired rows are not
> uniformly distributed, and in fact there are none near the start of the
> table. We do not keep stats detailed enough to let the planner discover
> this, so it has to estimate on the assumption of uniform distribution.
> On that assumption, it looks like a seqscan will hit a suitable tuple
> quickly enough to be faster than using the index.

It seems like this is another scenario where it would be helpful to have the
optimizer keep track of not just the average expected cost but also the
worst-case cost. Since the index scan in this case might have a higher
expected cost but a lower worst-case cost than the sequential scan.

For some applications the best bet may in fact be to go with the plan expected
to be fastest. But for others it would be more important to go with the plan
that is least likely to perform badly, even if it means paying a performance
penalty to avoid the risk.

--
greg