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
The upside is this would work for all releases of Postgres.
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
+ It's impossible for everything to be true. +
In response to
pgsql-hackers by date
|Next:||From: Daniel Farina||Date: 2012-03-14 01:41:48|
|Subject: Re: Chronic performance issue with Replication Failover and FSM.|
|Previous:||From: Bruce Momjian||Date: 2012-03-14 00:34:00|
|Subject: Re: pg_upgrade and statistics|