| PostgreSQL 9.3.1 中文手册 | ||||
|---|---|---|---|---|
| 上一页 | 上一级 | 章 60. 规划器如何使用统计信息 | 下一页 | |
下面的例子使用PostgreSQL回归测试数据库中的表。 输出结果是从8.3版获得的。之前或之后版本的动作可能会有所变化。 同时需要注意的是，在产生统计信息时，ANALYZE使用的是随机采样， 再次执行ANALYZE命令，结果可能会发生轻微的改变。
让我们以一个很简单的查询开始：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)规划器如何判断tenk1里面行的基数 在第 14.2 节介绍， 但为了完整，在这里重复一下。行数或页数是从pg_class中查出来的：
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
relpages | reltuples
----------+-----------
358 | 10000这些数字表示当前最近的VACUUM或者ANALYZE。 之后，表的块数和元组数。规划器随即取出表中当前实际的块数 （这个操作的开销很小，不需要扫描全表），如果实际的块数与 relpages不同，为估算当前的行数， 会对reltuples进行一定的缩放。 在这种情况下，relpages的值是最新的， 因此估计的行与reltuples相同。
换一个WHERE子句是范围条件的例子：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=24.06..394.64 rows=1007 width=244)
Recheck Cond: (unique1 < 1000)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)
规划器检查WHERE子句条件，
并在pg_operator中查找操作符<的选择性函数（selectivity function），
选择度函数在oprrest列中记录，
在本例中，对应的条目是scalarltsel。
scalarltsel函数从 pg_statistics中获取
unique1的直方图。
对于手工查询来说，在pg_stats视图中查找更方便：
SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='unique1';
histogram_bounds
------------------------------------------------------
{0,993,1997,3050,4040,5036,5957,7057,8029,9016,9995}然后，把直方图中"< 1000"的部分找出来。这就是选择性。 直方图把范围分隔成等频的桶， 所以我们要做的是将我们的数值所在的桶找出来， 然后计算它的部分以及所有该值之前的部分。 值1000很明显在第二个桶(993-1997)里，因此，假设每个桶里面的分布是线性的， 那么就可以计算出选择性：
selectivity = (1 + (1000 - bucket[2].min)/(bucket[2].max - bucket[2].min))/num_buckets
= (1 + (1000 - 993)/(1997 - 993))/10
= 0.100697也就是一个桶加上第二个桶的线性部分，除以总的桶数。 那么估计的行数现在可以用选择性和tenk1的基数之积计算：
rows = rel_cardinality * selectivity
= 10000 * 0.100697
= 1007 (rounding off)
然后考虑一个WHERE子句是等于条件的例子：
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'CRAAAA';
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=30 width=244)
Filter: (stringu1 = 'CRAAAA'::name)
规划器再次检查WHERE子句条件，并为=查找选择性函数（是eqsel）。
对于等价估计而言，直方图并没什么用；相反，
最常用值 most common values(MCV)表可以用于计算选择性。
让我们来看一下MCV，带有一些额外的列会很有效：
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';
null_frac | 0
n_distinct | 676
most_common_vals | {EJAAAA,BBAAAA,CRAAAA,FCAAAA,FEAAAA,GSAAAA,JOAAAA,MCAAAA,NAAAAA,WGAAAA}
most_common_freqs | {0.00333333,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003}
因为MCV中有CRAAAA，那么选择性只是MCF中的一个对应条目：
selectivity = mcf[3]
= 0.003像之前一样，行的估计数用tenk1的基数乘以选择性：
rows = 10000 * 0.003
= 30
现在看看同样的查询，但是字符串常量不在MCV列表中的情况:
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'xxx';
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=15 width=244)
Filter: (stringu1 = 'xxx'::name)这是一个完全不同的问题：在数值不在 MCV中时， 如何估计选择性。解决方法是利用该值不在MCV中的事实， 结合所有已知的MCV出现的频率，用减法得出：
selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 +
0.003 + 0.003 + 0.003 + 0.003))/(676 - 10)
= 0.0014559也就是说，将MCV中对应项的所有频率相加，并且将其从1减去，然后除以其它不重复值的个数。 这相当于假设不在MCV中的列在所有其他不重复值中是均匀分布的。 需要注意的是，没有NULL值，因此不需要担心这些（否则需要从分子中减去NULL的部分）。 按照往常的方式估算行数：
rows = 10000 * 0.0014559
= 15 (rounding off)
之前带有unique1 < 1000的例子对scalarltsel的实际执行过于简化了。
既然已经看过了使用MCV的例子，现在添加一些具体细节。
这个例子这样子是正确的，因为unique1是一个唯一属性列，
那么它没有MCV（显然，没有一个值能比其它值更常用）。
对一个非唯一属性列而言，通常会有直方图和MCV列表，
并且直方图不包括MCV表示的那部分列。在这种情况下，
scalarltsel直接在每个 MCV列表的值上应用这些条件（如"< 1000"），
并且增加那些条件判断为真的MCV的频率，这对于表中位于MCV的部分给出一个准确的选择性估计。
直方图用于使用上述相同的方法估算表中不在MCV中的部分的选择性，
最终将这两个数字组合起来估计总的选择性。例如，考虑
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 < 'IAAAAA';
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=3077 width=244)
Filter: (stringu1 < 'IAAAAA'::name)我们已看到关于stringu1的MCV信息，这里是它的直方图：
SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';
histogram_bounds
--------------------------------------------------------------------------------
{AAAAAA,CQAAAA,FRAAAA,IBAAAA,KRAAAA,NFAAAA,PSAAAA,SGAAAA,VAAAAA,XLAAAA,ZZAAAA}检查MCV列表，我们发现前6项满足条件stringu1 <'IAAAAA'，而最后4项不满足， 所以在MCV中的选择性是
selectivity = sum(relevant mvfs)
= 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003
= 0.01833333累加所有的MCF，也告诉我们由 MCV表示的成员的总比例是0.03033333， 而由直方图表示的比例是0.96966667 （同样没有NULL，否则这里我们必须去掉它们）。我们可以看到IAAAAA值落在直方图第三个桶的结尾部分。 关于不同字符串的频率，使用较普通的假设，规划器对于 直方图中小于IAAAAA的部分估计为0.298387，然后组合MCV部分和非MCV部分的估计值:
selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction
= 0.01833333 + 0.298387 * 0.96966667
= 0.307669
rows = 10000 * 0.307669
= 3077 (四舍五入)在这个例子中，MCV列表的纠正很小，因为实际上列分布很平坦 （统计分析显示，这些特殊值往往比其它的更常见，大部分由于抽样误差造成的）。 在更典型的情况下，有些值很明显的比其它的更常见，这中复杂的过程有利于提高准确度，因为那些最常见的值的选择性可以被精确的找到。
现在考虑一个WHERE子句中带有多个条件的情况：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=23.80..396.91 rows=1 width=244)
Recheck Cond: (unique1 < 1000)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)规划器假定这两个条件是独立的，因此子句各自的选择性可以相乘在一起：
selectivity = selectivity(unique1 < 1000) * selectivity(stringu1 = 'xxx')
= 0.100697 * 0.0014559
= 0.0001466
rows = 10000 * 0.0001466
= 1 (四舍五入)需要注意的是，从位图索引扫描中返回的行数估计值仅反映使用索引的条件； 这一点很重要，因为它会影响之后获取堆的开销估计。
最后来看一个包含连接的查询：
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.64..456.23 rows=50 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.64..142.17 rows=50 width=244)
Recheck Cond: (unique1 < 50)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.63 rows=50 width=0)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..6.27 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)tenk1上的约束，unique1 < 50，在嵌套循环连接之前计算， 这个条件是用类似上面的那个范围例子的方法处理的，这次数值50落在unique1直方图的第一个桶：
selectivity = (0 + (50 - bucket[1].min)/(bucket[1].max - bucket[1].min))/num_buckets
= (0 + (50 - 0)/(993 - 0))/10
= 0.005035
rows = 10000 * 0.005035
= 50 (四舍五入)
这个连接的约束是t2.unique2 = t1.unique2，操作符是我们熟悉的=，
从pg_operator的oprjoin字段获得的选择性函数是eqjoinsel。
eqjoinsel为tenk2和
tenk1查找统计信息：
SELECT tablename, null_frac,n_distinct, most_common_vals FROM pg_stats
WHERE tablename IN ('tenk1', 'tenk2') AND attname='unique2';
tablename | null_frac | n_distinct | most_common_vals
-----------+-----------+------------+------------------
tenk1 | 0 | -1 |
tenk2 | 0 | -1 |在这个例子中，没有unique2的MCV信息， 因为所有数值看上去都是唯一的， 因此可以使用一个只依赖唯一数值数目和NULL数目百分比的算法来计算选择性：
selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1, 1/num_distinct2)
= (1 - 0) * (1 - 0) / max(10000, 10000)
= 0.0001也就是说，把每个表都减去里面NULL的比例，然后除以不同数值的最大数量。 连接可能选出来的行数是以两个输入的笛卡尔积作为基数，乘以选择性计算出来的：
rows = (outer_cardinality * inner_cardinality) * selectivity
= (50 * 10000) * 0.0001
= 50
这里有两列的MCV列表，
eqjoinsel将直接使用MCV列表比较来决定由MCV列表表示的列成员的连接选择性。
其余成员的估计值使用这里介绍的方法即可。
需要注意的是，inner_cardinality是10000，即未修改的tenk2大小。 它可能出现在EXPLAIN的输出，其连接行的估计值为50 * 1，也就是，外部行的数量乘以tenk2上每个内部索引扫描获取的行的估计数。还有一种情况是在考虑任何特定的连接计划之前就估计连接关系大小。如果一切都正常工作，那么两种方式估计的连接大小将是相同的，但由于四舍五入的误差和其它因素，它们有时会有较明显的差异。
在src/backend/optimizer/util/plancat.c中有对一个表（在任何WHERE子句之前）大小估计的细节； 在src/backend/optimizer/path/clausesel.c中有子句选择性的通用逻辑； 在src/backend/utils/adt/selfuncs.c中有特定操作符的选择性函数。