"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> On Tue, May 16, 2000 at 01:41:48AM -0700, Dustin Sallings wrote:
>> I ran into this exact problem, and it was *very* significant on a
>> 15M row table I have. :) It didn't seem to want to use the index, even
>> freshly created, without a vacuum analyze.
> Hmm, if you drop the index, do a VACUUM ANALYZE, then create the index,
> it doesn't want to use it? That's be odd, since the statistics are
> only kept about the table relations, not the indices themselves.
Right, it doesn't matter whether the index existed at the time of the
VACUUM. But it does matter whether any VACUUM ANALYZE stats are
available or not...
> If you mean it won't use an fresh index on a fresh table, that's the
> expected behavior.
Just to clarify: it depends on the query, and 7.0's behavior is
different from prior versions. For an equality-type probe, like
"WHERE x = 33", I'd expect 7.0 to select an indexscan even without
stats. For an inequality like "WHERE x < 33", it will not select
an indexscan unless it has stats indicating that the inequality is
reasonably selective (less than about 10% of the table, I think).
For a range bound like "WHERE x > 22 AND x < 33", you will get an
indexscan without stats. Beyond that I'm not going to guess...
Prior versions had a bogus cost formula for indexscans that would
*drastically* underestimate the cost of an indexscan, so they tended
to pick an indexscan even where it wasn't justified. As it happened
they would pick an indexscan for the one-sided-inequality case even
with no stats available. In some cases that was good, in others
it'd lose big.
regards, tom lane