REINDEX on large DB vs. DROP INDEX/CREATE INDEX

From: Wes <wespvp(at)msg(dot)bt(dot)com>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: REINDEX on large DB vs. DROP INDEX/CREATE INDEX
Date: 2008-01-24 16:41:46
Message-ID: C3BE1BEA.6FD37%wespvp@msg.bt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Seems like it would be a common question, but I'm having problems finding an
answer in the archives on this...

I have a large database (now about 2 billion records), and about once a year
I have been dropping and recreating the indexes. Recreating the foreign key
constraints takes as long or longer than the index creation. Last year the
whole process took about 72 hours. This year the DB is bigger.

I'm running 8.1.4. Assume I have exclusive access to the DB.

1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX
DATABASE. I seem to remember DROP/CREATE had some advantages, but can't
find the information. Is there a performance hit with REINDEX during
creation because of locking issues?

2. I'm assuming REINDEX would avoid the time involved in recreating the
foreign key constraints?

3. With a REINDEX DATABASE, how can I monitor progress?

Wes

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2008-01-24 16:42:06 Re: Forgot to dump old data before re-installing machine
Previous Message Stefan Schwarzer 2008-01-24 16:41:20 Re: Forgot to dump old data before re-installing machine

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-01-24 18:48:30 Re: REINDEX on large DB vs. DROP INDEX/CREATE INDEX
Previous Message Jonah H. Harris 2008-01-24 03:35:22 Re: autonomous transactions