PostgreSql数据库表空间,索引,表大小查询
Postgresql 数据库
一: 查询表空间大小
select pg_size_pretty(pg_tablespace_size('pg_default'));
二: 查询所有数据库大小
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_databas;
三: 查询指定索引大小
select pg_size_pretty(pg_relation_size('索引字段名'));
四: 查询指定表所有索引大小
select pg_size_pretty(pg_indexes_size('表名'));
五: 查询指定模式里所有的索引大小,按从大到小的顺序排列
select relname,indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='模式名' order by pg_relation_size(relid) desc;
六: 查询指定表大小(仅仅指定表数据)
select pg_size_pretty(pg_relation_size('表名'));
七: 查询指定表总大小(包括表数据与索引)
select pg_size_pretty(pg_total_relation_size('表名'));
八: 查询指定模式里面所有表大小(仅仅指定表数据,从大到小)
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='模式名' order by pg_relation_size(relid) desc;
九:查询指定schema里面所有表数据大小,总索引大小,总大小,行数
SELECT
table_size.relname 表名,
pg_size_pretty ( pg_relation_size ( relid ) ) 表数据大小,
pg_size_pretty ( pg_indexes_size ( relid ) ) 表总索引大小,
pg_size_pretty ( pg_total_relation_size ( relid ) ) 表总大小,
表行数
FROM
pg_stat_user_tables table_size
LEFT JOIN (
SELECT
relname,
reltuples :: DECIMAL ( 19, 0 ) 表行数
FROM
pg_class r
JOIN pg_namespace n ON ( relnamespace = n.oid )
WHERE
relkind = 'r'
AND n.nspname = '模式名'
) table_num ON table_num.relname = table_size.relname
WHERE
schemaname = '模式名'
ORDER BY
pg_relation_size ( relid ) DESC;