PostgreSQL数据库中具有大小(相对和绝对)的架构列表
PostgreSQL数据库中具有大小(相对和绝对)的架构列表
提问于 2010-12-12 03:03:48
我正在寻找一个为任何数据库返回表单结果的查询(参见下面的示例，假设数据库使用的总空间为40 of )
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这样的聚合运算符可能是必要的，但到目前为止还没有成功。
回答 3
Stack Overflow用户
发布于 2020-08-26 22:56:14
更好的解决方案：
WITH
schemas AS (
SELECT schemaname as name, sum(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint as size FROM pg_tables
GROUP BY schemaname
),
db AS (
SELECT pg_database_size(current_database()) AS size
)
SELECT schemas.name,
pg_size_pretty(schemas.size) as absolute_size,
schemas.size::float / (SELECT size FROM db) * 100 as relative_size
FROM schemas;接受的答案解决了所描述的问题，但建议的查询效率不高。你可以通过解释来了解其中的区别：
EXPLAIN WITH
schemas AS (
SELECT schemaname as name, sum(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint as size FROM pg_tables
GROUP BY schemaname
),
db AS (SELECT pg_database_size(current_database()) AS size)
SELECT schemas.name,
pg_size_pretty(schemas.size) as absolute_size,
schemas.size::float / (SELECT size FROM db) * 100 as relative_size
FROM schemas;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
CTE Scan on schemas (cost=47100.79..47634.34 rows=16417 width=104)
CTE schemas
-> Finalize HashAggregate (cost=46854.50..47100.76 rows=16417 width=72)
Group Key: n.nspname
-> Gather (cost=43119.63..46608.25 rows=32834 width=96)
Workers Planned: 2
-> Partial HashAggregate (cost=42119.63..42324.85 rows=16417 width=96)
Group Key: n.nspname
-> Hash Left Join (cost=744.38..39763.93 rows=94228 width=128)
Hash Cond: (c.relnamespace = n.oid)
-> Parallel Seq Scan on pg_class c (cost=0.00..38772.14 rows=94228 width=72)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
-> Hash (cost=539.17..539.17 rows=16417 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..539.17 rows=16417 width=68)
CTE db
-> Result (cost=0.00..0.01 rows=1 width=8)
InitPlan 3 (returns $3)
-> CTE Scan on db (cost=0.00..0.02 rows=1 width=8)vs
EXPLAIN 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;
QUERY PLAN
-------------------------------------------------------------------------------------------
GroupAggregate (cost=283636.24..334759.75 rows=1202906 width=128)
Group Key: pg_namespace.nspname
-> Sort (cost=283636.24..286643.51 rows=1202906 width=72)
Sort Key: pg_namespace.nspname
-> Hash Join (cost=744.38..51446.15 rows=1202906 width=72)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
-> Seq Scan on pg_class (cost=0.00..44536.06 rows=1202906 width=8)
-> Hash (cost=539.17..539.17 rows=16417 width=68)
-> Seq Scan on pg_namespace (cost=0.00..539.17 rows=16417 width=68)Stack Overflow用户
发布于 2022-01-20 11:03:26
如果你想找到特定模式的大小，你可以简单地使用下面的查询：
select sum(
pg_total_relation_size(quote_ident(schemaname) ||
'.' ||
quote_ident(tablename))
)::bigint
from pg_tables where schemaname = 'mySchema';Stack Overflow用户
发布于 2020-12-05 09:19:05
https://www.depesz.com/2018/02/17/which-schema-is-using-the-most-disk-space/
显示了同时计算TOAST选项卡数量的解决方案。在PG12上测试：
WITH recursive all_elements AS (
SELECT 'base/' || l.filename AS path, x.*
FROM
pg_ls_dir('base/') AS l (filename),
LATERAL pg_stat_file( 'base/' || l.filename) AS x
UNION ALL
SELECT 'pg_tblspc/' || l.filename AS path, x.*
FROM
pg_ls_dir('pg_tblspc/') AS l (filename),
LATERAL pg_stat_file( 'pg_tblspc/' || l.filename) AS x
UNION ALL
SELECT
u.path || '/' || l.filename, x.*
FROM
all_elements u,
lateral pg_ls_dir(u.path) AS l(filename),
lateral pg_stat_file( u.path || '/' || l.filename ) AS x
WHERE
u.isdir
), all_files AS (
SELECT path, SIZE FROM all_elements WHERE NOT isdir
), interesting_files AS (
SELECT
regexp_replace(
regexp_replace(f.path, '.*/', ''),
'\.[0-9]*$',
''
) AS filename,
SUM( f.size )
FROM
pg_database d,
all_files f
WHERE
d.datname = current_database() AND
f.path ~ ( '/' || d.oid || E'/[0-9]+(\\.[0-9]+)?$' )
GROUP BY filename
)
SELECT
n.nspname AS schema_name,
SUM( f.sum ) AS total_schema_size
FROM
interesting_files f
JOIN pg_class c ON f.filename::oid = c.relfilenode
LEFT OUTER JOIN pg_class dtc ON dtc.reltoastrelid = c.oid AND c.relkind = 't'
JOIN pg_namespace n ON COALESCE( dtc.relnamespace, c.relnamespace ) = n.oid
GROUP BY
n.nspname
ORDER BY
total_schema_size DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4418403
复制相关文章
相似问题