这篇文章主要介绍“PostgreSQL查询语句分析”，在日常操作中，相信很多人在PostgreSQL查询语句分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL查询语句分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
子查询上拉在函数pull_up_subqueries中实现,该函数调用pull_up_subqueries_recurse函数递归实现子查询上拉.
pull_up_subqueries
void
pull_up_subqueries(PlannerInfo *root)
{
Assert(IsA(root->parse->jointree, FromExpr));
root->hasDeletedRTEs = false;
root->parse->jointree = (FromExpr *)
pull_up_subqueries_recurse(root, (Node *) root->parse->jointree,
NULL, NULL, NULL, false);
if (root->hasDeletedRTEs)
root->parse->jointree = (FromExpr *)
pull_up_subqueries_cleanup((Node *) root->parse->jointree);
Assert(IsA(root->parse->jointree, FromExpr));
}pull_up_subqueries_recurse
static Node *
pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
AppendRelInfo *containing_appendrel,
bool deletion_ok)
{
Assert(jtnode != NULL);
if (IsA(jtnode, RangeTblRef))//如为RTR
{
//获取该RTR相应的RTE
int varno = ((RangeTblRef *) jtnode)->rtindex;
RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable);
if (rte->rtekind == RTE_SUBQUERY &&
is_simple_subquery(rte->subquery, rte,
lowest_outer_join, deletion_ok) &&
(containing_appendrel == NULL ||
is_safe_append_member(rte->subquery)))//简单子查询
return pull_up_simple_subquery(root, jtnode, rte,
lowest_outer_join,
lowest_nulling_outer_join,
containing_appendrel,
deletion_ok);
if (rte->rtekind == RTE_SUBQUERY &&
is_simple_union_all(rte->subquery))//UNION ALL子查询
return pull_up_simple_union_all(root, jtnode, rte);
if (rte->rtekind == RTE_VALUES &&
lowest_outer_join == NULL &&
containing_appendrel == NULL &&
is_simple_values(root, rte, deletion_ok))//VALUES子查询
return pull_up_simple_values(root, jtnode, rte);
}
else if (IsA(jtnode, FromExpr))//如为FromExpr
{
FromExpr *f = (FromExpr *) jtnode;
bool have_undeleted_child = false;
ListCell *l;
Assert(containing_appendrel == NULL);
if (f->quals)
deletion_ok = false;
foreach(l, f->fromlist)
{
bool sub_deletion_ok = (deletion_ok ||
have_undeleted_child ||
lnext(l) != NULL ||
f == root->parse->jointree);
lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),
lowest_outer_join,
lowest_nulling_outer_join,
NULL,
sub_deletion_ok);//递归调用
if (lfirst(l) != NULL)
have_undeleted_child = true;
}
if (deletion_ok && !have_undeleted_child)
{
root->hasDeletedRTEs = true;
return NULL;
}
}
else if (IsA(jtnode, JoinExpr))//如为JoinExpr
{
JoinExpr *j = (JoinExpr *) jtnode;
Assert(containing_appendrel == NULL);
switch (j->jointype)
{
case JOIN_INNER:
j->larg = pull_up_subqueries_recurse(root, j->larg,
lowest_outer_join,
lowest_nulling_outer_join,
NULL,
true);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
lowest_outer_join,
lowest_nulling_outer_join,
NULL,
j->larg != NULL);
break;
case JOIN_LEFT:
case JOIN_SEMI:
case JOIN_ANTI:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
lowest_nulling_outer_join,
NULL,
false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
NULL,
false);
break;
case JOIN_FULL:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
NULL,
false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
NULL,
false);
break;
case JOIN_RIGHT:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
NULL,
false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
lowest_nulling_outer_join,
NULL,
false);
break;
default:
elog(ERROR, "unrecognized join type: %d",
(int) j->jointype);
break;
}
}
else
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(jtnode));
return jtnode;
}
static Node *
pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
AppendRelInfo *containing_appendrel,
bool deletion_ok)
{
Query *parse = root->parse;//查询树
int varno = ((RangeTblRef *) jtnode)->rtindex;//RTR中的index,指向rtable中的位置
Query *subquery;//子查询
PlannerInfo *subroot;//子root
int rtoffset;//rtable中的偏移
pullup_replace_vars_context rvcontext;//上下文
ListCell *lc;//临时变量
subquery = copyObject(rte->subquery);//子查询
//为子查询构建PlannerInfo,尝试对此子查询进行上拉
subroot = makeNode(PlannerInfo);
subroot->parse = subquery;
subroot->glob = root->glob;
subroot->query_level = root->query_level;
subroot->parent_root = root->parent_root;
subroot->plan_params = NIL;
subroot->outer_params = NULL;
subroot->planner_cxt = CurrentMemoryContext;
subroot->init_plans = NIL;
subroot->cte_plan_ids = NIL;
subroot->multiexpr_params = NIL;
subroot->eq_classes = NIL;
subroot->append_rel_list = NIL;
subroot->rowMarks = NIL;
memset(subroot->upper_rels, 0, sizeof(subroot->upper_rels));
memset(subroot->upper_targets, 0, sizeof(subroot->upper_targets));
subroot->processed_tlist = NIL;
subroot->grouping_map = NULL;
subroot->minmax_aggs = NIL;
subroot->qual_security_level = 0;
subroot->inhTargetKind = INHKIND_NONE;
subroot->hasRecursion = false;
subroot->wt_param_id = -1;
subroot->non_recursive_path = NULL;
Assert(subquery->cteList == NIL);
if (subquery->hasSubLinks)//子链接?上拉子链接
pull_up_sublinks(subroot);
inline_set_returning_functions(subroot);
pull_up_subqueries(subroot);//递归上拉子查询中的子查询
//子查询中子链接&子查询上拉后,再次检查,确保本次上拉没有问题
if (is_simple_subquery(subquery, rte,
lowest_outer_join, deletion_ok) &&
(containing_appendrel == NULL || is_safe_append_member(subquery)))
{
}
else
{
return jtnode;
}
//子查询中的targetList扁平化处理
subquery->targetList = (List *)
flatten_join_alias_vars(subroot, (Node *) subquery->targetList);
//调整Var.varno
rtoffset = list_length(parse->rtable);
OffsetVarNodes((Node *) subquery, rtoffset, 0);
OffsetVarNodes((Node *) subroot->append_rel_list, rtoffset, 0);
//调整Var.varlevelsup
IncrementVarSublevelsUp((Node *) subquery, -1, 1);
IncrementVarSublevelsUp((Node *) subroot->append_rel_list, -1, 1);
rvcontext.root = root;
rvcontext.targetlist = subquery->targetList;
rvcontext.target_rte = rte;
if (rte->lateral)
rvcontext.relids = get_relids_in_jointree((Node *) subquery->jointree,
true);
else
rvcontext.relids = NULL;
rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
rvcontext.varno = varno;
rvcontext.need_phvs = false;
rvcontext.wrap_non_vars = false;
rvcontext.rv_cache = palloc0((list_length(subquery->targetList) + 1) *
sizeof(Node *));
if (lowest_nulling_outer_join != NULL)
rvcontext.need_phvs = true;
if (containing_appendrel != NULL)
{
rvcontext.need_phvs = true;
rvcontext.wrap_non_vars = true;
}
if (parse->groupingSets)
{
rvcontext.need_phvs = true;
rvcontext.wrap_non_vars = true;
}
//处理投影
parse->targetList = (List *)
pullup_replace_vars((Node *) parse->targetList, &rvcontext);
parse->returningList = (List *)
pullup_replace_vars((Node *) parse->returningList, &rvcontext);
if (parse->onConflict)
{
parse->onConflict->onConflictSet = (List *)
pullup_replace_vars((Node *) parse->onConflict->onConflictSet,
&rvcontext);
parse->onConflict->onConflictWhere =
pullup_replace_vars(parse->onConflict->onConflictWhere,
&rvcontext);
}
replace_vars_in_jointree((Node *) parse->jointree, &rvcontext,
lowest_nulling_outer_join);
Assert(parse->setOperations == NULL);
parse->havingQual = pullup_replace_vars(parse->havingQual, &rvcontext);
//处理appendrels中的信息
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
bool save_need_phvs = rvcontext.need_phvs;
if (appinfo == containing_appendrel)
rvcontext.need_phvs = false;
appinfo->translated_vars = (List *)
pullup_replace_vars((Node *) appinfo->translated_vars, &rvcontext);
rvcontext.need_phvs = save_need_phvs;
}
//处理RTE中类型为RTE_JOIN的节点
foreach(lc, parse->rtable)
{
RangeTblEntry *otherrte = (RangeTblEntry *) lfirst(lc);
if (otherrte->rtekind == RTE_JOIN)
otherrte->joinaliasvars = (List *)
pullup_replace_vars((Node *) otherrte->joinaliasvars,
&rvcontext);
}
//LATERAL支持
if (rte->lateral)
{
foreach(lc, subquery->rtable)
{
RangeTblEntry *child_rte = (RangeTblEntry *) lfirst(lc);
switch (child_rte->rtekind)
{
case RTE_RELATION:
if (child_rte->tablesample)
child_rte->lateral = true;
break;
case RTE_SUBQUERY:
case RTE_FUNCTION:
case RTE_VALUES:
case RTE_TABLEFUNC:
child_rte->lateral = true;
break;
case RTE_JOIN:
case RTE_CTE:
case RTE_NAMEDTUPLESTORE:
break;
}
}
}
//子查询中的RTE填充至父查询中
parse->rtable = list_concat(parse->rtable, subquery->rtable);
parse->rowMarks = list_concat(parse->rowMarks, subquery->rowMarks);
if (parse->hasSubLinks || root->glob->lastPHId != 0 ||
root->append_rel_list)
{
Relids subrelids;
subrelids = get_relids_in_jointree((Node *) subquery->jointree, false);
substitute_multiple_relids((Node *) parse, varno, subrelids);
fix_append_rel_relids(root->append_rel_list, varno, subrelids);
}
root->append_rel_list = list_concat(root->append_rel_list,
subroot->append_rel_list);
Assert(root->join_info_list == NIL);
Assert(subroot->join_info_list == NIL);
Assert(root->placeholder_list == NIL);
Assert(subroot->placeholder_list == NIL);
parse->hasSubLinks |= subquery->hasSubLinks;
parse->hasRowSecurity |= subquery->hasRowSecurity;
if (subquery->jointree->fromlist == NIL)
{
Assert(deletion_ok);
Assert(subquery->jointree->quals == NULL);
root->hasDeletedRTEs = true;
return NULL;
}
return (Node *) subquery->jointree;
}is_simple_subquery
static bool
is_simple_subquery(Query *subquery, RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
bool deletion_ok)
{
if (!IsA(subquery, Query) ||
subquery->commandType != CMD_SELECT)
elog(ERROR, "subquery is bogus");
if (subquery->setOperations)
return false;//存在集合操作
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;//存在聚合函数/窗口函数...
if (rte->security_barrier)
return false;//
if (subquery->jointree->fromlist == NIL &&
(subquery->jointree->quals != NULL ||
!deletion_ok ||
lowest_outer_join != NULL))
return false;
if (rte->lateral)
{
bool restricted;
Relids safe_upper_varnos;
if (lowest_outer_join != NULL)
{
restricted = true;
safe_upper_varnos = get_relids_in_jointree((Node *) lowest_outer_join,
true);
}
else
{
restricted = false;
safe_upper_varnos = NULL;
}
if (jointree_contains_lateral_outer_refs((Node *) subquery->jointree,
restricted, safe_upper_varnos))
return false;
if (lowest_outer_join != NULL)
{
Relids lvarnos = pull_varnos_of_level((Node *) subquery->targetList, 1);
if (!bms_is_subset(lvarnos, safe_upper_varnos))
return false;
}
}
if (contain_volatile_functions((Node *) subquery->targetList))
return false;//存在易变函数
return true;
}pull_up_subqueries_cleanup
static Node *
pull_up_subqueries_cleanup(Node *jtnode)
{
Assert(jtnode != NULL);
if (IsA(jtnode, RangeTblRef))
{
}
else if (IsA(jtnode, FromExpr))
{
FromExpr *f = (FromExpr *) jtnode;
List *newfrom = NIL;
ListCell *l;
foreach(l, f->fromlist)
{
Node *child = (Node *) lfirst(l);
if (child == NULL)
continue;
child = pull_up_subqueries_cleanup(child);
newfrom = lappend(newfrom, child);
}
f->fromlist = newfrom;
}
else if (IsA(jtnode, JoinExpr))
{
JoinExpr *j = (JoinExpr *) jtnode;
if (j->larg)
j->larg = pull_up_subqueries_cleanup(j->larg);
if (j->rarg)
j->rarg = pull_up_subqueries_cleanup(j->rarg);
if (j->larg == NULL)
{
Assert(j->jointype == JOIN_INNER);
Assert(j->rarg != NULL);
return (Node *) makeFromExpr(list_make1(j->rarg), j->quals);
}
else if (j->rarg == NULL)
{
Assert(j->jointype == JOIN_INNER);
return (Node *) makeFromExpr(list_make1(j->larg), j->quals);
}
}
else
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(jtnode));
return jtnode;
}三、跟踪分析
gdb跟踪分析:
(gdb) b pull_up_subqueries
Breakpoint 1 at 0x77d63b: file prepjointree.c, line 612.
(gdb) c
Continuing.
Breakpoint 1, pull_up_subqueries (root=0x1d092d0) at prepjointree.c:612
612 root->hasDeletedRTEs = false;
(gdb)
#输入参数,root参见上拉子链接中的说明
#进入pull_up_subqueries_recurse
(gdb) step
615 pull_up_subqueries_recurse(root, (Node *) root->parse->jointree,
(gdb) step
pull_up_subqueries_recurse (root=0x1d092d0, jtnode=0x1d092a0, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=false) at prepjointree.c:680
680 if (IsA(jtnode, RangeTblRef))
(gdb)
#输入参数:
#1.root,同pull_up_subqueries
#2.jtnode,Query查询树
#3/4/5.lowest_outer_join/lowest_nulling_outer_join/containing_appendrel均为NULL
#6.deletion_ok,false
...
(gdb) p *jtnode
$2 = {type = T_FromExpr}
#FromExpr,进入相应的分支
...
#递归调用pull_up_subqueries_recurse
(gdb)
763 lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),
(gdb) step
pull_up_subqueries_recurse (root=0x1d092d0, jtnode=0x1c73078, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:680
680 if (IsA(jtnode, RangeTblRef))
#注意:这时候的jtnode类型为RangeTblRef
(gdb) n
682 int varno = ((RangeTblRef *) jtnode)->rtindex;
(gdb)
683 RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable);
(gdb)
692 if (rte->rtekind == RTE_SUBQUERY &&
(gdb) p varno
$4 = 1
#rtable中第1个RTE是父查询的Relation(即t_dwxx),不是子查询
(gdb) p *rte
$5 = {type = T_RangeTblEntry, rtekind = RTE_RELATION, relid = 16394, relkind = 114 'r', tablesample = 0x0, subquery = 0x0,
security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0, functions = 0x0, funcordinality = false,
tablefunc = 0x0, values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false, coltypes = 0x0,
coltypmods = 0x0, colcollations = 0x0, enrname = 0x0, enrtuples = 0, alias = 0x1c4fd58, eref = 0x1c72c98,
lateral = false, inh = true, inFromCl = true, requiredPerms = 2, checkAsUser = 0, selectedCols = 0x1d07698,
insertedCols = 0x0, updatedCols = 0x0, securityQuals = 0x0}
(gdb) n
712 if (rte->rtekind == RTE_SUBQUERY &&
(gdb)
722 if (rte->rtekind == RTE_VALUES &&
(gdb)
852 return jtnode;
(gdb)
...
#rtable中的第2个元素,类型为RTE_SUBQUERY
(gdb) step
pull_up_subqueries_recurse (root=0x1d092d0, jtnode=0x1d07358, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:680
680 if (IsA(jtnode, RangeTblRef))
(gdb) n
682 int varno = ((RangeTblRef *) jtnode)->rtindex;
(gdb)
(gdb) p *rte
$7 = {type = T_RangeTblEntry, rtekind = RTE_SUBQUERY, relid = 0, relkind = 0 '\000', tablesample = 0x0,
subquery = 0x1c72968, security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0, functions = 0x0,
funcordinality = false, tablefunc = 0x0, values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false,
coltypes = 0x0, coltypmods = 0x0, colcollations = 0x0, enrname = 0x0, enrtuples = 0, alias = 0x1c50548, eref = 0x1d071a0,
lateral = false, inh = false, inFromCl = true, requiredPerms = 0, checkAsUser = 0, selectedCols = 0x0,
insertedCols = 0x0, updatedCols = 0x0, securityQuals = 0x0}
...
#进入pull_up_simple_subquery
697 return pull_up_simple_subquery(root, jtnode, rte,
(gdb) step
pull_up_simple_subquery (root=0x1d092d0, jtnode=0x1d07358, rte=0x1c72a78, lowest_outer_join=0x0,
lowest_nulling_outer_join=0x0, containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:874
874 Query *parse = root->parse;
...
1247 return (Node *) subquery->jointree;
(gdb)
1248 }
(gdb)
pull_up_subqueries_recurse (root=0x1d09838, jtnode=0x1c736e0, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:853
853 }
(gdb)到此，关于“PostgreSQL查询语句分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
