有很多谷歌搜索结果可以解决这个问题,但是对我来说似乎都不起作用。因此,我创建了这个问题并为自己未来的参考以及通过谷歌到达此帖子的任何其他人回答它。
有很多谷歌搜索结果可以解决这个问题,但是对我来说似乎都不起作用。因此,我创建了这个问题并为自己未来的参考以及通过谷歌到达此帖子的任何其他人回答它。
以下是查询语句:
select sum(mbytes)/1024, database from (
select trim(pgdb.datname) as Database,
trim(a.name) as Table, b.mbytes
from stv_tbl_perm a
join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
where a.slice=0
order by db_id, name)
group by database;
输出:
?column? | database
----------+---------------
62 | db1
33 | db2
33 | db3
2 | db4
37 | db5
34 | db6
35 | db7
59 | db8
2 | db9
26 | db10
2 | db11
72 | db12
36 | db13
41 | db14
注意:上述数字以GB为单位
在查找表的磁盘空间使用情况时,请使用以下查询:
select
trim(pgdb.datname) as Database,
trim(pgn.nspname) as Schema,
trim(a.name) as Table,
b.mbytes,
a.rows
from (
select db_id, id, name, sum(rows) as rows
from stv_tbl_perm a
group by db_id, id, name
) as a
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id
join (
select tbl, count(*) as mbytes
from stv_blocklist
group by tbl
) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;