Re: pg_upgrade and statistics

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@momjian.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-onlyPGOPTIONS='-c default_statistics_target=10'
vacuumdb--all --analyze-onlyvacuumdb --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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +