PostgreSQL查询优化中如何实现上拉子链接
短信预约 -IT技能 免费直播动态提醒
本篇内容介绍了“PostgreSQL查询优化中如何实现上拉子链接”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
查询树
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);
}
else if (IsA(jtnode, FromExpr))//如为FromExpr类型
{
FromExpr *f = (FromExpr *) jtnode;
List *newfromlist = NIL;
Relids frelids = NULL;
FromExpr *newf;
Node *jtlink;
ListCell *l;
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);
}
newf = makeFromExpr(newfromlist, NULL);//创建新的FromExpr
jtlink = (Node *) newf;
//处理子链接中的表达式
//newf(指针,相当于jtlink)
newf->quals = pull_up_sublinks_qual_recurse(root, f->quals,
&jtlink, frelids,
NULL, NULL);//
*relids = frelids;//设置相关的relids
jtnode = jtlink;//返回值
}
else if (IsA(jtnode, JoinExpr))
{
JoinExpr *j;
Relids leftrelids;
Relids rightrelids;
Node *jtlink;
j = (JoinExpr *) palloc(sizeof(JoinExpr));
memcpy(j, jtnode, sizeof(JoinExpr));
jtlink = (Node *) j;
//递归处理左边&右边子树
j->larg = pull_up_sublinks_jointree_recurse(root, j->larg,
&leftrelids);
j->rarg = pull_up_sublinks_jointree_recurse(root, j->rarg,
&rightrelids);
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:
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;
}
*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
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;
if (sublink->subLinkType == ANY_SUBLINK)//ANY子链接
{
if ((j = convert_ANY_sublink_to_join(root, sublink,
available_rels1)) != NULL)
{
j->larg = *jtlink1;
*jtlink1 = (Node *) j;
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels1,
&j->rarg,
child_rels);
return NULL;
}
if (available_rels2 != NULL &&
(j = convert_ANY_sublink_to_join(root, sublink,
available_rels2)) != NULL)
{
j->larg = *jtlink2;
*jtlink2 = (Node *) j;
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels2,
&j->rarg,
child_rels);
return NULL;
}
}
else if (sublink->subLinkType == EXISTS_SUBLINK)//EXISTS子链接
{
if ((j = convert_EXISTS_sublink_to_join(root, sublink, false,
available_rels1)) != NULL)
{
j->larg = *jtlink1;
*jtlink1 = (Node *) j;
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels1,
&j->rarg,
child_rels);
return NULL;
}
if (available_rels2 != NULL &&
(j = convert_EXISTS_sublink_to_join(root, sublink, false,
available_rels2)) != NULL)
{
j->larg = *jtlink2;
*jtlink2 = (Node *) j;
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->larg,
available_rels2,
&j->rarg,
child_rels);
return NULL;
}
}
return node;
}
if (not_clause(node))//NOT语句
{
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)
{
j->larg = *jtlink1;
*jtlink1 = (Node *) j;
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->rarg,
child_rels,
NULL, NULL);
return NULL;
}
if (available_rels2 != NULL &&
(j = convert_EXISTS_sublink_to_join(root, sublink, true,
available_rels2)) != NULL)
{
j->larg = *jtlink2;
*jtlink2 = (Node *) j;
j->rarg = pull_up_sublinks_jointree_recurse(root,
j->rarg,
&child_rels);
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
&j->rarg,
child_rels,
NULL, NULL);
return NULL;
}
}
}
return node;
}
if (and_clause(node))//AND语句
{
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);
}
if (newclauses == NIL)
return NULL;
else if (list_length(newclauses) == 1)
return (Node *) linitial(newclauses);
else
return (Node *) make_andclause(newclauses);
}
return node;
}convert_ANY_sublink_to_join
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);
//ANY类型的子链接,子查询不能依赖父查询的任何变量,否则返回NULL(不能上拉)
if (contain_vars_of_level((Node *) subselect, 1))
return NULL;
//子查询的testexpr变量中必须含有父查询的某些Vars,否则不能上拉(join),返回NULL
upper_varnos = pull_varnos(sublink->testexpr);
if (bms_is_empty(upper_varnos))
return NULL;
//但是不能够依赖超出可用的范围之内,否则一样不能上拉
if (!bms_is_subset(upper_varnos, available_rels))
return NULL;
//组合操作符和左侧的表达式不能是易变(volatile)的,比如随机数等
if (contain_volatile_functions(sublink->testexpr))
return NULL;
//校验通过,上拉
pstate = make_parsestate(NULL);
//子链接上拉后,子查询变成了上层的RTE,在这里构造
rte = addRangeTableEntryForSubquery(pstate,
subselect,
makeAlias("ANY_subquery", NIL),
false,
false);
//添加到上层的rtable中
parse->rtable = lappend(parse->rtable, rte);
//rtable中的索引
rtindex = list_length(parse->rtable);
//产生了RTE,自然要生成RTR(RangeTblRef)
rtr = makeNode(RangeTblRef);
rtr->rtindex = rtindex;
//创建子查询的输出列(投影)
subquery_vars = generate_subquery_vars(root,
subselect->targetList,
rtindex);
//构造上层的条件表达式
quals = convert_testexpr(root, sublink->testexpr, subquery_vars);
//构造返回结果
result = makeNode(JoinExpr);
result->jointype = JOIN_SEMI;//变换为半连接
result->isNatural = false;
result->larg = NULL;
result->rarg = (Node *) rtr;
result->usingClause = NIL;
result->quals = quals;
result->alias = NULL;
result->rtindex = 0;
return result;
}三、数据结构
Param
typedef enum ParamKind
{
PARAM_EXTERN,
PARAM_EXEC,
PARAM_SUBLINK,
PARAM_MULTIEXPR
} ParamKind;
typedef struct Param
{
Expr xpr;
ParamKind paramkind;
int paramid;
Oid paramtype;
int32 paramtypmod;
Oid paramcollid;
int location;
} 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 = Qclass="lazy" data-src_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 = Qclass="lazy" data-src_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!“PostgreSQL查询优化中如何实现上拉子链接”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/47.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
