本篇内容介绍了“PostgreSQL查询优化对表达式预处理中连接Var溯源的过程是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
处理逻辑在主函数subquery_planner中通过调用flatten_join_alias_vars函数实现，该函数位于src/backend/optimizer/util/var.c文件中。
一、基本概念
连接Var溯源，意思是把连接产生的中间结果（中间结果也是Relation关系的一种）的投影替换为实际存在的关系的列（在PG中通过Var表示）。
如下面的SQL语句：
select a.*,b.grbh,b.je
from t_dwxx a,
lateral (select t1.dwbh,t1.grbh,t2.je
from t_grxx t1 inner join t_jfxx t2
on t1.dwbh = a.dwbh
and t1.grbh = t2.grbh) b;b与a连接运算,查询树Query中的投影b.grbh和b.je这两列如需依赖关系b(子查询产生的中间结果),则需要把中间关系的投影列替换为实际的Relation的投影列,即t_grxx和t_jfxx的数据列.
PG源码中的注释：
if (root->hasJoinRTEs &&
!(kind == EXPRKIND_RTFUNC ||
kind == EXPRKIND_VALUES ||
kind == EXPRKIND_TABLESAMPLE ||
kind == EXPRKIND_TABLEFUNC))
expr = flatten_join_alias_vars(root, expr);二、源码解读
flatten_join_alias_vars
Node *
flatten_join_alias_vars(PlannerInfo *root, Node *node)
{
flatten_join_alias_vars_context context;
context.root = root;
context.sublevels_up = 0;
context.possible_sublink = root->parse->hasSubLinks;
context.inserted_sublink = root->parse->hasSubLinks;
//调用flatten_join_alias_vars_mutator处理Vars
return flatten_join_alias_vars_mutator(node, &context);
}
static Node *
flatten_join_alias_vars_mutator(Node *node,
flatten_join_alias_vars_context *context)
{
if (node == NULL)
return NULL;
if (IsA(node, Var))//Var类型
{
Var *var = (Var *) node;
RangeTblEntry *rte;
Node *newvar;
if (var->varlevelsup != context->sublevels_up)
return node;
rte = rt_fetch(var->varno, context->root->parse->rtable);
if (rte->rtekind != RTE_JOIN)
return node;
//在rte->rtekind == RTE_JOIN时才需要处理
if (var->varattno == InvalidAttrNumber)
{
RowExpr *rowexpr;
List *fields = NIL;
List *colnames = NIL;
AttrNumber attnum;
ListCell *lv;
ListCell *ln;
attnum = 0;
Assert(list_length(rte->joinaliasvars) == list_length(rte->eref->colnames));
forboth(lv, rte->joinaliasvars, ln, rte->eref->colnames)
{
newvar = (Node *) lfirst(lv);
attnum++;
if (newvar == NULL)
continue;
newvar = copyObject(newvar);
if (context->sublevels_up != 0)
IncrementVarSublevelsUp(newvar, context->sublevels_up, 0);
if (IsA(newvar, Var))
((Var *) newvar)->location = var->location;
newvar = flatten_join_alias_vars_mutator(newvar, context);
fields = lappend(fields, newvar);
colnames = lappend(colnames, copyObject((Node *) lfirst(ln)));
}
rowexpr = makeNode(RowExpr);
rowexpr->args = fields;
rowexpr->row_typeid = var->vartype;
rowexpr->row_format = COERCE_IMPLICIT_CAST;
rowexpr->colnames = colnames;
rowexpr->location = var->location;
return (Node *) rowexpr;
}
//扩展join alias Var
Assert(var->varattno > 0);
newvar = (Node *) list_nth(rte->joinaliasvars, var->varattno - 1);
Assert(newvar != NULL);
newvar = copyObject(newvar);
if (context->sublevels_up != 0)
IncrementVarSublevelsUp(newvar, context->sublevels_up, 0);
if (IsA(newvar, Var))
((Var *) newvar)->location = var->location;
newvar = flatten_join_alias_vars_mutator(newvar, context);
if (context->possible_sublink && !context->inserted_sublink)
context->inserted_sublink = checkExprHasSubLink(newvar);
return newvar;
}
if (IsA(node, PlaceHolderVar))//占位符
{
PlaceHolderVar *phv;
phv = (PlaceHolderVar *) expression_tree_mutator(node,
flatten_join_alias_vars_mutator,
(void *) context);
if (phv->phlevelsup == context->sublevels_up)
{
phv->phrels = alias_relid_set(context->root,
phv->phrels);
}
return (Node *) phv;
}
if (IsA(node, Query))//查询树
{
Query *newnode;
bool save_inserted_sublink;
context->sublevels_up++;
save_inserted_sublink = context->inserted_sublink;
context->inserted_sublink = ((Query *) node)->hasSubLinks;
newnode = query_tree_mutator((Query *) node,
flatten_join_alias_vars_mutator,
(void *) context,
QTW_IGNORE_JOINALIASES);
newnode->hasSubLinks |= context->inserted_sublink;
context->inserted_sublink = save_inserted_sublink;
context->sublevels_up--;
return (Node *) newnode;
}
Assert(!IsA(node, SubPlan));
Assert(!IsA(node, SpecialJoinInfo));
Assert(!IsA(node, PlaceHolderInfo));
Assert(!IsA(node, MinMaxAggInfo));
//其他表达式
return expression_tree_mutator(node, flatten_join_alias_vars_mutator,
(void *) context);
}query_tree_mutator
Query *
query_tree_mutator(Query *query,
Node *(*mutator) (),
void *context,
int flags)//遍历查询树
{
Assert(query != NULL && IsA(query, Query));
if (!(flags & QTW_DONT_COPY_QUERY))
{
Query *newquery;
FLATCOPY(newquery, query, Query);
query = newquery;
}
MUTATE(query->targetList, query->targetList, List *);//投影列
MUTATE(query->withCheckOptions, query->withCheckOptions, List *);
MUTATE(query->onConflict, query->onConflict, OnConflictExpr *);
MUTATE(query->returningList, query->returningList, List *);
MUTATE(query->jointree, query->jointree, FromExpr *);
MUTATE(query->setOperations, query->setOperations, Node *);
MUTATE(query->havingQual, query->havingQual, Node *);
MUTATE(query->limitOffset, query->limitOffset, Node *);
MUTATE(query->limitCount, query->limitCount, Node *);
if (!(flags & QTW_IGNORE_CTE_SUBQUERIES))
MUTATE(query->cteList, query->cteList, List *);
else
query->cteList = copyObject(query->cteList);
query->rtable = range_table_mutator(query->rtable,
mutator, context, flags);//RTE
return query;
}range_table_mutator
List *
range_table_mutator(List *rtable,
Node *(*mutator) (),
void *context,
int flags)
{
List *newrt = NIL;
ListCell *rt;
foreach(rt, rtable)//遍历RTE
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
RangeTblEntry *newrte;
FLATCOPY(newrte, rte, RangeTblEntry);
switch (rte->rtekind)
{
case RTE_RELATION:
MUTATE(newrte->tablesample, rte->tablesample,
TableSampleClause *);
break;
case RTE_CTE:
case RTE_NAMEDTUPLESTORE:
break;
case RTE_SUBQUERY:
if (!(flags & QTW_IGNORE_RT_SUBQUERIES))
{
CHECKFLATCOPY(newrte->subquery, rte->subquery, Query);
MUTATE(newrte->subquery, newrte->subquery, Query *);//遍历处理子查询
}
else
{
newrte->subquery = copyObject(rte->subquery);
}
break;
case RTE_JOIN://连接,遍历处理joinaliasvars
if (!(flags & QTW_IGNORE_JOINALIASES))
MUTATE(newrte->joinaliasvars, rte->joinaliasvars, List *);
else
{
newrte->joinaliasvars = copyObject(rte->joinaliasvars);
}
break;
case RTE_FUNCTION:
MUTATE(newrte->functions, rte->functions, List *);
break;
case RTE_TABLEFUNC:
MUTATE(newrte->tablefunc, rte->tablefunc, TableFunc *);
break;
case RTE_VALUES:
MUTATE(newrte->values_lists, rte->values_lists, List *);
break;
}
MUTATE(newrte->securityQuals, rte->securityQuals, List *);
newrt = lappend(newrt, newrte);
}
return newrt;
}三、跟踪分析
在PG11中,没有进入"Expand join alias reference"的实现逻辑,猜测在上拉子查询的时候已作优化.
“PostgreSQL查询优化对表达式预处理中连接Var溯源的过程是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
