REINDEXing database-wide daily

От: Gnanakumar
Тема: REINDEXing database-wide daily
Дата: ,
Msg-id: 00f801cacfeb$e8b8f4a0$ba2adde0$@com
(см: обсуждение, исходный текст)
Ответы: Re: REINDEXing database-wide daily  (Andy Colson)
Re: REINDEXing database-wide daily  ("Kevin Grittner")
Список: pgsql-performance

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.

 


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: REINDEXing database-wide daily
От: "Kevin Grittner"
Дата:
Сообщение: Re: experiments in query optimization