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

  1. 获取一个库下面所有表占用空间:
select pg_size_pretty(pg_database_size('MyDatabase')); 
  1. 查询一个库下面各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;

  • 1
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值