Re: analyze after a database restore?

Tom Lane wrote:
> mlw <pgsql(at)mohawksoft(dot)com> writes:
>
>> From an "ease of use" perspective, it would be one less step.
>
>
> There is something to be said for that. As Rod notes, this has been
> considered and rejected before --- but I think that was back when
> ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on
> scanning the whole table. The current implementation is vastly
> lighter-weight than what we were looking at back then. Perhaps it's
> time to reconsider.
>
> Although I suggested doing a single unconditional ANALYZE at the end
> of the script, second thought leads me to think the per-table ANALYZE
> (probably issued right after the table's data-load step) might be
> better. That way you'd not have any side-effects on already-existing
> tables in the database you are loading to. OTOH, that way would leave
> the system catalogs un-analyzed, which might be bad.

Analyzing the impact a bit further, which has the more positives/negatives?

o Adding an ANALYZE (per table or not)

+ Restoring from backup and getting decent performance is more
reliably, especially for people new to PostgreSQL.

+ Reduces the number of things an admin has to remember or
checklist

- Could be redundant if the restore is part of a larger group of
restores

- Adds a bit more time to the restoration process (not much through)

o Leaving it the way it is (no ANALYZE included)

+ Stops the potential problem of it being redundant if the restore
is part of a larger group of restores

- The SysAdmin/DBA has to know/remember to do this. Especially bad
for people new to PostgreSQL

It seems like the maximum benefits would be on the side of including it,
and the only real negative from including an ANALYZE would be in the
extra time taken for it.

Of course we may be forgetting Other Big Consequences. ;-)

As a thought, we could introduce an option to explicitly not include it,
but that doesn't feel very worthwhile.

Regards and best wishes,

Justin Clift

> regards, tom lane

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi