下面显示的示例使用PostgreSQL回归测试数据库中的表。还要注意,由于 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
中查找运算符 <
的选择性函数。这保存在 oprrest
列中,本例中的条目是 scalarltsel
。scalarltsel
函数从 pg_statistic
中检索 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
。对于相等估计,直方图没有用;相反,使用最常见的值的列表(MCVs)来确定选择性。让我们看一下 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}
由于 CRAAAA
出现在 MCV 列表中,因此选择性仅仅是最常见的频率列表(MCFs)中的相应条目
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(mcv_freqs))/(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
sMCV的频率知识。也就是说,将所有
rows = 10000 * 0.0014559 = 15 (rounding off)
的频率相加并从 1 中减去,然后除以其他不同值的数量。这相当于假设列中不是任何 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)
前面带有 unique1 < 1000
的示例是对 scalarltsel
真正作用的简化;现在我们已经看到了使用 MCV 的示例,我们可以补充一些更多细节。该示例在迄今为止是正确的,因为由于 unique1
是唯一列,它没有 MCV(显然,没有值比任何其他值更常见)。对于非唯一列,通常会同时存在直方图和 MCV 列表,并且直方图不包括由 MCV 表示的列总体部分。我们这样做是因为它允许更精确的估计。在这种情况下,scalarltsel
将条件(例如 “< 1000”)直接应用于 MCV 列表的每个值,并加总满足条件的 MCV 的频率。这给出了 MCV 表部分的精确选择性估计。然后,以与上述相同的方式使用直方图来估计表中非 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}
我们已经看到了 stringu1
的 MCV 信息,这里是它的直方图
selectivity = sum(relevant mvfs) = 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 = 0.01833333
检查 MCV 列表,我们发现条件 stringu1 < 'IAAAAA'
由前六个条目满足,而不是最后四个条目满足,因此 MCV 部分人口的选择性为
selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction = 0.01833333 + 0.298387 * 0.96966667 = 0.307669 rows = 10000 * 0.307669 = 3077 (rounding off)
将所有 MCF 相加还告诉我们,MCV 代表的人口总比例为 0.03033333,因此直方图代表的比例为 0.96966667(同样,没有空值,否则我们必须在此处排除它们)。我们可以看到值 IAAAAA
几乎落在第三个直方图桶的末尾。使用一些关于不同字符频率的相当俗气的假设,规划器得出对直方图人口中小于 IAAAAA
的部分的估计值 0.298387。然后,我们将 MCV 和非 MCV 人口的估计值结合起来
在这个特定的示例中,MCV 列表的校正相当小,因为列分布实际上相当平坦(显示这些特定值比其他值更常见的统计信息主要是由于采样误差)。在某些值明显比其他值更常见的更典型情况下,这种复杂的过程可以有效地提高准确性,因为最常见值的选择性是精确找到的。
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)
现在让我们考虑 WHERE
子句中包含多个条件的情况
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 |
在这种情况下,没有MCVunique2
的信息,并且所有值似乎都是唯一的(n_distinct = -1),因此我们使用一种算法,该算法依赖于两个关系(未显示,但为 "tenk")的行数估计以及列的空值比例(两者都为零)。
selectivity = (1 - null_frac1) * (1 - null_frac2) / max(num_rows1, num_rows2) = (1 - 0) * (1 - 0) / max(10000, 10000) = 0.0001
这就是说,从每个关系的一减去空值比例,然后除以较大关系的行数(在非唯一情况下,该值会被缩放)。连接可能发出的行数计算为两个输入的笛卡尔积的基数乘以选择性。
rows = (outer_cardinality * inner_cardinality) * selectivity = (50 * 10000) * 0.0001 = 50
如果两个列都有 MCV 列表,eqjoinsel
将使用 MCV 列表的直接比较来确定 MCV 表示的列总体部分内的连接选择性。其余人口的估计遵循此处显示的相同方法。
请注意,我们将 inner_cardinality
显示为 10000,即 tenk2
的未修改大小。从 EXPLAIN
输出的检查中可能可以看出,连接行的估计来自 50 * 1,即外部行数乘以在 tenk2
上每次内部索引扫描估计获得的行数。但事实并非如此:连接关系的大小是在考虑任何特定的连接计划之前估计的。如果一切运行良好,那么估计连接大小的两种方法将产生大致相同的答案,但由于舍入误差和其他因素,它们有时会明显不同。
对于那些对更多细节感兴趣的人,在任何 WHERE
子句之前,对表大小的估计是在 src/backend/optimizer/util/plancat.c
中完成的。子句选择性的通用逻辑位于 src/backend/optimizer/path/clausesel.c
中。特定于运算符的选择性函数主要在 src/backend/utils/adt/selfuncs.c
中找到。
如果您在文档中发现任何不正确、与您使用特定功能的经验不符或需要进一步澄清的内容,请使用此表格报告文档问题。