pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim C. Nasby <decibel@decibel.org>
To: Y Sidhu <ysidhu@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_stats how-to?
Date: Mon, 21 May 2007 12:24:06 -0500
Message-ID: <20070521172405.GF62346@nasby.net> (raw)
In-Reply-To: <b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com>
References: <b09064f30705141109m7c2f57an802f8fe0485a65bb@mail.gmail.com>
	<20070514183615.GW69517@nasby.net>
	<b09064f30705141202o2cd183d7ta6914da150f3f503@mail.gmail.com>
	<20070514190748.GY69517@nasby.net>
	<b09064f30705141353p4f502135s5409c8c31c20718e@mail.gmail.com>
	<20070514170959.a951eefc.wmoran@collaborativefusion.com>
	<b09064f30705141515l5c7ec56n215dbd85f93bf6c2@mail.gmail.com>
	<1623.1179188449@sss.pgh.pa.us>
	<20070515233855.GC20707@nasby.net>
	<b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com>

On Fri, May 18, 2007 at 04:26:05PM -0700, Y Sidhu wrote:
> >To answer your original question, a way to take a look at how bloated
> >your tables are would be to ANALYZE, divide reltuples by relpages from
> >pg_class (gives how many rows per page you have) and compare that to 8k
> >/ average row size. The average row size for table rows would be the sum
> >of avg_width from pg_stats for the table + 24 bytes overhead. For
> >indexes, it would be the sum of avg_width for all fields in the index
> >plus some overhead (8 bytes, I think).
> >
> >An even simpler alternative would be to install contrib/pgstattuple and
> >use the pgstattuple function, though IIRC that does read the entire
> >relation from disk.
> >--
> >Jim Nasby                                      decibel@decibel.org
> >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
> 
> Here are my results:
> 
> a.  SELECT sum(reltuples)/sum(relpages) as rows_per_page FROM pg_class;
> 
> I get 66
> 
> b.  SELECT (8000/(sum(avg_width)+24)) as  table_stat FROM pg_stats;
> 
> I get 1

And those results will be completely meaningless because they're
covering the entire database (catalog tables included). You need to
compare the two numbers on a table-by-table basis, and you'd also have
to ignore any small tables (say smaller than 1000 pages). Also, a page
is 8192 bytes in size (though granted there's a page header that's
something like 16 bytes).
-- 
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



view thread (18+ messages)  latest in thread

Message-ID: <20070521172405.GF62346@nasby.net>
Permalink:  ../20070521172405.GF62346@nasby.net/
Also on:    postgresql.org/message-id/20070521172405.GF62346@nasby.net

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: decibel@decibel.org, ysidhu@gmail.com
  Subject: Re: pg_stats how-to?
  In-Reply-To: <20070521172405.GF62346@nasby.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox