How to generate sub-link execution plan in PostgreSQL, replace upper Vars with Param, and convert expression to implicit AND format

This article mainly shows you "how to generate sub-link execution plan in PostgreSQL, replace upper Vars with Param and convert expression to implicit AND format". The content is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn the article "how to generate sub-link execution plan in PostgreSQL, replace upper Vars with Param, and convert expressions to implicit AND format".

1. Main function

The main function preprocess_expression is also described in the previous section, in which related subfunctions such as generating sub-link execution plan, replacing upper-level Vars with Param and converting expression to implicit AND format (implicit-AND format) are called.

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) / / expand the sublink to the subplan 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); / / replace the upper Vars / * * If it's a qual or havingQual, convert it to implicit-AND format with Param node. 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) / / convert to implicit AND format expr = (Node *) make_ands_implicit ((Expr *) expr); return expr;} II. Generate sub-link execution plan

The previous section has introduced the process of pull-up sub-links. For sub-links that cannot be pulled up, PG will generate sub-execution plans. For child links that generate constants, the generated constants are recorded in Param and used by the parent query when needed.

Example 1: with the following sublinks, PG generates subplans and Materialize the results of the sublinks to improve overall performance.

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)

Example 2: in the following sublink, PG will record the generated constant in Param (note the generated parameter: $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)

The source code is as follows:

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); / / call the XX_mutator function to traverse and process} 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 = (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) / / generate sub-execution plan, which is similar to the overall execution plan} / * * 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 statement {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 statement {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); 3. Replace the upper variables with Param

The SQL example refers to example 2 in the second generation sub-link execution plan, which is also an example of using Param instead of Var.

The source code is as follows:

/ * 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 * / / call XX_mutator traversal processing 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) / replace} if (IsA (node, PlaceHolderVar)) {if (PlaceHolderVar *) node)-> phlevelsup > 0) return (Node *) replace_outer_placeholdervar (root, (PlaceHolderVar *) node) with Param } 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) / / construct Param to replace Var {Param * retval; int i; Assert (var- > varlevelsup > 0 & & var- > varlevelsup)

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 } IV. Conversion expression to implicit AND format

The source code is as follows

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) / / if NULL, return null pointer return NIL; / * NULL-> NIL list = = TRUE * / else if (and_clause ((Node *) clause)) / / AND statement, and directly return the args parameter return ((BoolExpr *) clause)-> args in AND Else if (IsA (clause, Const) & &! ((Const *) clause)-> constisnull & & DatumGetBool (Const *) clause)-> constvalue)) return NIL; / * constant TRUE, return null pointer constant TRUE input-> NIL list * / else return list_make1 (clause) / / return List} these are all the contents of the article "how to generate a sublink execution plan in PostgreSQL, replace the upper Vars with Param, and convert expressions to implicit AND format". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!