Re: pg_upgrade and statistics

On Mon, Mar 12, 2012 at 06:38:30PM -0700, Daniel Farina wrote:
> You probably are going to ask: "why not just run ANALYZE and be done
> with it?" The reasons are:
>
> * ANALYZE can take a sufficiently long time on large databases that
> the downtime of switching versions is not attractive
>
> * If we don't run ANALYZE and have no old statistics, then the plans
> can be disastrously bad for the user
>
> * If we do run the ANALYZE statement on a user's behalf as part of
> the upgrade, any compatibility fixups that require an exclusive lock
> (such as some ALTER TABLE statements) would have to block on this
> relatively long ANALYZE. autoanalyze/autovacuum, by comparison, backs
> off frequently, so disaster is averted.
>
> If anyone has any insightful comments as to how to meet these
> requirements, I'd appreciate them, otherwise I can consider it an
> interesting area for improvement and will eat the ANALYZE and salt the
> documentation with caveats.

Copying the statistics from the old server is on the pg_upgrade TODO
list. I have avoided it because it will add an additional requirement
that will make pg_upgrade more fragile in case of major version changes.

Does anyone have a sense of how often we change the statistics data
between major versions? Ideally, pg_dump/pg_dumpall would add the
ability to dump statistics, and pg_upgrade could use that.

To answer your specific question, I think clearing the last analyzed
fields should cause autovacuum to run on analyze those tables. What I
don't know is whether not clearing the last vacuum datetime will cause
the table not to be analyzed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +