Postgresql表记录及表大小统计

最新推荐文章于 2025-11-28 18:43:16 发布

原创最新推荐文章于 2025-11-28 18:43:16 发布 · 1.9k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql

数据库专栏收录该内容

20 篇文章

订阅专栏

本文提供SQL查询语句，用于查询数据库中各表的大小（包括数据与索引大小），并按总大小进行排序。同时，还展示了如何查询特定Schema下所有表的记录数，并按记录数降序排列。

查出表大小按大小排序并分离data与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,
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
ORDER BY total_size DESC
) AS pretty_sizes

查询某Schema下的每张表的记录数

select relname as TABLE_NAME, reltuples as rowCounts from pg_class 
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') 
order by rowCounts desc