Message-ID: <4BB20521.5000307@squeakycode.net>
Date: Tue, 30 Mar 2010 09:05:21 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3
MIME-Version: 1.0
To: Gnanakumar <gnanam@zoniac.com>, pgsql-performance@postgresql.org
Subject: Re: REINDEXing database-wide daily
References: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
In-Reply-To: <00f801cacfeb$e8b8f4a0$ba2adde0$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 3/30/2010 4:32 AM, Gnanakumar wrote:
> Hi,
>
> We're using PostgreSQL 8.2.
>
> I have a question in connection to this question posted by me earlier:
>
> http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php
>
> In our application, DML operations (INSERT/UPDATE/DELETE) are heavily
> performed in a day.
>
> I also read about pg_autovacuum & REINDEX at:
>
> http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html
>
> http://www.postgresql.org/docs/8.2/static/sql-reindex.html
>
> I do not want to run pg_autovacuum daemon on a busy hour.
>
> In case, if I can afford to take my database offline at low-usage time
> and perform REINDEX database-wide manually/linux cron, to boost up index
> performance, what is the community answer/suggestion on the following:
>
> 1. Is it a good idea to perform this on a daily basis?
>
> 2. Any implications of doing this on a daily basis?
>
> 3. Is there a way to find out bloated indexes?
>
> 4. Any other maintenance command, like ANALYZE, that has to be executed
> before/after REINDEX?
>
> 5. Is there a way to find out when REINDEX was last run on an
> INDEX/TABLE/DATABASE?
>
> NOTE: I've also seen from my past experience that REINDEX database-wide
> greatly improves performance of the application.
>


I could be way off base here, so I hope others will confirm/deny this: 
I think the more often you run vacuum, the less you notice it.  If you 
wait for too long then vacuum will have to work harder and you'll notice 
a speed decrease.  But many small vacuums which dont have as much work 
to do, you wont notice.

It could be, and I'm guessing again, because your database grew from 3 
to 30 gig (if I recall the numbers right), REINDEX had lots of affect. 
But if vacuum can keep up with space reuse, REINDEX may not be needed. 
(maybe a few weeks or once a month).

-Andy