PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式
这篇文章主要为大家展示了“PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式”这篇文章吧。
站在用户的角度思考问题,与客户深入沟通,找到盐田网站设计与盐田网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:成都做网站、网站制作、企业官网、英文网站、手机端网站、网站推广、域名注册、虚拟主机、企业邮箱。业务覆盖盐田地区。
一、主函数
主函数preprocess_expression先前章节也介绍过,在此函数中调用了生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式(implicit-AND format)等相关子函数。
preprocess_expression
/* * preprocess_expression * Do subquery_planner's preprocessing work for an expression, * which can be a targetlist, a WHERE clause (including JOIN/ON * conditions), a HAVING clause, or a few other things. */ static Node * preprocess_expression(PlannerInfo *root, Node *expr, int kind) { //... /* Expand SubLinks to SubPlans */ if (root->parse->hasSubLinks)//扩展子链接为子计划 expr = SS_process_sublinks(root, expr, (kind == EXPRKIND_QUAL)); /* * XXX do not insert anything here unless you have grokked the comments in * SS_replace_correlation_vars ... */ /* Replace uplevel vars with Param nodes (this IS possible in VALUES) */ if (root->query_level > 1) expr = SS_replace_correlation_vars(root, expr);//使用Param节点替换上层的Vars /* * If it's a qual or havingQual, convert it to implicit-AND format. (We * don't want to do this before eval_const_expressions, since the latter * would be unable to simplify a top-level AND correctly. Also, * SS_process_sublinks expects explicit-AND format.) */ if (kind == EXPRKIND_QUAL)//转换为隐式AND格式 expr = (Node *) make_ands_implicit((Expr *) expr); return expr; }
二、生成子链接执行计划
先前的章节已介绍了上拉子链接的相关处理过程,对于不能上拉的子链接,PG会生成子执行计划.对于会生成常量的子链接,则会把生成的常量记录在Param中,在需要的时候由父查询使用.
例1:以下的子链接,PG会生成子计划,并把子链接的结果物化(Materialize)提升整体性能.
testdb=# explain verbose select * from t_dwxx where dwbh > all (select b.dwbh from t_grxx b); QUERY PLAN --------------------------------------------------------------------------------- Seq Scan on public.t_dwxx (cost=0.00..1498.00 rows=80 width=474) Output: t_dwxx.dwmc, t_dwxx.dwbh, t_dwxx.dwdz Filter: (SubPlan 1) SubPlan 1 -> Materialize (cost=0.00..17.35 rows=490 width=38) Output: b.dwbh -> Seq Scan on public.t_grxx b (cost=0.00..14.90 rows=490 width=38) Output: b.dwbh (8 rows)
例2:以下的子链接,PG会把生成的常量记录在Param中(注意生成的参数:$0)
testdb=# explain verbose select * from t_dwxx a where exists (select max(b.dwbh) from t_grxx b); QUERY PLAN --------------------------------------------------------------------------------- Result (cost=16.14..27.73 rows=160 width=474) Output: a.dwmc, a.dwbh, a.dwdz One-Time Filter: $0 InitPlan 1 (returns $0) -> Aggregate (cost=16.12..16.14 rows=1 width=32) Output: max((b.dwbh)::text) -> Seq Scan on public.t_grxx b (cost=0.00..14.90 rows=490 width=38) Output: b.dwbh, b.grbh, b.xm, b.nl -> Seq Scan on public.t_dwxx a (cost=16.14..27.73 rows=160 width=474) Output: a.dwmc, a.dwbh, a.dwdz (10 rows)
源代码如下:
SS_process_sublinks
/* * Expand SubLinks to SubPlans in the given expression. * * The isQual argument tells whether or not this expression is a WHERE/HAVING * qualifier expression. If it is, any sublinks appearing at top level need * not distinguish FALSE from UNKNOWN return values. */ Node * SS_process_sublinks(PlannerInfo *root, Node *expr, bool isQual) { process_sublinks_context context; context.root = root; context.isTopQual = isQual; return process_sublinks_mutator(expr, &context);//调用XX_mutator函数遍历并处理 } static Node * process_sublinks_mutator(Node *node, process_sublinks_context *context) { process_sublinks_context locContext; locContext.root = context->root; if (node == NULL) return NULL; if (IsA(node, SubLink))//子链接 { SubLink *sublink = (SubLink *) node; Node *testexpr; /* * First, recursively process the lefthand-side expressions, if any. * They're not top-level anymore. */ locContext.isTopQual = false; testexpr = process_sublinks_mutator(sublink->testexpr, &locContext); /* * Now build the SubPlan node and make the expr to return. */ return make_subplan(context->root, (Query *) sublink->subselect, sublink->subLinkType, sublink->subLinkId, testexpr, context->isTopQual);//生成子执行计划,与整体的执行计划类似 } /* * Don't recurse into the arguments of an outer PHV or aggregate here. Any * SubLinks in the arguments have to be dealt with at the outer query * level; they'll be handled when build_subplan collects the PHV or Aggref * into the arguments to be passed down to the current subplan. */ if (IsA(node, PlaceHolderVar)) { if (((PlaceHolderVar *) node)->phlevelsup > 0) return node; } else if (IsA(node, Aggref)) { if (((Aggref *) node)->agglevelsup > 0) return node; } /* * We should never see a SubPlan expression in the input (since this is * the very routine that creates 'em to begin with). We shouldn't find * ourselves invoked directly on a Query, either. */ Assert(!IsA(node, SubPlan)); Assert(!IsA(node, AlternativeSubPlan)); Assert(!IsA(node, Query)); /* * Because make_subplan() could return an AND or OR clause, we have to * take steps to preserve AND/OR flatness of a qual. We assume the input * has been AND/OR flattened and so we need no recursion here. * * (Due to the coding here, we will not get called on the List subnodes of * an AND; and the input is *not* yet in implicit-AND format. So no check * is needed for a bare List.) * * Anywhere within the top-level AND/OR clause structure, we can tell * make_subplan() that NULL and FALSE are interchangeable. So isTopQual * propagates down in both cases. (Note that this is unlike the meaning * of "top level qual" used in most other places in Postgres.) */ if (and_clause(node))//AND语句 { List *newargs = NIL; ListCell *l; /* Still at qual top-level */ locContext.isTopQual = context->isTopQual; foreach(l, ((BoolExpr *) node)->args) { Node *newarg; newarg = process_sublinks_mutator(lfirst(l), &locContext); if (and_clause(newarg)) newargs = list_concat(newargs, ((BoolExpr *) newarg)->args); else newargs = lappend(newargs, newarg); } return (Node *) make_andclause(newargs); } if (or_clause(node))//OR语句 { List *newargs = NIL; ListCell *l; /* Still at qual top-level */ locContext.isTopQual = context->isTopQual; foreach(l, ((BoolExpr *) node)->args) { Node *newarg; newarg = process_sublinks_mutator(lfirst(l), &locContext); if (or_clause(newarg)) newargs = list_concat(newargs, ((BoolExpr *) newarg)->args); else newargs = lappend(newargs, newarg); } return (Node *) make_orclause(newargs); } /* * If we recurse down through anything other than an AND or OR node, we * are definitely not at top qual level anymore. */ locContext.isTopQual = false; return expression_tree_mutator(node, process_sublinks_mutator, (void *) &locContext); }
三、使用Param替换上层变量
SQL例子参考"二、生成子链接执行计划"中的例2,这也是使用Param替代Var的一个例子.
源代码如下:
/* * Replace correlation vars (uplevel vars) with Params. * * Uplevel PlaceHolderVars and aggregates are replaced, too. * * Note: it is critical that this runs immediately after SS_process_sublinks. * Since we do not recurse into the arguments of uplevel PHVs and aggregates, * they will get copied to the appropriate subplan args list in the parent * query with uplevel vars not replaced by Params, but only adjusted in level * (see replace_outer_placeholdervar and replace_outer_agg). That's exactly * what we want for the vars of the parent level --- but if a PHV's or * aggregate's argument contains any further-up variables, they have to be * replaced with Params in their turn. That will happen when the parent level * runs SS_replace_correlation_vars. Therefore it must do so after expanding * its sublinks to subplans. And we don't want any steps in between, else * those steps would never get applied to the argument expressions, either in * the parent or the child level. * * Another fairly tricky thing going on here is the handling of SubLinks in * the arguments of uplevel PHVs/aggregates. Those are not touched inside the * intermediate query level, either. Instead, SS_process_sublinks recurses on * them after copying the PHV or Aggref expression into the parent plan level * (this is actually taken care of in build_subplan). */ Node * SS_replace_correlation_vars(PlannerInfo *root, Node *expr) { /* No setup needed for tree walk, so away we go */ //调用XX_mutator遍历处理 return replace_correlation_vars_mutator(expr, root); } static Node * replace_correlation_vars_mutator(Node *node, PlannerInfo *root) { if (node == NULL) return NULL; if (IsA(node, Var))//Var { if (((Var *) node)->varlevelsup > 0) return (Node *) replace_outer_var(root, (Var *) node);//使用Param替换 } if (IsA(node, PlaceHolderVar)) { if (((PlaceHolderVar *) node)->phlevelsup > 0) return (Node *) replace_outer_placeholdervar(root, (PlaceHolderVar *) node); } if (IsA(node, Aggref)) { if (((Aggref *) node)->agglevelsup > 0) return (Node *) replace_outer_agg(root, (Aggref *) node); } if (IsA(node, GroupingFunc)) { if (((GroupingFunc *) node)->agglevelsup > 0) return (Node *) replace_outer_grouping(root, (GroupingFunc *) node); } return expression_tree_mutator(node, replace_correlation_vars_mutator, (void *) root); } /* * Generate a Param node to replace the given Var, * which is expected to have varlevelsup > 0 (ie, it is not local). */ static Param * replace_outer_var(PlannerInfo *root, Var *var)//构造Param替换Var { Param *retval; int i; Assert(var->varlevelsup > 0 && var->varlevelsup < root->query_level); /* Find the Var in the appropriate plan_params, or add it if not present */ i = assign_param_for_var(root, var); retval = makeNode(Param); retval->paramkind = PARAM_EXEC; retval->paramid = i; retval->paramtype = var->vartype; retval->paramtypmod = var->vartypmod; retval->paramcollid = var->varcollid; retval->location = var->location; return retval; }
四、转换表达式为隐式AND格式
源码如下:
List * make_ands_implicit(Expr *clause) { /* * NB: because the parser sets the qual field to NULL in a query that has * no WHERE clause, we must consider a NULL input clause as TRUE, even * though one might more reasonably think it FALSE. Grumble. If this * causes trouble, consider changing the parser's behavior. */ if (clause == NULL)//如为NULL,返回空指针 return NIL; /* NULL -> NIL list == TRUE */ else if (and_clause((Node *) clause))//AND语句,直接返回AND中的args参数 return ((BoolExpr *) clause)->args; else if (IsA(clause, Const) && !((Const *) clause)->constisnull && DatumGetBool(((Const *) clause)->constvalue)) return NIL; /* 常量TRUE ,返回空指针constant TRUE input -> NIL list */ else return list_make1(clause);//返回List }
以上是“PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
网站题目:PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式
标题来源:http://pwwzsj.com/article/jsides.html