postgresql数据库查询库大小、索引大小、表大小

--查看所有数据库大小
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;

--查看指定表中所有索引大小
select pg_size_pretty(pg_indexes_size('test'));

--查看指定schema 里所有的索引大小，按从大到小的顺序排列。
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

--查看指定表大小
select pg_size_pretty(pg_relation_size('test'));

--查看指定表的总大小
select pg_size_pretty(pg_total_relation_size('test'));

--查看指定schema 里所有的表大小，按从大到小的顺序排列。
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

--查看表空间大小
select spcname from pg_tablespace;
select pg_size_pretty(pg_tablespace_size('pg_default'));