PostgreSQL查询优化中怎么上拉子链接
短信预约 -IT技能 免费直播动态提醒
本篇内容主要讲解“PostgreSQL查询优化中怎么上拉子链接”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL查询优化中怎么上拉子链接”吧!
查询树
convert_EXISTS_sublink_to_join函数源码:
JoinExpr *
convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
bool under_not, Relids available_rels)
{
JoinExpr *result;//返回结果
Query *parse = root->parse;//查询树
Query *subselect = (Query *) sublink->subselect;//子查询
Node *whereClause;//where语句
int rtoffset;//
int varno;
Relids clause_varnos;
Relids upper_varnos;
Assert(sublink->subLinkType == EXISTS_SUBLINK);
if (subselect->cteList)//存在With子句,返回
return NULL;
subselect = copyObject(subselect);
//能否handle targetList?不行,则退出
//如果含有集合操作/聚合操作/Having子句等,子链接不能提升
if (!simplify_EXISTS_query(root, subselect))
return NULL;
if (subselect->jointree->fromlist == NIL)//子查询没有查询主体,退出
return NULL;
whereClause = subselect->jointree->quals;//子查询条件语句单独保存
subselect->jointree->quals = NULL;//子查询的条件语句设置为NULL
if (contain_vars_of_level((Node *) subselect, 1))//去掉条件语句后,如仍依赖父查询的Vars,退出
return NULL;
if (!contain_vars_of_level(whereClause, 1))//条件语句必须含有父查询的Vars,否则不构成连接,退出
return NULL;
if (contain_volatile_functions(whereClause))//条件语句存在易变函数(如随机函数等)
return NULL;
rtoffset = list_length(parse->rtable);//获取rtable的长度(新RTE插入的偏移)
//调整子查询中varno为0(指向rtable)的Vars,varno调整为父查询rtable的index
//(详见依赖函数解析)
OffsetVarNodes((Node *) subselect, rtoffset, 0);
OffsetVarNodes(whereClause, rtoffset, 0);
//子查询中与父查询相关的Vars,varlevelsup需要从Leve 1变为Level 0
//在子查询中,这些Vars的varlevelsup为1,表示依赖于父查询(上一层)的Vars,提升后不存在此依赖,需改为0
IncrementVarSublevelsUp((Node *) subselect, -1, 1);
IncrementVarSublevelsUp(whereClause, -1, 1);
clause_varnos = pull_varnos(whereClause);
upper_varnos = NULL;
while ((varno = bms_first_member(clause_varnos)) >= 0)
{
if (varno <= rtoffset)
upper_varnos = bms_add_member(upper_varnos, varno);
}
bms_free(clause_varnos);
Assert(!bms_is_empty(upper_varnos));
if (!bms_is_subset(upper_varnos, available_rels))
return NULL;
//把子查询的rtable拼接到父查询的rtable中
parse->rtable = list_concat(parse->rtable, subselect->rtable);
//构造JoinExpr
result = makeNode(JoinExpr);
result->jointype = under_not ? JOIN_ANTI : JOIN_SEMI;
result->isNatural = false;
result->larg = NULL;
if (list_length(subselect->jointree->fromlist) == 1)
result->rarg = (Node *) linitial(subselect->jointree->fromlist);
else
result->rarg = (Node *) subselect->jointree;
result->usingClause = NIL;
result->quals = whereClause;
result->alias = NULL;
result->rtindex = 0;
return result;
}二、基础信息
相关数据结构
1、Var
#define INNER_VAR 65000
#define OUTER_VAR 65001
#define INDEX_VAR 65002
#define IS_SPECIAL_VARNO(varno) ((varno) >= INNER_VAR)
#define PRS2_OLD_VARNO 1
#define PRS2_NEW_VARNO 2
typedef struct Var
{
Expr xpr;
Index varno;
AttrNumber varattno;
Oid vartype;
int32 vartypmod;
Oid varcollid;
Index varlevelsup;
Index varnoold;
AttrNumber varoattno;
int location;
} Var;XX_one_pos
static const uint8 rightmost_one_pos[256] = {
0, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
5, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
6, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
5, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
7, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
5, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
6, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
5, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0,
4, 0, 1, 0, 2, 0, 1, 0, 3, 0, 1, 0, 2, 0, 1, 0
};
static const uint8 leftmost_one_pos[256] = {
0, 0, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3,
4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7
};
static const uint8 number_of_ones[256] = {
0, 1, 1, 2, 1, 2, 2, 3, 1, 2, 2, 3, 2, 3, 3, 4,
1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
3, 4, 4, 5, 4, 5, 5, 6, 4, 5, 5, 6, 5, 6, 6, 7,
1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
3, 4, 4, 5, 4, 5, 5, 6, 4, 5, 5, 6, 5, 6, 6, 7,
2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
3, 4, 4, 5, 4, 5, 5, 6, 4, 5, 5, 6, 5, 6, 6, 7,
3, 4, 4, 5, 4, 5, 5, 6, 4, 5, 5, 6, 5, 6, 6, 7,
4, 5, 5, 6, 5, 6, 6, 7, 5, 6, 6, 7, 6, 7, 7, 8
};依赖的函数
simplify_EXISTS_query
static bool
simplify_EXISTS_query(PlannerInfo *root, Query *query)
{
if (query->commandType != CMD_SELECT ||
query->setOperations ||
query->hasAggs ||
query->groupingSets ||
query->hasWindowFuncs ||
query->hasTargetSRFs ||
query->hasModifyingCTE ||
query->havingQual ||
query->limitOffset ||
query->rowMarks)
return false;
if (query->limitCount)
{
Node *node = eval_const_expressions(root, query->limitCount);
Const *limit;
query->limitCount = node;
if (!IsA(node, Const))
return false;
limit = (Const *) node;
Assert(limit->consttype == INT8OID);
if (!limit->constisnull && DatumGetInt64(limit->constvalue) <= 0)
return false;
query->limitCount = NULL;
}
query->targetList = NIL;
query->groupClause = NIL;
query->windowClause = NIL;
query->distinctClause = NIL;
query->sortClause = NIL;
query->hasDistinctOn = false;
return true;
}OffsetVarNodes
void
OffsetVarNodes(Node *node, int offset, int sublevels_up)
{
OffsetVarNodes_context context;//上下文
context.offset = offset;//保存传入的offset
context.sublevels_up = sublevels_up;
if (node && IsA(node, Query))
{
Query *qry = (Query *) node;
if (sublevels_up == 0)
{
ListCell *l;
if (qry->resultRelation)
qry->resultRelation += offset;
if (qry->onConflict && qry->onConflict->exclRelIndex)
qry->onConflict->exclRelIndex += offset;
foreach(l, qry->rowMarks)
{
RowMarkClause *rc = (RowMarkClause *) lfirst(l);
rc->rti += offset;
}
}
query_tree_walker(qry, OffsetVarNodes_walker,
(void *) &context, 0);
}
else
OffsetVarNodes_walker(node, &context);
}IncrementVarSublevelsUp
void
IncrementVarSublevelsUp(Node *node, int delta_sublevels_up,
int min_sublevels_up)
{
IncrementVarSublevelsUp_context context;
context.delta_sublevels_up = delta_sublevels_up;
context.min_sublevels_up = min_sublevels_up;
query_or_expression_tree_walker(node,
IncrementVarSublevelsUp_walker,
(void *) &context,
QTW_EXAMINE_RTES);
}IncrementVarSublevelsUp
typedef struct
{
int delta_sublevels_up;
int min_sublevels_up;
} IncrementVarSublevelsUp_context;
static bool
IncrementVarSublevelsUp_walker(Node *node,
IncrementVarSublevelsUp_context *context)
{
if (node == NULL)
return false;
if (IsA(node, Var))
{
Var *var = (Var *) node;
if (var->varlevelsup >= context->min_sublevels_up)
var->varlevelsup += context->delta_sublevels_up;
return false;
}
if (IsA(node, CurrentOfExpr))
{
if (context->min_sublevels_up == 0)
elog(ERROR, "cannot push down CurrentOfExpr");
return false;
}
if (IsA(node, Aggref))
{
Aggref *agg = (Aggref *) node;
if (agg->agglevelsup >= context->min_sublevels_up)
agg->agglevelsup += context->delta_sublevels_up;
}
if (IsA(node, GroupingFunc))
{
GroupingFunc *grp = (GroupingFunc *) node;
if (grp->agglevelsup >= context->min_sublevels_up)
grp->agglevelsup += context->delta_sublevels_up;
}
if (IsA(node, PlaceHolderVar))
{
PlaceHolderVar *phv = (PlaceHolderVar *) node;
if (phv->phlevelsup >= context->min_sublevels_up)
phv->phlevelsup += context->delta_sublevels_up;
}
if (IsA(node, RangeTblEntry))
{
RangeTblEntry *rte = (RangeTblEntry *) node;
if (rte->rtekind == RTE_CTE)
{
if (rte->ctelevelsup >= context->min_sublevels_up)
rte->ctelevelsup += context->delta_sublevels_up;
}
return false;
}
if (IsA(node, Query))
{
bool result;
context->min_sublevels_up++;
result = query_tree_walker((Query *) node,
IncrementVarSublevelsUp_walker,
(void *) context,
QTW_EXAMINE_RTES);
context->min_sublevels_up--;
return result;
}
return expression_tree_walker(node, IncrementVarSublevelsUp_walker,
(void *) context);
}pull_varnos
Relids
pull_varnos(Node *node)
{
pull_varnos_context context;
context.varnos = NULL;
context.sublevels_up = 0;
query_or_expression_tree_walker(node,
pull_varnos_walker,
(void *) &context,
0);
return context.varnos;
}contain_vars_of_level
bool
contain_vars_of_level(Node *node, int levelsup)
{
int sublevels_up = levelsup;
return query_or_expression_tree_walker(node,
contain_vars_of_level_walker,
(void *) &sublevels_up,
0);
}query_tree_walker
bool
query_tree_walker(Query *query,
bool (*walker) (),
void *context,
int flags)
{
Assert(query != NULL && IsA(query, Query));
if (walker((Node *) query->targetList, context))
return true;
if (walker((Node *) query->withCheckOptions, context))
return true;
if (walker((Node *) query->onConflict, context))
return true;
if (walker((Node *) query->returningList, context))
return true;
if (walker((Node *) query->jointree, context))
return true;
if (walker(query->setOperations, context))
return true;
if (walker(query->havingQual, context))
return true;
if (walker(query->limitOffset, context))
return true;
if (walker(query->limitCount, context))
return true;
if (!(flags & QTW_IGNORE_CTE_SUBQUERIES))
{
if (walker((Node *) query->cteList, context))
return true;
}
if (!(flags & QTW_IGNORE_RANGE_TABLE))
{
if (range_table_walker(query->rtable, walker, context, flags))
return true;
}
return false;
}OffsetVarNodes_walker
typedef struct
{
int offset;
int sublevels_up;
} OffsetVarNodes_context;
static bool
OffsetVarNodes_walker(Node *node, OffsetVarNodes_context *context)
{
if (node == NULL)
return false;
if (IsA(node, Var))
{
Var *var = (Var *) node;
if (var->varlevelsup == context->sublevels_up)
{
var->varno += context->offset;
var->varnoold += context->offset;
}
return false;
}
if (IsA(node, CurrentOfExpr))
{
CurrentOfExpr *cexpr = (CurrentOfExpr *) node;
if (context->sublevels_up == 0)
cexpr->cvarno += context->offset;
return false;
}
if (IsA(node, RangeTblRef))
{
RangeTblRef *rtr = (RangeTblRef *) node;
if (context->sublevels_up == 0)
rtr->rtindex += context->offset;
return false;
}
if (IsA(node, JoinExpr))
{
JoinExpr *j = (JoinExpr *) node;
if (j->rtindex && context->sublevels_up == 0)
j->rtindex += context->offset;
}
if (IsA(node, PlaceHolderVar))
{
PlaceHolderVar *phv = (PlaceHolderVar *) node;
if (phv->phlevelsup == context->sublevels_up)
{
phv->phrels = offset_relid_set(phv->phrels,
context->offset);
}
}
if (IsA(node, AppendRelInfo))
{
AppendRelInfo *appinfo = (AppendRelInfo *) node;
if (context->sublevels_up == 0)
{
appinfo->parent_relid += context->offset;
appinfo->child_relid += context->offset;
}
}
Assert(!IsA(node, PlanRowMark));
Assert(!IsA(node, SpecialJoinInfo));
Assert(!IsA(node, PlaceHolderInfo));
Assert(!IsA(node, MinMaxAggInfo));
if (IsA(node, Query))
{
bool result;
context->sublevels_up++;
result = query_tree_walker((Query *) node, OffsetVarNodes_walker,
(void *) context, 0);
context->sublevels_up--;
return result;
}
return expression_tree_walker(node, OffsetVarNodes_walker,
(void *) context);
}query_or_expression_tree_walker
bool
query_or_expression_tree_walker(Node *node,
bool (*walker) (),
void *context,
int flags)
{
if (node && IsA(node, Query))
return query_tree_walker((Query *) node,
walker,
context,
flags);
else
return walker(node, context);
}pull_varnos_walker
static bool
pull_varnos_walker(Node *node, pull_varnos_context *context)
{
if (node == NULL)
return false;
if (IsA(node, Var))
{
Var *var = (Var *) node;
if (var->varlevelsup == context->sublevels_up)
context->varnos = bms_add_member(context->varnos, var->varno);
return false;
}
if (IsA(node, CurrentOfExpr))
{
CurrentOfExpr *cexpr = (CurrentOfExpr *) node;
if (context->sublevels_up == 0)
context->varnos = bms_add_member(context->varnos, cexpr->cvarno);
return false;
}
if (IsA(node, PlaceHolderVar))
{
PlaceHolderVar *phv = (PlaceHolderVar *) node;
pull_varnos_context subcontext;
subcontext.varnos = NULL;
subcontext.sublevels_up = context->sublevels_up;
(void) pull_varnos_walker((Node *) phv->phexpr, &subcontext);
if (phv->phlevelsup == context->sublevels_up)
{
subcontext.varnos = bms_int_members(subcontext.varnos,
phv->phrels);
if (bms_is_empty(subcontext.varnos))
context->varnos = bms_add_members(context->varnos,
phv->phrels);
}
context->varnos = bms_join(context->varnos, subcontext.varnos);
return false;
}
if (IsA(node, Query))
{
bool result;
context->sublevels_up++;
result = query_tree_walker((Query *) node, pull_varnos_walker,
(void *) context, 0);
context->sublevels_up--;
return result;
}
return expression_tree_walker(node, pull_varnos_walker,
(void *) context);
}contain_vars_of_level_walker
static bool
contain_vars_of_level_walker(Node *node, int *sublevels_up)
{
if (node == NULL)
return false;
if (IsA(node, Var))
{
if (((Var *) node)->varlevelsup == *sublevels_up)
return true;
return false;
}
if (IsA(node, CurrentOfExpr))
{
if (*sublevels_up == 0)
return true;
return false;
}
if (IsA(node, PlaceHolderVar))
{
if (((PlaceHolderVar *) node)->phlevelsup == *sublevels_up)
return true;
}
if (IsA(node, Query))
{
bool result;
(*sublevels_up)++;
result = query_tree_walker((Query *) node,
contain_vars_of_level_walker,
(void *) sublevels_up,
0);
(*sublevels_up)--;
return result;
}
return expression_tree_walker(node,
contain_vars_of_level_walker,
(void *) sublevels_up);
}expression_tree_walker
bool
expression_tree_walker(Node *node,
bool (*walker) (),
void *context)
{
ListCell *temp;
if (node == NULL)
return false;
check_stack_depth();
switch (nodeTag(node))
{
case T_Var:
case T_Const:
case T_Param:
case T_CaseTestExpr:
case T_SQLValueFunction:
case T_CoerceToDomainValue:
case T_SetToDefault:
case T_CurrentOfExpr:
case T_NextValueExpr:
case T_RangeTblRef:
case T_SortGroupClause:
break;
case T_WithCheckOption:
return walker(((WithCheckOption *) node)->qual, context);
case T_Aggref:
{
Aggref *expr = (Aggref *) node;
if (expression_tree_walker((Node *) expr->aggdirectargs,
walker, context))
return true;
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
if (expression_tree_walker((Node *) expr->aggorder,
walker, context))
return true;
if (expression_tree_walker((Node *) expr->aggdistinct,
walker, context))
return true;
if (walker((Node *) expr->aggfilter, context))
return true;
}
break;
case T_GroupingFunc:
{
GroupingFunc *grouping = (GroupingFunc *) node;
if (expression_tree_walker((Node *) grouping->args,
walker, context))
return true;
}
break;
case T_WindowFunc:
{
WindowFunc *expr = (WindowFunc *) node;
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
if (walker((Node *) expr->aggfilter, context))
return true;
}
break;
case T_ArrayRef:
{
ArrayRef *aref = (ArrayRef *) node;
if (expression_tree_walker((Node *) aref->refupperindexpr,
walker, context))
return true;
if (expression_tree_walker((Node *) aref->reflowerindexpr,
walker, context))
return true;
if (walker(aref->refexpr, context))
return true;
if (walker(aref->refassgnexpr, context))
return true;
}
break;
case T_FuncExpr:
{
FuncExpr *expr = (FuncExpr *) node;
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
}
break;
case T_NamedArgExpr:
return walker(((NamedArgExpr *) node)->arg, context);
case T_OpExpr:
case T_DistinctExpr:
case T_NullIfExpr:
{
OpExpr *expr = (OpExpr *) node;
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
}
break;
case T_ScalarArrayOpExpr:
{
ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
}
break;
case T_BoolExpr:
{
BoolExpr *expr = (BoolExpr *) node;
if (expression_tree_walker((Node *) expr->args,
walker, context))
return true;
}
break;
case T_SubLink:
{
SubLink *sublink = (SubLink *) node;
if (walker(sublink->testexpr, context))
return true;
return walker(sublink->subselect, context);
}
break;
case T_SubPlan:
{
SubPlan *subplan = (SubPlan *) node;
if (walker(subplan->testexpr, context))
return true;
if (expression_tree_walker((Node *) subplan->args,
walker, context))
return true;
}
break;
case T_AlternativeSubPlan:
return walker(((AlternativeSubPlan *) node)->subplans, context);
case T_FieldSelect:
return walker(((FieldSelect *) node)->arg, context);
case T_FieldStore:
{
FieldStore *fstore = (FieldStore *) node;
if (walker(fstore->arg, context))
return true;
if (walker(fstore->newvals, context))
return true;
}
break;
case T_RelabelType:
return walker(((RelabelType *) node)->arg, context);
case T_CoerceViaIO:
return walker(((CoerceViaIO *) node)->arg, context);
case T_ArrayCoerceExpr:
{
ArrayCoerceExpr *acoerce = (ArrayCoerceExpr *) node;
if (walker(acoerce->arg, context))
return true;
if (walker(acoerce->elemexpr, context))
return true;
}
break;
case T_ConvertRowtypeExpr:
return walker(((ConvertRowtypeExpr *) node)->arg, context);
case T_CollateExpr:
return walker(((CollateExpr *) node)->arg, context);
case T_CaseExpr:
{
CaseExpr *caseexpr = (CaseExpr *) node;
if (walker(caseexpr->arg, context))
return true;
foreach(temp, caseexpr->args)
{
CaseWhen *when = lfirst_node(CaseWhen, temp);
if (walker(when->expr, context))
return true;
if (walker(when->result, context))
return true;
}
if (walker(caseexpr->defresult, context))
return true;
}
break;
case T_ArrayExpr:
return walker(((ArrayExpr *) node)->elements, context);
case T_RowExpr:
return walker(((RowExpr *) node)->args, context);
case T_RowCompareExpr:
{
RowCompareExpr *rcexpr = (RowCompareExpr *) node;
if (walker(rcexpr->largs, context))
return true;
if (walker(rcexpr->rargs, context))
return true;
}
break;
case T_CoalesceExpr:
return walker(((CoalesceExpr *) node)->args, context);
case T_MinMaxExpr:
return walker(((MinMaxExpr *) node)->args, context);
case T_XmlExpr:
{
XmlExpr *xexpr = (XmlExpr *) node;
if (walker(xexpr->named_args, context))
return true;
if (walker(xexpr->args, context))
return true;
}
break;
case T_NullTest:
return walker(((NullTest *) node)->arg, context);
case T_BooleanTest:
return walker(((BooleanTest *) node)->arg, context);
case T_CoerceToDomain:
return walker(((CoerceToDomain *) node)->arg, context);
case T_TargetEntry:
return walker(((TargetEntry *) node)->expr, context);
case T_Query:
break;
case T_WindowClause:
{
WindowClause *wc = (WindowClause *) node;
if (walker(wc->partitionClause, context))
return true;
if (walker(wc->orderClause, context))
return true;
if (walker(wc->startOffset, context))
return true;
if (walker(wc->endOffset, context))
return true;
}
break;
case T_CommonTableExpr:
{
CommonTableExpr *cte = (CommonTableExpr *) node;
return walker(cte->ctequery, context);
}
break;
case T_List:
foreach(temp, (List *) node)
{
if (walker((Node *) lfirst(temp), context))
return true;
}
break;
case T_FromExpr:
{
FromExpr *from = (FromExpr *) node;
if (walker(from->fromlist, context))
return true;
if (walker(from->quals, context))
return true;
}
break;
case T_OnConflictExpr:
{
OnConflictExpr *onconflict = (OnConflictExpr *) node;
if (walker((Node *) onconflict->arbiterElems, context))
return true;
if (walker(onconflict->arbiterWhere, context))
return true;
if (walker(onconflict->onConflictSet, context))
return true;
if (walker(onconflict->onConflictWhere, context))
return true;
if (walker(onconflict->exclRelTlist, context))
return true;
}
break;
case T_PartitionPruneStepOp:
{
PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node;
if (walker((Node *) opstep->exprs, context))
return true;
}
break;
case T_PartitionPruneStepCombine:
break;
case T_JoinExpr:
{
JoinExpr *join = (JoinExpr *) node;
if (walker(join->larg, context))
return true;
if (walker(join->rarg, context))
return true;
if (walker(join->quals, context))
return true;
}
break;
case T_SetOperationStmt:
{
SetOperationStmt *setop = (SetOperationStmt *) node;
if (walker(setop->larg, context))
return true;
if (walker(setop->rarg, context))
return true;
}
break;
case T_PlaceHolderVar:
return walker(((PlaceHolderVar *) node)->phexpr, context);
case T_InferenceElem:
return walker(((InferenceElem *) node)->expr, context);
case T_AppendRelInfo:
{
AppendRelInfo *appinfo = (AppendRelInfo *) node;
if (expression_tree_walker((Node *) appinfo->translated_vars,
walker, context))
return true;
}
break;
case T_PlaceHolderInfo:
return walker(((PlaceHolderInfo *) node)->ph_var, context);
case T_RangeTblFunction:
return walker(((RangeTblFunction *) node)->funcexpr, context);
case T_TableSampleClause:
{
TableSampleClause *tsc = (TableSampleClause *) node;
if (expression_tree_walker((Node *) tsc->args,
walker, context))
return true;
if (walker((Node *) tsc->repeatable, context))
return true;
}
break;
case T_TableFunc:
{
TableFunc *tf = (TableFunc *) node;
if (walker(tf->ns_uris, context))
return true;
if (walker(tf->docexpr, context))
return true;
if (walker(tf->rowexpr, context))
return true;
if (walker(tf->colexprs, context))
return true;
if (walker(tf->coldefexprs, context))
return true;
}
break;
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(node));
break;
}
return false;
}三、跟踪分析
测试脚本:
select *
from t_dwxx a
where exists (select b.dwbh from t_grxx b where a.dwbh = b.dwbh);gdb分析:
(gdb) b convert_EXISTS_sublink_to_join
Breakpoint 1 at 0x77a4fe: file subselect.c, line 1426.
(gdb) c
Continuing.
Breakpoint 1, convert_EXISTS_sublink_to_join (root=0x22de828, sublink=0x22292a0, under_not=false, available_rels=0x22defa8)
at subselect.c:1426
1426 Query *parse = root->parse;
(gdb)
#1.root见上一节
#2.sublink,子链接,见本节查询树结构
#3.under_not,false,表示EXISTS
#4.available_rels,可用的rels
(gdb) p *available_rels
$1 = {nwords = 1, words = 0x22defac}
(gdb) p available_rels->words[0]
$3 = 2
(gdb)
...
1511 rtoffset = list_length(parse->rtable);
(gdb)
1512 OffsetVarNodes((Node *) subselect, rtoffset, 0);
(gdb) p rtoffset
$6 = 1
(gdb) step
OffsetVarNodes (node=0x22dee48, offset=1, sublevels_up=0) at rewriteManip.c:428
428 context.offset = offset;
...
#调整subselect->jointree->fromlist->head(类型为RTR)的rtindex,原为1,调整为2
(gdb) p *node
$20 = {type = T_RangeTblRef}
(gdb) p *(RangeTblRef *)node
$21 = {type = T_RangeTblRef, rtindex = 1}
(gdb) n
368 rtr->rtindex += context->offset;
(gdb)
370 return false;
(gdb) p *(RangeTblRef *)node
$22 = {type = T_RangeTblRef, rtindex = 2}
(gdb)
1513 OffsetVarNodes(whereClause, rtoffset, 0);
(gdb)
1520 IncrementVarSublevelsUp((Node *) subselect, -1, 1);
(gdb) p whereClause
$23 = (Node *) 0x22def58
(gdb) p *whereClause
$24 = {type = T_OpExpr}
(gdb) set $arg1=(RelabelType *)((OpExpr *)whereClause)->args->head->data.ptr_value
(gdb) p *$arg1->arg
$32 = {type = T_Var}
#第1个参数是t_dwxx.dwbh
(gdb) p *(Var *)$arg1->arg
$33 = {xpr = {type = T_Var}, varno = 1, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 1,
varnoold = 1, varoattno = 2, location = 72}
(gdb) set $arg2=(RelabelType *)((OpExpr *)whereClause)->args->tail->data.ptr_value
#第2个参数是t_grxx.dwbh
#varno/varnoold从原来的值1调整为2
(gdb) p *(Var *)$arg2->arg
$34 = {xpr = {type = T_Var}, varno = 2, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 2, varoattno = 1, location = 81}
(gdb)
(gdb) n
1521 IncrementVarSublevelsUp(whereClause, -1, 1);
(gdb)
1529 clause_varnos = pull_varnos(whereClause);
#调整varlevelsup为0
(gdb) p *(Var *)$arg1->arg
$36 = {xpr = {type = T_Var}, varno = 1, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 1, varoattno = 2, location = 72}
(gdb) p *(Var *)$arg2->arg
$37 = {xpr = {type = T_Var}, varno = 2, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 2, varoattno = 1, location = 81}
...
#构造返回值
(gdb)
1552 result = makeNode(JoinExpr);
1566 return result;
(gdb)
1567 }
(gdb)
pull_up_sublinks_qual_recurse (root=0x22de828, node=0x22292a0, jtlink1=0x7ffdcabbc918, available_rels1=0x22defa8,
jtlink2=0x0, available_rels2=0x0) at prepjointree.c:404
404 j->larg = *jtlink1;
#上拉成为半连接SEMI JOIN
(gdb) p *(JoinExpr *)jtnode
$65 = {type = T_JoinExpr, jointype = JOIN_SEMI, isNatural = false, larg = 0x22e7118, rarg = 0x22e7438, usingClause = 0x0,
quals = 0x22def58, alias = 0x0, rtindex = 0}
#DONE!到此，相信大家对“PostgreSQL查询优化中怎么上拉子链接”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/35.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
