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

问题描述

我在寻找一种方法来找到Postgres中所有表的行计数。我知道我可以用一个

  select count（*）from table_name; 
  

但我想看到所有表的行计数和顺序

解决方案

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

如果你想要一个真正的计数，你必须执行SELECT语句，就像你对每个表使用。这是因为PostgreSQL在行本身而不是其他任何地方保留行可见性信息，因此任何准确的计数只能是相对于某些事务。你得到一个事务在执行时的时间点看到的计数。您可以自动执行此操作，以对数据库中的每个表运行，但您可能不需要该级别的准确性，或希望等待这么长时间。

第二种方法统计信息收集器可以随时跟踪多少行活动（未被删除或由以后的更新过时）。这个值可以在繁重的活动下被关闭，但通常是一个很好的估计：

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

这也可以显示有多少行已经死亡，这本身就是一个有趣的监视器。 / p>

第三种方法是注意，系统ANALYZE命令（它由PostgreSQL 8.3定期执行以更新表统计信息）也计算行估计。你可以这样得到：

  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内部使用决定。对于基本的计数目的，只是为了看一般大的东西，要么应该是足够准确。

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 a

select count(*) from table_name;

but I'd like to see the row count for all the tables and the 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.

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.

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;

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屋！