了解 PostgreSQL 中的统计信息

• 数据分布统计信息
• 扩展的统计信息
• 监控统计数据

在本博文中，我们将更详细地说明每种类型。

数据分布统计信息

这些统计信息与每种关系的数据分布相关。它们提供了与关系的每个列中最常见的值、列的平均宽度、列中不同值的数目等方面相关的信息。我们在运行 ANALYZE 时或分析由 autovacuum 触发时收集统计信息，并将它们存储在 pg_statistic 系统目录（其公共可读视图为 pg_stats）中。

以下示例说明了如何生成和查看这些统计信息：

1. 创建一个表并插入一些虚拟数据：

   postgres=# CREATE TABLE test_stats(id INT, name VARCHAR);
   CREATE TABLE
   postgres=> INSERT INTO test_stats VALUES (generate_series(1,10),'test'||generate_series(1,10));
   INSERT 0 10
   postgres=> INSERT INTO test_stats VALUES (generate_series(1,10),'test'||generate_series(1,10));
   INSERT 0 10
   postgres=> INSERT INTO test_stats VALUES (generate_series(1,10),'test'||generate_series(1,10));
   INSERT 0 10
   postgres=> INSERT INTO test_stats VALUES (generate_series(11,20),'test'||generate_series(11,20));
   INSERT 0 10

2. 分析此表以生成表的统计信息：

   postgres=> ANALYZE VERBOSE test_stats ;
   INFO:  analyzing "public.test_stats"
   INFO:  "test_stats": scanned 1 of 1 pages, containing 40 live rows and 0 dead rows; 40 rows in sample, 40 estimated total rows
   ANALYZE

3. 在 pg_stats 视图中查看表的统计信息：

   postgres=> SELECT * FROM pg_stats WHERE tablename ='test_stats';
   -[ RECORD 1 ]----------+------------------------------------------------
   schemaname             | public
   tablename              | test_stats
   attname                | id
   inherited              | f
   null_frac              | 0
   avg_width              | 4
   n_distinct             | -0.5
   most_common_vals       | {1,2,3,4,5,6,7,8,9,10}
   most_common_freqs      | {0.075,0.075,0.075,0.075,0.075,0.075,0.075,0.075,0.075,0.075}
   histogram_bounds       | {11,12,13,14,15,16,17,18,19,20}
   correlation            | 0.7551595
   most_common_elems      |
   most_common_elem_freqs |
   elem_count_histogram   |
   
   -[ RECORD 2 ]----------+------------------------------------------------
   schemaname             | public
   tablename              | test_stats
   attname                | name
   inherited              | f
   null_frac              | 0
   avg_width              | 6
   n_distinct             | -0.5
   most_common_vals       | {test1,test10,test2,test3,test4,test5,test6,test7,test8,test9}
   most_common_freqs      | {0.075,0.075,0.075,0.075,0.075,0.075,0.075,0.075,0.075,0.075}
   histogram_bounds       | {test11,test12,test13,test14,test15,test16,test17,test18,test19,test20}
   correlation            | -0.19043152
   most_common_elems      |
   most_common_elem_freqs |
   elem_count_histogram   |
   
   postgres=>

如输出中所示，pg_stats 视图包含 test_stats 表的每个列的数据分布统计信息。例如，列 ID 有 20 个唯一值；但您只能查看 most_common_values 的 10 个值，因为这些值是重复的，而对于 name 列 test1、test2、... test9，则为最常见的值。最常见的值列表用于帮助计划器预测相等表达式的选择性，例如 where name='test5' 或 where state=’TX’。直方图范围用于帮助计划器预测不等式或范围表达式的选择性，例如 where id 介于 5000 – 10000 之间。

如果您查看 name 列的 correlation 列，其值为 -0.19（接近 0）。当值接近 -1 或 +1 时，由于减少了对磁盘的随机访问，因此，列的索引扫描的估算成本低于值接近 0 时的估算成本。由于只有 30 行，因此，接近 0 的值表示该列不需要索引。表中没有空值，因此 null_frac 为 0。

查询计划器需要估算由查询检索的行数，以便选择正确的查询计划。为了进行这些估算，使用了这些数据分布统计信息。

以下是需要注意的有关统计信息的一些要点：

• 对于大型表，ANALYZE 会随机对表内容进行采样，而不是检查每一行。这样一来，即使表非常大，也能在很短的时间内分析它。
• ANALYZE 考虑的样本量取决于 default_statistics_target 参数。值越大，执行 ANALYZE 所需的时间就越多，但这可能会提高计划器的估算质量。它的默认值为 100。默认值足以用来获得准确计划；但 default_statistics_target 是全局默认值。如果有 1 个列需要更多统计信息，您可以使用 ALTER TABLE [ TABLE ] ALTER [ COLUMN ] column_name SET STATISTICS 整数。但它将消耗更多的 CPU、内存和时间。如果此参数的值为 100，则从每个表中对 300 * 100 = 30,000 个行进行采样。此样本用于确定要存储在 most_common_vals 数组列中的最多 100 个最常见值，要存储在该数组中的最多 100 个直方图范围，以及其他一些标量统计信息（例如，不同的值的数量）。
• 没有用于重置这些统计信息的命令或函数（如重置为 0）。
• 完成引擎主要版本升级后，您应运行 ANALYZE 操作来刷新 pg_statistic 表（更新统计信息以供计划器使用）。
• 对于 Amazon RDS for PostgreSQL 中的只读副本和 Aurora PostgreSQL 中的读取器节点，这些统计信息与主节点或写入器的统计信息相同。这是因为它们存储在磁盘上的关系（pg_statistics）中（Amazon RDS for PostgreSQL 中副本上的物理块相同，对于 Aurora，读取器从同一存储读取数据）。这也是为什么不允许（也不合逻辑）在副本或读取器节点上运行 ANALYZE 的原因（两者都可以从 pg_statistics 关系中读取，但无法进行更新）。

扩展的统计信息

默认情况下，来自 ANALYZE 的统计信息按每表每列进行存储，因此无法捕获任何有关跨列关联的信息。由于查询子句中使用的多个列是关联的，因此经常会看到执行糟糕的运行计划的慢速查询。不过，利用 CREATE STATISTICS 命令，您可以为关联列创建扩展的统计信息。

您可以将 CREATE STATISTICS 用于单个表达式（称为单变量统计），这将提供与表达式索引类似的好处，而不会产生索引维护开销。您还可以在多个列或表达式上使用 CREATE STATISTICS（称为多变量统计）。

考虑一个具有美国的城市和州之间关系的公共表。例如，我们有一个查询，用于查找来自伊利诺斯州的 Springfield 市的行。大多数州都有一个名为 Springfield 的城市，并且伊利诺斯州有许多城市。在没有关系的情况下，通过这种组合确定不同的行的数目就是简单地将两个概率结合起来。扩展的统计信息将两个列上的统计信息收集在一起，因此，来自伊利诺斯州的 Springfield 的行数估计值会更为接近。

让我们使用一个包含两个整数列的表来演示扩展的统计信息的用法。请完成以下步骤：

1. 创建一个包含 a 和 b 列的表并插入一些数据：

   postgres=> CREATE TABLE ext_stats(a int, b int);
   CREATE TABLE
   postgres=> INSERT INTO ext_stats SELECT x/1000, x/10000 FROM generate_series(1,1000000) s(x);
   INSERT 0 1000000
   postgres=>
   postgres=> ANALYZE VERBOSE ext_stats;
   INFO:  analyzing "public.ext_stats"
   INFO:  "ext_stats": scanned 4425 of 4425 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
   ANALYZE
   postgres=>

   从插入的数据来看，这些列是函数相关的，知道第一列中的值就足以确定另一个列中的值。

2. 看看这些列的 pg_stats 值：

   postgres=> select * from pg_stats where tablename='ext_stats';
   -[ RECORD 1 ]----------+-----------------------------------------------------------
   schemaname             | public
   tablename              | ext_stats
   attname                | a
   inherited              | f
   null_frac              | 0
   avg_width              | 4
   n_distinct             | 1000
   most_common_vals       | {698,821,983}
   most_common_freqs      | {0.0015,0.0014666667,0.0014666667}
   histogram_bounds       | {0,10,20,….. ,999}
   correlation            | 1
   most_common_elems      |
   most_common_elem_freqs |
   elem_count_histogram   |
   -[ RECORD 2 ]----------+-----------------------------------------------------------
   schemaname             | public
   tablename              | ext_stats
   attname                | b
   inherited              | f
   null_frac              | 0
   avg_width              | 4
   n_distinct             | 100
   most_common_vals       | {84, 19,…..,55,5,74,62}
   most_common_freqs      | {0.011166667, 0. 011033333,….0.008733333,0.008433334}
   histogram_bounds       |
   correlation            | 1
   most_common_elems      |
   most_common_elem_freqs |
   elem_count_histogram   |

   您可以看到 a 列和 b 列的值不同，前者为 1000，后者为 100。

3. 如果没有扩展的统计信息，选择一个列看起来不错，估计的行数为 999，这几乎与实际行数 1000 相同：

   postgres=> set max_parallel_workers_per_gather =0;
   SET
   postgres=> explain analyze select * from ext_stats where a=1;
                                                    QUERY PLAN
   -------------------------------------------------------------------------------------------------------------
    Seq Scan on ext_stats  (cost=0.00..16925.00 rows=999 width=8) (actual time=0.073..58.429 rows=1000 loops=1)
      Filter: (a = 1)
      Rows Removed by Filter: 999000
    Planning Time: 0.037 ms
    Execution Time: 58.485 ms
   (5 rows)

4. 但是，如果您将第二个列添加到谓词，则计划器估计的行数会大不相同，因为没有收集依赖关系统计信息：

   postgres=> explain analyze select * from ext_stats where a=1 and b=0;
                                                    QUERY PLAN
   -------------------------------------------------------------------------------------------------------------
    Seq Scan on ext_stats  (cost=0.00..194247.65 rows=1 width=8) (actual time=0.077..612.787 rows=1000 loops=1)
      Filter: ((a = 1) AND (b = 0))
      Rows Removed by Filter: 9999000
    Planning Time: 0.044 ms
    Execution Time: 612.844 ms
   (5 rows)

   您可以看到估计的行数为 1，这与实际行数（1000）相去很远。

5. 让我们收集扩展的统计信息：

   postgres=> create statistics s_ext_depend(dependencies) on a,b from ext_stats ;
   CREATE STATISTICS
   postgres=> analyze VERBOSE ext_stats ;
   INFO:  analyzing "public.ext_stats"
   INFO:  "ext_stats": scanned 30000 of 44248 pages, containing 6779952 live rows and 0 dead rows; 30000 rows in sample, 9999977 estimated total rows
   ANALYZE
   postgres=> explain analyze select * from ext_stats where a=1 and b=0;
                                                      QUERY PLAN
   ----------------------------------------------------------------------------------------------------------------
    Seq Scan on ext_stats  (cost=0.00..194247.65 rows=1017 width=8) (actual time=0.076..615.219 rows=1000 loops=1)
      Filter: ((a = 1) AND (b = 0))
      Rows Removed by Filter: 9999000
    Planning Time: 0.126 ms
    Execution Time: 615.287 ms
   (5 rows)
   
   postgres=>

如您所见，估计的行数（1017）与实际行数非常接近。

通过使用扩展的统计信息，所有在两个列上都带有筛选条件的查询将获得更好的估计值。

监控统计数据

除了数据分布和扩展的统计信息之外，还提供了监控统计信息。这些类型的统计信息由统计信息收集器收集。

这些统计信息包含了关于对磁盘块和单个行项中的表和索引的访问计数的信息。它还跟踪每个表中的总行数，以及有关每个表的 vacuum 和分析操作的信息（上次对表运行这些操作的时间）。

在 PostgreSQL 15 中，构建了一种用于将服务器级统计信息存储在共享内存中的新机制。以前，这将通过 UDP 包进行更新，存储在文件系统中并由会话读取。不再有统计信息收集器进程。

以下输出显示了 monitor_stats 表中的有效行数、插入总量、无效行总数、vacuum 信息等信息：

postgres=> CREATE TABLE monitor_stats(id int, name varchar);
CREATE TABLE
postgres=> INSERT INTO monitor_stats VALUES(generate_series(1,10000), 'test'||generate_series(1,10000));
INSERT 0 10000
postgres=> ANALYZE monitor_stats;
ANALYZE
postgres=> DELETE FROM monitor_stats where id < 100;
DELETE 99
postgres=> UPDATE monitor_stats SET id=1 where id < 200;
UPDATE 100
postgres=> \x
Expanded display is on.
postgres=> SELECT * FROM pg_stat_user_tables WHERE relname='monitor_stats';
-[ RECORD 1 ]-------+------------------------------
relid               | 217223
schemaname          | public
relname             | monitor_stats
seq_scan            | 2
seq_tup_read        | 19901
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 10000
n_tup_upd           | 100
n_tup_del           | 99
n_tup_hot_upd       | 86
n_live_tup          | 9901
n_dead_tup          | 199
n_mod_since_analyze | 199
last_vacuum         |
last_autovacuum     |
last_analyze        | 2022-07-19 16:38:41.48811+00
last_autoanalyze    | 2022-07-19 16:38:32.805005+00
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 1
autoanalyze_count   | 1

postgres=>

对于 PostgreSQL 14 或更早版本，统计信息收集器通过临时文件将收集到的信息传输到其他 PostgreSQL 进程。默认情况下，这些文件存储在以 stats_temp_directory 参数 pg_stat_tmp 命名的目录中。

当服务器完全关闭时，统计数据的永久副本会存储在 pg_stat 子目录中，以便在服务器重新启动后保留统计信息。在服务器启动的情况下执行恢复时（例如，在立即关闭、服务器崩溃和时间点恢复后），所有统计信息计数器将重置。

收集器本身每 PGSTAT_STAT_INTERVAL 毫秒（除非在构建服务器时进行更改，否则为 500 毫秒）最多发出一次新报告。

pg_stat_activity、pg_stat_replication、pg_stat_all_tables、pg_stat_user_indexes 和 pg_statio_all_tables 是由统计信息收集器报告刷新的视图的一些示例。有关统计信息视图的更多信息，请参阅“查看统计信息”。

您可以使用 pg_stat_reset() 函数将当前数据库的所有统计信息计数器重置为零。

在以下示例中，对于表 reset_stats，此阶段的 seq_scan 为 0：

postgres=> CREATE TABLE reset_stats(id int, name varchar);
CREATE TABLE
postgres=> INSERT INTO reset_stats VALUES(generate_series(1,1000), 'test'||generate_series(1,1000));
INSERT 0 1000
postgres=> ANALYZE reset_stats ;
ANALYZE
postgres=> SELECT * FROM pg_stat_user_tables WHERE relname='reset_stats';
-[ RECORD 1 ]-------+------------------------------
relid               | 217229
schemaname          | public
relname             | reset_stats
seq_scan            | 0
seq_tup_read        | 0
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1000
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     |
last_analyze        | 2022-07-19 16:53:07.904961+00
last_autoanalyze    | 2022-07-19 16:53:03.792875+00
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 1
autoanalyze_count   | 1

postgres=>

explain analyze 命令会运行查询，这将使 seq_scan 计数器的计数递增：

postgres=> explain analyze select * from reset_stats ;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on reset_stats  (cost=0.00..16.00 rows=1000 width=11) (actual time=0.008..0.084 rows=1000 loops=1)
 Planning Time: 0.090 ms
 Execution Time: 0.131 ms
(3 rows)

postgres=>

您可以在表 tbl 上针对 select * 查询运行一次 explain analyze 后查看表统计信息。如我们所见，seq_scan 现在为 1：

postgres=> SELECT * FROM pg_stat_user_tables WHERE relname='reset_stats';
-[ RECORD 1 ]-------+------------------------------
relid               | 217229
schemaname          | public
relname             | reset_stats
seq_scan            | 1
seq_tup_read        | 1000
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1000
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     |
last_analyze        | 2022-07-19 16:53:07.904961+00
last_autoanalyze    | 2022-07-19 16:53:03.792875+00
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 1
autoanalyze_count   | 1

运行同一查询后，seq_scan 已再次递增：

postgres=> explain analyze select * from reset_stats ;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on reset_stats  (cost=0.00..16.00 rows=1000 width=11) (actual time=0.011..0.087 rows=1000 loops=1)
 Planning Time: 0.026 ms
 Execution Time: 0.136 ms
(3 rows)

postgres=> \x
Expanded display is on.
postgres=> SELECT * FROM pg_stat_user_tables WHERE relname='reset_stats';
-[ RECORD 1 ]-------+------------------------------
relid               | 217229
schemaname          | public
relname             | reset_stats
seq_scan            | 2
seq_tup_read        | 2000
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 1000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 1000
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     |
last_analyze        | 2022-07-19 16:53:07.904961+00
last_autoanalyze    | 2022-07-19 16:53:03.792875+00
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 1
autoanalyze_count   | 1

postgres=>

pg_stat_reset() 函数用于将数据库的所有统计信息重置为 0：

postgres=> select pg_stat_reset();
 pg_stat_reset
---------------

(1 row)

postgres=> \x
Expanded display is on.
postgres=> SELECT * FROM pg_stat_user_tables WHERE relname='reset_stats';
-[ RECORD 1 ]-------+------------
relid               | 217229
schemaname          | public
relname             | reset_stats
seq_scan            | 0
seq_tup_read        | 0
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

postgres=>

由于收集统计信息会增加查询运行的开销，因此，可将系统配置为收集或不收集信息。这可由配置参数控制，通常使用 track_activities、track_counts、track_functions 和 track_io_timing 在 postgresql.conf 中设置这些参数。

由于统计信息收集器进程默认在每台 PostgreSQL 服务器上运行，并且每台服务器上对数据库关系的访问将有所不同，因此，每个实例的统计信息是不同的。这意味着 RDS for PostgreSQL 副本和 Aurora PostgreSQL 读取器的统计信息将与主节点和写入器节点的统计信息不同。

总结

在本博文中，我们已了解 PostgreSQL 中可用的不同类型的统计信息。每当运行 analyze（手动或通过 autovacuum）时，都会收集数据分布统计信息，这些统计信息由查询计划器使用。当您需要在多个列之间建立关联以便计划器能够根据各个列之间的依赖关系查找统计信息时，扩展的统计信息会很有用。您可以使用监控统计信息来查看顺序扫描的次数、索引扫描次数、每个关系中的有效元组和无效元组、每个关系的 I/O 统计信息、对每个关系执行的 vacuum 和 analyze 操作的相关信息等。统计信息收集器每 500 毫秒刷新这些统计信息一次。

关于作者

Divya Sharma 是 AWS 的数据库专家级解决方案架构师，专注于 RDS/Aurora PostgreSQL。她已帮助多家企业客户将其数据库迁移到 AWS，并帮助实现出色的 PostgreSQL 性能和最佳实践。

Baji Shaik 是 AWS ProServe，GCC AMER 的高级数据库顾问。他在 SQL/NoSQL 数据库技术方面拥有广泛的专业知识和经验。他是一名数据库迁移专家，开发了许多成功的数据库解决方案，以解决将数据库从本地迁移到 Amazon RDS 和 Aurora PostgreSQL/MySQL 的挑战性业务需求。他是一位著名作家，撰写过几本有关 PostgreSQL 的书籍。他最近的一些著作包括《PostgreSQL Configuration》、《Beginning PostgreSQL on the Cloud》和《PostgreSQL Development Essentials》。此外，他还主持了几次会议和研讨会。