计数估计

代码片段

count_estimate 函数

适用于 PostgreSQL

任何版本

编写语言

sql

依赖

无

作者：Erwin Brandstetter，Michael Fuhr
评论者：Emanuel Calvo Franco

基本 SQL 标准查询用于统计表中的行数是

SELECT count(*) FROM table_name;

这可能会很慢，因为 PostgreSQL 必须检查所有行的可见性，这是由于MVCC 模型。

如果你不需要精确的计数，来自目录表pg_class的当前统计信息可能就足够了，而且对于大型表来说，获取速度快得多。

SELECT reltuples AS estimate FROM pg_class WHERE relname = 'table_name';

 estimate
-----------
       100

名为"table_name"的表可以存在于数据库的多个模式中，在这种情况下，此查询将返回多个行。为了消除歧义

SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;

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

引用Postgres 13 关于pg_class.reltuples的手册：

Number of live rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.

如果你最近没有进行ANALYZE（上次更改之后），估计值会或多或少地偏离。
如果你正在运行自动清理守护进程，这是现代 PostgreSQL 的默认设置，ANALYZE也会自动运行（临时表除外，需要手动关注）。因此，除非你最近有重大更改，否则估计值应该很好。

对于更复杂的查询（不仅仅是统计表的所有行），或者如果你无法从目录表pg_class中SELECT（默认情况下，每个用户都可以），请考虑使用Michael Fuhr 编写的 plpgsql 函数，它从EXPLAIN输出中收集信息

CREATE FUNCTION count_estimate(query text)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
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
$func$;

以及这个更新的版本，它利用了Postgres 9.0中添加的 JSON 格式化功能

CREATE OR REPLACE FUNCTION count_estimate(
    query text
) RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
    plan jsonb;
BEGIN
    EXECUTE 'EXPLAIN (FORMAT JSON)' || query INTO plan;
    RETURN plan->0->'Plan'->'Plan Rows';
END;
$$;

演示

CREATE TEMP TABLE tbl AS SELECT * FROM generate_series(1, 1000) AS t;
ANALYZE tbl;

SELECT count_estimate('SELECT * FROM tbl WHERE t < 100');

 count_estimate 
----------------
            100

EXPLAIN SELECT * FROM tbl WHERE t < 100;

                      QUERY PLAN
------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..35.00 rows=100 width=4)
   Filter: (t < 100)

正如你所见，这是一个估计值 - 实际计数将是 99。

相关网络资源

• 来源材料：为什么选择 PostgreSQL 而不是 MySQL（还讨论了这与 MySQL 的不同之处）
• Stackoverflow 上的查询替代方案：在 PostgreSQL 中快速查找表的行数