Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 454799FB43C for ; Mon, 21 May 2007 14:24:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 53806-01 for ; Mon, 21 May 2007 14:24:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id B5CB69FB3EE for ; Mon, 21 May 2007 14:24:08 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 7301F5645B; Mon, 21 May 2007 12:24:07 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 21 May 2007 12:24:06 -0500 Date: Mon, 21 May 2007 12:24:06 -0500 From: "Jim C. Nasby" To: Y Sidhu Cc: pgsql-performance@postgresql.org Subject: Re: pg_stats how-to? Message-ID: <20070521172405.GF62346@nasby.net> References: <20070514183615.GW69517@nasby.net> <20070514190748.GY69517@nasby.net> <20070514170959.a951eefc.wmoran@collaborativefusion.com> <1623.1179188449@sss.pgh.pa.us> <20070515233855.GC20707@nasby.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070521:ysidhu@gmail.com::MUZRJpeFgnfwqZ8V:00t3t X-Hashcash: 1:20:070521:pgsql-performance@postgresql.org::5g2W8x/nd+UeR5Pw:00000 0000000000000000000000000B+0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/394 X-Sequence-Number: 24784 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)