Re: analyze after a database restore?

On Thu, 2003-02-27 at 12:27, mlw wrote:
> I just dumped and restored a rather large database, I upgraded from
> 7.2.x to 7.3.x. When I went to test my application against the new
> database, it was dog slow. It had all the indexes, and looked fine.
>
> Then it dawned on me, Doh! ANALYZE!
>
> Should pg_dump appened an ANALYZE for each table?
>
> On small tables, this shouldn't take too long. On large tables, you're
> gonna have to do it anyway. I guess it could be an option as well.
>
> It just seems like on of the tasks that is required for a "restored"
> database to work properly, and as such, should probably be specified in
> the backup procedure.

It's been debated before whether pg_dump should append ANALYZE to the
end of the load script. It has always been determined that it shouldn't
(see archives for arguments).

However, an 'Auto-vacuum' process should watch stats and re-analyze the
table when the larger of 30% or 1000 rows has been affected (inserts, or
deletes mostly). That is probably a better solution overall.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc