Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 1EDE46325AC for ; Tue, 30 Mar 2010 06:32:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54390-09 for ; Tue, 30 Mar 2010 09:32:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88]) by mail.postgresql.org (Postfix) with ESMTP id 5C42062DBD0 for ; Tue, 30 Mar 2010 06:32:32 -0300 (ADT) Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in [122.165.58.186] (may be forged)) (authenticated bits=0) by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2U9WRok016902 for ; Tue, 30 Mar 2010 04:32:29 -0500 Reply-To: From: "Gnanakumar" To: Subject: REINDEXing database-wide daily Date: Tue, 30 Mar 2010 15:02:23 +0530 Organization: Zoniac Software Private Limited Message-ID: <00f801cacfeb$e8b8f4a0$ba2adde0$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00F9_01CAD01A.027130A0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcrP6+Z1Wjal5+jzQFm9DfnI/NXtxw== Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/381 X-Sequence-Number: 38206 This is a multipart message in MIME format. ------=_NextPart_000_00F9_01CAD01A.027130A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_00F9_01CAD01A.027130A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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.p= hp

 

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.ht= ml

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.

 

------=_NextPart_000_00F9_01CAD01A.027130A0--