Re: Performance

On Tue, May 16, 2000 at 01:41:48AM -0700, Dustin Sallings wrote:
> On Mon, 15 May 2000, Charles Tassell 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. If
you mean it won't use an fresh index on a fresh table, that's the
expected behavior.

VACUUM ANALYZE [tablename] fills in the statistics in pg_statistic
that the optimizer uses when deciding between sequential and index
scans. VACUUM is currently functionally overloaded: a simple VACUUM
recovers storage space in the table files, VACUUM ANALYZE does that as
well as collect statistics. It sometimes feels quicker to do a simple
VACUUM, then a VACUUM ANALYZE.

However, vacuuming a large table with indices on it can take a _long_
time: I've seen the recommendation given to drop indices, vacuum,
then recreate the indices. This is mostly a problem for the space
recovery aspect of vacuum, since each updated or deleted tuple causes
a update/delete to the index, as space is compacted.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005