如何在Postgres中查找所有表的行数

Question

我想找到一种方法在Postgres中找到所有表的行数。我知道可以使用以下方法逐个查询表的行数：

SELECT count(*) FROM table_name;

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

- mmrobins

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内部使用的有用信息数量来做出决定。对于基本计数目的，只是为了查看一般情况下的大小，任何一个都足够准确。

- Greg Smith

---

为了完整起见，请将以下内容添加到第一选项中（感谢@a_horse_with_no_name）：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 3 DESC;请注意，我会尽力使翻译通俗易懂，但不会更改原文的含义或提供额外的解释。 - estani

@Greg Smith，哪个版本引入了n_live_tup？我的Redshift数据库缺少该列。它是Postgres 8.0.2的一个派生版本。 - Iain Samuel McLean Elder

“第二种方法”的查询（使用pg_stat_user_tables）在我的情况下返回的大部分是0，因为从未运行过ANALYZE。我不想在每个模式/表上运行ANALYZE并永远等待答案，因此我首先使用“第三种方法”检查了结果，那个方法（使用pg_class）返回非常准确的计数。 - Brian D

@atsu85 我很惊讶你能读懂那个......因为它从未被添加到帖子中，我已经添加了一个新答案，所以现在更清楚其中发生了什么。 - estani

优秀的回答。请注意，n_live_tup可能会非常不可靠。我刚刚在一个有近2000行的表上得到了28。SELECT COUNT(*)查询似乎是唯一可靠获取准确计数的方法。我对Postgres没有缓存这些元数据感到惊讶。 - undefined

Answer 2

这里有一个解决方案，不需要使用函数就能准确计算每个表的数量：

select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t

query_to_xml函数会执行传入的SQL查询并返回一个包含结果（该表的行数）的XML。外部的xpath()函数将从该xml中提取计数信息并将其转换为数字。

派生表并不是必要的，但它使得xpath()函数更容易理解 - 否则整个query_to_xml()函数需要被传递给xpath()函数。

- user330315

---

@a_horse_with_no_name，在执行时，对于繁忙和巨大的表格会有任何性能问题吗？ - Spike

@Spike：相对于什么而言，性能问题有多严重？主要的性能瓶颈在于对每个表运行“select count(*)”操作。 - user330315

@Spike：xpath() 函数只适用于一个 单独的 行- count(*) 的结果。 - user330315

这提供了一个真实的计数，而接受的答案没有，这是可以预料的。谢谢！ - Ismail Yavuz

按行数降序排序 - mathewsun

Answer 3

要获取估计值，请参见Greg Smith的答案。

要获取精确计数，请注意到目前其他答案存在一些问题，其中一些问题比较严重（请参见下文）。以下是一个希望更好的版本：

CREATE FUNCTION rowcount_all(schema_name text default 'public')
  RETURNS table(table_name text, cnt bigint) as
$$
declare
 table_name text;
begin
  for table_name in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
  LOOP
    RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
       table_name, schema_name, table_name);
  END LOOP;
end
$$ language plpgsql;

该函数需要一个模式名称作为参数，如果没有给定参数，则使用public。

如果要处理特定的模式列表或来自查询的列表而不修改函数，则可以像这样从查询中调用它：

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

这将生成一个包含模式、表和行数的三列输出。

现在，其他答案中存在一些问题，而此函数避免了这些问题：

• 表和架构名称不能在可执行的 SQL 中注入，除非使用quote_ident或更现代的format()函数及其%I格式字符串进行引用。否则，一些恶意人可能命名他们的表为tablename;DROP TABLE other_table，这是完全有效的作为表名。

• 即使没有SQL注入和有趣的字符问题，表名可能存在不同大小写的变体。如果一个表名为ABCD，另一个表名为abcd，那么SELECT count(*) FROM ...必须使用带引号的名称，否则它将跳过ABCD并将abcd计数两次。格式的%I会自动执行此操作。

• 在旧版本的Postgres中，information_schema.tables列表中包含自定义复合类型以及表，即使table_type为 'BASE TABLE'。因此，我们不能在information_schema.tables上迭代，否则我们会冒险执行select count(*) from name_of_composite_type，这将失败。然而，pg_class where relkind='r'应该始终正常工作。

• COUNT()的类型为bigint，而不是int。可能存在超过21.5亿行的表（虽然在这些表上运行count(*)是个坏主意）。

• 一个永久类型无需为一个函数创建，即可返回具有多个列的结果集。RETURNS TABLE(definition...)是更好的选择。

- Daniel Vérité

Answer 4

如果您正在尝试评估需要哪个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

然后运行一个正则表达式搜索和替换，如下所示：

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

至：

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
;

你需要手动删除最后一个union并在末尾添加分号。

在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
[..]

- Aur Saraf

---

在Atom中，我必须像这样使用正则表达式进行搜索和替换：select '$1', count(*) from $1 union/g - chuck

此外，该帖子中提到：“您需要删除union并在末尾添加分号。”这是一个打字错误。您需要删除/g（保留union）并在末尾添加一个分号(;)。不要忘记在分号之前删除最后一个union。 - chuck

别忘了在分号之前删除最后一个 union。这就是我的意思 :) 我加上了“last”一词以澄清。 - Aur Saraf

对于VSCode，对我有效的是select '$1'，count(*) from $1 union。 - Karol Selak

Answer 5

如果您不介意潜在的过时数据，您可以访问与查询优化器使用相同的统计信息。

类似于：

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

- ig0774

---

如果您的自动清理间隔太长或者您没有在表上运行手动的ANALYZE，那么统计数据可能会严重偏差。这取决于数据库负载和数据库的配置（如果统计数据更新得更频繁，统计数据将更准确，但可能会降低运行时性能）。最终，获取准确数据的唯一方法是对所有表运行select count(*) from table。 - ig0774

Answer 6

简单的两个步骤：
（注意：不需要更改任何内容-只需复制粘贴）
1. 创建函数

create function 
cnt_rows(schema text, tablename text) returns integer
as
$body$
declare
  result integer;
  query varchar;
begin
  query := 'SELECT count(1) FROM ' || schema || '.' || tablename;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

2. 运行此查询以获取所有表的行数

select sum(cnt_rows) as total_no_of_rows from (select 
  cnt_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE') as subq;

或者

按表获取行数

select
  table_schema,
  table_name, 
  cnt_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE'
order by 3 desc;

- Raju Sah

Answer 7

不确定在bash中的答案是否适合您，但是FWIW（顺便说一下）...

PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""
TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")

for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
                SELECT   '$TABLENAME',
                         count(*) 
                FROM     $TABLENAME
                \""
    eval "$PGCOMMAND"
done

- Stew-au

---

本质上，这只是和原帖中的 select count(*) from table_name; 相同。 - Noach Magedman

@NoachMagedman - 这是一种替代的方法 - 为什么要否定它呢 - 基本上，OP想要的是SELECT COUNT(*) FROM all_tables;！ - undefined

Answer 8

从GregSmith的回答中提取，以使其更易读。

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 3 DESC;

感谢@a_horse_with_no_name

- estani

Answer 9

通常我不太依赖统计数据，特别是在 PostgreSQL 中。

SELECT table_name, dsql2('select count(*) from '||table_name) as rownum
FROM information_schema.tables
WHERE table_type='BASE TABLE'
    AND table_schema='livescreen'
ORDER BY 2 DESC;

CREATE OR REPLACE FUNCTION dsql2(i_text text)
  RETURNS int AS
$BODY$
Declare
  v_val int;
BEGIN
  execute i_text into v_val;
  return v_val;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

- Yuri Levinsky

---

这很好，但是第一个查询也应该包括rownum值的模式。如果不同模式中存在冲突的名称，则此操作将无法按预期工作。因此，查询的这一部分应该更像是 dsql2('select count(*) from livescreen.'||table_name) 或者最好将其转换为自己的函数。 - jakub-olczyk

Answer 10

这对我有用

按照n_live_tup降序排序，选择pg_stat_user_tables中的schemaname、relname和n_live_tup。

- Pradeep Maurya

---

这给出了一些有趣的数字，但并不总是行数。好的，文档说明它是估计值：https://www.postgresql.org/docs/9.3/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW（如何更新这些统计数据？） - Tomasz Gandor