下面显示的示例使用 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
中查找运算符 <
的选择性函数。这保存在列 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
。对于相等估计,直方图没有用;相反,使用 最常见值 (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}
由于 CRAAAA
出现在 MCV 列表中,因此选择性仅仅是对应于最常见频率 (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(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
也就是说,将所有 MCV 的频率加起来,然后从 1 中减去它们,然后除以 其他 不同值的数目。这相当于假设列中不是任何 MCV 的部分在所有其他不同值中均匀分布。请注意,没有空值,因此我们不必担心这些值(否则,我们也会从分子中减去空值部分)。然后按通常方式计算估计的行数
rows = 10000 * 0.0014559 = 15 (rounding off)
使用 unique1 < 1000
的上一个示例是对 scalarltsel
真正作用的过度简化;现在我们已经看到了 MCV 使用的示例,我们可以填写更多详细信息。该示例在它所涉及的范围内是正确的,因为由于 unique1
是一个唯一列,因此它没有 MCV(显然,没有哪个值比任何其他值更常见)。对于非唯一列,通常会有一个直方图和一个 MCV 列表,并且 直方图不包括 MCV 表示的列总体部分。我们这样做是因为它允许更精确的估计。在这种情况下,scalarltsel
直接将条件(例如,“< 1000”)应用于 MCV 列表的每个值,并累加条件为真的 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 列表,我们发现条件 stringu1 < 'IAAAAA'
由前六个条目满足,后四个条目不满足,因此 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(同样,没有空值,否则我们必须在此处排除它们)。我们可以看到值 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 |
在这种情况下,没有 MCV 信息用于 unique2
,因为所有值似乎都是唯一的,因此我们使用一种算法,该算法仅依赖于两个关系的唯一值数量以及它们的空值分数
selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1, 1/num_distinct2) = (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
中找到。