1、查询一张表的总大小(单位为MB,包含表的索引和数据):
select pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));
2、查询表的数据大小(单位MB,不包括索引):
select pg_size_pretty(pg_relation_size('<schemaname>.<tablename>'));
3、查询分区表所有分区的总大小(单位MB,包含表的索引和数据):
select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || partitiontablename))/1024/1024) "MB" from pg_partitions where schemaname='<schemaname>' and tablename='<tablename>' group by 1,2;
4、查询一个Schema下面的所有表的总大小(单位MB,包括索引和数据):
Select schemaname ,tablename,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='<schemaname>' group by 1,2;
5、查询每个数据库的大小(单位MB):
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database where datname='dbname';
6、查看表空间的大小
openGauss=# select pg_size_pretty(pg_tablespace_size('pg_default'));
pg_size_pretty
----------------
102 MB
(1 row)
openGauss=# select pg_size_pretty(pg_tablespace_size('hr_data'));
pg_size_pretty
----------------
1286 MB
(1 row)
7、查看所有数据库的大小
select pg_size_pretty(sum(pg_database_size(oid))) from pg_database;
8、查看每个数据库的大小
openGauss=# select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;
datname | pg_size_pretty
-----------+----------------
template1 | 14 MB
test_gbk | 14 MB
template0 | 14 MB
test1 | 14 MB
postgres | 31 MB
mm | 14 MB
db_hr | 1286 MB
(7 rows)
9、查看指定数据库的大小
openGauss=# select pg_size_pretty(pg_database_size('db_hr'));
pg_size_pretty
----------------
1286 MB
(1 row)
10、查看每个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 group by schemaname;
11、查看所有表的大小,并以表大小排序
select tableowner,schemaname,tablename,pg_table_size(schemaname||'.'||tablename) as table_size from pg_tables order by table_size desc;
12、查看指定schema下所有表的大小
select schemaname || '.' || tablename tname, pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')) from pg_tables where schemaname = '模式名' order by pg_total_relation_size('"' || schemaname || '"."' || tablename || '"') desc ;
13、查看单张表的大小
select pg_size_pretty(pg_table_size('表名'));
select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables where tablename='表名';
14、查看表的分区的大小
select pg_size_pretty(pg_partition_size('表名',' 分区名'));