Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 93A5D9FB842 for ; Fri, 18 May 2007 20:26:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 83571-09 for ; Fri, 18 May 2007 20:26:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id CEA119FB7EC for ; Fri, 18 May 2007 20:26:11 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so265057and for ; Fri, 18 May 2007 16:26:10 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=oS7oaCArJWh+vsz0J2BQgxiwn7WcBLbYb92AivoFsAP4iRjxD25QWQZ0oneLnKz7cFXO1aX9ca/0T9BS/3PSEYcZyOeIS+8aI3zhxYqF1hGLRg/PA2GvVkUYHqHLu5RQIg/4onD5AYF22p7FEUZUXDlWeqZv+TzUMNE00ytc4Ac= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=cNcZbJrMna99THQIRERUWlEwMZmfZl0l4TeJ5lYVssnCWn1JGr1NVLYqP8JyTTJMhB8Q4hbMEiyrm0wXGT231Rqx/75hKWNJGOw0zs0nRVUbjC7jC92eg2UYz8ykvCmc33GOZ2glTieErjm9cfb2gDj+IXPBuyaPIwX8yc525TY= Received: by 10.100.120.5 with SMTP id s5mr1446669anc.1179530765996; Fri, 18 May 2007 16:26:05 -0700 (PDT) Received: by 10.100.6.20 with HTTP; Fri, 18 May 2007 16:26:05 -0700 (PDT) Message-ID: Date: Fri, 18 May 2007 16:26:05 -0700 From: "Y Sidhu" To: "Jim C. Nasby" Subject: Re: pg_stats how-to? Cc: pgsql-performance@postgresql.org In-Reply-To: <20070515233855.GC20707@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_82991_13739746.1179530765934" References: <20070514183615.GW69517@nasby.net> <20070514190748.GY69517@nasby.net> <20070514170959.a951eefc.wmoran@collaborativefusion.com> <1623.1179188449@sss.pgh.pa.us> <20070515233855.GC20707@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/358 X-Sequence-Number: 24748 ------=_Part_82991_13739746.1179530765934 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 5/15/07, Jim C. Nasby wrote: > > On Mon, May 14, 2007 at 08:20:49PM -0400, Tom Lane wrote: > > "Y Sidhu" 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 ------=_Part_82991_13739746.1179530765934 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_82991_13739746.1179530765934--