PostgreSQL 查询 count(*)，怎样速度最快？-51CTO.COM

介绍

有时，我们并不需要完全精确地报告出数值，如果我们无论如何都会显示1.5 million用户，那么1,568,121用户和1,569,200用户之间又有什么区别呢？在这种情况下，我们可以使用数据库的基数估计值。

使用估计值的好处是，我们可以依赖于查询数据库中的基础表，获取最近的近似基数，而不必扫描实际的表。

使用数据库基数估计表中的近似计数

数据库的基数估计值，是数据库中包含的表中可用的行数和非重复值行数的粗略计数。

这些计数，对于数据库引擎确定执行一个特定 SQL 查询的最有效计划非常有用：它应该扫描整个表还是使用索引？它应该执行哈希连接还是嵌套循环？通过很好地掌握查询中包含的数据量，所有这些问题都可以得到正确的答案，数据库引擎将它们保存在一组专用的表中。

在 PostgreSQL 中，我们可以查询pg_class，其中包含了一个特定表的最新估计值。在我们的示例中，如果我们执行：

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

估计值将显示0行。很奇怪吧？这是因为，我们在加载表数据后没有运行ANALYZE，因此数据库没有关于EXAMPLE_TBL最新的统计信息。如果我们运行：

ANALYZE EXAMPLE_TBL;

然后重新运行我们上面定义的相同SELECT语句，我们将得到更新的计数值5。我们可以使用pg_class表，来获取特定查询中COUNT(*)的估计值，但我们需要注意，该数值仅会由VACUUM、ANALYZE和其他 DDL 命令（如CREATE INDEX）进行更新。

注意：MySQL 也提供了一个类似的视图，它在INFORMATION_SCHEMA中的STATISTICS表中。

使用 EXPLAIN 结果中的近似计数

上面的解决方案是相当静态的，方案中存在假设，我们在对单个表列进行计数（而不是计算非重复值的行数），且没有应用特定的WHERE条件。PostgreSQL wiki 给出了一个建议，通过解析 EXPLAIN 命令的结果，可以获得更好的估计值，尤其是对于复杂的查询。EXPLAIN命令会为查询返回当前数据库的基数估计和执行计划，并考虑了WHERE条件的影响。

我们可以创建一个函数，来解析EXPLAIN调用的结果，如下所示：

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

然后使用下面的查询，来获取特定查询的数据库估计值：

SELECT count_estimate('SELECT DISTINCT username FROM example_tbl WHERE Qty >= 2');

EXPLAIN的结果如下：

QUERY PLAN
-----------------------------------------------------------------------
 Unique  (cost=1.07..1.08 rows=2 width=5)
   ->  Sort  (cost=1.07..1.08 rows=2 width=5)
         Sort Key: username
         ->  Seq Scan on example_tbl  (cost=0.00..1.06 rows=2 width=5)
               Filter: (qty >= 2)
(5 rows)

并且该函数能够正确检索出预期基数值2。请再次注意，该结果是估计值，实际正确的结果是3（Fred、Liza和Mary）。