Thread: VACUUM and performance after pg_dumpall

what is it about pg_dumpall that necessitates an immediate VACUUM to
restore performance?

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?

-tfo

"Thomas F. O'Connell" <tfo@monsterlabs.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