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(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. +