快速发现 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;

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

在 Postgres 9.4+ 中，将 'myschema.mytable'::regclass 替换为 to_regclass('myschema.mytable') 不会得到任何结果，而是出现无效表名的异常。请参阅：

• 如何检查是否给定架构中存在表

更好的估计（只需很少的额外成本）

这不适用于分区表，因为对于父表，relpages 始终为 -1（而 reltuples 包含覆盖所有分区的实际估计值） - 在 Postgres 14 中进行测试。
您必须将所有分区的估计值相加。

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

这些数字是截至上次 VACUUM 或 ANALYZE 上的最新数字
桌子。然后规划器获取当前实际的页数
表（这是一个廉价的操作，不需要表扫描）。如果
与 relpages 不同，然后 reltuples 被缩放
相应地得出当前的行数估计。

Postgres 使用 src/backend/ 中定义的 estimate_rel_size utils/adt/plancat.c，它还涵盖了 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，则表似乎是空的。但我会分析来确定。 （也许还可以检查您的 autovacuum 设置。）

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

表和架构限定使其不受任何 search_path 和范围的影响。

无论哪种方式，查询始终采用 <对我来说是 0.1 毫秒。

更多网络资源：

• Postgres Wiki 常见问题解答
• Postgres wiki 页面的 count估计和count(*) 性能

TABLESAMPLE SYSTEM (n) in Postgres 9.5+

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

Like @a_horse 评论了，如果 pg_class 中的统计信息不够最新，则为 SELECT 命令添加的子句可能会很有用由于某种原因。例如：

• 没有运行 autovacuum。
• 紧接着一个大的INSERT / UPDATE / DELETE。
• TEMPORARY 表（autovacuum 未涵盖）。

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

• 行大小的分布。如果给定的块碰巧容纳比平常更宽的行，则计数会低于平常等。
• 死元组或 FILLFACTOR 占用每个块的空间。如果表中分布不均匀，估计值可能会偏离。
• 一般舍入误差。

通常，pg_class 的估计会更快、更准确。

回答实际问题

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

以及是否...

...有可能在计数超过我的常数值时，它会
停止点票（而不是等到点票结束才通知
行数更大）。

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

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

Postgres实际上会停止计数超过给定的限制，您会得到一个精确当前计数最多为 n 行（示例中为 500000 行），否则为 n 行。不过，远没有 pg_class 中估计的那么快。

Answer 2

我在 postgres 应用程序中通过运行执行此操作：

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 可以在合理的误差范围内预测它将返回多少行，那么它可能适合您。

Answer 3

参考来自此博客。

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

使用 pg_class：

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

使用 pg_stat_user_tables：

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

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;

Answer 5

文本栏有多宽？

使用 GROUP BY 时，您无法采取太多措施来避免数据扫描（至少是索引扫描）。

我建议：

1. 如果可能，更改架构以删除重复的文本数据。这样，计数将发生在“many”表中的窄外键字段上。

2. 或者，使用文本的哈希创建生成列，然后按哈希列进行分组。
   同样，这是为了减少工作量（扫描狭窄的列索引）

编辑：

您原来的问题与您的编辑不太匹配。我不确定您是否知道 COUNT 与 GROUP BY 一起使用时，将返回每个组的项目数，而不是整个表中的项目数。

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)

Answer 7

您也可以直接SELECT MAX(id) FROM;将 id 更改为表的 PK 值

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

MSDN

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

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

甚至更快（但不可靠）的方法有详细介绍 这里。