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

Re: pg_upgrade and statistics

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Daniel Farina <daniel(at)heroku(dot)com>,pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_upgrade and statistics
Date: 2012-03-13 15:14:33
Message-ID: 20120313151433.GL10441@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Mar 13, 2012 at 09:28:58AM -0500, Kevin Grittner wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>  
> > I just received a sobering blog comment stating that pg_upgrade
> > took 5 minutes on a 0.5TB database, but analyze took over an hour:
>  
> Yeah, we have had similar experiences.  Even if this can't be done
> for every release or for every data type, bringing over statistics
> from the old release as a starting point would really help minimize
> downtime on large databases.
>  
> Of course, release docs should indicate which statistics *won't* be
> coming across, and should probably recommend a database ANALYZE or
> VACUUM ANALYZE be done when possible.

Having a "works timetimes" behavior is really not good;  some users
aren't going to notice until it is too late that they need to run
analyze.  It is fine for hard-core folks like Kevin, but not for the
average user.

At best, pg_upgrade needs to copy over the statistics it can, and adjust
the system statistics to cause autoanalyze to run on those that can't be
migrated.  Frankly, as Tom stated, we have been adjusting the system
statistics collection so often that I have avoided hard-coding that
information into pg_upgrade --- it could potentially make pg_upgrade
less reliable, i.e. vacuumdb --all --analyze always works.

We might decide we want a consistently slow process rather than one that
is sometimes fast and sometimes slow.

As you can see, I am at a loss in how to improve this.

-- 
  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

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2012-03-13 15:16:16
Subject: Re: pg_upgrade and statistics
Previous:From: Alvaro HerreraDate: 2012-03-13 15:14:06
Subject: Re: Command Triggers, patch v11

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