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

概述我的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 da 我的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 ENDFROM pg_classleft OUTER JOIN pg_namespace ON (pg_namespace.oID = pg_class.relnamespace)GROUP BY pg_class.relkind,nspnameORDER 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 ENDFROM pg_class left OUTER JOIN pg_namespace ON (pg_namespace.oID = pg_class.relnamespace)ORDER BY pg_relation_size(pg_class.oID) DESC;

总结

以上是内存溢出为你收集整理的postgresql总数据库大小不匹配各个表大小的总和全部内容，希望文章能够帮你解决postgresql总数据库大小不匹配各个表大小的总和所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。