From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: REINDEX DATABASE
Date: Tue, 26 Jul 2005 13:14:42 -0400
Message-ID: <60pst51ocd.fsf@dba2.int.libertyrms.com> (raw)
References: <m3vf2xk8md.fsf@mobile.int.cbbrowne.com>
<Pine.LNX.4.44.0507261701340.32187-100000@matrix.gatewaynet.com>
achill@matrix.gatewaynet.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@~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/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: REINDEX DATABASE
In-Reply-To: <60pst51ocd.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox