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 02DF3554CD for ; Thu, 3 Mar 2005 07:33:58 +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 67183-07 for ; Thu, 3 Mar 2005 07:33:56 +0000 (GMT) Received: from mail.tanto.de (mail.tanto.de [213.61.178.43]) by svr1.postgresql.org (Postfix) with ESMTP id 3E6A152A4E for ; Thu, 3 Mar 2005 07:33:55 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by mail.tanto.de (Postfix) with ESMTP id DE70C23CE0 for ; Thu, 3 Mar 2005 08:34:52 +0100 (CET) Received: from mail.tanto.de ([127.0.0.1]) by localhost (mail.tanto.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31444-04 for ; Thu, 3 Mar 2005 08:34:52 +0100 (CET) Received: from tucholsky.office.tanto.de (tucholsky.office.tanto.de [10.0.0.35]) by mail.tanto.de (Postfix) with ESMTP id BE82523BF5 for ; Thu, 3 Mar 2005 08:34:52 +0100 (CET) Received: from tucholsky.office.tanto.de (morus@localhost [127.0.0.1]) by tucholsky.office.tanto.de (8.12.3/8.12.3/Debian-7.1) with ESMTP id j237ZB72012802 for ; Thu, 3 Mar 2005 08:35:11 +0100 Received: (from morus@localhost) by tucholsky.office.tanto.de (8.12.3/8.12.3/Debian-7.1) id j237ZAUA012798; Thu, 3 Mar 2005 08:35:10 +0100 Resent-From: Morus Walter Resent-Message-ID: <16934.48686.933788.276413@tanto-xipolis.de> Resent-Date: Thu, 3 Mar 2005 08:35:10 +0100 Resent-To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16934.48644.771352.662613@tanto-xipolis.de> In-Reply-To: <4225B600.1000303@tinet.org> References: <4225B600.1000303@tinet.org> X-Mailer: VM 7.03 under 21.4 (patch 6) "Common Lisp" XEmacs Lucid From: Morus Walter To: Daniel Rubio Subject: Re: schema sizes Date: Thu, 3 Mar 2005 08:34:28 +0100 X-Virus-Scanned: by amavisd-new at mail.tanto.de Q&A postmaster@tanto.de 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/36 X-Sequence-Number: 16534 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.