On Fri, 2003-10-31 at 12:25, Nitz wrote:
> You were right, the volume of the data changes the optimizer's
> willingness to use indexes.
AFAICS, the optimizer seems to be making exactly the right guesses for
the production data -- i.e. there's no problem/bug.
> Another funny thing though... I actually did two tests. One with the
> actual production
> data and the other one using only a slice of that (just 1000 rows). On
> the second smaller
> test the optimizer insisted to go with the SeqScan eventhou IndexScan
> (after forcing it)
> turned out to be about 10 times faster. Here are the both traces...
Well, the principle the optimizer is using (that as the size of the
table shrinks, the usefulness of an index scan shrinks) is correct, but
it's just being a little too enthusiastic about switching to a seqscan.
Try decreasing the random_page_cost GUC variable until the optimizer
starts to use index scans more appropriately for the small table.
-Neil