查看Postgresql每个表的大小

查询PostgreSQL表与索引大小

最新推荐文章于 2025-07-02 18:30:00 发布

原创最新推荐文章于 2025-07-02 18:30:00 发布 · 979 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #python

Postgresql 专栏收录该内容

1 篇文章

订阅专栏

本文提供了一段SQL查询语句，用于查询PostgreSQL数据库中各表及其索引的大小，并通过一个实例展示了查询结果。该查询可以帮助数据库管理员了解表的实际存储情况及索引占用的空间。

不知道是哪个大神写的，因为我从别人的blog看到的时候，别人也注明说是忘记哪个大神写的。所以，对原创者很抱歉，不能写出来源了。

SELECT
schemaname as schema,
tablename as table_name,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS size_p,
pg_total_relation_size(schemaname || '.' || tablename) AS siz,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS 表总大小,
pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename) AS 索引大小,
(100*(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)))/CASE WHEN pg_total_relation_size(schemaname || '.' || tablename) = 0 THEN 1 ELSE pg_total_relation_size(schemaname || '.' || tablename) END || '%' AS index_pct
FROM pg_tables
ORDER BY siz DESC

以下为SQL执行结果：
[img]http://dl2.iteye.com/upload/attachment/0122/1558/467626dc-084e-3acc-9f63-d6bcddf5769d.jpg[/img]