explain
SELECT STUDENT.sname, random(), SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno = SCORE.sno
LEFT JOIN COURSE ON SCORE.cno = COURSE.cno
ORDER BY STUDENT.sno;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=182.67..213.27 rows=2040 width=54)
-> Sort (cost=182.67..187.77 rows=2040 width=46)
Sort Key: student.sno
-> Hash Right Join (cost=34.75..70.53 rows=2040 width=46)
Hash Cond: (score.sno = student.sno)
-> Seq Scan on score (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=21.00..21.00 rows=1100 width=42)
-> Seq Scan on student (cost=0.00..21.00 rows=1100 width=42)
上面用例经过set_plan_references调整前后的完整例子:
当前查询优化的状态,包含了当前查询的所有信息:
全局结构,包含了跨多个查询级别的信息。例如一个包含子查询或CTE的查询中,每个子查询都会有自己的 PlannerInfo结构,会共享同一个PlannerGlobal。包含了:
#define INNER_VAR (-1) /* reference to inner subplan */
#define OUTER_VAR (-2) /* reference to outer subplan */
#define INDEX_VAR (-3) /* reference to index column */
#define ROWID_VAR (-4) /* row identity column during planning */
set_plan_references → add_rtes_to_flat_rtable
首先把引用的rtable全部拉平到一个级别,重新排列RTE。
具体在PlannerGlobal中构造全局范围表finalrtable,所有子PlannerInfo共享的一套RTE。
p *root->glob->finalrtable
$7 = {type = T_List, length = 5, max_length = 5, elements = 0x3085520, initial_elements = 0x3085520}
add_rtes_to_flat_rtable后生成五个RTE:
{rtekind = RTE_RELATION, relid = 16656, inh = false, relkind = 114 'r'}
{rtekind = RTE_RELATION, relid = 16671, inh = false, relkind = 114 'r'}
{rtekind = RTE_JOIN, relid = 0, inh = false, relkind = 0}
{rtekind = RTE_RELATION, relid = 16661, inh = false, relkind = 114 'r'}
{rtekind = RTE_JOIN, relid = 0, inh = false, relkind = 0}
PlannerInfo→PlannerGlobal:
set_plan_references → set_plan_refs
case T_Result:
… 处理result子树plan->lefttree = set_plan_refs(root, plan->lefttree, rtoffset);
递归处理左树plan->righttree = set_plan_refs(root, plan->righttree, rtoffset);
递归处理右树处理前 vs 处理后
set_plan_refs处理T_Result节点:
set_plan_refs
...
...
case T_Result:
Result *splan = (Result *) plan;
if (splan->plan.lefttree != NULL)
set_upper_references(root, plan, rtoffset);
...
...
// subplan 是 SORT节点
// subplan->targetlist 中返回三列:STUDENT.sname, SCORE.degree, STUDENT.sno
// 注意缺了一列random函数
subplan_itlist = build_tlist_index(subplan->targetlist);
varno = 1, varattno = 2, vartype = 1043
varno = 2, varattno = 3, vartype = 23
varno = 1, varattno = 1, vartype = 23
subplan_itlist->tlist = subplan->targetlist
subplan_itlist->vars[0] = {varno = 1, varattno = 2, resno = 1, varnullingrels = 0x0}
subplan_itlist->vars[1] = {varno = 2, varattno = 3, resno = 2, varnullingrels = ...}
subplan_itlist->vars[2] = {varno = 1, varattno = 1, resno = 3, varnullingrels = 0x0}
foreach(l, plan->targetlist)
...
newexpr = fix_upper_expr(...)
...
// 计算完成
plan->targetlist = output_targetlist;
expr = 0x308f0c8, resno = 1, resname = 0x2f4d670 "sname"
varno = OUTER_VAR = -2, varattno = 1, vartype = 1043
expr = 0x308f1b8, resno = 2, resname = 0x2f4d7e8 "random"
funcid = 1598, funcresulttype = 701, funcretset = false
expr = 0x308f258, resno = 3, resname = 0x2f4d928 "degree"
varno = OUTER_VAR = -2, varattno = 2, vartype = 23
expr = 0x308f2f8, resno = 4, resname = 0x0, ressortgroupref = 1
varno = OUTER_VAR = -2, varattno = 3, vartype = 23
case T_Sort:
… 处理sort子树set_dummy_tlist_referencesplan->lefttree = set_plan_refs(root, plan->lefttree, rtoffset);
递归处理左树plan->righttree = set_plan_refs(root, plan->righttree, rtoffset);
递归处理右树排序只需要引用下面一层的结果即可。
// These plan types don't actually bother to evaluate their
// targetlists, because they just return their unmodified input
// tuples. Even though the targetlist won't be used by the
// executor, we fix it up for possible use by EXPLAIN (not to
// mention ease of debugging --- wrong varnos are very confusing).
set_dummy_tlist_references
case T_HashJoin:
… 处理join子树set_join_referencesplan->lefttree = set_plan_refs(root, plan->lefttree, rtoffset);
递归处理左树plan->righttree = set_plan_refs(root, plan->righttree, rtoffset);
递归处理右树explain
SELECT STUDENT.sname, random(), SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno = SCORE.sno
LEFT JOIN COURSE ON SCORE.cno = COURSE.cno
ORDER BY STUDENT.sno;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=182.67..213.27 rows=2040 width=54)
-> Sort (cost=182.67..187.77 rows=2040 width=46)
Sort Key: student.sno
-> Hash Right Join (cost=34.75..70.53 rows=2040 width=46)
Hash Cond: (score.sno = student.sno)
-> Seq Scan on score (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=21.00..21.00 rows=1100 width=42)
-> Seq Scan on student (cost=0.00..21.00 rows=1100 width=42)