Skip site navigation (1) Skip section navigation (2)

Re: pg_upgrade and statistics

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Daniel Farina <daniel(at)heroku(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_upgrade and statistics
Date: 2012-03-13 03:10:37
Message-ID: 20120313031037.GH10441@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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. +

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2012-03-13 03:21:26
Subject: Re: query planner does not canonicalize infix operators
Previous:From: Tom LaneDate: 2012-03-13 03:03:42
Subject: Re: xlog location arithmetic

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group