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
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 |
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 |