PostgreSQL查询表和index占用空间大小
November 12th, 2018
No comments
PostgreSQL查询表和index占用空间大小
PostgreSQL表和index占用空间大小信息存储在
information_schema.tables中
通过SQL可以查询到相应的统计数据
--查出单个表的大小 SELECT pg_size_pretty(pg_relation_size('TABLENAME')); |
查出表大小按大小含Index
-- 查出表大小按大小含Index SELECT "table_name", pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT TABLE_NAME, SUBSTRING("table_name",1,10) AS short_name, pg_table_size(TABLE_NAME) AS table_size, pg_indexes_size(TABLE_NAME) AS indexes_size, pg_total_relation_size(TABLE_NAME) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME FROM information_schema.tables ) AS all_tables WHERE all_tables.table_name LIKE '%TABLENAME%' ORDER BY total_size DESC ) AS pretty_sizes |
Recent Comments