PostgreSQL已经成为许多企业开发人员和初创公司首选的开源关系数据库，并为领先的商业和移动应用提供了动力。AWS提供了两个受管理的PostgreSQL选项:针对PostgreSQL的Amazon关系数据库服务(Amazon RDS)和Amazon Aurora PostgreSQL兼容版。
数据库统计信息在提高数据库性能方面起着关键作用。查询计划器使用统计数据为查询生成有效的运行计划。这篇文章的目的是解释PostgreSQL中的统计数据类型以及如何阅读和理解它们。这适用于Amazon RDS for PostgreSQL和Aurora PostgreSQL。
PostgreSQL中收集并提供以下类型的统计信息:
-
数据分布统计信息
-
扩展的统计信息
-
监控统计数据
我们将在这篇文章中更详细地解释每种类型。
数据分布统计信息
这些统计信息与每个关系的数据分布相关。它们提供有关关系中每个列中最常见值、列的平均宽度、列中不同值的数量等信息。它们在运行ANALYZE或autovacuum触发ANALYZE时被收集，并存储在pg_statistic系统目录中(其公共可读视图为pg_stats)。
以下是如何生成和查看这些数据的示例:
创建一个表并插入一些虚拟数据
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
复制分析表以生成表的统计信息
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
复制在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是最常见的值。最常见的值列表用于帮助计划器预测等式表达式的选择性，例如name='test5’或state= ’ TX '。直方图边界用于帮助规划者预测不等式或范围表达式的选择性，例如id在5000-10000之间。
如果查看名称列的相关性列，它的值是-0.19(接近0)。当该值接近-1或+1时，对列的索引扫描估计要比接近0时便宜，这是因为减少了对磁盘的随机访问。因为只有30行，接近0的值表示此列不需要索引。表中没有空值，因此null_frac为0。
查询计划器需要估计查询检索的行数，以便对查询计划做出良好的选择。为了进行这些估计，使用了这些数据分布统计信息。
以下是关于统计数据需要注意的几点:
-
对于大型表，ANALYZE取表内容的随机样本，而不是检查每一行。这允许在很短的时间内分析非常大的表。
-
ANALYZE考虑的样本数量取决于default_statistics_target参数。较大的值会增加分析所需的时间，但可能会提高计划人员估计的质量。默认值是100。要得到准确的规划，默认值就足够了;而default_statistics_target是全局默认值。对于一个列需要更多的统计信息的情况，可以使用ALTER TABLE [TABLE] ALTER [column] column_name SET STATISTICS integer。但是，它将消耗更多的CPU、内存和时间。如果该参数的值为100，则从每个表中采样300 * 100 = 30000行。该示例用于确定最多100个最常用值存储在most_common_vals数组列中，最多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的行数估计要接近得多。
让我们使用一个有两个整数列的表来演示扩展统计的使用。完成以下步骤:
创建一个包含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=>
复制从插入的数据来看，这些列在功能上依赖于第一列中的值的知识足以确定另一列的值。
查看这些列的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的不同值为1000，列b的不同值是100。
如果没有扩展统计信息，选择一列看起来很好，估计行数为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)
复制但是，如果将第二列添加到谓词中，规划器会以非常不同的方式估计行，因为不会收集依赖关系统计信息：
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）相差甚远。
收集扩展的统计数据
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=>
复制如您所见，估计的行数(1,017)非常接近实际的行数。
所有在两个列上都有过滤器的查询将使用扩展统计信息得到更好的估计。
监控统计数据
除了数据分布和扩展统计信息外，还有监视统计信息。这些类型的统计信息由统计信息收集器收集。
这些统计信息收集关于对磁盘块和单个行项中的表和索引的访问计数的信息。它还跟踪每个表中的行总数，以及关于每个表的清空和分析操作的信息(它们最后一次在表上运行是什么时候)。
在PostgreSQL 15中有一个新的机制用来在共享内存中存储服务器级的统计信息。以前，这是通过UDP包更新的，存储在文件系统中，并由会话读取。不再有统计收集器进程。
下面的输出显示了诸如活行数、总插入、总死行、真空信息等monitor_stats表的信息:
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()函数将当前数据库的所有统计计数器重置为零。
在下面的例子中，seq_scan在表reset_stats的这个阶段为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=>
复制对于select*查询，可以在表tbl上运行一次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=>
复制函数用于将数据库的所有统计信息重置为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=>
复制因为收集统计信息会增加一些查询运行的开销，所以可以配置系统收集或不收集信息。这是由配置参数控制的，通常在postgresql.conf中使用track_activities、track_counts、track_functions和track_io_timing设置这些配置参数。
因为默认情况下，统计收集器进程运行在每个PostgreSQL服务器上，而对数据库关系的访问在每个服务器上是不同的，所以这些统计对每个实例都是不同的。这意味着用于PostgreSQL副本和Aurora PostgreSQL阅读器的RDS与主节点和写入节点具有不同的统计信息。
总结
在这篇文章中，我们学习了PostgreSQL中不同类型的统计数据。每当analyze运行时(手动或通过autovacuum)都会收集数据分布统计信息，查询计划器会使用这些统计信息。当您需要多个列之间的相关性时，扩展的统计信息非常有用，以便计划器可以根据列之间的依赖关系找到统计信息。您可以使用监视统计信息查看顺序扫描的数量、索引扫描的数量、每个关系中的活元组和死元组、每个关系的I/O统计信息、关于真空的信息和每个关系的分析信息，等等。这些统计信息由统计信息收集器每500毫秒刷新一次。
原文标题:Understanding statistics in PostgreSQL
原文作者:Divya Sharma and Baji Shaik
原文地址:https://aws.amazon.com/cn/blogs/database/understanding-statistics-in-postgresql/
