0 總結

對于SQL：select * from student, (select * from score where sno > 2) s where student.sno = s.sno;

pullup在pull_up_subqueries函數內遞歸完成，分幾步：

1. 將內層rte score追加到上層rtbable中：rte1是student、rte2帶subquery是子查詢、rte3是score。
2. 調整所有var的varno（從1指向3）、varlevelsup（本例不涉及）；還有其他調整本例不涉及。
3. 將上層代表子查詢的rte2的subquery清空，但rte2不刪除。
4. 將上層jointree中，指向子查詢的rte替換為 子查詢中的FromExpr（sno > 2）。

1 待分析場景

drop table student;
create table student(sno int, sname varchar(10), ssex int);
insert into student values(1, 'stu1', 0);
insert into student values(2, 'stu2', 1);
insert into student values(3, 'stu3', 1);
insert into student values(4, 'stu4', 0);drop table course;
create table course(cno int, cname varchar(10), tno int);
insert into course values(10, 'meth', 1);
insert into course values(11, 'english', 2);drop table teacher;
create table teacher(tno int, tname varchar(10), tsex int);
insert into teacher values(1, 'te1', 1);
insert into teacher values(2, 'te2', 0);drop table score;
create table score (sno int, cno int, degree int);
insert into score values (1, 10, 100);
insert into score values (1, 11, 89);
insert into score values (2, 10, 99);
insert into score values (2, 11, 90);
insert into score values (3, 10, 87);
insert into score values (3, 11, 20);
insert into score values (4, 10, 60);
insert into score values (4, 11, 70);

帶子查詢的語句：select * from student, (select * from score where sno > 2) s where student.sno = s.sno;

set enable_hashjoin to off;
set enable_mergejoin to off;explain select * from student, (select * from score where sno > 2) s where student.sno = s.sno;QUERY PLAN
---------------------------------------------------------------------Nested Loop  (cost=0.00..11278.20 rows=3740 width=58)Join Filter: (student.sno = score.sno)->  Seq Scan on student  (cost=0.00..21.00 rows=1100 width=46)->  Materialize  (cost=0.00..38.90 rows=680 width=12)->  Seq Scan on score  (cost=0.00..35.50 rows=680 width=12)Filter: (sno > 2)||||||||||
||等價寫法||
vvvvvvvvvvvexplain select * from student, score where score.sno > 2 and student.sno = score.sno;QUERY PLAN
---------------------------------------------------------------------Nested Loop  (cost=0.00..11278.20 rows=3740 width=58)Join Filter: (student.sno = score.sno)->  Seq Scan on student  (cost=0.00..21.00 rows=1100 width=46)->  Materialize  (cost=0.00..38.90 rows=680 width=12)->  Seq Scan on score  (cost=0.00..35.50 rows=680 width=12)Filter: (sno > 2)

---

pull_up_subqueries做的事情就是幫我們把子查詢上拉了，下面分析上拉是如何做的。

select * from student, (select * from score where sno > 2) s where student.sno = s.sno;

---

Plannerinfo完整結構

注意pull_up_subqueries只對jointree做處理。

void
pull_up_subqueries(PlannerInfo *root)
{/* Top level of jointree must always be a FromExpr */Assert(IsA(root->parse->jointree, FromExpr));/* Recursion starts with no containing join nor appendrel */root->parse->jointree = (FromExpr *)pull_up_subqueries_recurse(root, (Node *) root->parse->jointree,NULL, NULL);/* We should still have a FromExpr */Assert(IsA(root->parse->jointree, FromExpr));
}

2 pull_up_subqueries流程分析

2.1 處理FromExpr下面掛的第一個RANGETBLREF（student表）

FromExpr的第一張表是student表，指向一個rtekind = RTE_RELATION普通表類型，無需做任何處理。

2.2 處理FromExpr下面掛的第二個RANGETBLREF（子查詢）

FromExpr的第二個rte是子查詢(select * from score where sno > 2) s，可以看到引用的rte結構的subquery指向了內層query：

開始進入pull_up_simple_subquery內部處理，進入路徑：

在分析pull_up_simple_subquery前有兩個準入條件：

1. rte->rtekind == RTE_SUBQUERY
2. is_simple_subquery：不全部列舉了，其中重要的是子查詢不能帶有一些特殊的語法：

is_simple_subquery...if (subquery->hasAggs ||subquery->hasWindowFuncs ||subquery->hasTargetSRFs ||subquery->groupClause ||subquery->groupingSets ||subquery->havingQual ||subquery->sortClause ||subquery->distinctClause ||subquery->limitOffset ||subquery->limitCount ||subquery->hasForUpdate ||subquery->cteList)return false;...

2.3 進入pull_up_simple_subquery開始處理子查上拉

第一步：拿到rte指向的子查詢的Query樹，構造PlannerInfo開始處理。

static Node *
pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,JoinExpr *lowest_outer_join,AppendRelInfo *containing_appendrel)
{Query	   *subquery;Query	   *parse = root->parse;PlannerInfo *subroot;subquery = copyObject(rte->subquery);subroot = makeNode(PlannerInfo);subroot->parse = subquery;......

第二步：遞歸的處理sublink、subquery等。

	replace_empty_jointree(subquery);if (subquery->hasSubLinks)pull_up_sublinks(subroot);preprocess_function_rtes(subroot);pull_up_subqueries(subroot);

第三步：開始pull up

到這里subroot就是rte2的subquery子查詢的結構還沒有任何調整：

	/** Adjust level-0 varnos in subquery so that we can append its rangetable* to upper query's.  We have to fix the subquery's append_rel_list as* well.*/rtoffset = list_length(parse->rtable);  // 2OffsetVarNodes((Node *) subquery, rtoffset, 0);OffsetVarNodes((Node *) subroot->append_rel_list, rtoffset, 0);

parse是上層查詢的，上層有兩個rtable。因為要把子查詢拉平，所以把子查詢的varno的指向調整一下，因為是要append到父查詢，所以直接加上父查詢rte的個數就好了（這里是2）。加完了應該指向父查詢rte的3的位置（現在父查詢只有兩個rte，3位置是空的）。

OffsetVarNodes((Node *) subquery, 2, 0);

1. 調整var→varno：1→3。
2. 調整rangetblref→rindex：1→3。
   

	/** Upper-level vars in subquery are now one level closer to their parent* than before.*/IncrementVarSublevelsUp((Node *) subquery, -1, 1);IncrementVarSublevelsUp((Node *) subroot->append_rel_list, -1, 1);

• 這一步調整的目的：因為varlevelsup=1表示引用上一層的列（相當于距離）這里拉平后，varlevelsup就需要-1了，因為距離少了1。
• 在當前SQL中select * from student, (select * from score where sno > 2) s where student.sno = s.sno;，開始調整var→varlevelsup字段，注意這個字段表示當前查詢中使用了上層的變量，但上面子查詢中(select * from score where sno > 2)沒有引用上層的任何列，所以子查詢中的var→varlevelsup都是0。這一步調整不會有影響。

	/** Now append the adjusted rtable entries and their perminfos to upper* query. (We hold off until after fixing the upper rtable entries; no* point in running that code on the subquery ones too.)*/CombineRangeTables(&parse->rtable, &parse->rteperminfos,subquery->rtable, subquery->rteperminfos);

開始把子查詢的RTE拷貝到上層，現在子查詢里面的varno=3指向就對了。

	/** We no longer need the RTE's copy of the subquery's query tree.  Getting* rid of it saves nothing in particular so far as this level of query is* concerned; but if this query level is in turn pulled up into a parent,* we'd waste cycles copying the now-unused query tree.*/rte->subquery = NULL;

刪除子查詢RTE帶的Query，注意現在還缺一個條件。

pull_up_simple_subqueryreturn (Node *) subquery->jointree;

返回一個jointree帶著條件。

返回去后，在這里把fromlist指向的第二個rte（子查詢）換成 上面計算好的jointree。

然后就拉平了。

pull_up_subqueries_recurse...else if (IsA(jtnode, FromExpr)){FromExpr   *f = (FromExpr *) jtnode;ListCell   *l;Assert(containing_appendrel == NULL);/* Recursively transform all the child nodes */foreach(l, f->fromlist){lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),lowest_outer_join,NULL);}}

最終效果對比

pullup前 vs pullup后