GreenPlum获取table、schema及其database大小

1. 获取某一个特定表的大小：

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',
);

2. 获取一个库下面所有表占用空间：

select pg_size_pretty(pg_database_size('MyDatabase')); 

3. 查询一个库下面各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;