60.1. 行预期的例子 · PostgreSQL 中文文档 9.3

# 60.1\. 行预期的例子下面的例子使用的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`里面行的基数在[Section 14.2](#calibre_link-1188)里面介绍，为了完整，在这里重复一下。行数或页数是从`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`中的`<`执行器查找可选函数。将保持在`oprrest`列中，并且在这个例子中的条目是`scalarltsel`。 `scalarltsel`函数从`unique1`为 `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`）查找可选函数。对于等价估计而言，直方图并不是有用的；相反， _最常见的值_(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出现的频率，用减法得出： ``` 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，也告诉我们由 MCVs表示的常见的总比例是0.03033333，而且因此由直方图表示的比例是0.96966667。（再次,没有NULL，否则这里我们排斥它们）我们可以看到`IAAAAA`值落在第三段直方图的结尾部分。关于不同字符串的频率使用些较普通的假设，规划器达到估计0.298387为直方图中小于`IAAAAA`的部分。我们然后组合估计值为MCV和非MCV常见: ``` selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction = 0.01833333 + 0.298387 * 0.96966667 = 0.307669 rows = 10000 * 0.307669 = 3077 (rounding off) ``` 尤其是在这个例子中，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 (rounding off) ``` 需要注意的是，从位图索引扫描中返回的估计行数值影响索引使用的条件；这一点很重要，因为它会影响之后的堆栈抓取估计开销。最后检查一个包含连接的查找： ``` 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 (rounding off) ``` 此链接的限制是`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`中有特定操作符的可选函数。