Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

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

2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

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)

< 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 } 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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report