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: Peter Eisentraut <peter_e(at)gmx(dot)net>, Daniel Farina <daniel(at)heroku(dot)com>,Greg Stark <stark(at)mit(dot)edu>,pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: pg_upgrade and statistics
Date: 2012-03-14 01:15:52
Message-ID: 20120314011552.GC9132@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Mar 13, 2012 at 08:22:51PM -0400, Bruce Momjian wrote:
> On Tue, Mar 13, 2012 at 05:33:29PM -0500, Kevin Grittner wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >  
> > > What is the target=10 duration?  I think 10 is as low as we can
> > > acceptably recommend.  Should we recommend they run vacuumdb
> > > twice, once with default_statistics_target = 4, and another with
> > > the default?
> >  
> > Here are the results at various settings.
> >  
> > 1   :  172198.892 ms
> > 2   :  295536.814 ms
> > 4   :  474319.826 ms
> > 10  :  750458.312 ms
> > 100 :  3433794.609 ms
> 
> Thanks, good numbers to know.

OK, new crazy idea.  Kevin has shown that his database can get a single
bucket in 2.8 minutes.  What if we have pg_upgrade create a vacuumdb
script that generates increasingly accurate statistics, e.g. it runs for
default_statistics_target values of 1, 10, and default (100).  That
would give basic statistics quickly (2.8 minutes), and full statistics
in an hour, for Kevin's database.

	PGOPTIONS='-c default_statistics_target=1' vacuumdb --all --analyze-only
	PGOPTIONS='-c default_statistics_target=10' vacuumdb --all --analyze-only
	vacuumdb --all --analyze-only

The only problem I see is that users who use non-default statistics per
table would not be affected by the increasing default_statistics_target
values.

The upside is this would work for all releases of Postgres.

-- 
  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: Daniel FarinaDate: 2012-03-14 01:41:48
Subject: Re: Chronic performance issue with Replication Failover and FSM.
Previous:From: Bruce MomjianDate: 2012-03-14 00:34:00
Subject: Re: pg_upgrade and statistics

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