Re: analyze after a database restore?

D'Arcy J.M. Cain wrote:

>On Thursday 27 February 2003 13:12, mlw wrote:
>
>
>>Tom Lane wrote:
>>
>>
>>>A single ANALYZE at the end of the script would be sufficient. I'm not
>>>sure that pg_dump should do this automatically though. If you're not
>>>done restoring then it's mostly a waste of cycles, and how is pg_dump to
>>>know that?
>>>
>>>
>>[...]
>> From an "ease of use" perspective, it would be one less step.
>>
>>
>
>Why not have pg_dump emit a friendly reminder?
>
>
>
The reminder won't work, because the backup may be happening in an
automatic fashion, and anything but error messages will be lost. I
dislike having to have an "expert" be present at the database "restore"
phase of operation.

Suppose a company loses the PG admin and a reasonably experienced person
takes his or her place temporarily, This scenario happens all the time
with all sorts of projects. A reasonably experienced person will be able
to accomplish a DB restore but will probably not know about performing
an analyze. Under the pressure of restoring after a crash on a live
system, even a reasonably experienced PG admin may forget, hell I forgot
and I've been using PG since 1997.

The "correct" view of a database backup should be to include the
statistics of the database as it existed at the time backup, these
statistics are part of this state "snapshot" because the directly affect
the operation of the database. I do not want to evoke the name of
Larry's evil product, but it saves its statistics when the data is exported.

Short of including the relevant statistics, there should be an option on
pg_dump to emit an "ANALYZE;" at the end of a database dump. This will
allow a "knowledgeable" admin to selectively add the vacuum so that
someone possibly less qualified than he can do the restore.

Does anyone disagree that a query's "explain" should look the same or
better after a successful restore? From a product QA point of view, if a
valid backup set, when restored, does not recreate the system in a state
at least as efficient and workable as the system when it was backed up,
you did not have a successful restore. Any QA department would rate this
as a serious bug.

Are there any reasons why it should not be an option on pg_dump?