如何加速PostgreSQL中的count？

原创贺晓群 2020-10-09

12334

在PostgreSQL中经常被吐槽的问题是count(*)太慢了，下面讨论一些思路加快count(*)统计。

首先需要搞清楚为什么count(*)这么慢？

SELECT count(*)
FROM /* 复杂表关联查询 */;
复制

对于上面一个复杂查询，PostgreSQL必须先计算结果，然后才能知道它将包含多少行数据。
但是，对于下面一个简单的查询，如果也非常慢，估计你得吐槽了：

SELECT count(*) FROM /* 非常大的表*/;
复制

仔细一想，原理是一样的：PostgreSQL必须先计算结果，然后才能统计结果集。由于表中没有存储“magical row count”（像MySQL的MyISAM引擎），所以对行进行计数的唯一方法是对它们进行遍历。因此count(*)通常将对表执行顺序扫描，这个代价可能非常昂贵。

这里可能对很多人有个误区，需要先说明下：
SELECT * FROM …这里的“*”是指查询涉及到的表的所有列。因此，许多人认为使用count(*)效率低下，应该这样写count(id)或改写count(1)。但是count(*)中的“*”完全不同，它仅表示“行”，而根本没有扩展到所有列（实际上，这是“zero-argument aggregate”）。使用count(1)或count(id)实际上比count(*)还要慢，因为它们必须判断参数是否为 NULL（count与大多数集合一样，忽略NULL参数）。因此，避免使用“*”并不能提升性能。

使用仅索引扫描
尝试扫描一个小的索引而不是整个表来计算行数是很好的一个解决方案。然而，由于PostgreSQL的多版本并发控制策略，这并不是那么简单。每个行版本（“元组”）均包含可见的数据库快照的信息。但是，此信息未（冗余地）存储在索引中。因此，通常不足以对索引中的条目进行计数，因为PostgreSQL必须访问表条目（“堆元组”）以确保索引条目可见。为了缓解这个问题，PostgreSQL引入了可见性映射（visibility map），这是一种数据结构，用于存储每个人是否都可以看到表块中的所有元组。如果大多数表块都是可见的，则索引扫描不需要经常访问堆元组来确定可见性。这样的索引扫描称为“仅索引扫描”，因此会更快地扫描索引以对行进行计数。现在是VACUUM维护了可见性映射，所以如果你想使用索引来加速count(*)，请确保autovacuum在表上运行得足够频繁。

使用计数器表
在业务上使用一个表记录表行数的变化，保持这样的行数将会有一定的开销，每次数据修改都必须付出一定的代价。此外，由于不同的查询可以看到不同的行版本，因此也必须对计数器进行版本控制。如果你要跟踪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 处理
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
--初始化记录行数
INSERT INTO mytable_count SELECT count(*) FROM mytable;
 
COMMIT;
复制

我们在单个事务中进行所有操作，因此不会因竞争条件而“丢失”并发事务的数据修改。这是有保证的，因为CREATE TRIGGER将表锁定为SHARE ROW EXCLUSIVE模式，可以防止所有并发修改。不利的一面当然是所有并发数据修改必须等到SELECT count(*)完成。
这为我们提供了一个真正快速的替代count(*)的方案，但代价是减慢了表上所有数据的修改。使用延迟约束触发器将确保将行中的锁mytable_count保持得尽可能短，以提高并发性。即使此计数器表可能收到很多更新，也没有“表膨胀”的危险，因为这些都是热更新。

你真的需要count(*)吗？
通常，近似值就足够了，你不需要精确的计数。在这种情况下，您可以使用PostgreSQL用于查询计划的估值：

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

该值会同时通过autovacuum和autoanalyze进行更新，因此它的偏差不会超过10％。你可以减少autovacuum_analyze_scale_factor该表的参数值，以便自动分析在该表上运行的频率更高。

估计查询结果行数
到目前为止，我们已经研究了如何加快对表行的计数。但是有时你想知道一条SELECT语句将返回多少行而不实际运行它。显然，获得确切答案的唯一方法是执行查询。但是，如果估算值足够好，则可以使用PostgreSQL的优化器为你获取。以下简单函数使用动态SQL并EXPLAIN获取作为参数传递的查询的执行计划，并返回行计数估计值：

CREATE FUNCTION row_count(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;
$$;
复制