数据库
查看某个数据库的大小
select pg_database_size('test');
以人性化的方式显示大小
select pg_size_pretty(pg_database_size('test'));
查看所有库的大小
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
以人性化的方式显示大小
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;
表
通过pg_relation_size查看某个表的大小
select pg_relation_size('test');
以人性化的方式显示大小
select pg_size_pretty(pg_relation_size('test'));
通过pg_table_size查看某个表的大小
select pg_table_size('test');
以人性化的方式显示大小
select pg_size_pretty(pg_table_size('test'));
通过pg_total_relation_size查看某个表的大小
select pg_total_relation_size('test');
以人性化的方式显示大小
select pg_size_pretty(pg_total_relation_size('test'));
查看所有表的空间，并按大小排序
SELECT table_schema || '.' || table_name AS table_full_name,
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size
FROM information_schema.tables WHERE table_schema = 'public'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
以人性化的方式显示大小
SELECT table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables WHERE table_schema = 'public'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
索引
查看某个索引的大小
select pg_relation_size('pk_test');
以人性化的方式显示大小
select pg_size_pretty(pg_relation_size('pk_test'));
查看某个表的索引的大小
select pg_indexes_size('p_test');
以人性化的方式显示大小
select pg_size_pretty(pg_indexes_size('p_test'));
查看某个库的索引的大小
select indexrelname, pg_relation_size(relid) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
以人性化的方式显示大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
