X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DAF2252859 for ; Thu, 26 May 2005 23:48:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97818-04 for ; Fri, 27 May 2005 02:48:10 +0000 (GMT) Received: from mr2.surnet.cl (smtp2.surnet.cl [216.155.73.163]) by svr1.postgresql.org (Postfix) with ESMTP id 54D1252851 for ; Thu, 26 May 2005 23:48:08 -0300 (ADT) Received: from smtp2.surnet.cl (216.155.73.169) by mr2.surnet.cl (7.0.031.3) id 4259ADFA00A2815F; Thu, 26 May 2005 22:48:35 -0400 Received: from smtp2.surnet.cl (mr2.surnet.cl []) by mr2.surnet.cl ([216.155.73.169]); Fri, 27 May 2005 02:48:35 +0000 Received: from cluster.surnet.cl (216.155.73.164) by smtp2.surnet.cl (7.0.031.3) id 4259ADE30061955F; Thu, 26 May 2005 22:48:35 -0400 Received: from localhost (200.85.219.244) by cluster.surnet.cl (7.0.024) id 4276578F003662C2; Thu, 26 May 2005 22:48:10 -0400 Received: by localhost (Postfix, from userid 1000) id 84B91C10966; Thu, 26 May 2005 22:48:12 -0400 (CLT) Date: Thu, 26 May 2005 22:48:12 -0400 From: Alvaro Herrera To: Steve Lane Cc: pgsql-admin@postgresql.org Subject: Re: Convenient dump of table and object size in PG 7.4 Message-ID: <20050527024812.GB18417@surnet.cl> References: <4294CFC0.6040901@ca.afilias.info> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.689 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200505/280 X-Sequence-Number: 17709 On Thu, May 26, 2005 at 09:39:26PM -0500, Steve Lane wrote: > Attached is a view I wrote for postgres 7.4 that seems to provide pretty > accurate reporting of table sizes. For each non-system table in a database > it shows base table size, size of any related TOAST data, and aggregate size > of indexes. > > The code is somewhat ugly in that it contains a bunch of CASE statements the > sole point of which is to transform the occasional NULL into a 0. > > Object sizes are computed from pg_class.relpages with an assumed page size > of 8192. > > Comments and improvements most welcome. You could change all those "CASE WHEN foo IS NULL THEN 0 ELSE foo" into COALESCE(foo, 0). Additionally, "show block_size" will give you the block (page) size. I don't see how would you use that in a query though. -- Alvaro Herrera () "La tristeza es un muro entre dos jardines" (Khalil Gibran)