Re: Index Scans become Seq Scans after VACUUM ANALYSE

At 07:15 AM 4/17/02 -0700, Thomas Lockhart wrote:

>Tom is a mathematician by training, and is trying to balance the
>optimizer decisions right on the transition between best and next-best
>possibility. Biasing it to one decision or another when all of his test
>cases clearly show the *other* choice would be better puts it in the
>realm of an arbitrary choice *not* supported by the data!

I do agree with Mark that most cases an index is added to increase 
performance, if the index is used but doesn't improve performance DBAs will 
drop them to improve insert/update performance (or they will leave it for 
when the table grows big). Thus the bias should be towards using the index 
(which may already be the case for most situations).

My guess on why one hears many complaints about Postgresql not using the 
index is because when things work fine you don't hear complaints :). I also 
suspect when Postgresql wrongly uses the index instead of sequential scans 
not as many people bother dropping the index to test for a performance 
increase.

But it may well be that the cost of wrongly using the index is typically 
not as high as wrongly doing a sequential scan, and that is why people 
don't get desperate enough to drop the index and grumble about it.

Weighing these factors, perhaps once we get one or two complaining about 
postgresql using an index vs 20 complaining about not using an index, then 
the optimizer values have reached a good compromise :). But maybe the ratio 
should be 1 vs 100?

What do you think? ;).

Cheerio,
Link.