主页 / dba / 问题 / 345321
Accepted
Chris Douglas
Asked: 2025-02-17 18:01:53 +0800 CST

将行数列添加到表大小查询

  • 772

我有以下 SQL，它输出数据库中所有表的大小的格式良好的列表：

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size
FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

这将输出如下数据（为了隐藏数据，一些名称和大小发生了改变，但您可以明白我的意思）：

     schema_name     |                 relname                 |    size
---------------------+-----------------------------------------+------------
 information_schema  | log                                     | 1024 kB
 information_schema  | collations                              | 1024 kB
 information_schema  | routines                                | 1024 kB
(30 rows)

我想将每个表上的 count() 结果添加为此处的第四列，但不知道如何执行此操作。我使用的是 PostgreSQL 14，不知道这是否有什么特别之处。

postgresql

1 个回答

  1. Best Answer

    您可以这样做（db<>fiddle here）：

    SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
    
  estimate AS row_count_est     --   <<  Note!

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname        AS schema_name,

         pg_catalog.pg_class.reltuples::BIGINT  AS estimate,     --  <<   Note!
         
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

    结果（来自 db<>fiddle> - 为简洁起见已剪辑）：

    schema_name          relname                   size        row_count_est
information_schema   sql_features              64 kB       755
information_schema   sql_sizing                8192 bytes  23
information_schema   sql_implementation_info   8192 bytes  12
information_schema   sql_parts                 8192 bytes  11
information_schema   administrable_role_...    0 bytes     -1
...
...
...

    请注意，最后一条记录的估计值为 -1 - 这意味着系统从未分析过该表，因此它不知道计数是多少，甚至不想提供估计值。

    您可以从 Erwin Brandstetter 的回答中获得不同程度的准确度，但请注意，更准确的答案需要更多的系统资源来提供该答案 - 由于 PostgreSQL 的 MVCC 系统，准确的计数比使用不同机制的 MySQL 更困难。

    从这个db<>fiddleANALYZE中可以看出 -ing的重要性，其中分析之前的估计值为 -1，而分析之后的（正确数字）为 100,000 - 这表明保持系统统计数据的最新状态是多么重要！

    • 4

相关问题