磐维数据库获取表和索引大小

最新推荐文章于 2024-10-10 23:05:59 发布

PanWeiDB_DBA

最新推荐文章于 2024-10-10 23:05:59 发布

阅读量204

点赞数 3

文章标签：数据库 sql

本文链接：https://blog.csdn.net/PanWeiDB_DBA/article/details/142720005

版权

1、获取数据表的大小并排序

SELECT CURRENT_CATALOG AS datname,nsp.nspname,rel.relname,
pg_total_relation_size(rel.oid) AS bytes,
pg_relation_size(rel.oid) AS relsize,
pg_indexes_size(rel.oid) AS indexsize,
pg_total_relation_size(reltoastrelid) AS toastsize
FROM pg_namespace nsp JOIN pg_class rel ON nsp.oid = rel.relnamespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema','snapshot') AND rel.relkind = 'r'
order by 4 desc limit 100;

2、获取索引的大小并排序

select CURRENT_CATALOG AS datname,schemaname schema_name,relname table_name,indexrelname index_name,pg_table_size(indexrelid) as index_size from pg_stat_user_indexes where schemaname not in('pg_catalog', 'information_schema','snapshot') order by 4 desc limit 100;