Re: VACUUM and performance after pg_dumpall

"Thomas F. O'Connell" <tfo(at)monsterlabs(dot)com> writes:

> in upgrading recently from a pre-7.1 release to a 7.1.3 release for a fairly
> sizeable database, i noticed a severe decrease in performance that seemed to
> be related only to the new version of postgres that was running. everything
> else, including DBI, DBD::Pg, etc., remained the same.
>
>
> i had done a pg_dumpall to preserve the data.
>
> after a VACUUM, performance returned to within acceptable limits.
>
> does pg_dumpall and the subsequent restoration of data create a bunch of
> proverbial dust that needs to be vacuumed?

Possibly, also did you do VACUUM ANALYZE after restore? It's quite
possible that the statistics were all wrong after the restore, leading
to suboptimal query plans, which ANALYZE would have fixed.

VACUUM ANALYZE after a full restore is definitely recommended from my
experience.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863