Just a follow-up on this... The REINDEX took about 2 1/2 days. I didn't
gain much disk space back - a full backup takes just as long as before, but
the vacuum time dropped from 30 hours to 3 hours.
Wes
1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX
DATABASE.
On Feb 4, 2008, at 10:00 AM, Wes wrote:
Just a follow-up on this... The REINDEX took about 2 1/2 days. I
didn't
gain much disk space back - a full backup takes just as long as
before, but
the vacuum time dropped from 30 hours to 3 hours.
what you need to do is compare the relpages from
On 2/4/08 9:53 AM, Vivek Khera [EMAIL PROTECTED] wrote:
what you need to do is compare the relpages from the pg_class table
for that index before and after.
if you didn't get much disk space back, make sure you have no long
running transactions that may have kept some older files open.
I
Wes [EMAIL PROTECTED] writes:
The reason for the huge change in the vacuum time is that the indexes are
scanned in index order instead of disk order. I understand that is fixed in
8.2 or 8.3 (don't recall which I saw it in), but have never gotten
confirmation from anyone on that.
Yeah, 8.2.
Wes [EMAIL PROTECTED] writes:
I guess I should also turn off fsync for the duration.
It shouldn't make a big difference. fsync only happens at the end of a
transaction or at a checkpoint.
Since you're concerned with very long operations the slowdown at the end of
the transaction won't make a
On 1/25/08 5:40 AM, Gregory Stark [EMAIL PROTECTED] wrote:
It shouldn't make a big difference. fsync only happens at the end of a
transaction or at a checkpoint.
Since you're concerned with very long operations the slowdown at the end of
the transaction won't make a big difference.
Ok, I
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
Wes [EMAIL PROTECTED] writes:
I'm running 8.1.4. Assume I have exclusive access to the DB.
You really ought to update to 8.1.something-newer, but I digress.
1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX
DATABASE.
No, not if you don't mind exclusive locks.
On 1/24/08 12:48 PM, Tom Lane [EMAIL PROTECTED] wrote:
Wes [EMAIL PROTECTED] writes:
I'm running 8.1.4. Assume I have exclusive access to the DB.
You really ought to update to 8.1.something-newer, but I digress.
I was planning on upgrading to 8.x at the same time as this reindex - just
do