【ADB for PostgreSQL】数据库、schema与表大小的查看方法

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('表名',' 分区名'));