pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Y Sidhu <ysidhu@gmail.com>
To: Jim C. Nasby <decibel@decibel.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_stats how-to?
Date: Fri, 18 May 2007 16:26:05 -0700
Message-ID: <b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com> (raw)
In-Reply-To: <20070515233855.GC20707@nasby.net>
References: <b09064f30705140928x6b44f03dtdb93ebd18e7cdaca@mail.gmail.com>
	<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>

On 5/15/07, Jim C. Nasby <decibel@decibel.org> wrote:
>
> On Mon, May 14, 2007 at 08:20:49PM -0400, Tom Lane wrote:
> > "Y Sidhu" <ysidhu@gmail.com> writes:
> > > it may be table fragmentation. What kind of tables? We have 2 of them
> which
> > > experience lots of adds and deletes only. No updates. So a typical day
> > > experiences record adds a few dozen times on the order of 2.5 million.
> And
> > > deletes once daily. Each of these tables has about 3 btree indexes.
> >
> > With an arrangement like that you should vacuum once daily, shortly
> > after the deletes --- there's really no point in doing it on any other
> > schedule.  Note "shortly" not "immediately" --- you want to be sure that
> > any transactions old enough to see the deleted rows have ended.
>
> Also, think about ways you might avoid the deletes altogether. Could you
> do a truncate instead? Could you use partitioning? If you are using
> deletes then look at CLUSTERing the table some time after the deletes
> (but be aware that prior to 8.3 CLUSTER doesn't fully obey MVCC).
>
> 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


Yudhvir

view thread (18+ messages)  latest in thread

Message-ID: <b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com>
Permalink:  ../b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com/
Also on:    postgresql.org/message-id/b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: ysidhu@gmail.com, decibel@decibel.org
  Subject: Re: pg_stats how-to?
  In-Reply-To: <b09064f30705181626u46fd68fu6a98be475734c20f@mail.gmail.com>
  References: <b09064f30705140928x6b44f03dtdb93ebd18e7cdaca@mail.gmail.com> <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>

* 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