Thread: schema sizes

schema sizes

From
Daniel Rubio
Date:
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?

Thanks in advance.
--
********************************************************
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
********************************************************


Re: schema sizes

From
Morus Walter
Date:
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.

Re: schema sizes

From
Daniel Rubio
Date:
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
********************************************************