Обсуждение: schema sizes
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 ********************************************************
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.
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 ********************************************************