From: Gnanakumar <gnanam@zoniac.com>
To: pgsql-performance@postgresql.org
Subject: REINDEXing database-wide daily
Date: Tue, 30 Mar 2010 15:02:23 +0530
Message-ID: <00f801cacfeb$e8b8f4a0$ba2adde0$@com> (raw)
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.htmlhttp://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.
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-performance@postgresql.org
Cc: gnanam@zoniac.com
Subject: Re: REINDEXing database-wide daily
In-Reply-To: <00f801cacfeb$e8b8f4a0$ba2adde0$@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