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.
