🍩 📬 🏓 加快POSTGRESQL计数（*） 👩🏿‍💼 🧒🏼 🕴🏽

经常有人抱怨PostgreSQL中的count（*）很慢。

在本文中，我想探索一些选项，以便您尽快获得结果。

为什么计数（*）这么慢？

大多数人毫无疑问地理解以下请求将被缓慢执行：

SELECT count(*)
FROM /*   */;

毕竟，这是一个复杂的查询，PostgreSQL必须先计算结果，然后才能知道它将包含多少行。

但是，许多人发现以下查询很慢时会感到震惊：

SELECT count(*) FROM large_table;

但是，如果您再想一想，以上所有条件均成立：PostgreSQL必须先计算结果集，然后才能对其计数。由于“魔术行计数器”未存储在表中（如MyISAM MySQL中一样），因此对行进行计数的唯一方法是查看它们。

因此，count（*）通常执行顺序表扫描，这可能会非常昂贵。

计数（）中的“ ”是否有问题？

SELECT * FROM ...中的“ *”适用于所有列。因此，许多人发现使用count（*）效率低下，而是使用count（id）或count（1）代替。

但是计数（*）中的“ *”完全不同，它只是表示“字符串”而根本不扩展（实际上，它是“带有零参数的集合”）。计数（1）或计数（id）实际上比计数（*）慢，因为必须检查参数是否为NULL（与大多数聚合一样，计数忽略参数NULL）。

因此，避免使用“ *”将不会有任何效果。

使用仅索引扫描

试图扫描一个小的索引而不是整个表来计算行数是很诱人的。但是，由于PostgreSQL的多版本并发管理策略，这并不是那么简单。该行的每个版本（“元组”）都包含有关该数据库快照可见的信息。但是，此（冗余）信息未存储在索引中。因此，通常不足以对索引中的条目进行计数，因为PostgreSQL必须查看表条目（“堆元组”）以确保索引条目可见。

为了减轻这个问题，PostgreSQL实现了可见性图，这是一种数据结构，用于存储有关表块中所有元组是否对所有人可见的信息。
如果表中的大多数块都是完全可见的，则索引扫描不需要经常访问一堆元组来确定可见性。这种索引扫描称为“仅索引扫描”，并且扫描索引以对行进行计数通常会更快。

现在是VACUUM支持可见性图，因此，如果您想使用较小的索引来加快计数（*），请确保足够频繁地进行自动清理。

使用数据透视表

我在上面写道，PostgreSQL不在表中存储行数。

保持这样的行计数是很大的开销，因为此事件在每次数据修改时都会发生并且不会带来回报。那将是一个糟糕的交易。此外，由于不同的请求可以看到不同版本的字符串，因此还必须对计数器进行版本控制。

但是没有什么可以阻止您自己实现这样的线路计数器。
假设您要跟踪mytable中的行数。您可以按照以下步骤进行操作：

START TRANSACTION;
 
CREATE TABLE mytable_count(c bigint);
 
CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER mytable_count_mod
   AFTER INSERT OR DELETE ON mytable
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();
 
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;
 
COMMIT;

我们在一个事务中进行所有操作，因此不会因发生环回情况而“丢失”并发事务上的任何数据更改。
这是通过CREATE TRIGGER命令将表锁定在SHARE ROW EXCLUSIVE模式中来保证的，该模式可以防止所有并发更改。
缺点是所有并行数据修改都必须等到执行SELECT count（*）为止。

这为我们提供了一个非常快速的计数（*）替代方法，但以减慢表中所有数据更改的代价为代价。使用延迟约束触发器可确保mytable_count中的行锁尽可能短，以提高并发性。

尽管该计数器表可以接收许多更新，但没有危险没有“表膨胀”，因为所有这些都是“热”更新（HOT更新）。

您真的需要数（*）

有时最好的解决方案是寻找替代方案。

通常，近似值足够好，您不需要确切的数量。在这种情况下，您可以使用PostgreSQL用于调度查询的分数：

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

该值会同时通过自动真空和自动分析进行更新，因此它不得超过10％。您可以减少此表的autovacuum_analyze_scale_factor，以便自动分析在该表上更频繁地运行。

估计查询结果数

到目前为止，我们一直在探索如何加快表行的计数。

但是有时您需要知道SELECT语句将返回多少行而无需实际执行查询。

显然，获得此问题的准确答案的唯一方法是完成请求。但是，如果等级足够好，则可以使用PostgreSQL优化器来获得它。

以下简单函数使用动态SQL和EXPLAIN来获取作为参数传递的查询执行计划，并返回行数的估计值：

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

不要使用此函数来处理不受信任的SQL语句，因为它固有地容易受到SQL注入的攻击。

加快POSTGRESQL计数（*）