但不确定值具有特殊意义，需要特别对待。除了理论上的不一致外，还存在大量实际困难，必须加以考虑。普通的布尔逻辑变为三值逻辑，而 NOT IN 子句的行为变得出人意料。不确定值是否应被视为小于或大于普通值尚不明确（因此引入了 NULLS FIRST 和 NULLS LAST 用于排序）。不确定值是否应计入聚合函数也并不明显。由于严格来说，不确定值根本不是值，因此规划器需要额外信息才能正确处理它们。
=> 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 的比例：
=> 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)
精确值：
=> SELECT count(*) FROM flights WHERE actual_departure IS NULL;
count
-------
16348
(1 row)
=> 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 仅执行一次，其计算结果在主计划中复用。
=> 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 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)
=> 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)
座位号被特意选择为恰好位于直方图箱子的边界上。
此类条件的选择性估计值为 箱子数量N，其中 N 是满足条件的箱子数量（即位于该值右侧的箱子）。需要注意的是，最频繁值不包含在直方图中。
一般来说，不确定值也不包含在直方图中，但在 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)
图 17.4直方图
首先计算满足条件的最频繁值所占比例：
=> 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)
未被直方图包含的最频繁值的总比例为：
=> 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.6747334
(1 row)
由于该区间恰好占据直方图中相同数量的箱子（从100个可能箱子中），我们得到如下估计：
=> SELECT round( reltuples * (
0.20833333 -- вклад частых значений
+ (1 - 0.6747334 - 0) * (52 / 100.0) -- вклад гистограммы
))
FROM pg_class WHERE relname = 'boarding_passes';
round
---------
2991708
(1 row)
一般情况下，当值不位于边界时，通过线性插值来考虑该值所在箱子的部分比例。
精确值为：
=> SELECT count(*) FROM boarding_passes WHERE seat_no > '30B';
count
---------
2993735
(1 row)
=> 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)
=> CREATE STATISTICS flights_expr_stat 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=16502 width=63)
Filter: (EXTRACT(month FROM (scheduled_departure AT TIME ZONE ...
(2 rows)
=> 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)
=> SELECT n_distinct FROM pg_stats
WHERE tablename = 'flights_extract_idx';
n_distinct
------------
12
(1 row)
可通过 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;
=> CREATE STATISTICS flights_mcv_stat(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=2127 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_stat'
AND values = '{SVO,773}';
values | frequency
-----------+----------------------
{SVO,773} | 0.005066666666666666
(1 row)
