数据库表大小统计方法参考

查询表大小

select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   order by KB desc;

select schemaname,tablename,indexname,pg_relation_size(concat(schemaname,'."', indexname,'"'))/1024 KB from sys_indexes where schemaname='webdba' and tablename in
(select relname  from sys_stat_user_tables 
where schemaname='webdba'  
)order by KB desc ;

select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='cs_org_t_employee';
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='cs_org_t_organization';
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='cs_sys_dept';
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='cs_sys_register';
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='cs_sys_user';
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='cs_ticket';
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    from sys_stat_user_tables where schemaname='webdba'   and relname='or_order';

sys_dump -Usystem -d archwebaws  -t 'webdba.cs_org_t_employee' -t 'webdba.cs_org_t_organization' -t 'webdba.cs_sys_dept' -t 'webdba.cs_sys_register' -t 'webdba.cs_sys_user' -t 'webdba.cs_ticket' -t 'webdba.or_order' -Fc -f  /备份目录/aws20230611.dmp -v 

select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    
from sys_stat_user_tables where schemaname='webdba'   
and relname in ('cs_org_t_employee','cs_org_t_organization','cs_sys_dept','cs_sys_register','cs_sys_user','cs_ticket','or_order');
 schemaname |        relname        |  kb   
------------+-----------------------+-------
 webdba     | cs_ticket             | 17024
 webdba     | cs_org_t_employee     |   120
 webdba     | cs_org_t_organization |    24
 webdba     | cs_sys_dept           |    56
 webdba     | cs_sys_register       |    32
 webdba     | cs_sys_user           |   336
 webdba     | or_order              |    16
(7 rows)

select schemaname,tablename,indexname,pg_relation_size(concat(schemaname,'."', indexname,'"'))/1024 KB from sys_indexes where schemaname='webdba' and tablename in
(select relname  from sys_stat_user_tables 
where schemaname='webdba'  
and  relname in ('cs_org_t_employee','cs_org_t_organization','cs_sys_dept','cs_sys_register','cs_sys_user','cs_ticket','or_order')
)order by KB desc ;

 schemaname |       tablename       |             indexname             | kb  
------------+-----------------------+-----------------------------------+-----
 webdba     | cs_ticket             | con_webdba_cs_ticket_constraint_1 | 896
 webdba     | cs_sys_user           | cs_sys_user_pkey                  |  48
 webdba     | cs_org_t_employee     | idx_operatorid_ee_new             |  32
 webdba     | cs_sys_dept           | cs_sys_dept_pkey                  |  16
 webdba     | cs_org_t_organization | idx_orgid_new                     |  16
(5 rows)

查询索引

select schemaname,tablename,indexname,pg_relation_size(concat(schemaname,'."', indexname,'"'))/1024 KB from sys_indexes where schemaname='webdba' and tablename in
(select relname  from sys_stat_user_tables 
where schemaname='webdba'  
)order by KB desc ;

webdba    cs_file_status_detail
webdba    cs_testcard_cdr
fval    fee_reg_satellite
fval    fee_reg_short_code
fval    fee_reg_sms
fval    fee_reg_special_calls
fval    fee_reg_voice

sys_dump -Usystem -d archwebaws  -t 'webdba.cs_testcard_cdr' -t 'fval.fee_reg_satellite' -t 'fval.fee_reg_short_code' -t 'fval.fee_reg_sms' -t 'fval.fee_reg_special_calls' -t 'fval.fee_reg_voice' -t 'webdba.cs_org_t_employee' -t 'webdba.cs_org_t_organization' -t 'webdba.cs_sys_dept' -t 'webdba.cs_sys_register' -t 'webdba.cs_sys_user' -t 'webdba.cs_ticket' -t 'webdba.or_order' -Fc -f  /备份目录/aws20230611.dmp -v 

-t 'webdba.cs_testcard_cdr' -t 'fval.fee_reg_satellite' -t 'fval.fee_reg_short_code' -t 'fval.fee_reg_sms' -t 'fval.fee_reg_special_calls' -t 'fval.fee_reg_voice'

===============
select schemaname,relname,pg_relation_size(concat(schemaname,'.',relname))/1024 KB    
from sys_stat_user_tables where schemaname='fval'   
and relname in ('fee_reg_satellite','fee_reg_short_code','fee_reg_sms','fee_reg_special_calls','fee_reg_voice');
 schemaname |        relname        |  kb   
------------+-----------------------+-------
 fval       | fee_reg_satellite     |  2736
 fval       | fee_reg_short_code    |  7736
 fval       | fee_reg_sms           |  1896
 fval       | fee_reg_special_calls |  2032
 fval       | fee_reg_voice         | 69840
(5 rows)

select schemaname,tablename,indexname,pg_relation_size(concat(schemaname,'."', indexname,'"'))/1024 KB from sys_indexes where schemaname='fval' and tablename in
(select relname  from sys_stat_user_tables 
where schemaname='fval'  
and  relname in ('fee_reg_satellite','fee_reg_short_code','fee_reg_sms','fee_reg_special_calls','fee_reg_voice')
)order by KB desc ;
 schemaname |       tablename       |                     indexname                     |  kb   
------------+-----------------------+---------------------------------------------------+-------
 fval       | fee_reg_voice         | fee_reg_voice_ind_fee_reg_voice_union             | 40528
 fval       | fee_reg_voice         | ind_fee_reg_voice_union                           | 40528
 fval       | fee_reg_voice         | pk_fee_reg_voice                                  | 12984
 fval       | fee_reg_short_code    | ind_fee_reg_short_code_union                      |  2784
 fval       | fee_reg_satellite     | ind_fee_reg_satellite_union                       |  1784
 fval       | fee_reg_short_code    | pk_fee_reg_short_code                             |  1064
 fval       | fee_reg_special_calls | fee_reg_special_calls_ind_fee_reg_special_calls_u |   784
 fval       | fee_reg_satellite     | pk_fee_reg_satellite                              |   600
 fval       | fee_reg_sms           | pk_fee_reg_sms                                    |   576
 fval       | fee_reg_special_calls | fee_reg_special_calls_primary                     |   480
(10 rows)

select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_file_status_detail%';

select schemaname,tablename,indexname,pg_relation_size(concat(schemaname,'."', indexname,'"'))/1024 KB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_file_status_detail%'  limit 10;

select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_file_status_detail%'  limit 10;

====
20230625 arch网站201/241
select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_file_status_detail%';
select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_file_status_detail%';
1251GB
273GB

select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_ca_cdr_delayed%';
select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_ca_cdr_delayed%';
743GB
948GB

select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_ar_cdr_delayed%';
select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_ar_cdr_delayed%';
166GB
149GB

select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_ar_cdr_err%';
select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_ar_cdr_err%';
92GB
99GB

select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_ca_cdr_err%';
select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_ca_cdr_err%';
68GB
72GB

select sum(pg_relation_size(concat(schemaname,'.',relname)))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_relevant_file%';
select sum(pg_relation_size(concat(schemaname,'."', indexname,'"')))/1024/1024/1024 GB from sys_indexes where schemaname='webdba' and tablename ilike 'cs_relevant_file%';
94GB
116GB
====

select pg_relation_size('')))/1024/1024/1024 GB

select pg_relation_size(concat(schemaname,'.',relname))/1024/1024/1024  from sys_stat_user_tables where schemaname='webdba' and relname ilike 'cs_file_status_detail%';