# 统计信息 LLMS 索引： [llms.txt](/zh18/llms.txt) --- ## 17.1. 基础统计信息 {#internal-ru-section-17-1} 关系级别的基本统计信息存储在系统目录的 `pg_class` 表中。其中包括： [^internal-ru-347-1] - 关系中的行数（`reltuples`）； - 关系的大小，以页面为单位（`relpages`）； - 在可见性映射中标记的页面数量（`relallvisible`）。(*参见* [32](/zh18/01-introduction/#internal-ru-page-33)) 以下是 `flights` 表的这些值示例： ```text {label="psql"} => SELECT reltuples, relpages, relallvisible FROM pg_class WHERE relname = 'flights'; reltuples | relpages | relallvisible -----------+----------+--------------- 214867 | 2624 | 0 (1 row) ``` `reltuples` 的值在查询未对表中的行施加任何条件时用作基数估计。 ```text {label="psql" hl_lines="4"} => EXPLAIN SELECT * FROM flights; QUERY PLAN ---------------------------------------------------------------- Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=63) (1 row) ``` 统计信息在分析过程中收集，无论是手动还是自动[^internal-ru-348-1]。然而，由于基本统计信息的重要性，某些操作（如 `VACUUM FULL` 和 `CLUSTER`[^internal-ru-348-2]、`CREATE INDEX` 和 `REINDEX` [^internal-ru-348-3]）也会计算基本统计信息，并在清理过程中更新[^internal-ru-348-4]。(*参见* [137](/zh18/06-vacuum-and-freezing/#internal-ru-page-138)) 分析时随机选择 300 × *default\_statistics\_target* 行。由于构建给定精度统计信息所需的样本量对所分析数据量的依赖性较弱，因此不考虑表的大小[^internal-ru-348-5]。（默认值：`100`）这些行从相同数量（300 × *default\_statistics\_target*）的随机页面中选取[^internal-ru-348-6]。当然，对于小表，读取的页面数量和用于分析的行数可能更少。由于在足够大的表中，统计信息并非基于所有行收集，因此估计值可能与实际情况略有偏差。这是正常的：如果数据持续变化，统计信息不可能始终精确。通常，只要估计值落在正确数量级内，就足以选择合适的执行计划。创建一个禁用自动清理的 `flights` 表副本，以便控制分析的执行时间： ```text {label="psql"} => CREATE TABLE flights_copy(LIKE flights) WITH (autovacuum_enabled = false); ``` 对于新表，尚未有任何统计信息： ```text {label="psql"} => SELECT reltuples, relpages, relallvisible FROM pg_class WHERE relname = 'flights_copy'; reltuples | relpages | relallvisible -----------+----------+--------------- -1 | 0 | 0 (1 row) ``` `reltuples` 的值为 −1 可用于区分从未收集过统计信息的表与确实没有行的空表。（v. 14）但表在创建后很可能立即会插入一些行。因此，在缺乏信息的情况下，规划器认为该表占用 10 个页面： ```text {label="psql" hl_lines="4"} => EXPLAIN SELECT * FROM flights_copy; QUERY PLAN ----------------------------------------------------------------- Seq Scan on flights_copy (cost=0.00..14.10 rows=410 width=170) (1 row) ``` 行数的计算基于单行的大小；它在查询计划中显示为 width。通常使用分析时计算的平均值进行估计，但在此情况下，由于缺乏统计信息，行大小是根据各列数据类型的近似值计算的[^internal-ru-349-1]。现在将 `flights` 表中的数据复制过来并执行分析： ```text {label="psql"} => INSERT INTO flights_copy SELECT * FROM flights; INSERT 0 214867 => ANALYZE flights_copy; ``` 当前统计信息与实际行数一致（表的大小使得统计信息基于完整数据收集）： ```text {label="psql"} => SELECT reltuples, relpages, relallvisible FROM pg_class WHERE relname = 'flights_copy'; reltuples | relpages | relallvisible -----------+----------+--------------- 214867 | 2624 | 0 (1 row) ``` `relallvisible` 的值用于估计仅索引扫描的成本。它在清理过程中更新：(*参见* [422](/zh18/20-index-scans/#internal-ru-page-423)) ```text {label="psql"} => VACUUM flights_copy; => SELECT relallvisible FROM pg_class WHERE relname = 'flights_copy'; relallvisible --------------- 2624 (1 row) ``` 现在将行数翻倍，但不重新收集统计信息，并检查查询计划中的基数估计： ```text {label="psql" hl_lines="10"} => INSERT INTO flights_copy SELECT * FROM flights; => SELECT count(*) FROM flights_copy; count -------- 429734 (1 row) => EXPLAIN SELECT * FROM flights_copy; QUERY PLAN --------------------------------------------------------------------- Seq Scan on flights_copy (cost=0.00..9545.34 rows=429734 width=63) (1 row) ``` 尽管 `pg_class` 中的信息已过时，但估计值仍然准确： ```text {label="psql"} => SELECT reltuples, relpages FROM pg_class WHERE relname = 'flights_copy'; reltuples | relpages -----------+---------- 214867 | 2624 (1 row) ``` 这是因为规划器通过根据实际数据文件大小与 `relpages` 值的偏差来缩放 `reltuples` 的值，从而提高估计精度[^internal-ru-350-1]。由于文件大小相比 `relpages` 增加了一倍，因此行数根据数据密度未发生变化的假设进行了调整： ```text {label="psql"} => SELECT reltuples * (pg_relation_size('flights_copy') / 8192) / relpages AS tuples FROM pg_class WHERE relname = 'flights_copy'; tuples -------- 429734 (1 row) ``` 当然，这种调整并非总是有效（例如，如果删除部分行，估计值不会改变），但在某些情况下，它能帮助在大规模数据变更后“撑到”下一次分析执行。 ## 17.2. 空值 {#internal-ru-section-17-2} 尽管理论家们批评不确定值[^internal-ru-351-1]，但在关系型数据库中，它们仍扮演着重要角色，作为表示值不存在或未知的便捷方式。但不确定值具有特殊意义，需要特别对待。除了理论上的不一致外，还存在大量实际困难，必须加以考虑。普通的布尔逻辑变为三值逻辑，而 `NOT IN` 子句的行为变得*出人意料*。不确定值是否应被视为小于或大于普通值尚不明确（因此引入了 `NULLS FIRST` 和 `NULLS LAST` 用于排序）。不确定值是否应计入聚合函数也并不明显。由于严格来说，不确定值根本不是值，因此规划器需要额外信息才能正确处理它们。除了关系级别的基本统计信息外，分析还会为关系中的每个列收集统计信息。这些信息存储在系统目录表 `pg_statistic` 中[^internal-ru-351-2]，但使用视图 `pg_stats` 更为方便，它以更易读的格式展示信息。不确定值的比例正是列级别的统计信息；分析时计算的值显示在 `null_frac` 属性中。例如，要查找尚未起飞的航班，可以利用其起飞时间未定义这一事实： ```text {label="psql" hl_lines="4"} => EXPLAIN SELECT * FROM flights WHERE actual_departure IS NULL; QUERY PLAN --------------------------------------------------------------- Seq Scan on flights (cost=0.00..4772.67 rows=16473 width=63) Filter: (actual_departure IS NULL) (2 rows) ``` 估计值计算方式为：总行数乘以 `NULL` 的比例： ```text {label="psql"} => SELECT round(reltuples * s.null_frac) AS rows FROM pg_class JOIN pg_stats s ON s.tablename = relname WHERE s.tablename = 'flights' AND s.attname = 'actual_departure'; rows ------- 16473 (1 row) ``` 精确值： ```text {label="psql"} => SELECT count(*) FROM flights WHERE actual_departure IS NULL; count ------- 16348 (1 row) ``` ## 17.3. 非重复值 {#internal-ru-section-17-3} `pg_stats` 视图中的 `n_distinct` 字段显示列中唯一值的数量。如果 `n_distinct` 的值为负数，则其绝对值表示唯一值的比例而非数量。例如，−1 表示列中所有值都是唯一的，而 −3 表示每个值平均出现在三行中。当分析计算出的唯一值数量超过总行数的 10% 时，分析器会使用比例[^internal-ru-353-1]。 ![唯一值：频率；null\_frac；n\_distinct；值](/images/internal/ru/p353-01.svg) {#internal-ru-fig-17-1 num="17.1" caption="唯一值" .book-original-figure width=714 height=379} 当分析计算出的唯一值数量超过总行数的 10% 时，使用比例；在这种情况下，比例很可能在数据进一步变化时仍保持稳定[^internal-ru-353-1]。唯一值数量用于所有假设数据均匀分布的情况。例如，在评估条件「*列* = *表达式*」的基数时，如果表达式值在规划阶段未知，则认为表达式可能以相等概率取列中的任意可能值[^internal-ru-353-2]： ```text {label="psql" hl_lines="8 9 10"} => EXPLAIN SELECT * FROM flights WHERE departure_airport = ( SELECT airport_code FROM airports WHERE city = 'Санкт-Петербург' ); QUERY PLAN --------------------------------------------------------------------- Seq Scan on flights (cost=30.56..5340.40 rows=2066 width=63) Filter: (departure_airport = (InitPlan 1).col1) InitPlan 1 -> Seq Scan on airports_data ml (cost=0.00..30.56 rows=1 wi... Filter: ((city ->> lang()) = 'Санкт-Петербург'::text) (5 rows) ``` 此处计划节点 InitPlan 仅执行一次，其计算结果在主计划中复用。 ```text {label="psql"} => SELECT round(reltuples / s.n_distinct) AS rows FROM pg_class JOIN pg_stats s ON s.tablename = relname WHERE s.tablename = 'flights' AND s.attname = 'departure_airport'; rows ------ 2066 (1 row) ``` 如果由于采样范围有限导致唯一值数量计算错误，可以显式为列指定该值： ```sql ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...); ``` 如果所有数据始终均匀分布，仅凭这些信息（加上最小值和最大值）就足够了。但在实践中更常见的非均匀分布情况下，这种估计将不准确： ```text {label="psql"} => SELECT min(cnt), round(avg(cnt)) avg, max(cnt) FROM ( SELECT departure_airport, count(*) cnt FROM flights GROUP BY departure_airport ) t; min | avg | max -----+------+------- 113 | 2066 | 20875 (1 row) ``` ## 17.4. 高频值 {#internal-ru-section-17-4} 为提高非均匀分布情况下的估计精度，会收集最频繁出现的值（most common values, MCV）及其出现频率。`pg_stats` 视图在 `most_common_vals` 和 `most_common_freqs` 列中显示这两个数组。 ![最频繁值：频率；\[ most\_common\_vals \]；\[ most\_common\_freqs \]；null\_frac；值](/images/internal/ru/p355-02.svg) {#internal-ru-fig-17-2 num="17.2" caption="最频繁值" .book-original-figure width=714 height=436} 以下是关于不同飞机类型使用频率的统计示例： ```text {label="psql"} => SELECT most_common_vals AS mcv, left(most_common_freqs::text,60) || '...' AS mcf FROM pg_stats WHERE tablename = 'flights' AND attname = 'aircraft_code' \gx -[ RECORD 1 ]-------------------------------------------------------- mcv | {CN1,CR2,SU9,321,733,319,763,773} mcf | {0.28123334,0.2653,0.264,0.0595,0.03816667,0.0374,0.0368,0.0... ``` 对于条件「*列* = *值*」的选择性评估，只需在 `most_common_vals` 数组中查找该值，并从同索引的 `most_common_freqs` 数组元素中获取频率[^internal-ru-355-1]： ```text {label="psql" hl_lines="4"} => EXPLAIN SELECT * FROM flights WHERE aircraft_code = '733'; QUERY PLAN -------------------------------------------------------------- Seq Scan on flights (cost=0.00..5309.84 rows=8201 width=63) Filter: (aircraft_code = '733'::bpchar) (2 rows) => SELECT round(reltuples * s.most_common_freqs[ array_position((s.most_common_vals::text::text[]),'733') ]) FROM pg_class JOIN pg_stats s ON s.tablename = relname WHERE s.tablename = 'flights' AND s.attname = 'aircraft_code'; round ------- 8201 (1 row) ``` 显然，这种估计将非常接近真实值： ```text {label="psql"} => SELECT count(*) FROM flights WHERE aircraft_code = '733'; count ------- 8263 (1 row) ``` 最频繁值列表也用于不等式条件的选择性评估。例如，对于条件「*列* < *值*」，需要在 `most_common_vals` 中找出所有小于目标值的值，并将对应的 `most_common_freqs` 中的频率相加[^internal-ru-356-1]。当不同值的数量不多时，最频繁值统计效果极佳。数组的最大大小由相同的参数 *default\_statistics\_target* 决定，该参数也限制了分析时随机行样本的大小。（默认值：`100`）在某些情况下，增加默认参数值以扩展最频繁值列表并提高估计精度是有意义的。这可以在单个列级别进行： ```sql ALTER TABLE ... ALTER COLUMN ... SET STATISTICS ...; ``` 这将增加样本量，但仅针对指定的表。由于最频繁值数组中保存的是实际值，因此该数组可能占用相当大的空间。为避免过度增大 `pg_statistic` 并使规划器陷入无用工作，分析和统计信息中会排除大小超过 1 KB 的值。此外，这些大值很可能本身就是唯一的，因此无论如何也不会进入 `most_common_vals`。 ## 17.5. 直方图 {#internal-ru-section-17-5} 当不同值的数量太多，无法存储在数组中时，就会用到直方图。直方图由若干个“箱子”组成，这些值被放入箱子中。箱子的数量受同一个参数 *default\_statistics\_target* 限制。箱子的宽度被设定为使每个箱子中包含的值数量大致相等（图中该属性对应于大阴影矩形的面积相等）。此时仅考虑未进入最频繁值列表的那些值。在这种构造下，任意一个箱子中值的总频率等于 \(\frac{1}{\textit{箱子数量}}\)。 ![直方图：频率；\[ mcv \]；\[ mcf \]；null\_frac；值；\[ histogram\_bounds \]](/images/internal/ru/p357-03.svg) {#internal-ru-fig-17-3 num="17.3" caption="直方图" .book-original-figure width=714 height=453} 直方图存储在 `pg_stats` 视图的 `histogram_bounds` 字段中，作为界定箱子的值的数组： ```text {label="psql"} => SELECT left(histogram_bounds::text,60) || '...' AS hist_bounds FROM pg_stats s WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no'; hist_bounds ----------------------------------------------------------------- {10A,10A,10F,10F,11G,12B,13B,13K,14C,14C,15B,15C,15C,16B,17B... (1 row) ``` 直方图特别用于与最频繁值列表结合，评估「大于」或「小于」操作符的选择性[^internal-ru-358-1]。考虑一个例子——分配给远排座位的登机牌数量： ```text {label="psql" hl_lines="4"} => EXPLAIN SELECT * FROM boarding_passes WHERE seat_no > '30B'; QUERY PLAN --------------------------------------------------------------------- Seq Scan on boarding_passes (cost=0.00..157374.55 rows=2991709 ... Filter: ((seat_no)::text > '30B'::text) (2 rows) ``` 座位号被特意选择为恰好位于直方图箱子的边界上。此类条件的选择性估计值为 \(\frac{N}{\textit{箱子数量}}\)，其中 *N* 是满足条件的箱子数量（即位于该值右侧的箱子）。需要注意的是，最频繁值不包含在直方图中。一般来说，不确定值也不包含在直方图中，但在 `seat_no` 列中不可能存在此类值： ```text {label="psql"} => SELECT s.null_frac FROM pg_stats s WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no'; null_frac ----------- 0 (1 row) ``` ![直方图：频率；null\_frac；x；值](/images/internal/ru/p359-04.svg) {#internal-ru-fig-17-4 num="17.4" caption="直方图" .book-original-figure width=714 height=436} 首先计算满足条件的最频繁值所占比例： ```text {label="psql"} => SELECT sum(s.most_common_freqs[ array_position((s.most_common_vals::text::text[]),v) ]) FROM pg_stats s, unnest(s.most_common_vals::text::text[]) v WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no' AND v > '30B'; sum ------------ 0.20833333 (1 row) ``` 未被直方图包含的最频繁值的总比例为： ```text {label="psql"} => SELECT sum(s.most_common_freqs[ ``` ```text array_position((s.most_common_vals::text::text[]),v) ]) FROM pg_stats s, unnest(s.most_common_vals::text::text[]) v WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no'; sum ----------- 0.6747334 (1 row) ``` 由于该区间恰好占据直方图中相同数量的箱子（从100个可能箱子中），我们得到如下估计： ```text {label="psql"} => SELECT round( reltuples * ( 0.20833333 -- вклад частых значений + (1 - 0.6747334 - 0) * (52 / 100.0) -- вклад гистограммы )) FROM pg_class WHERE relname = 'boarding_passes'; round --------- 2991708 (1 row) ``` 一般情况下，当值不位于边界时，通过线性插值来考虑该值所在箱子的部分比例。精确值为： ```text {label="psql"} => SELECT count(*) FROM boarding_passes WHERE seat_no > '30B'; count --------- 2993735 (1 row) ``` 增加 *default\_statistics\_target* 参数可能改善估计，然而，正如示例所示，即使在列中存在大量唯一值的情况下，与最频繁值列表结合使用时，直方图通常仍能提供良好结果： ```text {label="psql"} => SELECT n_distinct FROM pg_stats WHERE tablename = 'boarding_passes' AND attname = 'seat_no'; n_distinct ------------ 461 (1 row) ``` 只有当提高估计精度能带来更优的执行计划时，这种提升才有意义。盲目增加该参数可能会减慢分析和规划器的执行速度，而不会带来任何改进。另一方面，降低该参数（甚至降至零）可能加快分析和规划，但也可能导致较差的执行计划。这种‘节省’通常并不合理。 ## 17.6. 非标量数据类型的统计信息 {#internal-ru-section-17-6} 对于非标量数据类型，可以收集不仅包括值本身分布，还包括这些值所包含元素分布的统计信息。这有助于更准确地规划涉及非第一范式列的查询。 - 数组 `most_common_elems` 和 `most_common_elem_freqs` 显示了 *最频繁元素* 列表及其频率。该统计信息用于评估数组 [^internal-ru-361-1] 和 `tsvector` 数据类型 [^internal-ru-361-2] 的选择性。 - 数组 `elem_count_histogram` 显示了值中 *唯一元素数量* 的直方图。该统计信息仅用于数组的选择性评估。 - 对于非空范围，会收集下界和上界分布的直方图（数组 `range_bounds_histogram`）（v. 17）以及长度分布的直方图（数组 `range_length_histogram`）。空范围的比例（列 `range_empty_frac`）也被单独记录。这些数据用于评估各种范围类型操作的选择性 [^internal-ru-361-3]。相同类型的统计信息也用于多范围类型 [^internal-ru-361-4]。（v. 14） ## 17.7. 平均列宽 {#internal-ru-section-17-7} `pg_stats` 视图的 `avg_width` 字段显示了列中值的平均大小。当然，对于 `integer` 或 `char(3)` 等类型，该指标始终相同，但对于长度可变的数据类型（如 `text`），该值可能在不同列之间差异显著： ```text {label="psql"} => SELECT attname, avg_width FROM pg_stats WHERE (tablename, attname) IN ( VALUES ('tickets', 'passenger_name'), ('ticket_flights','fare_conditions') ); attname | avg_width -----------------+----------- fare_conditions | 8 passenger_name | 16 (2 rows) ``` 该统计信息用于评估某些操作（如排序或哈希）所需的内存容量。 ## 17.8. 相关性 {#internal-ru-section-17-8} `pg_stats` 视图的 `correlation` 字段显示了数据物理位置与比较操作逻辑顺序之间的相关性。如果值严格按升序存储，相关性接近于1；若按降序，则接近于-1。数据在磁盘上越混乱，该值越接近于0。（*参见* [546](/zh18/25-b-tree/#internal-ru-page-547)） ```text {label="psql"} => SELECT attname, correlation FROM pg_stats WHERE tablename = 'airports_data' ORDER BY abs(correlation) DESC; attname | correlation --------------+------------- coordinates | airport_code | -0.21120238 city | -0.1970127 airport_name | -0.18223621 timezone | 0.17961165 (5 rows) ``` 相关性用于评估索引扫描的成本。对于 `coordinates` 列，不收集此类统计信息，因为 `point` 数据类型未定义「大于」和「小于」操作符。（*参见* [415](/zh18/20-index-scans/#internal-ru-page-416)） ## 17.9. 表达式统计信息 {#internal-ru-section-17-9} 通常，只有当比较操作中左侧或右侧为列本身而非表达式时，才能使用列的统计信息。例如，规划器不知道对列应用函数后统计信息如何变化，因此对于「*函数调用* = *常量*」条件，总是使用固定的0.5%估计值 [^internal-ru-363-1]： ```text {label="psql" hl_lines="7"} => EXPLAIN SELECT * FROM flights WHERE extract( month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow' ) = 1; QUERY PLAN --------------------------------------------------------------------- Seq Scan on flights (cost=0.00..6384.17 rows=1074 width=63) Filter: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE ... (2 rows) => SELECT round(reltuples * 0.005) FROM pg_class WHERE relname = 'flights'; round ------- 1074 (1 row) ``` 规划器甚至不了解标准函数的语义。虽然我们从一般常识知道，一月份执行的航班数量约为总数的 \(\frac{1}{12}\)，即比预测值高出一个数量级。 > [!NOTE] > PostgreSQL 允许为规划器创建辅助函数[^internal-ru-363-2]，以提示其在给定参数下某个函数的预期输出行数。因此，对于某些内建函数（如 `generate_series`），规划器了解一些信息。但并非所有函数都如此，也并非所有情况都适用。为解决此问题，应基于表达式而非表列收集统计信息。这可以通过两种方式实现。 ### 扩展表达式统计信息v. 14 {#internal-ru-subsection-star-230} 第一种方法是使用 *扩展统计信息* [^internal-ru-364-1] 来收集表达式的统计信息。此类统计信息不会自动收集，必须手动通过 `CREATE STATISTICS` 命令创建数据库对象： ```text {label="psql"} => CREATE STATISTICS flights_expr_stat ON (extract( month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow' )) FROM flights; ``` 收集统计信息后，估计值得到修正： ```text {label="psql" hl_lines="8"} => ANALYZE flights; => EXPLAIN SELECT * FROM flights WHERE extract( month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow' ) = 1; QUERY PLAN --------------------------------------------------------------------- Seq Scan on flights (cost=0.00..6384.17 rows=16502 width=63) Filter: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE ... (2 rows) ``` 为使收集的统计信息被使用，查询条件中的表达式必须与 `CREATE STATISTICS` 命令中写的一致。可通过 `ALTER STATISTICS` 命令单独调整收集的扩展统计信息的大小，例如：（v. 13） ```text {label="psql"} => ALTER STATISTICS flights_expr_stat SET STATISTICS 42; ``` 关于扩展统计信息的总体信息存储在系统目录表 `pg_statistic_ext` 中，而实际收集的统计信息则存储在单独的表 `pg_statistic_ext_data` 中。这种分离的目的是允许限制用户对敏感信息的访问。（v. 12）用户可访问的表达式扩展统计信息可通过专用视图以更方便的形式查看： ```text {label="psql"} => SELECT left(expr,50) || '...' AS expr, null_frac, avg_width, n_distinct, most_common_vals AS mcv, left(most_common_freqs::text,50) || '...' AS mcf, correlation FROM pg_stats_ext_exprs WHERE statistics_name = 'flights_expr_stat' \gx -[ RECORD 1 ]------------------------------------------------------ expr | EXTRACT(month FROM (scheduled_departure AT TIME ZO... null_frac | 0 avg_width | 8 n_distinct | 12 mcv | {8,9,11,7,5,12,1,3,10,4,6,2} mcf | {0.121,0.1112,0.0796,0.0793,0.0791,0.07746667,0.07... correlation | 0.08110773 ``` ### 表达式索引统计信息 {#internal-ru-subsection-star-232} 第二种修正基数估计的方法是利用创建表达式索引时会为该索引单独收集统计信息（如同表一样）的特性。如果确实需要该索引，这种方法较为方便。（*参见* [401](/zh18/19-index-access-methods/#internal-ru-page-402)） ```text {label="psql" hl_lines="12"} => DROP STATISTICS flights_expr_stat; => CREATE INDEX ON flights(extract( month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow' )); => ANALYZE flights; => EXPLAIN SELECT * FROM flights WHERE extract( month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow' ) = 1; QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on flights (cost=318.81..3237.23 rows=16824 wi... Recheck Cond: (EXTRACT(month FROM (scheduled_departure AT TIME... -> Bitmap Index Scan on flights_extract_idx (cost=0.00..314.6... Index Cond: (EXTRACT(month FROM (scheduled_departure AT TI... (4 rows) ``` 表达式索引的统计信息存储方式与表的统计信息相同。例如，可通过在 `tablename` 中指定索引名称，从 `pg_stats` 中获取唯一值的数量： ```text {label="psql"} => SELECT n_distinct FROM pg_stats WHERE tablename = 'flights_extract_idx'; n_distinct ------------ 12 (1 row) ``` 可通过 `ALTER INDEX` 命令修改索引的统计信息精度。为此，可能需要先确定对应表达式的列名称。例如： ```text {label="psql"} => SELECT attname FROM pg_attribute WHERE attrelid = 'flights_extract_idx'::regclass; attname --------- extract (1 row) => ALTER INDEX flights_extract_idx ALTER COLUMN extract SET STATISTICS 42; ``` ## 17.10. 多元统计信息 {#internal-ru-section-17-10} PostgreSQL 支持收集 *多变量统计信息*，覆盖表中的多个列。为此，必须手动创建相应的扩展统计信息。已实现三种类型的多变量统计信息。 ### 列之间的函数依赖 {#internal-ru-subsection-star-234} 如果一个列的值由另一个列的值（完全或部分）决定，且查询中同时包含这两个列的条件，则基数估计将被严重低估。考虑一个包含两个条件的查询： ```text {label="psql"} => SELECT count(*) FROM flights WHERE flight_no = 'PG0007' AND departure_airport = 'VKO'; count ------- 396 (1 row) ``` 估计值被严重低估： ```text {label="psql" hl_lines="5 9"} => EXPLAIN SELECT * FROM flights WHERE flight_no = 'PG0007' AND departure_airport = 'VKO'; QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on flights (cost=10.49..814.25 rows=14 width=63) Recheck Cond: (flight_no = 'PG0007'::bpchar) Filter: (departure_airport = 'VKO'::bpchar) -> Bitmap Index Scan on flights_flight_no_scheduled_departure_key (cost=0.00..10.48 rows=275 width=0) Index Cond: (flight_no = 'PG0007'::bpchar) (6 rows) ``` 这是著名的 *相关谓词问题*。规划器假设谓词相互独立，并将整体选择性计算为各条件选择性的乘积（逻辑“与”连接）。这在所附计划中清晰可见：在 Bitmap Index Scan 节点中，基于 `flight_no` 列的条件得出的估计值，在 Bitmap Heap Scan 节点中经过 `departure_airport` 列的条件过滤后显著降低。（*参见* [336](/zh18/16-query-execution-stages/#internal-ru-page-337)）然而我们清楚，航班号唯一确定了出发和到达机场：实际上第二个条件是冗余的（当然前提是机场名称正确）。在此类情况下，功能依赖的扩展统计信息可改善估计。创建两个列之间功能依赖的扩展统计信息： ```text {label="psql"} => CREATE STATISTICS flights_dep_stat(dependencies) ON flight_no, departure_airport FROM flights; ``` 在下一次分析表时，将收集所需的统计信息，评估将得到改进： ```text {label="psql" hl_lines="6"} => ANALYZE flights; => EXPLAIN SELECT * FROM flights WHERE flight_no = 'PG0007' AND departure_airport = 'VKO'; QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on flights (cost=10.56..816.91 rows=276 width=63) Recheck Cond: (flight_no = 'PG0007'::bpchar) Filter: (departure_airport = 'VKO'::bpchar) -> Bitmap Index Scan on flights_flight_no_scheduled_departure_key (cost=0.00..10.49 rows=276 width=0) Index Cond: (flight_no = 'PG0007'::bpchar) (6 rows) ``` 可以在系统目录中通过以下方式查看收集到的统计信息： ```text {label="psql"} => SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'flights_dep_stat'; dependencies ------------------------------------------ {"2 => 5": 1.000000, "5 => 2": 0.010300} (1 row) ``` 数字 2 和 5 是 `pg_attribute` 中表的列号。这些值表示函数依赖程度：从 0（无依赖）到 1（第一个列中的值完全决定第二个列中的值）. ### 多元非重复值的数量 {#internal-ru-subsection-star-236} 关于多个列中不同值组合数量的信息，有助于更准确地评估多列分组的基数。例如，规划器将出发机场和到达机场的可能组合数量估算为机场数量的平方，但实际值要小得多，因为并非每对机场之间都有直达航班： ```text {label="psql" hl_lines="13"} => SELECT count(*) FROM ( SELECT DISTINCT departure_airport, arrival_airport FROM flights ) t; count ------- 618 (1 row) => EXPLAIN SELECT DISTINCT departure_airport, arrival_airport FROM flights; QUERY PLAN -------------------------------------------------------------------- HashAggregate (cost=5847.01..5955.16 rows=10816 width=8) Group Key: departure_airport, arrival_airport -> Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=8) (3 rows) ``` 创建并收集不同值数量的扩展统计信息： ```text {label="psql"} => CREATE STATISTICS flights_nd_stat(ndistinct) ON departure_airport, arrival_airport FROM flights; => ANALYZE flights; ``` 现在基数评估已得到修正： ```text {label="psql" hl_lines="5"} => EXPLAIN SELECT DISTINCT departure_airport, arrival_airport FROM flights; QUERY PLAN -------------------------------------------------------------------- HashAggregate (cost=5847.01..5853.19 rows=618 width=8) Group Key: departure_airport, arrival_airport -> Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=8) (3 rows) ``` 收集到的统计信息可以在系统目录中查看： ```text {label="psql"} => SELECT n_distinct FROM pg_stats_ext WHERE statistics_name = 'flights_nd_stat'; n_distinct --------------- {"5, 6": 618} (1 row) ``` ### 多元 MCV 列表v. 12 {#internal-ru-subsection-star-238} 当某一列的值分布不均匀时，仅知道函数依赖关系可能不够，因为评估结果在很大程度上取决于具体的值对。例如，规划器在估算从谢列梅捷沃机场出发的波音 737 航班数量时会出现错误： ```text {label="psql" hl_lines="11"} => SELECT count(*) FROM flights WHERE departure_airport = 'SVO' AND aircraft_code = '733'; count ------- 2037 (1 row) => EXPLAIN SELECT * FROM flights WHERE departure_airport = 'SVO' AND aircraft_code = '733'; QUERY PLAN --------------------------------------------------------------------- Seq Scan on flights (cost=0.00..5847.00 rows=737 width=63) Filter: ((departure_airport = 'SVO'::bpchar) AND (aircraft_cod... (2 rows) ``` 在这种情况下，可以通过收集多值频繁值列表的统计信息来改进评估 [^internal-ru-370-1]： ```text {label="psql"} => CREATE STATISTICS flights_mcv_stat(mcv) ON departure_airport, aircraft_code FROM flights; => ANALYZE flights; ``` 新的基数评估要精确得多： ```text {label="psql" hl_lines="5"} => EXPLAIN SELECT * FROM flights WHERE departure_airport = 'SVO' AND aircraft_code = '733'; QUERY PLAN --------------------------------------------------------------------- Seq Scan on flights (cost=0.00..5847.00 rows=2127 width=63) Filter: ((departure_airport = 'SVO'::bpchar) AND (aircraft_cod... (2 rows) ``` 为了获得该评估，规划器使用了系统目录中保存的频率信息： ```text {label="psql"} => SELECT values, frequency FROM pg_statistic_ext stx JOIN pg_statistic_ext_data stxd ON stx.oid = stxd.stxoid, pg_mcv_list_items(stxdmcv) m WHERE stxname = 'flights_mcv_stat' AND values = '{SVO,773}'; values | frequency -----------+---------------------- {SVO,773} | 0.005066666666666666 (1 row) ``` 与普通频繁值列表一样，多值列表中也保存 *default_statistics_target* 个值（如果在列级别设置了该参数，则使用最大值）。（默认值：`100`）与表达式扩展统计信息类似，如需，也可以调整列表大小：(v. 13) ```sql ALTER STATISTICS ... SET STATISTICS ...; ``` 在所有示例中，我仅使用了两个列，但多值统计信息也可以针对更多列创建。在同一个对象中，可以组合不同类型的统计信息，通过逗号分隔指定（如果不指定类型，则将为给定列同时收集所有可能的统计信息类型）. 此外，与表达式统计信息一样，多值统计信息也可以使用任意表达式，而不仅限于列名。(v. 14) [^internal-ru-347-1]: [postgrespro.ru/docs/postgresql/18/planner-stats](). [^internal-ru-348-1]: [backend/commands/analyze.c](), 函数 do\_analyze\_rel. [^internal-ru-348-2]: [backend/commands/cluster.c](), 函数 copy\_table\_data. [^internal-ru-348-3]: [backend/catalog/heap.c](), 函数 index\_update\_stats. [^internal-ru-348-4]: [backend/access/heap/vacuumlazy.c](), 函数 heap\_vacuum\_rel. [^internal-ru-348-5]: [backend/commands/analyze.c](), 函数 std\_typanalyze. [^internal-ru-348-6]: [backend/commands/analyze.c](), 函数 acquire\_sample\_rows; [backend/utils/misc/sampling.c](). [^internal-ru-349-1]: [backend/access/table/tableam.c](), 函数 table\_block\_relation\_estimate\_size. [^internal-ru-350-1]: [backend/access/table/tableam.c](), 函数 table\_block\_relation\_estimate\_size. [^internal-ru-351-1]: [citforum.ru/database/articles/evergreen\_nulls](). [^internal-ru-351-2]: [include/catalog/pg\_statistic.h](). [^internal-ru-353-1]: [backend/commands/analyze.c](), 函数 compute\_distinct\_stats. [^internal-ru-353-2]: [backend/utils/adt/selfuncs.c](), 函数 var\_eq\_non\_const. [^internal-ru-355-1]: [backend/utils/adt/selfuncs.c](), 函数 var\_eq\_const. [^internal-ru-356-1]: [backend/utils/adt/selfuncs.c](), 函数 scalarineqsel. [^internal-ru-358-1]: [backend/utils/adt/selfuncs.c](), 函数 ineq\_histogram\_selectivity. [^internal-ru-361-1]: [postgrespro.ru/docs/postgresql/18/arrays](); [backend/utils/adt/array\_typanalyze.c](); [backend/utils/adt/array\_selfuncs.c](). [^internal-ru-361-2]: [postgrespro.ru/docs/postgresql/18/datatype-textsearch](); [backend/tsearch/ts\_typanalyze.c](); [backend/tsearch/ts\_selfuncs.c](). [^internal-ru-361-3]: [postgrespro.ru/docs/postgresql/18/rangetypes](); [backend/utils/adt/rangetypes\_typanalyze.c](); [backend/utils/adt/rangetypes\_selfuncs.c](). [^internal-ru-361-4]: [backend/utils/adt/multirangetypes\_selfuncs.c](). [^internal-ru-363-1]: [backend/utils/adt/selfuncs.c](), 函数 eqsel. [^internal-ru-363-2]: [postgrespro.ru/docs/postgresql/18/xfunc-optimization](). [^internal-ru-364-1]: [postgrespro.ru/docs/postgresql/18/planner-stats#PLANNER-STATS-EXTENDED](); [backend/statistics/README](). [^internal-ru-370-1]: [backend/statistics/README.mcv](); [backend/statistics/mcv.c]().