数据库对象大小统计脚本

获取数据库排名前20的表

select t.table_catalog as db, 
       n.nspname as schemaname, 
       c.relname, 
       c.reltuples::numeric as rowcount, 
       sys_size_pretty(sys_table_size ( '"' || nspname || '"."' || relname || '"' )) as table_size, 
       sys_size_pretty(sys_indexes_size ( '"' || nspname || '"."' || relname || '"' )) as indexes_size, 
       sys_size_pretty (sys_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) as total_size 
       from sys_class c left join sys_namespace n on ( n.oid = c.relnamespace ) 
       left join information_schema.tables t on (n.nspname= t.table_schema and c.relname=t."table_name" ) 
       where nspname not in ( 'sys_catalog', 'information_schema' ) and relkind in ('r','p') order by reltuples desc limit 20;

统计数据库对象类型和数量

select * from (
select 
    nsp.nspname as schemaname, 
    case c.relkind 
        when 'r' then 'table' 
        when 'm' then 'materialized_view' 
        when 'i' then 'index' 
        when 'S' then 'sequence' 
        when 'v' then 'view' 
        when 'c' then 'composite type' 
        when 't' then 'toast' 
        when 'f' then 'foreign table' 
        when 'p' then 'partitioned_table' 
        when 'I' then 'partitioned_index' 
        else c.relkind::text 
    end as objecttype, 
    count(*) total 
from sys_class c 
join sys_namespace nsp 
 on nsp.oid = c.relnamespace 
where nsp.nspname not in ('information_schema', 'sys_catalog') 
  and nsp.nspname not like 'sys_toast%' 
group by nsp.nspname,c.relkind
union all
select n.nspname as schemaname, 
 case p.prokind 
  when 'a' then 'agg' 
  when 'w' then 'window' 
  when 'p' then 'proc' 
  else 'func' 
 end as "type", 
 count(*) total 
from sys_catalog.sys_proc p 
left join sys_catalog.sys_namespace n on n.oid = p.pronamespace 
where sys_catalog.sys_function_is_visible(p.oid) 
and n.nspname not in ('information_schema', 'sys_catalog') 
group by n.nspname ,p.prokind) order by schemaname;