Re: Identical query slower on 8.4 vs 8.3 - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Identical query slower on 8.4 vs 8.3
Date
Msg-id 4C3EE8A00200002500033637@gw.wicourts.gov
Whole thread Raw
In response to Re: Identical query slower on 8.4 vs 8.3  (Patrick Donlin <pdonlin@oaisd.org>)
List pgsql-performance
Patrick Donlin <pdonlin@oaisd.org> wrote:

> I have run vacuumdb --full --analyze, it
> actually runs as a nightly cron job.

That's usually not wise -- VACUUM FULL can cause index bloat, and is
not normally necessary.  If you have autovacuum turned on and run a
database vacuum each night, you can probably avoid ever running
VACUUM FULL.  A long-running transaction or mass deletes might still
make aggressive cleanup necessary on occasion, but you should
consider using CLUSTER instead of VACUUM FULL.  So, you should
probably change your crontab job to vacuum --all --analyze.

Also, after a bulk load of a database like this you might consider a
one-time VACUUM FREEZE of the database.  Without that there will
come a time when autovacuum will need to rewrite all rows from the
bulk load which haven't subsequently been modified, in order to
prevent transaction ID wraparound problems.

-Kevin

pgsql-performance by date:

Previous
From: Jon Nelson
Date:
Subject: Re: Identical query slower on 8.4 vs 8.3
Next
From: Merlin Moncure
Date:
Subject: Re: Identical query slower on 8.4 vs 8.3