sql - 在 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;

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';

投到 bigint格式化 real数字很​​好，特别是对于大计数。
更好

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

更快、更简单、更安全、更优雅。请参阅 Object Identifier Types 上的手册.
替换 'myschema.mytable'::regclass与 to_regclass('myschema.mytable')在 Postgres 9.4+ 中，除了无效表名的异常之外，什么也得不到。见:

How to check if a table exists in a given schema

更好(只需很少的额外成本)
我们可以做 Postgres 规划器所做的。引用 Row Estimation Examples in the manual :

These numbers are current as of the last VACUUM or ANALYZE on thetable. The planner then fetches the actual current number of pages inthe table (this is a cheap operation, not requiring a table scan). Ifthat is different from relpages then reltuples is scaledaccordingly to arrive at a current number-of-rows estimate.

所以:

SELECT ((reltuples / relpages)
      * (pg_relation_size('myschema.mytable') / current_setting('block_size')::int)
       )::bigint
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

通常，我们可以替换 current_setting('block_size')::int只需 8192让它稍微快一点。但是查询只需要大约 1 毫秒。
更多网络资源:

Postgres Wiki FAQ

Postgres wiki 页面 count estimates和 count(*) performance

TABLESAMPLE SYSTEM (n) 在 Postgres 9.5+

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

赞 @a_horse commented ，为 SELECT 添加的子句如果 pg_class 中的统计信息很有用，则命令很有用由于某种原因不够当前。例如:

否 autovacuum运行。

紧接着大INSERT/UPDATE/DELETE .

TEMPORARY表(不包括在 autovacuum 中)。

这仅查看随机 n %(在示例中为 1)块的选择并计算其中的行数。更大的样本会增加成本并减少错误，您的选择。准确性取决于更多因素:

行大小的分布。如果给定的块碰巧包含比通常更宽的行，则计数低于通常等。

死元组或 FILLFACTOR每块占用空间。如果表中分布不均，则估计值可能会偏离。

一般舍入误差。

通常，估计来自 pg_class会更快更准确。
回答实际问题

First, I need to know the number of rows in that table, if the totalcount is greater than some predefined constant,

以及是否...

... is possible at the moment the count pass my constant value, it willstop the counting (and not wait to finish the counting to inform therow count is greater).

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

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

Postgres 实际上会停止超出给定限制的计数，您可以获得最多 n 行(示例中为 500000)的准确和当前计数，否则为 n。没有 pg_class 中的估计那么快，不过。