Re: Index Scans become Seq Scans after VACUUM ANALYSE

Tom Lane wrote:
>
> mlw <markw(at)mohawksoft(dot)com> writes:
> > For instance: say we have two similarly performing plans, close to one another,
> > say within 20%, one plan uses an index, and one does not. It is unlikely that
> > the index plan will perform substantially worse than the non-index plan, right?
>
> This seems to be the crux of the argument ... but I've really seen no
> evidence to suggest that it's true. The downside of improperly picking
> an indexscan plan is *not* any less than the downside of improperly
> picking a seqscan plan, in my experience.

Our experiences differ. I have fought with PostgreSQL on a number of occasions
when it would not use an index. Inevitably, I would have to set "enable_seqscan
= false." I don't like doing that because it forces the use of an index when it
doesn't make sense.

I don't think we will agree, we have seen different behaviors, and our
experiences seem to conflict. This however does not mean that either of us is
in error, it just may mean that we use data with very different
characteristics.

This thread is kind of frustrating for me because over the last couple years I
have seen this problem many times and the answer is always the same, "The
statistics need to be improved." Tom, you and I have gone back and forth about
this more than once.

I submit to you that the statistics will probably *never* be right. They will
always need improvement here and there. Perhaps instead of fighting over an
algorithmic solution, and forcing the users to work around problems with
choosing an index, should we not just allow the developer to place hints in the
SQL, as:

select /*+ INDEX(a_id, b_id) */ * from a, b where a.id = b.id;

That way if there is a performance issue with using or not using an index, the
developer can have better control over the evaluation of the query.