为什么看不到我的架构大小 [英] why cant see my schema size

问题描述

如果您用Google搜索 postgresql表大小，则会得到此查询。

但未在结果中显示：

为什么 app 模式未显示在结果中？

我尝试第二个 查询 ，还限制了空结果。

 从information_schema.tables 
中选择table_name，pg_relation_size（table_name）
，其中table_schema ='app'
订单2 
  

解决方案

不幸的是，甚至psql命令l ine工具不会显示架构中所有对象的总大小。所有命令，例如用于数据库的 \l +或用于表的 \dt +，都以 +形式显示。

从pg_class中选择可能是最好的选择，但不仅针对 r进行过滤-模式还包含索引和其他对象。

If you google for "postgresql table size" you get this nice query. https://wiki.postgresql.org/wiki/Disk_Usage

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

I have a schema app:

But doesnt show in the result:

Why app schema doesnt show in the result?

I try a second query, and also returm empty result.

select table_name, pg_relation_size(table_name)
from information_schema.tables
where table_schema = 'app'
order by 2

解决方案

Unfortunately even psql command line tool does not show total size of all objects in schema. All commands like "\l+" for databases or "\dt+" for tables show in "+" form also total size. Only "\dn+" for schemas does not show size.

Select from pg_class is probably the best but do not filter only for "r" - schemas contain also indexes and other objects.

这篇关于为什么看不到我的架构大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！