- 获取某一个特定表的大小:
select pg_size_pretty(pg_relation_size('schema_name.table_name'));
主要,如果这里是一个分区表,那么查询到的结果为0,详见:http://blog.csdn.net/sptoor/article/details/11170799
查询一个schema下各表的空间:
select schemaname || '.' || tablename, pg_size_pretty(pg_relation_size( schemaname || '.' || tablename))
from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname where schemaname='my_schema';
可以看到,即便对于分区表,也能够通过其下的子表得到大小,如果用一个sum集函数,也能将分区表统计进来。
查询一个schema下指定表的空间:
select schemaname, pg_size_pretty(pg_relation_size( schemaname || '.' || tablename))
from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname where schemaname='schema_name'
and tablename in (
'table1',
'table2',
);
查询一个schema下指定表的以schema分组的汇总空间:
select schemaname, pg_size_pretty(cast( sum(pg_relation_size( schemaname || '.' || tablename)) as bigint))
from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname where schemaname='schema_name'
and tablename in (
'table1',
'table2',
)
group by schemaname;
不以schema分组:
select pg_size_pretty(cast( sum(pg_relation_size( schemaname || '.' || tablename)) as bigint))
from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname where schemaname='schema_name'
and tablename in (
'table1',
'table2',
);
- 获取一个库下面所有表占用空间:
select pg_size_pretty(pg_database_size('MyDatabase'));
- 查询一个库下面各Schema占用空间:
select pg_size_pretty(cast( sum(pg_relation_size( schemaname || '.' || tablename)) as bigint)), schemaname
from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname group by schemaname;
select schemaname, count(tablename), pg_size_pretty(cast(sum(pg_relation_size(schemaname||'.'||tablename)) as bigint))
as schema_size from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname group by schemaname;