如何查看表、索引、表空间、数据库大小?

查询一个索引大小

1
select pg_size_pretty(pg_relation_size('indexname))

查看一张表和它的索引总大小

1
select pg_size_pretty(pg_total_relation_size('tablename'));

查看所有schema里索引大小,按从大到小排列

1
2
3
select indexrelname,pg_size_pretty( pg_relation_size(relid))   
from pg_stat_user_indexes
where schemaname = 'schemaname' order by pg_relation_size(relid) desc;

查看所有schema里表大小,按从大到小排列

1
2
3
select relname, pg_size_pretty(pg_relation_size(relid))  
from pg_stat_user_tables
where schemaname = 'schemaname' order by pg_relation_size(relid) desc;

查看数据库大小

1
2
select pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size  
from pg_database;

查看表空间大小

1
select pg_tablespace_size('tbs_index')/1024/1024  as  "SIZE M";

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%