Re: REINDEX DATABASE

O Christopher Browne έγραψε στις Jul 26, 2005 :

> > Hello
> >
> > Would you like to advice to use REINDEX DATABASE on regular basis ?
> >
> > if (yes)
> > how it should be connected with VACUUM FULL ANALYZE which is run
> > regularly ? (reindex before vacuum or vacuum before reindex?)
> >
> > else
> > haw to determine _when_ to run REINDEX ?
>
> If you are doing ordinary VACUUM ANALYZE frequently enough, it
> shouldn't be necessary to either VACUUM FULL or REINDEX.
>
> Back in the 7.2 days, there were sorts of update patterns that would
> mandate reindexing every so often, as you could get cases where index
> pages would be very sparsely populated. That was alleviated in
> version 7.3, I believe, and was clearly evident in 7.4.
>
> You know you need to REINDEX if analysis of an index shows that it is
> sparsely populated. This generally shows up if you do an analyze on
> the table and find an index has more pages than tuples.
>
> But if you run VACUUM reasonably frequently, this shouldn't be
> necessary...

I vacuum analyze every hour, however after reindexdb
my (currently) 2.47 GB db is reduced to 2.37 GB,
thus helping both db-wise and freebsd_cache-wise boost performance.

All the above in 7.4.6.

I am not arguing that i have done detailed analysis of the
situation, i'm just saying that i have witnessed a performance gain after
running contrib/reindexdb every month (or so).

>

--
-Achilleus