Re: REINDEX DATABASE

achill(at)matrix(dot)gatewaynet(dot)com (Achilleus Mantzios) writes:
> 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).

That doesn't strike me as being a material improvement, and it comes
at a pretty high cost.

You can get a savings of about 4% of the space, but at the cost of
taking an appreciable outage during which the database is not usable.

I wouldn't expect the 4% savings in space to lead to a particularly
measurable improvement in performance, certainly not one worth the
outage.
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>