PostgreSQL 关系级统计信息很简单，小白也能轻松拿捏优化器！

准备工作

让我们开始设置一个小的测试用例：

CREATE TABLE test1 (id integer, str text);

INSERT INTO test1 (id, str)
  SELECT i, repeat('x', 64) as str
    FROM generate_series(1, 240000) AS s(i);

VACUUM ANALYZE test1;

基本统计信息

基本的关系级统计信息存储在系统表pg_class中。统计信息包括以下数据：

• • 关系的行数（reltuples）。
• • 关系的大小，以页面为单位（relpages）。
• • 在关系的可见性映射表中标记的页数（relallvisible）。

SELECT reltuples, relpages, relallvisible
  FROM pg_class WHERE relname = 'test1';
 reltuples | relpages | relallvisible
-----------+----------+---------------
    240000 |     2963 |          2963
(1 row)

对于没有条件（过滤器）的查询，基数估计值将等于reltuples：

EXPLAIN SELECT * FROM test1;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on test1  (cost=0.00..5363.00 rows=240000 width=69)
(1 row)

统计信息是如何收集的？

统计信息是在自动或手动分析期间收集的。基本统计信息是重要信息，在执行某些操作时也会进行计算，例如VACUUM FULL、CLUSTER、CREATE INDEX和REINDEX。系统还会在 VACUUM 清理期间更新统计信息。

为了收集统计信息，分析器会随机选择 300 × default_statistics_target 行（默认值为 100，因此总共 30,000 行）。此处不考虑表大小，因为要获得足够准确的统计信息，总体数据集大小对于样本大小被认为影响不大。

从 300 × default_statistics_target 个随机页面中选择随机的行。如果表大小小于所需的样本大小，则分析器会读取整个表。

在大表中，统计信息会不太精确，因为分析器不会扫描每一行。即使有扫描，统计信息也总会有些过时，因为表数据在不断变化。无论如何，我们不需要统计信息那么精确：高达一个数量级的变化仍然足够准确，也可以产生出适当的计划。

示例

让我们创建一个test1表的副本表，并禁用 autovacuum，这样我们就可以控制何时进行分析。

CREATE TABLE test1_copy(LIKE test1)
  WITH (autovacuum_enabled = false);

新表尚无统计信息：

SELECT reltuples, relpages, relallvisible
  FROM pg_class WHERE relname = 'test1_copy';
 reltuples | relpages | relallvisible
-----------+----------+---------------
        -1 |        0 |             0
(1 row)

值 reltuples = −1（在 PostgreSQL 14 和更高版本中），可帮助我们区分从未收集过统计信息的表和没有任何行的表。

通常情况下，新创建的表会立即填充。规划器对新表一无所知，因此默认情况下会假定表的大小为 10 个页面：

EXPLAIN SELECT * FROM test1_copy;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on test1_copy  (cost=0.00..22.70 rows=1270 width=36)
(1 row)

规划器根据单行的宽度计算行数。宽度通常是在分析过程中计算的平均值。但是，这一次没有分析数据，因此系统会根据列数据类型来估算宽度。

让我们将数据从test1复制到新表中，并运行分析器：

INSERT INTO test1_copy SELECT * FROM test1;

ANALYZE test1_copy;

现在，统计信息与实际行数匹配。该表足够紧凑，分析器可以遍历每一行：

SELECT reltuples, relpages, relallvisible
  FROM pg_class WHERE relname = 'test1_copy';
 reltuples | relpages | relallvisible
-----------+----------+---------------
    240000 |     2963 |             0
(1 row)

VACUUM 清理后，relallvisible值将更新：

VACUUM test1_copy;

SELECT relallvisible FROM pg_class WHERE relname = 'test1_copy';
 relallvisible
---------------
          2963
(1 row)

此值在估算仅索引扫描的成本时会用到。

让我们在保留旧的统计信息的同时，将行数加倍，看看规划器得出的基数是多少：

INSERT INTO test1_copy SELECT * FROM test1;

SELECT count(*) FROM test1_copy;
 count
--------
 480000
(1 row)

EXPLAIN SELECT * FROM test1_copy;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on test1_copy  (cost=0.00..10726.00 rows=480000 width=69)
(1 row)

尽管pg_class中的数据已过时，但估计是准确的：

SELECT reltuples, relpages
  FROM pg_class WHERE relname = 'test1_copy';
 reltuples | relpages
-----------+----------
    240000 |     2963
(1 row)

规划器注意到数据文件的大小不再与旧的relpages值匹配，因此它适当地扩展reltuples，以尝试提高准确性。文件大小增加了一倍，因此相应地调整了行数（假定数据密度不变）：

SELECT reltuples * (pg_relation_size('test1_copy') / 8192) / relpages
  FROM pg_class WHERE relname = 'test1_copy';
 ?column?
----------
   480000
(1 row)

此调整并不总是有效（例如，您可以删除几行，但是估计值不会变化），但是当进行重大更改时，此方法允许统计信息保留，直到分析器再次启动。