如何列出PostgreSQL数据库的所有表并按大小排序?
当前回答
select table_name,n_live_tup, pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
inner join pg_stat_user_tables on table_name=relname
where table_schema = 'public'
order by 2 desc
另一种替代方法
其他回答
这可以很容易地从psql控制台完成。
表大小:\dt+ 数据库大小\l+ 索引大小:\di+
对于表大小,首先使用\c db_name连接数据库。
请注意:上面不会按OP要求的大小排序,但它仍然可以提供帮助。
这将显示模式名、表名、size pretty和size(排序所需)。
SELECT
schema_name,
relname,
pg_size_pretty(table_size) AS size,
table_size
FROM (
SELECT
pg_catalog.pg_namespace.nspname AS schema_name,
relname,
pg_relation_size(pg_catalog.pg_class.oid) AS table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;
我基于PostgreSQL数据库中具有大小(相对大小和绝对大小)的模式列表的解决方案来构建它
您可以获得总关系大小和关系大小,这可能取决于您的表关系。 下面是如何获取数据库中的前100个表:
SELECT schemaname AS table_schema,
relname AS table_name,
PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid)) AS total_size,
PG_SIZE_PRETTY(PG_RELATION_SIZE(relid)) AS data_size,
PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(relid) - PG_RELATION_SIZE(relid))
AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY PG_TOTAL_RELATION_SIZE(relid) DESC,
PG_RELATION_SIZE(relid) DESC
LIMIT 100;
如果你正在寻找一个总的分解,吐司和索引大小使用这个:
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a ORDER BY total_bytes DESC;
select uv.a tablename, pg_size_pretty(uv.b) sizepretty
from (select tb.tablename a, pg_table_size('schemaname.'||tb.tablename::text) b
from pg_tables tb
where tb.schemaname ilike 'schemaname'
order by 2 desc
) uv
推荐文章
- 使用SQL查询查找最近的纬度/经度
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- 如何首次配置postgresql ?
- SQL Server中User和Login的区别
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- 如何在PostgreSQL中自动更新时间戳
- 设置NOW()为datetime数据类型的默认值?
- 在MySQL中Datetime等于或大于今天