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