Thread: reindex in v8.2

reindex in v8.2

From
"Bill Willits"
Date:
Hello:
 
We are in the process of upgrading posgres from v7.3.9 to v8.2.4  In our current environment (v7.3), we run a reindex operation on all tables to recover space and improve performance (we have several tables with high insert/update load - no deletes).  Is it likely that the reindex will still be beneficial after the upgrade?  Or has the pg code changed such that this operation is obsolete?
 
Thanks in advance.
Bill Willits

Re: reindex in v8.2

From
Alvaro Herrera
Date:
Bill Willits wrote:
> Hello:
>
> We are in the process of upgrading posgres from v7.3.9 to v8.2.4  In
> our current environment (v7.3), we run a reindex operation on all
> tables to recover space and improve performance (we have several
> tables with high insert/update load - no deletes).  Is it likely that
> the reindex will still be beneficial after the upgrade?  Or has the pg
> code changed such that this operation is obsolete?

The index code was improved in 7.4, but some reindexing is still needed
in (rarer) corner cases.  Try getting rid of the reindex and track your
indexes sizes for a while.  If you don't notice a problem you can forget
it entirely.  Most people gets away without it.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: reindex in v8.2

From
Dan Harris
Date:
Bill Willits wrote:
> Hello:
>
> We are in the process of upgrading posgres from v7.3.9 to v8.2.4  In our
> current environment (v7.3), we run a reindex operation on all tables to
> recover space and improve performance (we have several tables with high
> insert/update load - no deletes).  Is it likely that the reindex will
> still be beneficial after the upgrade?  Or has the pg code changed such
> that this operation is obsolete?
>
> Thanks in advance.
> Bill Willits

When you reload your dump file into 8.2, the indexes should be created fresh and
AFAIK, a reindex at that point would not gain anything for you.