PostgreSQL 原始碼解讀（27）- 查詢語句#12（查詢優化-上拉子連結#2）
本文簡單介紹了PG查詢邏輯優化中的子查詢連結(subLink),以ANY子連結為例介紹了子查詢連結上拉主函式處理邏輯以及使用gdb跟蹤分析。
一、子連結上拉簡介
PG嘗試對ANY_SUBLINK和EXISTS_SUBLINK兩種型別的子連結嘗試提升,ANY_SUBLINK表示IN/ANY子句,EXISTS_SUBLINK表示EXISTS子句.子連結上拉(pull up)的目的是為了提升效能,把查詢一條記錄對比一條記錄的邏輯實現變換為半連線或反半連線實現.
上拉樣例:
-- 原始SQL
select *
from t_dwxx a
where dwbh > any (select b.dwbh from t_grxx b);
-- 上拉後的SQL(示意,實際不能執行)
select a.*
from t_dwxx a semi join select b.dwbh from t_grxx b on a.dwbh > pullup.dwbh;
執行了子連結上拉後的執行計劃:
testdb=# explain select *
testdb-# from t_dwxx a
testdb-# where dwbh > any (select b.dwbh from t_grxx b);
QUERY PLAN
------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..815.76 rows=53 width=474)
Join Filter: ((a.dwbh)::text > (b.dwbh)::text)
-> Seq Scan on t_dwxx a (cost=0.00..11.60 rows=160 width=474)
-> Materialize (cost=0.00..17.35 rows=490 width=38)
-> Seq Scan on t_grxx b (cost=0.00..14.90 rows=490 width=38)
(5 rows)
沒有上拉的執行計劃:
testdb=# explain select *
testdb-# from t_dwxx a
testdb-# where dwbh > any (select b.dwbh from t_grxx b where a.dwbh = b.dwbh);
QUERY PLAN
------------------------------------------------------------------
Seq Scan on t_dwxx a (cost=0.00..1302.40 rows=80 width=474)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on t_grxx b (cost=0.00..16.12 rows=2 width=38)
Filter: ((a.dwbh)::text = (dwbh)::text)
(5 rows)
二、原始碼解讀
pull_up_sublinks
/*
* pull_up_sublinks
* Attempt to pull up ANY and EXISTS SubLinks to be treated as
* semijoins or anti-semijoins.
* 嘗試上拉(pull up) ANY/IN 和 EXISTS 子連結,變換為半連線或反半連線
*
* A clause "foo op ANY (sub-SELECT)" can be processed by pulling the
* sub-SELECT up to become a rangetable entry and treating the implied
* comparisons as quals of a semijoin. However, this optimization *only*
* works at the top level of WHERE or a JOIN/ON clause, because we cannot
* distinguish whether the ANY ought to return FALSE or NULL in cases
* involving NULL inputs. Also, in an outer join's ON clause we can only
* do this if the sublink is degenerate (ie, references only the nullable
* side of the join). In that case it is legal to push the semijoin
* down into the nullable side of the join. If the sublink references any
* nonnullable-side variables then it would have to be evaluated as part
* of the outer join, which makes things way too complicated.
*
* Under similar conditions, EXISTS and NOT EXISTS clauses can be handled
* by pulling up the sub-SELECT and creating a semijoin or anti-semijoin.
*
* express op ANY (sub-SELECT) 語句可以通過上拉子查詢為RTE並且把對比操作變換
* 為半連線操作,這種優化只能在最上層的WHERE語句中實現
* EXISTS/NOT EXISTS語句類似
*
*
* This routine searches for such clauses and does the necessary parsetree
* transformations if any are found.
*
* This routine has to run before preprocess_expression(), so the quals
* clauses are not yet reduced to implicit-AND format, and are not guaranteed
* to be AND/OR-flat either. That means we need to recursively search through
* explicit AND clauses. We stop as soon as we hit a non-AND item.
*/
void
pull_up_sublinks(PlannerInfo *root)
{
Node *jtnode;
Relids relids;
/* Begin recursion through the jointree */
jtnode = pull_up_sublinks_jointree_recurse(root,
(Node *) root->parse->jointree,
&relids);//執行上拉操作
/*
* root->parse->jointree must always be a FromExpr, so insert a dummy one
* if we got a bare RangeTblRef or JoinExpr out of the recursion.
*/
if (IsA(jtnode, FromExpr))//jointree要求FromExpr型別
root->parse->jointree = (FromExpr *) jtnode;
else
root->parse->jointree = makeFromExpr(list_make1(jtnode), NULL);
}
pull_up_sublinks_jointree_recurse
處理流程如下圖所示:
pull_up_sublinks_jointree_recurse處理流程
查詢樹結構如下:
查詢樹
/*
* Recurse through jointree nodes for pull_up_sublinks()
*
* In addition to returning the possibly-modified jointree node, we return
* a relids set of the contained rels into *relids.
*/
static Node *
pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode,
Relids *relids)
{
if (jtnode == NULL)
{
*relids = NULL;
}
else if (IsA(jtnode, RangeTblRef))//如為RangeTblRef型別
{
int varno = ((RangeTblRef *) jtnode)->rtindex;
*relids = bms_make_singleton(varno);
/* jtnode is returned unmodified */
}
else if (IsA(jtnode, FromExpr))//如為FromExpr型別
{
FromExpr *f = (FromExpr *) jtnode;
List *newfromlist = NIL;
Relids frelids = NULL;
FromExpr *newf;
Node *jtlink;
ListCell *l;
/* First, recurse to process children and collect their relids */
foreach(l, f->fromlist)//
{
Node *newchild;
Relids childrelids;
//對fromlist中的元素執行上拉操作
//如能夠上拉,則把子查詢從WHERE子句中提升到FROM子句,newchild作為連線的一部分
newchild = pull_up_sublinks_jointree_recurse(root,
lfirst(l),
&childrelids);
newfromlist = lappend(newfromlist, newchild);
frelids = bms_join(frelids, childrelids);
}
/* Build the replacement FromExpr; no quals yet */
newf = makeFromExpr(newfromlist, NULL);//建立新的FromExpr
/* Set up a link representing the rebuilt jointree */
jtlink = (Node *) newf;
/* Now process qual --- all children are available for use */
//處理子連結中的表示式
//newf(指標,相當於jtlink)
newf->quals = pull_up_sublinks_qual_recurse(root, f->quals,
&jtlink, frelids,
NULL, NULL);//
/*
* Note that the result will be either newf, or a stack of JoinExprs
* with newf at the base. We rely on subsequent optimization steps to
* flatten this and rearrange the joins as needed.
*
* Although we could include the pulled-up subqueries in the returned
* relids, there's no need since upper quals couldn't refer to their
* outputs anyway.
*/
*relids = frelids;//設定相關的relids
jtnode = jtlink;//返回值
}
else if (IsA(jtnode, JoinExpr))
{
JoinExpr *j;
Relids leftrelids;
Relids rightrelids;
Node *jtlink;
/*
* Make a modifiable copy of join node, but don't bother copying its
* subnodes (yet).
*/
j = (JoinExpr *) palloc(sizeof(JoinExpr));
memcpy(j, jtnode, sizeof(JoinExpr));
jtlink = (Node *) j;
/* Recurse to process children and collect their relids */
//遞迴處理左邊&右邊子樹
j->larg = pull_up_sublinks_jointree_recurse(root, j->larg,
&leftrelids);
j->rarg = pull_up_sublinks_jointree_recurse(root, j->rarg,
&rightrelids);
/*
* Now process qual, showing appropriate child relids as available,
* and attach any pulled-up jointree items at the right place. In the
* inner-join case we put new JoinExprs above the existing one (much
* as for a FromExpr-style join). In outer-join cases the new
* JoinExprs must go into the nullable side of the outer join. The
* point of the available_rels machinations is to ensure that we only
* pull up quals for which that's okay.
*
* We don't expect to see any pre-existing JOIN_SEMI or JOIN_ANTI
* nodes here.
*/
switch (j->jointype)
{
case JOIN_INNER:
j->quals = pull_up_sublinks_qual_recurse(root, j->quals,
&jtlink,
bms_union(leftrelids,
rightrelids),
NULL, NULL);
break;
case JOIN_LEFT:
j->quals = pull_up_sublinks_qual_recurse(root, j->quals,
&j->rarg,
rightrelids,
NULL, NULL);
break;
case JOIN_FULL:
/* can't do anything with full-join quals */
break;
case JOIN_RIGHT:
j->quals = pull_up_sublinks_qual_recurse(root, j->quals,
&j->larg,
leftrelids,
NULL, NULL);
break;
default:
elog(ERROR, "unrecognized join type: %d",
(int) j->jointype);
break;
}
/*
* Although we could include the pulled-up subqueries in the returned
* relids, there's no need since upper quals couldn't refer to their
* outputs anyway. But we *do* need to include the join's own rtindex
* because we haven't yet collapsed join alias variables, so upper
* levels would mistakenly think they couldn't use references to this
* join.
*/
*relids = bms_join(leftrelids, rightrelids);
if (j->rtindex)
*relids = bms_add_member(*relids, j->rtindex);
jtnode = jtlink;
}
else
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(jtnode));
return jtnode;
}
pull_up_sublinks_qual_recurse
/*
* Recurse through top-level qual nodes for pull_up_sublinks()
*
* jtlink1 points to the link in the jointree where any new JoinExprs should
* be inserted if they reference available_rels1 (i.e., available_rels1
* denotes the relations present underneath jtlink1). Optionally, jtlink2 can
* point to a second link where new JoinExprs should be inserted if they
* reference available_rels2 (pass NULL for both those arguments if not used).
* Note that SubLinks referencing both sets of variables cannot be optimized.
* If we find multiple pull-up-able SubLinks, they'll get stacked onto jtlink1
* and/or jtlink2 in the order we encounter them. We rely on subsequent
* optimization to rearrange the stack if appropriate.
*
* Returns the replacement qual node, or NULL if the qual should be removed.
*/
static Node *
pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
Node **jtlink1, Relids available_rels1,
Node **jtlink2, Relids available_rels2)
{
if (node == NULL)
return NULL;
if (IsA(node, SubLink))//子連結
{
SubLink *sublink = (SubLink *) node;
JoinExpr *j;
Relids child_rels;
/* Is it a convertible ANY or EXISTS clause? */
if (sublink->subLinkType == ANY_SUBLINK)//ANY子連結
{
if ((j = convert_ANY_sublink_to_join(root, sublink,
available_rels1)) != NULL)
{
/* Yes; insert the new join node into the join tree */
j->larg = *jtlink1;
*jtlink1 = (Node *) j;
/* Recursively process pulled-up jointree nodes */
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
/*
* Now recursively process the pulled-up quals. Any inserted
* joins can get stacked onto either j->larg or j->rarg,
* depending on which rels they reference.
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels1,
&j->rarg,
child_rels);
/* Return NULL representing constant TRUE */
return NULL;
}
if (available_rels2 != NULL &&
(j = convert_ANY_sublink_to_join(root, sublink,
available_rels2)) != NULL)
{
/* Yes; insert the new join node into the join tree */
j->larg = *jtlink2;
*jtlink2 = (Node *) j;
/* Recursively process pulled-up jointree nodes */
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
/*
* Now recursively process the pulled-up quals. Any inserted
* joins can get stacked onto either j->larg or j->rarg,
* depending on which rels they reference.
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels2,
&j->rarg,
child_rels);
/* Return NULL representing constant TRUE */
return NULL;
}
}
else if (sublink->subLinkType == EXISTS_SUBLINK)//EXISTS子連結
{
if ((j = convert_EXISTS_sublink_to_join(root, sublink, false,
available_rels1)) != NULL)
{
/* Yes; insert the new join node into the join tree */
j->larg = *jtlink1;
*jtlink1 = (Node *) j;
/* Recursively process pulled-up jointree nodes */
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
/*
* Now recursively process the pulled-up quals. Any inserted
* joins can get stacked onto either j->larg or j->rarg,
* depending on which rels they reference.
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels1,
&j->rarg,
child_rels);
/* Return NULL representing constant TRUE */
return NULL;
}
if (available_rels2 != NULL &&
(j = convert_EXISTS_sublink_to_join(root, sublink, false,
available_rels2)) != NULL)
{
/* Yes; insert the new join node into the join tree */
j->larg = *jtlink2;
*jtlink2 = (Node *) j;
/* Recursively process pulled-up jointree nodes */
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
/*
* Now recursively process the pulled-up quals. Any inserted
* joins can get stacked onto either j->larg or j->rarg,
* depending on which rels they reference.
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels2,
&j->rarg,
child_rels);
/* Return NULL representing constant TRUE */
return NULL;
}
}
/* Else return it unmodified */
return node;
}
if (not_clause(node))//NOT語句
{
/* If the immediate argument of NOT is EXISTS, try to convert */
SubLink *sublink = (SubLink *) get_notclausearg((Expr *) node);
JoinExpr *j;
Relids child_rels;
if (sublink && IsA(sublink, SubLink))
{
if (sublink->subLinkType == EXISTS_SUBLINK)
{
if ((j = convert_EXISTS_sublink_to_join(root, sublink, true,
available_rels1)) != NULL)
{
/* Yes; insert the new join node into the join tree */
j->larg = *jtlink1;
*jtlink1 = (Node *) j;
/* Recursively process pulled-up jointree nodes */
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
/*
* Now recursively process the pulled-up quals. Because
* we are underneath a NOT, we can't pull up sublinks that
* reference the left-hand stuff, but it's still okay to
* pull up sublinks referencing j->rarg.
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->rarg,
child_rels,
NULL, NULL);
/* Return NULL representing constant TRUE */
return NULL;
}
if (available_rels2 != NULL &&
(j = convert_EXISTS_sublink_to_join(root, sublink, true,
available_rels2)) != NULL)
{
/* Yes; insert the new join node into the join tree */
j->larg = *jtlink2;
*jtlink2 = (Node *) j;
/* Recursively process pulled-up jointree nodes */
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
/*
* Now recursively process the pulled-up quals. Because
* we are underneath a NOT, we can't pull up sublinks that
* reference the left-hand stuff, but it's still okay to
* pull up sublinks referencing j->rarg.
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->rarg,
child_rels,
NULL, NULL);
/* Return NULL representing constant TRUE */
return NULL;
}
}
}
/* Else return it unmodified */
return node;
}
if (and_clause(node))//AND語句
{
/* Recurse into AND clause */
List *newclauses = NIL;
ListCell *l;
foreach(l, ((BoolExpr *) node)->args)
{
Node *oldclause = (Node *) lfirst(l);
Node *newclause;
newclause = pull_up_sublinks_qual_recurse(root,
oldclause,
jtlink1,
available_rels1,
jtlink2,
available_rels2);
if (newclause)
newclauses = lappend(newclauses, newclause);
}
/* We might have got back fewer clauses than we started with */
if (newclauses == NIL)
return NULL;
else if (list_length(newclauses) == 1)
return (Node *) linitial(newclauses);
else
return (Node *) make_andclause(newclauses);
}
/* Stop if not an AND */
return node;
}
convert_ANY_sublink_to_join
/*
* convert_ANY_sublink_to_join: try to convert an ANY SubLink to a join
*
* The caller has found an ANY SubLink at the top level of one of the query's
* qual clauses, but has not checked the properties of the SubLink further.
* Decide whether it is appropriate to process this SubLink in join style.
* If so, form a JoinExpr and return it. Return NULL if the SubLink cannot
* be converted to a join.
*
* The only non-obvious input parameter is available_rels: this is the set
* of query rels that can safely be referenced in the sublink expression.
* (We must restrict this to avoid changing the semantics when a sublink
* is present in an outer join's ON qual.) The conversion must fail if
* the converted qual would reference any but these parent-query relids.
*
* On success, the returned JoinExpr has larg = NULL and rarg = the jointree
* item representing the pulled-up subquery. The caller must set larg to
* represent the relation(s) on the lefthand side of the new join, and insert
* the JoinExpr into the upper query's jointree at an appropriate place
* (typically, where the lefthand relation(s) had been). Note that the
* passed-in SubLink must also be removed from its original position in the
* query quals, since the quals of the returned JoinExpr replace it.
* (Notionally, we replace the SubLink with a constant TRUE, then elide the
* redundant constant from the qual.)
*
* On success, the caller is also responsible for recursively applying
* pull_up_sublinks processing to the rarg and quals of the returned JoinExpr.
* (On failure, there is no need to do anything, since pull_up_sublinks will
* be applied when we recursively plan the sub-select.)
*
* Side effects of a successful conversion include adding the SubLink's
* subselect to the query's rangetable, so that it can be referenced in
* the JoinExpr's rarg.
*/
JoinExpr *
convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
Relids available_rels)
{
JoinExpr *result;
Query *parse = root->parse;
Query *subselect = (Query *) sublink->subselect;
Relids upper_varnos;
int rtindex;
RangeTblEntry *rte;
RangeTblRef *rtr;
List *subquery_vars;
Node *quals;
ParseState *pstate;
Assert(sublink->subLinkType == ANY_SUBLINK);
/*
* The sub-select must not refer to any Vars of the parent query. (Vars of
* higher levels should be okay, though.)
*/
//ANY型別的子連結,子查詢不能依賴父查詢的任何變數,否則返回NULL(不能上拉)
if (contain_vars_of_level((Node *) subselect, 1))
return NULL;
/*
* The test expression must contain some Vars of the parent query, else
* it's not gonna be a join. (Note that it won't have Vars referring to
* the subquery, rather Params.)
*/
//子查詢的testexpr變數中必須含有父查詢的某些Vars,否則不能上拉(join),返回NULL
upper_varnos = pull_varnos(sublink->testexpr);
if (bms_is_empty(upper_varnos))
return NULL;
/*
* However, it can't refer to anything outside available_rels.
*/
//但是不能夠依賴超出可用的範圍之內,否則一樣不能上拉
if (!bms_is_subset(upper_varnos, available_rels))
return NULL;
/*
* The combining operators and left-hand expressions mustn't be volatile.
*/
//組合操作符和左側的表示式不能是易變(volatile)的,比如隨機數等
if (contain_volatile_functions(sublink->testexpr))
return NULL;
//校驗通過,上拉
/* Create a dummy ParseState for addRangeTableEntryForSubquery */
pstate = make_parsestate(NULL);
/*
* Okay, pull up the sub-select into upper range table.
*
* We rely here on the assumption that the outer query has no references
* to the inner (necessarily true, other than the Vars that we build
* below). Therefore this is a lot easier than what pull_up_subqueries has
* to go through.
*/
//子連結上拉後,子查詢變成了上層的RTE,在這裡構造
rte = addRangeTableEntryForSubquery(pstate,
subselect,
makeAlias("ANY_subquery", NIL),
false,
false);
//新增到上層的rtable中
parse->rtable = lappend(parse->rtable, rte);
//rtable中的索引
rtindex = list_length(parse->rtable);
/*
* Form a RangeTblRef for the pulled-up sub-select.
*/
//產生了RTE,自然要生成RTR(RangeTblRef)
rtr = makeNode(RangeTblRef);
rtr->rtindex = rtindex;
/*
* Build a list of Vars representing the subselect outputs.
*/
//建立子查詢的輸出列(投影)
subquery_vars = generate_subquery_vars(root,
subselect->targetList,
rtindex);
/*
* Build the new join's qual expression, replacing Params with these Vars.
*/
//構造上層的條件表示式
quals = convert_testexpr(root, sublink->testexpr, subquery_vars);
/*
* And finally, build the JoinExpr node.
*/
//構造返回結果
result = makeNode(JoinExpr);
result->jointype = JOIN_SEMI;//變換為半連線
result->isNatural = false;
result->larg = NULL; /* caller must fill this in */
result->rarg = (Node *) rtr;
result->usingClause = NIL;
result->quals = quals;
result->alias = NULL;
result->rtindex = 0; /* we don't need an RTE for it */
return result;
}
三、資料結構
Param
/*
* Param
*
* paramkind specifies the kind of parameter. The possible values
* for this field are:
*
* PARAM_EXTERN: The parameter value is supplied from outside the plan.
* Such parameters are numbered from 1 to n.
*
* PARAM_EXEC: The parameter is an internal executor parameter, used
* for passing values into and out of sub-queries or from
* nestloop joins to their inner scans.
* For historical reasons, such parameters are numbered from 0.
* These numbers are independent of PARAM_EXTERN numbers.
*
* PARAM_SUBLINK: The parameter represents an output column of a SubLink
* node's sub-select. The column number is contained in the
* `paramid' field. (This type of Param is converted to
* PARAM_EXEC during planning.)
*
* PARAM_MULTIEXPR: Like PARAM_SUBLINK, the parameter represents an
* output column of a SubLink node's sub-select, but here, the
* SubLink is always a MULTIEXPR SubLink. The high-order 16 bits
* of the `paramid' field contain the SubLink's subLinkId, and
* the low-order 16 bits contain the column number. (This type
* of Param is also converted to PARAM_EXEC during planning.)
*/
typedef enum ParamKind
{
PARAM_EXTERN,
PARAM_EXEC,
PARAM_SUBLINK,
PARAM_MULTIEXPR
} ParamKind;
typedef struct Param
{
Expr xpr;
ParamKind paramkind; /* kind of parameter. See above */
int paramid; /* numeric ID for parameter */
Oid paramtype; /* pg_type OID of parameter's datatype */
int32 paramtypmod; /* typmod value, if known */
Oid paramcollid; /* OID of collation, or InvalidOid if none */
int location; /* token location, or -1 if unknown */
} Param;
四、跟蹤分析
測試指令碼:
select *
from t_dwxx a
where dwbh > any (select b.dwbh from t_grxx b);
gdb跟蹤:
(gdb) b pull_up_sublinks
Breakpoint 1 at 0x77cbc6: file prepjointree.c, line 157.
(gdb) c
Continuing.
Breakpoint 1, pull_up_sublinks (root=0x249f318) at prepjointree.c:157
157 (Node *) root->parse->jointree,
(gdb)
#輸入引數root->parse是查詢樹
#查詢樹結構見第2小結中的查詢樹圖
...
(gdb) p *root->parse
$2 = {type = T_Query, commandType = CMD_SELECT, querySource = QSRC_ORIGINAL, queryId = 0, canSetTag = true,
utilityStmt = 0x0, resultRelation = 0, hasAggs = false, hasWindowFuncs = false, hasTargetSRFs = false,
hasSubLinks = true, hasDistinctOn = false, hasRecursive = false, hasModifyingCTE = false, hasForUpdate = false,
hasRowSecurity = false, cteList = 0x0, rtable = 0x23b0798, jointree = 0x23d3290, targetList = 0x23b0bc8,
override = OVERRIDING_NOT_SET, onConflict = 0x0, returningList = 0x0, groupClause = 0x0, groupingSets = 0x0,
havingQual = 0x0, windowClause = 0x0, distinctClause = 0x0, sortClause = 0x0, limitOffset = 0x0, limitCount = 0x0,
rowMarks = 0x0, setOperations = 0x0, constraintDeps = 0x0, withCheckOptions = 0x0, stmt_location = 0, stmt_len = 70}
(gdb)
#第1次進入pull_up_sublinks_jointree_recurse
(gdb) n
156 jtnode = pull_up_sublinks_jointree_recurse(root,
(gdb) step
pull_up_sublinks_jointree_recurse (root=0x249f318, jtnode=0x23d3290, relids=0x7ffc4fd1ad90) at prepjointree.c:180
180 if (jtnode == NULL)
(gdb) n
184 else if (IsA(jtnode, RangeTblRef))
(gdb)
206 newchild = pull_up_sublinks_jointree_recurse(root,
(gdb) p *jtnode
$3 = {type = T_FromExpr}
(gdb)
#第2次呼叫pull_up_sublinks_jointree_recurse,輸入引數的jtnode為RangeTblRef
#第2次呼叫後返回資訊
(gdb) n
209 newfromlist = lappend(newfromlist, newchild);
(gdb) p *(RangeTblRef *)newchild
$8 = {type = T_RangeTblRef, rtindex = 1}
...
#進入pull_up_sublinks_qual_recurse
(gdb) step
pull_up_sublinks_qual_recurse (root=0x249f318, node=0x23b00e8, jtlink1=0x7ffc4fd1ad28, available_rels1=0x249fa98,
jtlink2=0x0, available_rels2=0x0) at prepjointree.c:335
335 if (node == NULL)
#1.root=PlannerInfo
#2.node=f->quals,即SubLink(結構參見查詢樹圖)
#3.jtlink1=FromExpr(指標陣列)
(gdb) p **(FromExpr **)jtlink1
$29 = {type = T_FromExpr, fromlist = 0x246e0b8, quals = 0x0}
...
#進入convert_ANY_sublink_to_join
(gdb)
346 if ((j = convert_ANY_sublink_to_join(root, sublink,
(gdb)
#輸入引數
#1.root見上
#2.sublink,子連結
#3.available_rels,可用的rels
...
#sublink中的子查詢
1322 Query *subselect = (Query *) sublink->subselect;
(gdb)
1337 if (contain_vars_of_level((Node *) subselect, 1))
(gdb) p *subselect
$2 = {type = T_Query, commandType = CMD_SELECT,
querySource = QSRC_ORIGINAL, queryId = 0, canSetTag = true,
utilityStmt = 0x0, resultRelation = 0, hasAggs = false,
hasWindowFuncs = false, hasTargetSRFs = false,
hasSubLinks = false, hasDistinctOn = false,
hasRecursive = false, hasModifyingCTE = false,
hasForUpdate = false, hasRowSecurity = false, cteList = 0x0,
rtable = 0x1cb1030, jointree = 0x1cb1240,
targetList = 0x1cb1210, override = OVERRIDING_NOT_SET,
onConflict = 0x0, returningList = 0x0, groupClause = 0x0,
groupingSets = 0x0, havingQual = 0x0, windowClause = 0x0,
distinctClause = 0x0, sortClause = 0x0, limitOffset = 0x0,
limitCount = 0x0, rowMarks = 0x0, setOperations = 0x0,
constraintDeps = 0x0, withCheckOptions = 0x0,
stmt_location = 0, stmt_len = 0}
...
(gdb) p *upper_varnos.words
$4 = 2
(gdb) p available_rels
$5 = (Relids) 0x1c8ab68
(gdb) p *available_rels
$6 = {nwords = 1, words = 0x1c8ab6c}
(gdb) p *available_rels.words
$7 = 2
...
#子連結被上拉為上一層jointree的rarg
#larg由上層填充
1411 return result;
(gdb) p *result
$10 = {type = T_JoinExpr, jointype = JOIN_SEMI,
isNatural = false, larg = 0x0, rarg = 0x1c96e88,
usingClause = 0x0, quals = 0x1c96ef0, alias = 0x0,
rtindex = 0}
(gdb) n
1412 }
(gdb) n
#回到pull_up_sublinks_qual_recurse
pull_up_sublinks_qual_recurse (root=0x1c8a3e8, node=0x1bc1038,
jtlink1=0x7ffc99e060e8, available_rels1=0x1c8ab68,
jtlink2=0x0, available_rels2=0x0) at prepjointree.c:350
350 j->larg = *jtlink1;
(gdb) n
351 *jtlink1 = (Node *) j;
#larg為RTF(rtindex=1)
(gdb) p *jtlink1
$13 = (Node *) 0x1c96ca8
(gdb) p **jtlink1
$14 = {type = T_FromExpr}
(gdb) p **(FromExpr **)jtlink1
$15 = {type = T_FromExpr, fromlist = 0x1c96c78, quals = 0x0}
(gdb) p **(FromExpr **)jtlink1->fromlist
There is no member named fromlist.
(gdb) p *(*(FromExpr **)jtlink1)->fromlist
$16 = {type = T_List, length = 1, head = 0x1c96c58,
tail = 0x1c96c58}
(gdb) p *(Node *)(*(FromExpr **)jtlink1)->fromlist->head->data.ptr_value
$17 = {type = T_RangeTblRef}
(gdb) p *(RangeTblRef *)(*(FromExpr **)jtlink1)->fromlist->head->data.ptr_value
$18 = {type = T_RangeTblRef, rtindex = 1}
#遞迴上拉右樹
(gdb) step
pull_up_sublinks_jointree_recurse (root=0x1c8a3e8,
jtnode=0x1c96e88, relids=0x7ffc99e06048)
at prepjointree.c:180
180 if (jtnode == NULL)
(gdb) p *jtnode
$19 = {type = T_RangeTblRef}
#RTR,退出
(gdb) finish
Run till exit from #0 pull_up_sublinks_jointree_recurse (
root=0x1c8a3e8, jtnode=0x1c96e88, relids=0x7ffc99e06048)
at prepjointree.c:180
0x000000000077d00a in pull_up_sublinks_qual_recurse (
root=0x1c8a3e8, node=0x1bc1038, jtlink1=0x7ffc99e060e8,
available_rels1=0x1c8ab68, jtlink2=0x0, available_rels2=0x0)
at prepjointree.c:353
353 j->rarg = pull_up_sublinks_jointree_recurse(root,
Value returned is $20 = (Node *) 0x1c96e88
(gdb) n
362 j->quals = pull_up_sublinks_qual_recurse(root,
(gdb) step
#遞迴上拉條件表示式,節點型別為OpExpr,無需處理
Breakpoint 1, pull_up_sublinks_qual_recurse (root=0x1c8a3e8,
node=0x1c96ef0, jtlink1=0x1c97100,
available_rels1=0x1c8ab68, jtlink2=0x1c97108,
available_rels2=0x1c97140) at prepjointree.c:335
335 if (node == NULL)
(gdb) n
337 if (IsA(node, SubLink))
(gdb) p *node
$21 = {type = T_OpExpr}
...
369 return NULL;
(gdb)
552 }
(gdb)
#回到pull_up_sublinks_jointree_recurse
pull_up_sublinks_jointree_recurse (root=0x1c8a3e8,
jtnode=0x1cb1540, relids=0x7ffc99e06150)
at prepjointree.c:230
230 *relids = frelids;
(gdb) p *newf
#newf的條件表示式為NULL(TRUE)
$22 = {type = T_FromExpr, fromlist = 0x1c96c78, quals = 0x0}
(gdb) p *jtlink
$23 = {type = T_JoinExpr}
(gdb) n
231 jtnode = jtlink;
(gdb) n
312 return jtnode;
(gdb)
313 }
(gdb)
pull_up_sublinks (root=0x1c8a3e8) at prepjointree.c:164
164 if (IsA(jtnode, FromExpr))
(gdb)
167 root->parse->jointree = makeFromExpr(list_make1(jtnode), NULL);
(gdb)
168 }
(gdb)
subquery_planner (glob=0x1bc1d50, parse=0x1bc1328,
parent_root=0x0, hasRecursion=false, tuple_fraction=0)
at planner.c:656
warning: Source file is more recent than executable.
656 inline_set_returning_functions(root);
(gdb) finish
Run till exit from #0 subquery_planner (glob=0x1bc1d50,
parse=0x1bc1328, parent_root=0x0, hasRecursion=false,
tuple_fraction=0) at planner.c:656
0x0000000000769a49 in standard_planner (parse=0x1bc1328,
cursorOptions=256, boundParams=0x0) at planner.c:405
405 root = subquery_planner(glob, parse, NULL,
Value returned is $24 = (PlannerInfo *) 0x1c8a3e8
(gdb)
#DONE!
五、小結
1、上拉過程：上拉FromExpr中的子連結->上拉條件表示式quals中的子連結。處理過程使用遞迴；
2、上拉條件：ANY子句，子查詢中含有父查詢的Vars（資料列）、依賴更高層次的RTE或者存在易變函式，不能上拉。
參考資料：
prepjointree.c
list.c
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374889/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（28）- 查詢語句#13（查詢優化-上拉子連結#3）SQL原始碼優化
- PostgreSQL 原始碼解讀（26）- 查詢語句#11（查詢優化-上拉子連結#1）SQL原始碼優化
- PostgreSQL 原始碼解讀（29）- 查詢語句#14（查詢優化-上拉子查詢）SQL原始碼優化
- PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）SQL原始碼優化
- PostgreSQL 原始碼解讀（36）- 查詢語句#21（查詢優化-消除外連線）SQL原始碼優化
- PostgreSQL 原始碼解讀（25）- 查詢語句#10（查詢優化概覽）SQL原始碼優化
- PostgreSQL 原始碼解讀（37）- 查詢語句#22（查詢優化-grouping_plan...SQL原始碼優化
- PostgreSQL 原始碼解讀（32）- 查詢語句#17（查詢優化-表示式預處理#2）SQL原始碼優化
- PostgreSQL 原始碼解讀（42）- 查詢語句#27（等價類）SQL原始碼
- PostgreSQL 原始碼解讀（35）- 查詢語句#20（查詢優化-簡化Having和Grou...SQL原始碼優化
- PostgreSQL 原始碼解讀（24）- 查詢語句#9（查詢重寫）SQL原始碼
- PostgreSQL 原始碼解讀（30）- 查詢語句#15（查詢優化-扁平化處理UNION ALL）SQL原始碼優化
- PostgreSQL 原始碼解讀（31）- 查詢語句#16（查詢優化-表示式預處理#1）SQL原始碼優化
- PostgreSQL 原始碼解讀（33）- 查詢語句#18（查詢優化-表示式預處理#3）SQL原始碼優化
- PostgreSQL 原始碼解讀（34）- 查詢語句#19（查詢優化-表示式預處理#4）SQL原始碼優化
- PostgreSQL 原始碼解讀（20）- 查詢語句#5（查詢樹Query詳解）SQL原始碼
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（51）- 查詢語句#36（Optimizer Review#2）SQL原始碼View
- PostgreSQL 原始碼解讀（18）- 查詢語句#3（SQL Parse）SQL原始碼
- PostgreSQL 原始碼解讀（19）- 查詢語句#4（ParseTree詳解）SQL原始碼
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（75）- 查詢語句#60（Review - standard_...SQL原始碼View
- PostgreSQL 原始碼解讀（74）- 查詢語句#59（Review - subquery_...SQL原始碼View
- PostgreSQL 原始碼解讀（39）- 查詢語句#24（query_planner函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（82）- 查詢語句#67（PortalXXX系列函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（81）- 查詢語句#66（Review - exec_simp...SQL原始碼View
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（87）- 查詢語句#72（PortalRunSelect->E...SQL原始碼
- PostgreSQL 原始碼解讀（84）- 查詢語句#69（PortalStart->InitP...SQL原始碼
- PostgreSQL 原始碼解讀（85）- 查詢語句#70（PortalRun->InitPla...SQL原始碼
- PostgreSQL 原始碼解讀（86）- 查詢語句#71（PortalRun->PortalR...SQL原始碼
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（50）- 查詢語句#35（Optimizer Review#1）SQL原始碼View
- PostgreSQL 原始碼解讀（62）- 查詢語句#47（make_one_rel函式#12-...SQL原始碼函式
- PostgreSQL 原始碼解讀（22）- 查詢語句#7（PlannedStmt結構詳解-日誌分析）SQL原始碼
- PostgreSQL 原始碼解讀（73）- 查詢語句#58（grouping_planner函式...SQL原始碼函式