这对我很有效

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

下面的查询将给出每个表的行数和大小

选择table_schema, table_name， pg_relation_size('“' | | table_schema | |”“。”“| | table_name | |”“”)/ 1024/1024 size_MB, (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 从information_schema.tables order by size_MB

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

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.

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中使用来做出决定。出于基本的计数目的，只是为了了解事物的总体大小，这两种方法都应该足够准确。

我想从所有表的总数+表的列表与他们的计数。有点像绩效表，显示大部分时间都花在了哪里

WITH results AS ( 
  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'
     GROUP BY schemaname, relname, reltuples
)

SELECT * FROM results
UNION
SELECT 'all' AS schemaname, 'all' AS relname, SUM(reltuples) AS "reltuples" FROM results

ORDER BY reltuples DESC

当然，你也可以在这个版本的结果上加上一个LIMIT条款，这样你就可以得到最大的n个违例者以及总数。

需要注意的一点是，在大量进口后，您需要让它静置一段时间。我通过跨几个表向数据库中添加5000行(使用实际导入数据)来测试这一点。它显示了大约一分钟的1800条记录(可能是一个可配置的窗口)

这是基于https://stackoverflow.com/a/2611745/1548557的工作，所以感谢并认可在CTE中使用的查询

对于那些试图评估他们需要哪一个Heroku计划，又不能等待Heroku的慢行计数器刷新的人来说，一个简单实用的答案是:

基本上你想在psql中运行\dt，将结果复制到你最喜欢的文本编辑器中(它看起来像这样:

 public | auth_group                     | table | axrsosvelhutvw
 public | auth_group_permissions         | table | axrsosvelhutvw
 public | auth_permission                | table | axrsosvelhutvw
 public | auth_user                      | table | axrsosvelhutvw
 public | auth_user_groups               | table | axrsosvelhutvw
 public | auth_user_user_permissions     | table | axrsosvelhutvw
 public | background_task                | table | axrsosvelhutvw
 public | django_admin_log               | table | axrsosvelhutvw
 public | django_content_type            | table | axrsosvelhutvw
 public | django_migrations              | table | axrsosvelhutvw
 public | django_session                 | table | axrsosvelhutvw
 public | exercises_assignment           | table | axrsosvelhutvw

)，然后运行regex搜索并替换，如下所示:

^[^|]*\|\s+([^|]*?)\s+\| table \|.*$

to:

select '\1', count(*) from \1 union/g

这将会给你一个非常类似的结果:

select 'auth_group', count(*) from auth_group union
select 'auth_group_permissions', count(*) from auth_group_permissions union
select 'auth_permission', count(*) from auth_permission union
select 'auth_user', count(*) from auth_user union
select 'auth_user_groups', count(*) from auth_user_groups union
select 'auth_user_user_permissions', count(*) from auth_user_user_permissions union
select 'background_task', count(*) from background_task union
select 'django_admin_log', count(*) from django_admin_log union
select 'django_content_type', count(*) from django_content_type union
select 'django_migrations', count(*) from django_migrations union
select 'django_session', count(*) from django_session
;

(您需要删除最后一个联合，并手动在末尾添加分号)

在psql中运行它，就完成了。

            ?column?            | count
--------------------------------+-------
 auth_group_permissions         |     0
 auth_user_user_permissions     |     0
 django_session                 |  1306
 django_content_type            |    17
 auth_user_groups               |   162
 django_admin_log               |  9106
 django_migrations              |    19
[..]

我做了一个小的变化，包括所有的表，也是非公共的表。

CREATE TYPE table_count AS (table_schema TEXT,table_name TEXT, num_rows INTEGER); 

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 

BEGIN
    FOR t_name IN 
        SELECT table_schema,table_name
        FROM information_schema.tables
        where table_schema !=''pg_catalog''
          and table_schema !=''information_schema''
        ORDER BY 1,2
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name
            LOOP 
            END LOOP; 

            r.table_schema := t_name.table_schema;
            r.table_name := t_name.table_name; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

使用select count_em_all();叫它。

希望这对你有用。 保罗