Re: analyze after a database restore?

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: mlw <pgsql(at)mohawksoft(dot)com>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: analyze after a database restore?
Date: 2003-02-27 18:01:09
Message-ID: 1046368869.91396.80.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message mlw 2003-02-27 18:12:29 Re: analyze after a database restore?
Previous Message Rod Taylor 2003-02-27 17:55:49 Re: Can pessimistic locking be emulated?