PostgreSQL 教程: 优化范围匹配的选择率估计

二月 29, 2024

摘要:在本教程中,您将学习如何优化范围匹配的选择率估计。

介绍

当非重复值的数量太大而无法将它们全部存储到数组中时,系统会开始使用直方图的表示形式。直方图使用多个桶来存储值。桶的数量由相同的 default_statistics_target 参数控制。

每个桶的宽度的选择,要使得值均匀地分布在它们之间(如下图中,由具有大致相同面积的矩形所指示的)。这种表示使得系统只需要存储直方图的边界值,而不用浪费存储每个桶的频率的空间。直方图不包括 MCV 列表中的值。

边界值存储在pg_statshistogram_bounds字段中。任何一个桶中的值的总频率等于 1 / 桶数

histogram bounds

直方图是如何工作的?

让我们开始设置一个小的测试用例:

CREATE TABLE test2 (id integer, val integer);

INSERT INTO test2 (id, val)
  SELECT i, CAST(random() * 400 AS integer)
  FROM generate_series(1, 10000) AS s(i);

ANALYZE test2;

SHOW default_statistics_target;
 default_statistics_target
---------------------------
 100
(1 row)

一个直方图是以桶的边界值数组的形式存储的:

SELECT '...' || right(histogram_bounds::text, 60) AS histogram_bounds
FROM pg_stats s
WHERE s.tablename = 'test2' AND s.attname = 'val';
                        histogram_bounds
-----------------------------------------------------------------
 ...346,350,353,357,360,364,370,374,378,382,386,389,393,397,400}
(1 row)

其中,直方图和 MCV 列表,一起用于估计“大于”和“小于”操作的选择率。

例如,我们想从表中查询一些记录,根据列val进行筛选。

EXPLAIN SELECT * FROM test2 WHERE val > 302;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on test2  (cost=0.00..150.00 rows=2462 width=8)
   Filter: (val > 302)
(2 rows)

在这里,截断值被特别地选择为两个桶之间的边界值。

此条件的选择率是 N / 桶数,其中 N 是具有匹配值的桶数(截止点右侧)。请记住,直方图中不考虑最常见的值和未定义的值。

让我们先来看看最常见值中匹配值的占比:

SELECT sum(s.most_common_freqs[
  array_position(s.most_common_vals::text::integer[], v)
])
FROM pg_stats s, unnest(s.most_common_vals::text::integer[]) v
WHERE s.tablename = 'test2' AND s.attname = 'val'
AND v > 302;
  sum
--------
 0.0237
(1 row)

现在,让我们来看看最常见值的占比(要从直方图中排除掉):

SELECT sum(freq)
FROM pg_stats s, unnest(s.most_common_freqs) freq
WHERE s.tablename = 'test2' AND s.attname = 'val';
  sum
--------
 0.1442
(1 row)

val列中没有 NULL 值:

SELECT s.null_frac
FROM pg_stats s
WHERE s.tablename = 'test2' AND s.attname = 'val';
 null_frac
-----------
         0
(1 row)

间隔正好覆盖了 26 个桶(总共100个)。由此得出的估计行数:

SELECT round( reltuples * (
   0.0237                          -- from most common values
 + (1 - 0.1442 - 0) * (26 / 100.0) -- from histogram
))
FROM pg_class WHERE relname = 'test2';
 round
-------
  2462
(1 row)

实际行数为 2445。

当截止值不在桶的边界时,会使用线性插值来计算该桶的匹配占比。

linear interpolation

当直方图数据过时

让我们检查下列id的直方图数组:

SELECT '...' || right(histogram_bounds::text, 60) AS histogram_bounds
FROM pg_stats s
WHERE s.tablename = 'test2' AND s.attname = 'id';
                        histogram_bounds
-----------------------------------------------------------------
 ...900,9000,9100,9200,9300,9400,9500,9600,9700,9800,9900,10000}
(1 row)

然后向表中插入一批记录,并立即从表中查询新记录,根据列id进行筛选。

INSERT INTO test2 (id, val)
  SELECT i, CAST(random() * 400 AS integer)
  FROM generate_series(10001, 11000) AS s(i);

EXPLAIN (analyze) SELECT * FROM test2 WHERE id > 10000;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on test2  (cost=0.00..168.00 rows=1 width=8) (actual time=1.527..1.752 rows=1000 loops=1)
   Filter: (id > 10000)
   Rows Removed by Filter: 10000
 Planning time: 0.115 ms
 Execution time: 1.809 ms
(5 rows)

实际行数为 1000,而估计行数为 1!

更新表的统计信息

不管什么时候你显著地改变了表中的数据分布后,我们都强烈推荐运行 ANALYZE。这包括向表中批量加载大量数据的情况。运行ANALYZE(或者VACUUM ANALYZE)保证规划器有表的最新统计信息。 如果没有统计数据或者统计数据过时,那么规划器在查询规划时可能会选择出很差劲的执行计划,导致在任意表上的查询性能低下。

需要注意的是,如果启用了 autovacuum 守护进程,它可能会自动运行ANALYZE;有关详细信息,请参阅参数 autovacuum_analyze_thresholdautovacuum_analyze_scale_factor

了解更多

PostgreSQL 优化