在PostgreSQL中快速发现表的行数

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

我可以使用SELECT count(*) FROM table。但是如果我的常量值是500000年，并且我的表中有5000000000年行，计算所有行将浪费大量时间。

是否有可能在我的常数值被超过时就停止计数?

我需要精确的行数，只要它低于给定的限制。否则，如果计数超过极限，我将使用极限值，并希望尽快得到答案。

就像这样:

SELECT text,count(*), percentual_calculus()
FROM token
GROUP BY text
ORDER BY count DESC;
192866 次浏览
小开

在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)
小开

我曾经在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(*)。

根据查询的复杂程度，这个数字可能会越来越不准确。事实上，在我的应用程序中，当我们添加连接和复杂条件时，它变得非常不准确，甚至不知道在100的幂范围内我们会返回多少行，所以我们不得不放弃这种策略。

但是，如果您的查询足够简单，Pg可以在合理的误差范围内预测它将返回多少行，那么它可能适合您。

小开

文本列有多宽?

使用GROUP BY，您无法避免数据扫描(至少是索引扫描)。

我建议:

  1. 如果可能，更改模式以删除文本数据的重复。这样，计数将发生在'many'表中的一个狭窄的外键字段上。

    2. 或者，创建一个生成的列，对文本进行HASH，然后对HASH列进行GROUP BY。 同样，这是为了减少工作负载(通过窄列索引扫描)

编辑:

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

小开
最佳答案

众所周知，在PostgreSQL中，大表中的行计数很慢。MVCC模型需要活动行的完整计数以获得精确的数字。如果计数确实不必须是确切的，就像在你的情况下一样，有大大加快速度的变通方法。

(记住，即使是&;exact"Count在并发写负载下到达时可能会死。)

准确的数

慢为大表。
对于并发写操作，它可能在你得到它的那一刻就过时了 

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

极其快:

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

通常情况下，估算值非常接近。有多接近，取决于ANALYZE或VACUUM是否运行得足够多——其中"enough"由向表写入活动的级别定义。

安全评估

上面忽略了在一个数据库中使用相同名称的多个表的可能性——在不同的模式中。为了解释这一点:

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/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毫秒。

更多网络资源:


TABLESAMPLE SYSTEM (n)在Postgres 9.5+

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

和@a_horse评论一样，如果由于某种原因，pg_class中的统计数据不够最新，为SELECT命令添加的子句就会很有用。例如:

  • 没有运行autovacuum。
  • 紧接在大的INSERT / UPDATE / DELETE之后。
  • TEMPORARY表(不被autovacuum覆盖)。

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

  • 行大小的分布。如果一个给定的块刚好拥有比通常更宽的行，计数就会比通常更低等等。
  • 死元组或FILLFACTOR占用每个块的空间。如果在表中分布不均匀，估计可能会出错。
  • 一般舍入错误。

通常，来自pg_class的估计将更快更准确。

实际问题的答案

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

是否……

< p >…是否有可能此时计数通过我的常数值，它会吗 停止计数(不要等到计数结束才通知

你可以使用LIMIT子查询:

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

Postgres 实际上停止计数超出给定的限制，你将得到精确的和当前的计数，最多为n行(在示例中为500000)，否则为n。虽然没有pg_class中的估计快。

小开

对于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是有序表(聚集索引)

甚至更快(但不可靠)的方法详细在这里。

小开

参考资料来自本博客。

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

使用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;
小开

你也可以SELECT MAX(id) FROM <table_name>;将id更改为表的PK


提交答案