pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
schema sizes
3+ messages / 2 participants
[nested] [flat]

* schema sizes
@ 2005-03-02 12:48 Daniel Rubio <drubior@tinet.org>
  2005-03-03 07:34 ` Re: schema sizes Morus Walter <morus.walter@tanto.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Daniel Rubio @ 2005-03-02 12:48 UTC (permalink / raw)
  To: pgsql-admin

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
********************************************************




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: schema sizes
  2005-03-02 12:48 schema sizes Daniel Rubio <drubior@tinet.org>
@ 2005-03-03 07:34 ` Morus Walter <morus.walter@tanto.de>
  2005-03-03 12:49   ` Re: schema sizes Daniel Rubio <drubior@tinet.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Morus Walter @ 2005-03-03 07:34 UTC (permalink / raw)
  To: Daniel Rubio <drubior@tinet.org>

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: schema sizes
  2005-03-02 12:48 schema sizes Daniel Rubio <drubior@tinet.org>
  2005-03-03 07:34 ` Re: schema sizes Morus Walter <morus.walter@tanto.de>
@ 2005-03-03 12:49   ` Daniel Rubio <drubior@tinet.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Daniel Rubio @ 2005-03-03 12:49 UTC (permalink / raw)
  To: pgsql-admin

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
********************************************************




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-03-03 12:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-02 12:48 schema sizes Daniel Rubio <drubior@tinet.org>
2005-03-03 07:34 ` Morus Walter <morus.walter@tanto.de>
2005-03-03 12:49   ` Daniel Rubio <drubior@tinet.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox