PostgreSQL统计与性能优化深度分析

2022-03-21 15:42:21

基本关系级别的统计信息存储在pg_class系统目录的表中。统计数据包括以下数据：

关系的行数 ( reltuples)。
关系的页面大小 ( relpages)。
在关系的可见性图中标记的页面数(relallvisible)。

SELECT reltuples, relpages, relallvisible FROM pg_class WHERE relname = 'flights';
reltuples | relpages | relallvisible
−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−−−−−−
    214867 |     2624 |         2624
(1 row)

对于没有条件（过滤器）的查询，基数估计将等于reltuples：

EXPLAIN SELECT * FROM flights;

                           QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights  (cost=.00..4772.67 rows=214867 width=63)
(1 row)

在自动或手动分析期间收集统计数据。基本统计数据是重要信息，在执行某些操作时也会计算出来，例如VACUUM FULL and CLUSTER或CREATE INDEX and REINDEX。系统也会在清理过程中更新统计信息。

为了收集统计数据，分析器随机选择 300 × default_statistics_target行（默认值为 100，因此总共 30,000 行）。此处未考虑表大小，因为总体数据集大小对被认为足以进行准确统计的样本大小几乎没有影响。

从 300 × default_statistics_target随机页面中选择随机行。如果表格小于所需的样本量，分析仪只会读取整个表格。

在大表中，统计数据将不，因为分析器不会扫描每一行。即便如此，统计数据也总会有些过时，因为表数据一直在变化。无论如何，我们不需要统计数据那么：高达一个数量级的变化仍然足够准确以产生适当的计划。

让我们创建一个禁用自动清理的表的副本flights，以便我们可以控制何时进行分析。

CREATE TABLE pgccc_flights_copy(LIKE flights)
WITH (autovacuum_enabled = false);

新表还没有统计信息：

SELECT reltuples, relpages, relallvisible
FROM pg_class WHERE relname = 'pgccc_flights_copy';
 reltuples | relpages | relallvisible
−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−−−−−−
        −1 |        0 |             0
(1 row)

值reltuples= -1（在 PostgreSQL 14 及更高版本中）帮助我们区分从未收集过统计信息的表和没有任何行的表。

通常情况下，新创建的表会立即填充。规划器对新表一无所知，因此默认情况下假定该表为 10 页长：

EXPLAIN SELECT * FROM pgccc_flights_copy;
                           QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on pgccc_flights_copy  (cost=0.00..14.10 rows=410 width=170)
(1 row)

规划器根据单行的宽度计算行数。宽度通常是在分析期间计算的平均值。但是，这次没有分析数据，因此系统根据列数据类型来近似宽度。

让我们将数据从新表中复制flights并运行分析器：

INSERT INTO pgccc_flights_copy SELECT * FROM flights;

INSERT  214867

ANALYZE pgccc_flights_copy;

现在统计信息与实际行数匹配。该表足够紧凑，分析器可以遍历每一行：

SELECT reltuples, relpages, relallvisible
FROM pg_class WHERE relname = 'pgccc_flights_copy';
 reltuples | relpages | relallvisible
−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−−−−−−
    214867 |     2624 |             0
(1 row)

清空relallvisible后值更新：

VACUUM pgccc_flights_copy;

SELECT relallvisible FROM pg_class WHERE relname = 'pgccc_flights_copy';

 relallvisible
−−−−−−−−−−−−−−−
          2624
(1 row)

在估计仅索引扫描成本时使用此值。

让我们在保留旧统计信息的同时将行数加倍，看看规划器得出的基数是多少：

INSERT INTO pgccc_flights_copy SELECT * FROM flights;
SELECT count(*) FROM pgccc_flights_copy;
 count
−−−−−−−−
 429734
(1 row)

EXPLAIN SELECT * FROM pgccc_flights_copy;

                            QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on pgccc_flights_copy  (cost=0.00..9545.34 rows=429734 width=63)
(1 row)

尽管数据已过时，但该估计是准确的pg_class：

SELECT reltuples, relpages
FROM pg_class WHERE relname = 'pgccc_flights_copy';
 reltuples | relpages
−−−−−−−−−−−+−−−−−−−−−−
    214867 |     2624
(1 row)

规划器注意到数据文件的大小不再匹配旧relpages值，因此它reltuples适当地缩放以尝试提高准确性。文件大小增加了一倍，因此行数也相应调整（假设数据密度不变）：

SELECT reltuples *
  (pg_relation_size('flights_copy') / 8192) / relpages
FROM pg_class WHERE relname = 'pgccc_flights_copy';
 ?column?
−−−−−−−−−−
   429734
(1 row)

这种调整并不总是有效（例如，您可以删除几行，而估计值不会改变），但当发生大的变化时，这种方法可以让统计数据保持到分析器出现。

空值

NULL 值可以方便地表示未知或不存在的值。

但特殊值需要特殊处理。使用 NULL 值时需要牢记一些实际的注意事项。布尔逻辑变成了三进制，NOT IN构造开始表现得很奇怪。目前尚不清楚 NULL 值是否被视为低于或高于常规值（特殊条款NULLS FIRST和NULLS LAST帮助）。在聚合函数中使用 NULL 值也很粗略。因为 NULL 值实际上根本不是值，所以规划器需要额外的数据来容纳它们。

除了基本的关系级别统计信息外，分析器还收集关系中每一列的统计信息。此数据存储在pg_statistic系统目录的表中，可以使用pg_stats视图方便地显示。

null 值的分数是列级统计信息。它被指定null_frac为pg_stats。在这个例子中，一些飞机还没有起飞，所以它们的起飞时间是不确定的：

EXPLAIN SELECT * FROM flights WHERE actual_departure IS NULL;
                          QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights  (cost=0.00..4772.67 rows=16036 width=63)
   Filter: (actual_departure IS NULL)
(2 rows)

优化器将总行数乘以 NULL 分数：

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
−−−−−−−
 16036
(1 row)

这已经很接近16348的真实值了。

Distinct values

列中不同值的数量存储在pg_stats中的n_distinct字段中。

如果n_distinct为负，则其值表示不同值的比例。例如，-1 的值意味着列中的每个项目都是的。当不同值的数量达到行数的 10% 或更多时，分析器将切换到分数模式。此时假设修改数据时该比例通常会保持不变。

如果不同值的数量计算不正确（因为样本恰好不具有代表性），您可以手动设置此值：

ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...);

在数据均匀分布的情况下，不同值的数量很有用。考虑“ column = expression ”子句的基数估计。如果在规划阶段表达式的值未知，则规划器假定表达式同样可能从列中返回任何值。

EXPLAIN
SELECT * FROM flights WHERE departure_airport = (
  SELECT airport_code FROM airports WHERE city = 'Saint Petersburg'
);
                         QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights  (cost=30.56..5340.40 rows=2066 width=63)
   Filter: (departure_airport = $)
   InitPlan 1 (returns $)
     −> Seq Scan on airports_data ml  (cost=.00..30.56 rows=1 wi...
         Filter: ((city −>> lang()) = 'Saint Petersburg'::text)
(5 rows)

InitPlan 节点只执行一次，然后在主计划中使用该值而不是 $0。

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)

如果所有数据均匀分布，则这些统计数据（连同小值和大值）足以进行准确的估计。不幸的是，这种估计不适用于非均匀分布，后者更为常见：

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)

常见的值

为了提高非均匀分布的估计精度，分析器收集常见值 (MCV) 及其频率的统计信息。这些值存储在pg_statsasmost_common_vals和most_common_freqs中。

以下是常见飞机类型的此类统计数据示例：

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,763,733,319,773}
mcf | {0.2783,0.27473333,0.25816667,0.059233334,0.038533334,0.0370...

估计“ column = expression ”的选择性非常简单：规划器只需从most_common_vals数组中获取一个值并将其乘以数组中相同位置的频率most_common_freqs。

EXPLAIN SELECT * FROM flights WHERE aircraft_code = '733';
                          QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights  (cost=0.00..5309.84 rows=7957 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
−−−−−−−
  7957
(1 row)

这个估计值将接近 8263 的真实值。

MCV 列表也用于不等式的选择性估计：为了找到“ column < value ”的选择性，规划器搜索most_common_vals所有低于给定值的值，然后将它们的频率相加most_common_freqs。

当不同值的数量较少时，公共值统计有效。MCV 数组的大大小由default_statistics_target定义，该参数与分析期间控制行样本大小的参数相同。

在某些情况下，将值（以及数组大小）增加到超出默认值将提供更准确的估计。您可以为每列设置此值：

ALTER TABLE ... ALTER COLUMN ... SET STATISTICS ...;

行样本大小也会增加，但仅限于表。

公共值数组存储值本身，并且根据值的不同，可能会占用大量空间。这就是为什么超过 1 kB 的值被排除在分析和统计之外的原因。它可以pg_statistic控制大小，并且不会使计划器超载。无论如何，这么大的值通常是不同的，不会包含在most_common_vals。

柱状图

当不同值的数量变得太大而无法将它们全部存储在数组中时，系统开始使用柱状图表示。柱状图使用多个存储桶来存储值。存储桶的数量受相同的default_statistics_target参数限制。

每个桶的宽度以这样一种方式选择，即在它们之间均匀分布值（如图像上具有大致相同面积的矩形所示）。这种表示使系统能够只存储直方图边界，而不是浪费空间来存储每个桶的频率。直方图不包括 MCV 列表中的值。

边界存储在中的histogram_bounds字段中pg_stats。任何桶中值的汇总频率等于 1 /桶数。

柱状图存储为存储桶边界数组：

SELECT left(histogram_bounds::text,60) || '...' AS histogram_bounds
FROM pg_stats s
WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no';
                        histogram_bounds
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 {10B,10D,10D,10F,11B,11C,11H,12H,13B,14B,14H,15H,16D,16D,16H...
(1 row)

除其他外，直方图与 MCV 列表一起用于估计“大于”和“小于”操作的选择性。

示例：计算为后排座位签发的登机牌数量。

EXPLAIN SELECT * FROM boarding_passes WHERE seat_no > '30C';
                             QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on boarding_passes  (cost=.00..157353.30 rows=2943394 ...
   Filter: ((seat_no)::text > '30C'::text)
(2 rows)

截止座位号专门选择在两个桶之间的边缘。

此条件的选择性是N /桶数，其中N是具有匹配值的桶数（在截止点的右侧）。请记住，直方图没有考虑常见的值和未定义的值。

让我们先看看匹配常见值的分数：

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 > '30C';
  sum
−−−−−−−−
 0.2127
(1 row)

现在让我们看看常见值的分数（从柱状图中排除）：

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';
  sum
−−−−−−−−
 0.6762
(1 row)

列中没有 NULL 值seat_no：

SELECT s.null_frac
FROM pg_stats s
WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no';
 null_frac
−−−−−−−−−−−
         0
(1 row)

间隔正好涵盖 49 个桶（总共 100 个）。结果估计：

SELECT round( reltuples * (
   0.2127 -- from most common values
 + (1 - 0.6762 - ) * (49 / 100.0) -- from histogram
))
FROM pg_class WHERE relname = 'boarding_passes';
  round
−−−−−−−−−
 2943394
(1 row)

真实值为 2986429。

当截止值不在桶的边缘时，该桶的匹配分数是使用线性插值计算的。

较高的default_statistics_target值可能会提高估计精度，但直方图与 MCV 列表一起已经产生了很好的结果，即使有大量不同的值：

SELECT n_distinct FROM pg_stats
WHERE tablename = 'boarding_passes' AND attname = 'seat_no';
 n_distinct
−−−−−−−−−−−−
        461
(1 row)

更高的估计精度只有在提高规划质量时才有用。在没有正当理由的情况下增加default_statistics_target可能会减慢分析和计划，但对优化没有影响。

另一方面，降低参数（一直降至零）可能会提高分析和计划速度，但也可能导致计划质量低下，因此这种“节省时间”很少有道理。

非标量数据类型的统计

非标量数据类型的统计可能不仅包括非标量值本身的分布数据，还包括它们的组成元素的分布数据。这允许在查询非范式中的列时进行更准确的计划。

1）数组most_common_elems和分别

most_common_elem_freqs包含常见的元素及其频率。

这些统计数据被收集并用于估计阵列和tsvector数据的选择性。

2）elem_count_histogram数组是值中不同元素数量的直方图。

收集这些统计数据并仅用于估计阵列的选择性。

3）对于范围数据类型，直方图用于表示范围长度的分布及其下限和上限的分布。然后，这些直方图有助于估计使用这些数据类型的各种操作的选择性。它们未显示在中pg_stats。

这些统计信息也用于 PostgreSQL 14 中引入的多范围数据类型。

平均场宽度

pg_stats中的avg_width字段表示列中的平均字段宽度。数据类型(如integer或char(3))的字段宽度显然是固定的，但当涉及到没有设置宽度的数据类型(如文本)时，值可能会因列而显著不同:

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)

这些统计信息有助于估计排序或散列等操作的内存使用情况。

相关性

correlation中的字段pg_stats表示磁盘上的物理行排序与列值的逻辑排序（“大于”或“小于”）之间的相关性，范围从 -1 到 +1。如果这些值按顺序存储，则相关性将接近 +1。如果它们以相反的顺序存储，则相关性将更接近 -1。数据在磁盘上分布越混乱，值越接近于零。

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无法收集列的统计信息，因为未为点数据类型定义比较操作（“小于”和“大于”）。

相关性用于索引扫描成本估计。

表达统计

一般来说，列统计只在操作调用列本身时使用，而不是用于以列为参数的表达式。规划器不知道函数将如何影响列统计信息，因此像“ function-call = constant ”这样的条件总是估计为 0.5%：

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)

计划程序甚至无法处理标准函数，而对我们来说，很明显 1 月份的航班比例将约为总航班的 1/12：

SELECT count(*) AS total,
  count(*) FILTER (WHERE extract(
    month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow'
  ) = 1) AS january
FROM flights;
 total  | january
−−−−−−−−+−−−−−−−−−
 214867 |   16831
(1 row)

这就是表达式统计的用武之地。

扩展表达式统计

PostgreSQL 14 引入了一种称为扩展表达式统计的特性。扩展表达式统计信息不会自动收集。要手动收集它们，请使用该CREATE STATISTICS命令创建扩展统计数据库对象。

CREATE STATISTICS flights_expr ON (extract(
  month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow'
))
FROM flights;

新的统计数据将改进估计：

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=16222 width=63)
   Filter: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE ...
(2 rows)

要使统计信息起作用，统计信息生成命令中的表达式必须与原始查询中的表达式相同。

扩展统计元数据存储在pg_statistic_ext系统目录的表中，而统计数据本身存储在单独的表中pg_statistic_ext_data（在 PostgreSQL 12 及更高版本中）。如有必要，它与元数据分开存储，以限制用户访问敏感信息。

有些视图以用户友好的形式显示收集的统计信息。可以使用以下命令显示扩展表达式统计信息：

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' \gx
-[ RECORD 1 ]−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
expr        | EXTRACT(month FROM (scheduled_departure AT TIME ZO...
null_frac   | 
avg_width   | 8
n_distinct  | 12
mcv         | {8,9,3,5,12,4,10,7,11,1,6,2}
mcf         | {0.12526667,0.11016667,0.07903333,0.07903333,0.078..
correlation | 0.095407926

可以使用以下ALTER STATISTICS命令更改收集的统计数据量：

ALTER STATISTICS flights_expr SET STATISTICS 42;

表达式索引统计

建立表达式索引时，系统会收集其统计信息，就像使用常规表一样。计划者也可以使用这些统计数据。这很方便，但前提是我们真正关心索引。

DROP STATISTICS flights_expr;
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.42..3235.96 rows=16774 wi...
   Recheck Cond: (EXTRACT(month FROM (scheduled_departure AT TIME...
   −> Bitmap Index Scan on flights_extract_idx  (cost=0.00..314.2...
       Index Cond: (EXTRACT(month FROM (scheduled_departure AT TI...
(4 rows)

表达式索引统计信息的存储方式与表统计信息相同。例如，这是不同值的数量：

SELECT n_distinct FROM pg_stats
WHERE tablename = 'flights_extract_idx';
 n_distinct
−−−−−−−−−−−−
         12
(1 row)

在 PostgreSQL 11 及更高版本中，可以使用ALTER INDEX命令更改索引统计的准确性。您可能需要引用该表达式的列的名称。例子：

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;

多元统计

PostgreSQL 10 引入了同时从多个列收集统计信息的能力，也称为多变量统计信息。这需要手动生成必要的扩展统计信息。

多元统计分为三种类型。

列之间的功能依赖关系

当一列中的值（完全或部分）由另一列中的值确定时，并且在查询中存在引用两列的条件，则结果基数将被低估。

这是一个具有两个条件的示例：

SELECT count(*) FROM flights
WHERE flight_no = 'PG0007' AND departure_airport = 'VKO';
 count
−−−−−−−
   396
(1 row)

估计值明显低于应有的值，只有 26 行：

EXPLAIN SELECT * FROM flights
WHERE flight_no = 'PG0007' AND departure_airport = 'VKO';
                             QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Bitmap Heap Scan on flights  (cost=12.03..1238.70 rows=26 width=63)
   Recheck Cond: (flight_no = 'PG0007'::bpchar)
   Filter: (departure_airport = 'VKO'::bpchar)
   −> Bitmap Index Scan on flights_flight_no_scheduled_departure_key
       (cost=.00..12.02 rows=480 width=)
       Index Cond: (flight_no = 'PG0007'::bpchar)
(6 rows)

这就是臭名昭著的关联谓词问题。计划者期望谓词是独立的，并将结果的选择性计算为条件选择性的乘积，并与and结合。位图索引扫描估计，为flight_no条件计算，在应用位图堆扫描中的departure_airport条件后显著下降。

当然，航班号已经明确定义了出发机场，因此第二个条件实际上是多余的。这是扩展功能依赖统计可以帮助改进估计的地方。

让我们为两列创建扩展的函数依赖统计信息：

CREATE STATISTICS flights_dep(dependencies)
ON flight_no, departure_airport FROM flights;

再次分析，现在使用新的统计数据，估计得到改善：

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=.00..10.49 rows=276 width=)
       Index Cond: (flight_no = 'PG0007'::bpchar)
(6 rows)

统计信息存储在系统目录中，可以使用以下命令显示：

SELECT dependencies
FROM pg_stats_ext WHERE statistics_name = 'flights_dep';
               dependencies
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 {"2 => 5": 1.000000, "5 => 2": .010567}
(1 row)

数字 2 和 5 是表中的列号pg_attribute。它们旁边的值表示函数依赖的程度，从 0（独立）到 1（第二列中的值完全由列中的值定义）。

不同值的多元数量

对来自多列的值的不同组合数量的统计将显着提高GROUP BY对多列的操作的基数。

在此示例中，规划器将可能的出发和到达机场对的数量估计为机场总数的平方。然而，真实的成对数量要低得多，因为并非每两个机场都通过直飞航班连接：

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)

让我们为不同值的数量创建一个扩展统计信息：

CREATE STATISTICS flights_nd(ndistinct)
ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
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=.00..4772.67 rows=214867 width=8)
(3 rows)

统计信息存储在系统目录中：

SELECT n_distinct
FROM pg_stats_ext WHERE statistics_name = 'flights_nd';
  n_distinct
−−−−−−−−−−−−−−−
 {"5, 6": 618}
(1 row)

多元常见值列表

当值分布不均匀时，仅功能依赖数据可能不够，因为估计值将根据特定的值对而显着变化。考虑这个例子，计划者错误地估计了波音 737 从谢列梅捷沃机场起飞的航班数量：

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=733 width=63)
   Filter: ((departure_airport = 'SVO'::bpchar) AND (aircraft_cod...
(2 rows)

我们可以使用多元 MCV 列表统计来改进估计：

CREATE STATISTICS flights_mcv(mcv)
ON departure_airport, aircraft_code FROM flights;
ANALYZE flights;
EXPLAIN SELECT * FROM flights
WHERE departure_airport = 'SVO' AND aircraft_code = '733';
                             QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Seq Scan on flights  (cost=0.00..5847.00 rows=2077 width=63)
   Filter: ((departure_airport = 'SVO'::bpchar) AND (aircraft_cod...
(2 rows)

现在系统目录中有频率数据供规划器使用：

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'
AND values = '{SVO,773}';
  values   |      frequency
−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−
 {SVO,773} | .005733333333333333
(1 row)

多变量常见值列表存储default_statistics_target值，就像常规 MCV 列表一样。如果参数是在列级别定义的，则使用大值。

与扩展表达式统计信息一样，您可以更改列表大小（在 PostgreSQL 13 及更高版本中）：

ALTER STATISTICS ... SET STATISTICS ...;

在这些示例中，仅为两列收集了多变量统计信息，但您可以根据需要为任意多的列收集它们。

您还可以将不同类型的统计信息收集到单个扩展统计信息对象中。为此，只需在创建对象时列出用逗号分隔的所需统计类型。如果没有定义特定的统计类型，系统将一次收集所有可用的统计信息。

PostgreSQL 14 还允许您在进行多变量和表达式统计时不仅使用列名，还可以使用任意表达式。

原文链接：https://mp.weixin.qq.com/s/D6EziA8ZqTbggLg_42zGlg

分享好友

分享这个小栈给你的朋友们，一起进步吧。

PostgreSQL

创建时间：2020-06-17 14:30:20

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

小雨滴
专家