PostgreSQL 中的统计信息,你知道有哪些吗?

2024年 5月 31日 67.9k 0

数据分布统计信息

这些统计信息与每个关系的数据分布相关。它们提供了一个关系中每个列最常见的值、列的平均宽度、列中不同值的数量等信息。当我们运行 ANALYZE 或者在 autovacuum 触发 ANALYZE 时,它们会被收集,并存储在 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是最常见的值。最常见的值列表,用于帮助规划器预测等式表达式的选择性,例如name='test5'或where state=’TX’。直方图边界用于帮助规划器预测不等式或范围表达式的选择性,例如 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是全局默认值。如果有一个列需要更多的统计信息,可以使用ALTER TABLE [ TABLE ] ALTER [ COLUMN ] column_name SET STATISTICS integer。但是,这会消耗更多的 CPU、内存和时间。如果此参数的值为 100,则会从每个表中采样 300 * 100 = 30,000 行。该采样会用于确定要存储在most_common_vals数组列中的最多 100 个最常见的值,以及要存储在数组中的最多 100 个直方图边界,还有一些其他的标量统计信息,如不同值的数量。

• 没有用于重置这些统计信息的命令或函数(如重置为 0)。

• 在完成一次引擎主要版本的升级后,您应该运行 ANALYZE 操作来刷新pg_statistic表(更新统计信息以供规划器使用)。

• 对于 PostgreSQL 中的只读副本,这些统计信息会与主库或写库保持相同。这是因为它们存储在磁盘上的一个关系(pg_statistics)中(PostgreSQL 中副本上的物理块是相同的)。这也是为什么不允许(也不符合逻辑)在一个副本或只读节点上运行ANALYZE的原因(它们都可以读取pg_statistics关系,但不能更新它)。

扩展统计信息

默认情况下,ANALYZE产生的统计信息是按每个表的每个列存储的,因此不能捕获关于多列间相关性的任何信息。通常会看到使用了不好的计划来运行的慢查询,因为查询子句中使用的多个列是相关的。但是,使用 CREATE STATISTICS 命令,可以为相关列创建扩展统计信息。

您可以对单个表达式使用 CREATE STATISTICS(称为单变量统计信息),它提供了类似于表达式索引的好处,但没有索引维护的开销。还可以对多个列或表达式使用 CREATE STATISTICS(称为多变量统计信息)。

考虑一个中国城市和区之间关系的普通表。例如,我们有一个查询来查找上海市高新区的行。很多城市都有一个叫高新的区,上海市有很多区。在没有关系的情况下,确定具有该组合的不同行的数量仅仅是组合两个概率。扩展统计信息会将两列的统计数据收集在一起,因此对关联上海市高新区的行数的估计要接近得多。

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

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

postgres=> CREATE TABLE ext_tests(a int, b int);
CREATE TABLE
postgres=> INSERT INTO ext_tests SELECT x/1000, x/10000 FROM generate_series(1,1000000) s(x);
INSERT 0 1000000
postgres=>
postgres=> ANALYZE VERBOSE ext_tests;
INFO:  analyzing "public.ext_tests"
INFO:  "ext_tests": 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_tests';
-[ RECORD 1 ]----------+-----------------------------------------------------------
schemaname             | public
tablename              | ext_tests
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_tests
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。

3. 在没有扩展的统计信息时,查询单个列看起来还好,估计的行数为 999,这与实际的行数 1000 差不多相同:

postgres=> set max_parallel_workers_per_gather =0;
SET
postgres=> explain analyze select * from ext_tests where a=1;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on ext_tests  (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_tests where a=1 and b=0;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on ext_tests  (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_tests ;
CREATE STATISTICS
postgres=> analyze VERBOSE ext_tests ;
INFO:  analyzing "public.ext_tests"
INFO:  "ext_tests": 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_tests where a=1 and b=0;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on ext_tests  (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)非常接近实际的行数。

在两列上都有过滤条件的所有查询,都会使用扩展的统计信息来进行更好的估计。

相关文章

JavaScript2024新功能:Object.groupBy、正则表达式v标志
PHP trim 函数对多字节字符的使用和限制
新函数 json_validate() 、randomizer 类扩展…20 个PHP 8.3 新特性全面解析
使用HTMX为WordPress增效:如何在不使用复杂框架的情况下增强平台功能
为React 19做准备:WordPress 6.6用户指南
如何删除WordPress中的所有评论

发布评论