PG中的查询：2.统计--（1）

yanzongshuaiDBA 2022-03-16

372

本节讨论成本优化器的基础：统计。通过示例进行讲解。这里会由很多执行计划，后续会更加详细讨论这些计划如何运行。现在只需要注意每个计划的第一行看到的数字以及行数。这些是行数估计值。

基本统计

pg_class系统表存储着基本关系级别的统计信息。统计信息包括：

1) 关系的行数reltuples

2) 关系大小，以页为单位relpages

3) 关系visibility map中被标记的页的页数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=0.00..4772.67 rows=214867 width=63)
(1 row)
复制

自动或手动分析期间采集统计信息。基本统计数据是重要信息，在执行某些操作时也会计算处理，例如VACUUM FULL和CLUSTER或CREATE INDEX和REINDEX。系统还会在VACCUM期间更新统计信息。

为采集统计信息，分析器随机select 300*default_statistics_target行数（默认值是100，因此总共为30000行）。此处未考虑表大小，因为总体数据集大小对足以进行精确统计的样本大小没有影响。

从300*default_statistics_target随机页中选择随机行。如果表比预期的样本大小小，分析器读取整个表。

大表中，统计数据将不准确。因为分析器不会扫描每一行。即便扫描每一行，统计数据也总会有过期，因为表中数据一直在变化。无论如何，我们不需要统计数据那么精确：高达一个数量级的变化仍然足够准确以产生适当的计划。让我们创建一个禁用自动vacuum的表的副本flights，以便我们可以控制何时进行分析。

CREATE TABLE flights_copy(LIKE flights) WITH (autovacuum_enabled = false);
复制

新表中还没有统计信息：

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

reltuples=-1（PG14及更高版本）帮助我们区分从没采集统计信息的表和空表。通常情况下，新创建的表会立即填充，规划器对新表无感知，因此默认情况下假定该表10页：

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

规划器基于单行宽度计算行个数。宽度通常是在分析期间计算的平均值。但是，这次没有分析数据，因此系统根据列数据类型来估算宽度。从flights表拷贝数据到新表然后执行分析器：

INSERT INTO flights_copy SELECT * FROM flights;
INSERT 0 214867
ANALYZE flights_copy;
复制

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

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

Vacuum后relallvisible值会更新：

VACUUM flights_copy;
SELECT relallvisible FROM pg_class WHERE relname = 'flights_copy';
 relallvisible
−−−−−−−−−−−−−−−
          2624
(1 row)
复制

评估index-only扫描代价的时候会用到这个值。

我们保留老的统计信息，插入1倍元组，看下规划器得到的基数是多少：

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数据已过时，但该估计是准确的:

SELECT reltuples, relpages
FROM pg_class WHERE relname = 'flights_copy';

 reltuples | relpages
−−−−−−−−−−−+−−−−−−−−−−
    214867 |     2624
(1 row)
复制

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

SELECT reltuples *
  (pg_relation_size('flights_copy') / 8192) / relpages
FROM pg_class WHERE relname = 'flights_copy';

 ?column?
−−−−−−−−−−
   429734
(1 row)
复制

这种调整并不总是有效，例如可以删除几行，但估算值不会变化。但当发生较大变化时，这种方法可以让统计数据保持不变，直到analyze。

NULL值

虽然正统主义者看不起，但是NULL值可以方便地表示未知或者不存在的值。但是特殊值需要特殊处理。使用NULL值时需要考虑一些实际的注意事项。布尔逻辑变成三进制，NOT IN构造开始表现的很奇怪。目前尚不清楚NULL值是否被视为低于或者高于常规值（特殊从句NULLS FIRST和NULLS LAST帮助）。聚合函数中使用NULL值也很粗略。因为NULL值实际上根本不是值，规划器需要额外的数据来容纳他们。

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

NULL值的分数是列级别的统计信息。被指定为pg_stats中的null_frac。本例中，一些飞机还没起飞，所以他们的起飞时间是不确定的：

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值

一列中distinct值个数存储在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 = $0)
   InitPlan 1 (returns $0)
     −> Seq Scan on airports_data ml  (cost=0.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)
复制

最常见的值

为提高非均匀分布的估算精度，分析器通常收集最常见值及其频率的统计信息。这些值存储在pg_stats的most_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 ...;
复制

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

原文

https://postgrespro.com/blog/pgsql/5969296

postgresql

文章转载自yanzongshuaiDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。