postgresql总数据库大小不匹配各个表大小的总和

发布时间：2020-12-13 16:17:46 所属栏目：百科来源：网络整理

导读：我的postgres数据库(版本8.2.3)显示的大小为24 GB. 为了得到这个数字,我执行这个查询： SELECT oid,datname,pg_database_size(datname) as actualsize,pg_size_pretty(pg_database_size(datname)) as size FROM pg_database ORDER BY datname 但是,执行此查

我的postgres数据库(版本8.2.3)显示的大小为24 GB.

为了得到这个数字,我执行这个查询：

SELECT
  oid,datname,pg_database_size(datname) as actualsize,pg_size_pretty(pg_database_size(datname)) as size 
FROM pg_database 
ORDER BY datname

但是,执行此查询时,同一数据库中各个表的大小不会增加24 GB：

SELECT 
  schemaname,tablename,pg_size_pretty(size) AS size_pretty,pg_size_pretty(total_size) AS total_size_pretty 
FROM
  (SELECT *,pg_relation_size(schemaname||'.'||tablename) AS size,pg_total_relation_size(schemaname||'.'||tablename) AS total_size 
   FROM pg_tables where schemaname = 'public') AS TABLES 
ORDER BY total_size DESC;

我已经用漂亮的大小和total_size总结了各个表的大小,但是值不匹配：

我的大小相当于3.5 GB

我得到一个5.2 GB的total_size_pretty.

我在哪里可以找到剩余的总空间用途？

通常我使用以下两个查询来获取数据库对象的大小.希望这可以帮助.

SELECT pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint),nspname,CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'toast' ELSE pg_class.relkind::text END
FROM pg_class
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
GROUP BY pg_class.relkind,nspname
ORDER BY sum(pg_relation_size(pg_class.oid)) DESC;

–

SELECT pg_size_pretty(pg_relation_size(pg_class.oid)),pg_class.relname,pg_namespace.nspname,CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST' ELSE pg_class.relkind::text END
FROM pg_class 
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
ORDER BY pg_relation_size(pg_class.oid) DESC;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!