postgresql - 您如何找到 Postgres 中所有表的行数

Question

我正在寻找一种方法来查找我在 Postgres 中所有表的行数。我知道我可以一次只做一张 table :

SELECT count(*) FROM table_name;

但我想查看所有表的行数，然后按行数排序以了解我的所有表有多大。

Answer 1

有三种方法可以得到这种计数，每种方法都有自己的权衡。

如果你想要一个真实的计数，你必须像你对每个表使用的那样执行 SELECT 语句。这是因为 PostgreSQL 将行可见性信息保存在行本身中，而不是其他任何地方，因此任何准确的计数都只能与某些事务相关。您正在计算该事务在执行时看到的内容。您可以自动执行此操作以针对数据库中的每个表运行，但您可能不需要那种级别的准确性或不想等待那么久。

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

第二种方法指出，统计信息收集器随时大致跟踪有多少行是“事件的”(未被删除或被以后的更新废弃)。在繁重的事件下，此值可能会略有偏差，但通常是一个不错的估计值:

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

这还可以显示有多少行已失效，这本身就是一个值得监控的有趣数字。

第三种方法是注意系统 ANALYZE 命令，从 PostgreSQL 8.3 开始由 autovacuum 进程定期执行以更新表统计信息，它也计算行估计。你可以像这样捕获那个:

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

很难说这些查询中的哪一个更好用。通常我根据是否有更多有用的信息我也想在 pg_class 内部或 pg_stat_user_tables 内部使用来做出决定。对于基本的计数目的，只是为了了解一般情况下有多大，两者都应该足够准确。