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: Daniel Farina <daniel(at)heroku(dot)com>, Greg Stark <stark(at)mit(dot)edu>,pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_upgrade and statistics
Date: 2012-03-13 19:30:19
Message-ID: 20120313193019.GD23967@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Mar 13, 2012 at 02:07:14PM -0500, Kevin Grittner wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > On Tue, Mar 13, 2012 at 01:18:58PM -0500, Kevin Grittner wrote:
>  
> >> cir=# analyze "CaseHist";
> >> ANALYZE
> >> Time: 143450.467 ms
> >> cir=# select relpages, reltuples from pg_class where relname =
> >> 'CaseHist';
> >>  relpages |  reltuples  
> >> ----------+-------------
> >>   3588659 | 2.12391e+08
> >> (1 row)
> >>  
> >> Either way, there are about 500 tables in the database.
> > 
> > That is 2.5 minutes.  How large is that database?
>  
> cir=# select pg_size_pretty(pg_database_size('cir'));
>  pg_size_pretty 
> ----------------
>  2563 GB
> (1 row)
>  
> In case you meant "How large is that table that took 2.5 minutes to
> analyze?":
>  
> cir=# select pg_size_pretty(pg_total_relation_size('"CaseHist"'));
>  pg_size_pretty 
> ----------------
>  44 GB
> (1 row)
>  
> I've started a database analyze, to see how long that takes.  Even
> if each table took 1/4 second (like on the small database) with over
> 500 user tables, plus the system tables, it'd be 15 minutes.  I'm
> guessing it'll run over an hour, but I haven't timed it lately, so
> -- we'll see.

OK, so a single 44GB tables took 2.5 minutes to analyze;  that is not
good.  It would require 11 such tables to reach 500GB (0.5 TB), and
would take 27 minutes.  The report I had was twice as long, but still in
the ballpark of "too long".  :-(

-- 
  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: Peter EisentrautDate: 2012-03-13 19:41:56
Subject: Re: about EncodeDateTime() arguments
Previous:From: Tom LaneDate: 2012-03-13 19:27:28
Subject: Re: Potential reference miscounts and segfaults in plpython.c

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