PostgreSQL 规划器使用的统计信息

由 John Doe 二月 25, 2024

规划器统计信息在提高数据库性能方面起着关键作用。查询规划器使用统计数据为查询生成高效的运行计划。本文目的是解释 PostgreSQL 中统计信息的类型，以及如何阅读和理解它们。

数据分布统计信息

这些统计信息与每个关系的数据分布相关。它们提供了一个关系中每个列最常见的值、列的平均宽度、列中不同值的数量等信息。当我们运行 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）非常接近实际的行数。

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

搜索

分类

标签