Postgresql统计表名表行数表注释表大小

偏执的太偏执、 2023-02-22 10:56 29阅读 0赞

# 统计表属性 #

目录pg\_class记录表和几乎所有具有列或者像表的东西。这包括索引（但还要参见pg\_index）、序列、视图、物化视图、组合类型和TOAST表，参见relkind。下面，当我们提及所有这些类型的对象时我们使用"关系"。并非所有列对于所有关系类型都有意义。

这里介绍几个关键列：

<table> 
 <thead> 
  <tr> 
   <th align="left">名称</th> 
   <th align="left">类型</th> 
   <th align="left">注释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">oid</td> 
   <td align="left">oid</td> 
   <td align="left">行标识符（隐藏属性，必须被显式选择才会显示）</td> 
  </tr> 
  <tr> 
   <td align="left">relname</td> 
   <td align="left">name</td> 
   <td align="left">表、索引、视图等的名字</td> 
  </tr> 
  <tr> 
   <td align="left">relowner</td> 
   <td align="left">oid</td> 
   <td align="left">关系的拥有者</td> 
  </tr> 
  <tr> 
   <td align="left">reltuples</td> 
   <td align="left">float4</td> 
   <td align="left">表中的行数。这只是一个由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令（如CREATE INDEX）所更新。</td> 
  </tr> 
  <tr> 
   <td align="left">…</td> 
   <td align="left">…</td> 
   <td align="left">…</td> 
  </tr> 
 </tbody> 
</table>

详细列说明请参见：  
http://postgres.cn/docs/9.6/catalog-pg-class.html

根据下面SQL语句查询表命名空间、表名称、对象类型、表大小、表行数、表注释等：

SELECT 
      n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind 
           WHEN 'r' THEN 'table' 
           WHEN 'v' THEN 'view' 
           WHEN 'm' THEN 'materialized view' 
           WHEN 'i' THEN 'index' 
           WHEN 'S' THEN 'sequence' 
           WHEN 's' THEN 'special' 
           WHEN 'f' THEN 'foreign table' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
      pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
      pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
      c.reltuples as "rows"
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','s','')
    			AND n.nspname <> 'pg_catalog'
          AND n.nspname <> 'information_schema'
          AND n.nspname !~ '^pg_toast'
    ORDER BY 1,2 limit 10; 
    
    --==============================================
    -- 查询结果显示如下：
    
    postgres#
     Schema |          Name           | Type  |  Owner   |  Size   | Description | rows  
    --------+-------------------------+-------+----------+---------+-------------+-------
     ab     | ab_testtest_type        | table | postgres | 88 kB   | 测试表1     |   675
     ab     | ab_testtest_type_copy   | table | postgres | 88 kB   | 测试表2     |   675
     kfb    | abcd                    | table | postgres | 16 kB   | 测试表3     |     0
     kfb    | abcd_efhg               | table | postgres | 448 kB  | 测试表4     |   677
     kfb    | abcd_efhg_copy          | table | postgres | 440 kB  | 测试表5     |   677
     kfb    | abcd_efghijk_lomno      | table | postgres | 8752 kB | 测试表6     | 60881
     kfb    | abcd_efghijk_lomno_copy | table | postgres | 8752 kB | 测试表7     | 60881
     kfb    | zhaocheng               | table | postgres | 16 kB   | 测试表8     |     0
     tt     | chenzhaocheng           | table | postgres | 1408 kB | 测试表9     |  9397
     tt     | cheng                   | table | postgres | 824 kB  | 测试表10    |  4317
    (10 rows)