在PostgreSQL中快速发现表格的行数的方法

Question

我需要知道表格中的行数以计算百分比。如果总计数大于某个预定义常量，我将使用该常量的值。否则，我将使用实际行数。

我可以使用 SELECT count(*) FROM table。但是如果我的常量值为 500,000，而表格中有 5,000,000,000 行，则统计所有行将浪费很多时间。

是否可能在超过我的常量值后停止计数？

我只需要在行数低于给定限制时得到准确的行数。否则，如果计数超过限制，则使用限制值并尽快得到答案。

类似这样：

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

- Renato Dinhani

---

你能否尝试选择前 n 行，其中 n = _常数 + 1_？如果返回的行数超过了你的常数，那么你就知道应该使用你的常数，如果没有超过，那么就没问题了。 - g.d.d.c

你的表中有身份证或自增字段吗？ - Sparky

@Sparky：序列支持的主键不能保证连续，行可能会被删除或由于中止的事务而导致间隙。 - mu is too short

@Flimzy 两者都需要。如果低于阈值，我需要精确数字；否则，我将使用阈值作为限制。 - Renato Dinhani

@RenatoDinhaniConceição：你能解释一下你想要解决的确切问题吗？我认为我下面的答案解决了你最初提出的问题。更新后，它看起来像你想要计算count(*)以及许多其他字段。如果你能准确地解释你想做什么，那会很有帮助。谢谢。 - Ritesh

Answer 1

在PostgreSQL中，计算大表的行数被认为是缓慢的。MVCC模型需要对存活行进行完整的计数以得到精确的数字。如果计数不需要像您的情况一样精确，有一些解决方法可以极大地加快速度。

（请记住，即使“精确”计数在并发写入负载下也有可能失败。）

精确计数

对于大表来说是缓慢的。
在并发写入操作中，它可能在获取时就已经过时了。

SELECT count(*) AS exact_count FROM myschema.mytable;

估计

极其快速：

SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';

通常，估计值非常接近。有多接近，取决于是否运行了足够的ANALYZE或VACUUM - 其中“足够”由表的写入活动水平定义。

更安全的估算

上述方法忽略了一个可能性，即一个数据库中具有相同名称但位于不同模式下的多个表。为了解决这个问题：

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = 'mytable'
AND    n.nspname = 'myschema';

将real数转换为bigint格式可以很好地格式化数字，尤其是对于大数。

更好的估计

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

更快、更简单、更安全、更优雅。请参阅对象标识符类型的手册。

在Postgres 9.4+中，用to_regclass('myschema.mytable')替换'myschema.mytable'::regclass，可以得到无效表名的情况下不会抛出异常。请参见：

• 如何检查给定模式中是否存在表

更好的估计（成本很低）

这对于分区表是无效的，因为父表的relpages始终为-1（而reltuples包含涵盖所有分区的实际估计值）- 在Postgres 14中测试过。
您必须将所有分区的估计值相加。

我们可以做Postgres计划器所做的事情。引用手册中的行估计示例：

这些数字是根据表上最后一次的VACUUM或ANALYZE而确定的。规划器会获取表中当前实际的页面数（这是一个廉价的操作，不需要进行表扫描）。如果实际页面数与relpages不同，则按比例缩放reltuples以得出当前行数估计值。 Postgres使用src/backend/utils/adt/plancat.c中定义的estimate_rel_size，该函数还涵盖了没有数据在pg_class中的特殊情况，因为关系从未被清理过。我们可以在SQL中执行类似的操作：

最简形式

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'mytable'::regclass;  -- your table here

安全且明确

SELECT (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
             WHEN c.relpages = 0 THEN float8 '0'  -- empty table
             ELSE c.reltuples / c.relpages END
     * (pg_catalog.pg_relation_size(c.oid)
      / pg_catalog.current_setting('block_size')::int)
       )::bigint
FROM   pg_catalog.pg_class c
WHERE  c.oid = 'myschema.mytable'::regclass;      -- schema-qualified table here

不会因为空表或从未使用VACUUM或ANALYZE的表而中断。关于pg_class的手册:

如果该表尚未进行过清理或分析，则reltuples包含-1，表示未知行数。

如果此查询返回NULL，请运行ANALYZE或VACUUM以进行表重复操作。(或者，您可以像Postgres那样基于列类型估算行宽度，但这很繁琐且容易出错。)

如果此查询返回0，则该表似乎为空。但我建议运行ANALYZE以确保。(也许要检查您的autovacuum设置。)

通常，block_size为8192。current_setting('block_size')::int涵盖了罕见的例外情况。

表和模式限定使其免受任何search_path和范围的影响。

无论如何，对我来说，该查询始终需要小于0.1毫秒。

更多网络资源:

• Postgres Wiki FAQ（Postgres维基常见问题解答）
• Postgres维基页面count estimates（计数估计）和count(*) performance（计数性能）

TABLESAMPLE SYSTEM (n) in Postgres 9.5+

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

像@a_horse评论的那样，对于SELECT命令添加的子句可能在某些情况下非常有用，例如：

• 没有运行autovacuum。
• 在大型INSERT/UPDATE/DELETE之后立即执行。
• TEMPORARY表（不受autovacuum覆盖）。

这只查看随机选择的一个n%（在示例中为1）块，并计算其中的行数。更大的样本会增加成本并减少误差，你可以自己选择。准确性取决于更多因素：

• 行大小的分布。如果给定的块恰好包含比通常更宽的行，则计数低于通常值等。
• 死元组或FILLFACTOR占据每个块的空间。如果在整个表中分布不均，则估计可能有误。
• 一般的舍入误差。

通常，从pg_class得出的估计值将更快且更准确。

实际问题的答案

首先，我需要知道该表中的行数，如果总计数大于某个预定义常量，

以及它是否......

如果计数超过我的常量值，它将停止计数（而不是等待计数完成以通知行计数大于此值）。

是的。 您可以使用带有 LIMIT 的子查询：

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

Postgres实际上会在给定的限制之外停止计数，你可以得到最多n行（例如500000）的确切和当前的计数，否则只能得到n。然而，这种方法不如pg_class中的估算快。

- Erwin Brandstetter

---

最终我使用改进后的查询更新了Postgres Wiki页面。 - Erwin Brandstetter

使用“tablesample”子句，应该可以快速获取9.5的估计值，例如：select count(*) * 100 as cnt from mytable tablesample system (1); - user330315

@JeffWidman：所有这些估计值都可能因为各种原因大于实际行数。最主要的是，在此期间可能发生了删除操作。 - Erwin Brandstetter

@ErwinBrandstetter 意识到这个问题很旧，但如果您将查询包装在子查询中，然后进行限制，这仍然有效吗？还是整个子查询将在外部查询中执行然后进行限制。SELECT count(*) FROM (Select * from (SELECT 1 FROM token) query) LIMIT 500000) limited_query;（我之所以问是因为我正在尝试从任意查询中获取计数，该查询可能已经有了限制子句）。 - Nicholas Erdenberger

@NicholasErdenberger：这取决于子查询。Postgres可能需要考虑比限制更多的行（例如使用“ORDER BY something”时无法使用索引，或使用聚合函数）。除此之外，只有来自子查询的有限行被处理。 - Erwin Brandstetter

Answer 2

我曾在一个PostgreSQL应用程序中通过执行以下命令来完成此操作：

EXPLAIN SELECT * FROM foo;

然后使用正则表达式或类似的逻辑来检查输出。对于一个简单的SELECT *查询，输出的第一行应该类似于这样：

Seq Scan on uids  (cost=0.00..1.21 rows=8 width=75)

您可以使用rows=(\d+)值作为返回行数的大致估计，如果估计结果小于您设定的阈值的1.5倍（或者您认为对应的数字更合适），那么只有在这种情况下才进行实际的SELECT COUNT(*)操作。

根据查询的复杂程度，该估算值可能会变得越来越不准确。事实上，在我的应用程序中，随着我们添加连接和复杂条件，它变得如此不准确，以至于即使知道我们将返回多少行数据的数量级也是完全没有意义的，因此我们不得不放弃该策略。

但是，如果您的查询足够简单，Pg可以在一定的误差范围内预测将返回多少行数据，那么这个方法可能适用于您。

- Jonathan Hall

---

这对于交互使用实际上非常方便 :) - exyi

Answer 3

参考此博客。

您可以使用以下查询来查找行数。

使用pg_class：

 SELECT reltuples::bigint AS EstimatedCount
    FROM   pg_class
    WHERE  oid = 'public.TableName'::regclass;

Using pg_stat_user_tables:

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

- Anvesh

---

请注意，您需要对表进行VACUUM ANALYZE操作才能使用此方法。 - William Abma

Answer 4

这是实现@Jonathan Hall答案的函数（不是我的）。

CREATE OR REPLACE FUNCTION count_estimate(query text) RETURNS integer AS $$
DECLARE
    rec   record;
    rows  integer;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
            rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
            EXIT WHEN rows IS NOT NULL;
        END LOOP;
    RETURN rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

- gshilin

Answer 5

文本列有多宽？

使用 GROUP BY 时，你无法避免数据扫描（至少是索引扫描）。

我建议：

1. 如果可能的话，更改模式以消除文本数据的重复。这样，计数将在“多”表中的窄外键字段上进行。

2. 或者，创建一个带有文本哈希值的生成列，然后按哈希列进行分组。 同样，这是为了减轻工作量（通过窄列索引扫描）。

编辑：

你最初的问题与你的编辑不太匹配。我不确定你是否知道，在使用 GROUP BY 时，COUNT 将返回每个组中项目的计数，而不是整个表中项目的计数。

- Chris Bednarski

Answer 6

在Oracle中，您可以使用rownum来限制返回的行数。我猜其他SQL也存在类似的结构。因此，对于您提供的示例，您可以将返回的行数限制为500001并应用count(*)：

SELECT (case when cnt > 500000 then 500000 else cnt end) myCnt
FROM (SELECT count(*) cnt FROM table WHERE rownum<=500001)

- Ritesh

---

SELECT count(*) cnt FROM table 总是会返回一行结果。不确定 LIMIT 如何在这里提供任何帮助。 - Chris Bednarski

@ChrisBednarski：我在Oracle数据库上验证了我的答案的版本。它运行良好，解决了我认为是OP问题的问题（使用rownum的count(*)只需0.05秒，而不使用rownum则需要1秒）。是的，SELECT count(*) cnt FROM table总是会返回1行，但是通过LIMIT条件，当表的大小超过500000且<size>小于等于500000时，它将返回“500001”。 - Ritesh

你的PostgreSQL查询完全是胡言乱语。在语法和逻辑上都是错误的。请纠正或删除它。 - Erwin Brandstetter

@ErwinBrandstetter：已删除，没想到PostgreSQL如此不同。 - Ritesh

@allrite：毫无疑问，你的Oracle查询很好用。但LIMIT的工作方式不同。基本上，它限制了返回给客户端的行数，而不是数据库引擎查询的行数。 - Chris Bednarski

Answer 7

你也可以只使用 SELECT MAX(id) FROM <table_name>；将 id 更改为表的主键

- masterforker

---

如果强制使用从1开始的无间隔整数ID，那将是一个非常奇特的情况。 - Erwin Brandstetter

Answer 8

对于SQL Server（2005或以上版本），一种快速而可靠的方法是：

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('MyTableName')   
AND (index_id=0 or index_id=1);

关于sys.dm_db_partition_stats的详细信息在MSDN中有解释。

该查询从（可能）分区表的所有部分添加行。

index_id=0是无序表（堆），index_id=1是有序表（聚集索引）

更快速（但不可靠）的方法在这里详细说明。

- DrKoch

---

错误的数据库，伙计。问题是关于PostgreSQL的。 - Shayne