五月 16, 2024
摘要：在本教程中，您将学习系统表中基本的关系级别统计信息。
目录
准备工作
让我们开始设置一个小的测试用例：
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)
此调整并不总是有效（例如，您可以删除几行，但是估计值不会变化），但是当进行重大更改时，此方法允许统计信息保留，直到分析器再次启动。