postgresql - 使用 where 条件进行更快的 Postgres 计数

Question

我需要用 where 子句计算表中的总行数。我的应用程序可以容忍一定程度的不准确。

SELECT count(*) AS "count" FROM "Orders" AS "Order" WHERE "Order"."orderType" = 'online' AND "Order"."status" = 'paid';

但很明显，这是一个非常慢的查询。我遇到了 this 答案，但它返回了表中所有行的计数。

当我有 where 子句时，什么是更快的计数方法？我正在使用 sequelize 的 ORM，因此 sequelize 中的任何相关方法也会有所帮助。

因此，执行 EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) AS "count" FROM "Orders" AS "Order" WHERE "Order"."orderType" = 'online' AND "Order"."status" != 'paid'; 会返回以下信息:

Aggregate  (cost=47268.10..47268.11 rows=1 width=8) (actual time=719.722..719.723 rows=1 loops=1)
 Buffers: shared hit=32043
  ->  Seq Scan on ""Orders"" ""Order""  (cost=0.00..47044.35 rows=89501 width=0) (actual time=0.011..674.316 rows=194239 loops=1)
       Filter: (((status)::text <> 'paid'::text) AND ((""orderType"")::text = 'online'::text))
        Rows Removed by Filter: 830133
        Buffers: shared hit=32043
Planning time: 0.069 ms
Execution time: 719.755 ms

Answer 1

My application can tolerate some level of inaccuracy.

这在 PostgreSQL 中很难利用。获得近似答案相当容易，但很难限制该答案的近似程度。总会有估计可能非常错误的情况，如果不做获得准确答案所需的工作，就很难知道这种情况何时发生。

在您的查询计划中，它下降了 2.17 倍。这够好吗？

(cost=0.00..47044.35 rows=89501 width=0) (actual time=0.011..674.316 rows=194239 loops=1)

或者，您能否在其他维度上限制可容忍的不准确性？就像“在最后一个小时的某个时间点准确”？有了这种容忍度，您可以制作一个物化 View 来部分汇总数据，例如:

create materialized view order_counts as 
    SELECT "orderType", "status", count(*) AS "count" FROM "Orders"
    group by 1,2;

然后使用您的 WHERE 子句从中提取计数(并可能重新总结它们)。其有效性取决于“orderType”和“status”的组合数远小于主表中的总行数。您必须设置计划作业以定期刷新 matview。没有实现让 PostgreSQL 重写您的原始查询以使用 matview，您必须自己重写它。

您向我们展示了两个不同的查询， status = 'paid' 和 status != 'paid' 。其中之一是错误，还是反射(reflect)了您实际想要运行的查询的变化？在这个类似查询池中，还有哪些其他方面可能有所不同？您应该能够使用索引来提高一些速度，但具体哪个索引将取决于您的查询。对于相等查询，您可以在索引中包含“状态”。对于不等式查询，这不会有多大好处，因此您可以使用部分索引 WHERE status<>'paid' 。 (但是，例如，如果“付费”更改为“拖欠”，则该索引将没有用处。)