你如何在 Postgres 中找到所有表的行数 [英] How do you find the row count for all your tables in Postgres

问题描述

我正在寻找一种方法来查找 Postgres 中所有表的行数.我知道我可以一次完成一张桌子:

I'm looking for a way to find the row count for all my tables in Postgres. I know I can do this one table at a time with:

SELECT count(*) FROM table_name;

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

but I'd like to see the row count for all the tables and then order by that to get an idea of how big all my tables are.

推荐答案

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

There's three ways to get this sort of count, each with their own tradeoffs.

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

If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can only be relative to some transaction. You're getting a count of what that transaction sees at the point in time when it executes. You could automate this to run against every table in the database, but you probably don't need that level of accuracy or want to wait that long.

第二种方法指出，统计收集器在任何时候都会大致跟踪有多少行是活动的"(未被删除或被后续更新废弃).这个值在活动量大的情况下可能会有一点偏差，但通常是一个很好的估计:

The second approach notes that the statistics collector tracks roughly how many rows are "live" (not deleted or obsoleted by later updates) at any time. This value can be off by a bit under heavy activity, but is generally a good estimate:

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

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

That can also show you how many rows are dead, which is itself an interesting number to monitor.

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

The third way is to note that the system ANALYZE command, which is executed by the autovacuum process regularly as of PostgreSQL 8.3 to update table statistics, also computes a row estimate. You can grab that one like this:

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 内部使用来做出决定.对于基本的计数目的，只是为了了解事物的总体大小，两者都应该足够准确.

Which of these queries is better to use is hard to say. Normally I make that decision based on whether there's more useful information I also want to use inside of pg_class or inside of pg_stat_user_tables. For basic counting purposes just to see how big things are in general, either should be accurate enough.

这篇关于你如何在 Postgres 中找到所有表的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！