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,
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 op
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
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 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.
"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 mak
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
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.
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 long
