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 77D5252A77 for ; Thu, 3 Mar 2005 12:52:07 +0000 (GMT) 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 55052-09 for ; Thu, 3 Mar 2005 12:52:04 +0000 (GMT) Received: from tinet.org (mail.tinet.org [195.77.216.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6360F52A66 for ; Thu, 3 Mar 2005 12:52:01 +0000 (GMT) Received: (qmail 23774 invoked from network); 3 Mar 2005 12:52:02 -0000 Received: from serena.tinet.org (HELO tinet.org) ([194.224.210.4]) (envelope-sender ) by mail.tinet.org (SMTP OASI) with SMTP for ; 3 Mar 2005 12:52:02 -0000 Message-ID: <422707F2.9090305@tinet.org> Date: Thu, 03 Mar 2005 13:49:54 +0100 From: Daniel Rubio User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: schema sizes References: <4225B600.1000303@tinet.org> <16934.48644.771352.662613@tanto-xipolis.de> In-Reply-To: <16934.48644.771352.662613@tanto-xipolis.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/39 X-Sequence-Number: 16537 Thanks, it works fine!! Morus Walter wrote: > Daniel Rubio writes: > >>Hi all! >> >>We want to obtain the size of the diferent schemas of some databases. >>We've installed dbsize and it works fine, but it returns the size of all >>the database. >>Exists some method to determine the size of a concrete schema? >> > > I use > SELECT nspname, > sum(relpages * cast( 8192 AS bigint )) as "table size", > sum( ( select sum(relpages) > from pg_class i, pg_index idx > where i.oid = idx.indexrelid > and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size", > sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages) > from pg_class i, pg_index idx > where i.oid = idx.indexrelid > and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size" > FROM pg_class t, pg_namespace > WHERE relnamespace = pg_namespace.oid > and pg_namespace.nspname not like 'pg_%' > and pg_namespace.nspname != 'information_schema' > and relkind = 'r' group by nspname; > > to sum over all tables/indices of a schema. > Note that the input for the sum is relpages in pg_class and this value > is only updated by VACUUM, ANALYZE und CREATE INDEX. > So you should analyze all tables before you execute the statement. > > -- ******************************************************** Daniel Rubio Rodríguez OASI (Organisme Autňnom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ********************************************************