我正在寻找一个返回任何数据库的窗体结果的查询
(参见下面的示例假设数据库使用的总空间是40GB)
(参见下面的示例假设数据库使用的总空间是40GB)
schema | size | relative size ----------+------------------- foo | 15GB | 37.5% bar | 20GB | 50% baz | 5GB | 12.5%
我已经设法使用数据库中的实体在模式中排列的空间列表,
这是有用的,但是从每个模式获取摘要看起来并不容易.
见下文.
SELECT relkind,relname,pg_catalog.pg_namespace.nspname,pg_size_pretty(pg_relation_size(pg_catalog.pg_class.oid))
FROM pg_catalog.pg_class
INNER JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
ORDER BY pg_catalog.pg_namespace.nspname,pg_relation_size(pg_catalog.pg_class.oid) DESC;
这给出了结果
relkind | relname | nspname | pg_size_pretty ---------+---------------------------------------+--------------------+---------------- r | geno | btsnp | 11 GB i | geno_pkey | btsnp | 5838 MB r | anno | btsnp | 63 MB i | anno_fid_key | btsnp | 28 MB i | ix_btsnp_anno_rsid | btsnp | 28 MB [...] r | anno | btsnp_shard | 63 MB r | geno4681 | btsnp_shard | 38 MB r | geno4595 | btsnp_shard | 38 MB r | geno4771 | btsnp_shard | 38 MB r | geno4775 | btsnp_shard | 38 MB
看起来像使用像SUM这样的聚合运算符可能是必要的,到目前为止没有成功.
Regards,Faheem
尝试这个：
原文链接：https://www.f2er.com/postgresql/192688.htmlSELECT schema_name,sum(table_size),(sum(table_size) / database_size) * 100
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,pg_relation_size(pg_catalog.pg_class.oid) as table_size,sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name,database_size
编辑：只是注意到,总结所有表以获取数据库大小的解决方法是不必要的：
SELECT schema_name,pg_size_pretty(sum(table_size)::bigint),(sum(table_size) / pg_database_size(current_database())) * 100
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,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
GROUP BY schema_name
ORDER BY schema_name
