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 ********************************************************
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
