数据分布统计信息
这些统计信息与每个关系的数据分布相关。它们提供了一个关系中每个列最常见的值、列的平均宽度、列中不同值的数量等信息。当我们运行 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)非常接近实际的行数。
在两列上都有过滤条件的所有查询,都会使用扩展的统计信息来进行更好的估计。