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

This article mainly introduces "the processing logic analysis of the query_planner function in PostgreSQL". In the daily operation, I believe that many people have doubts about the processing logic analysis of the query_planner function in PostgreSQL. The editor consulted all kinds of materials and sorted out the simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "the processing logic analysis of the query_planner function in PostgreSQL". Next, please follow the editor to study!

1. Important data structures

RelOptInfo

After the query statement has been processed by query rewriting / expression simplification / outer join elimination, the query tree Query has been normalized, and the RangeTblEntry (RTE) data structure in the query tree has completed its historical mission, so it can enter the logical optimization processing and use the RelOptInfo data structure at this stage.

Typedef enum RelOptKind {RELOPT_BASEREL,// basic relations (such as base tables / subqueries, etc.) relationships generated by RELOPT_JOINREL,// joins, it should be noted that the results produced by joins can also be regarded as relations RELOPT_OTHER_MEMBER_REL, RELOPT_OTHER_JOINREL, RELOPT_UPPER_REL,// upper layer relations RELOPT_OTHER_UPPER_REL, RELOPT_DEADREL} RelOptKind / * * Is the given relation a simple relation i.e a base or "other" member * relation? * / # define IS_SIMPLE_REL (rel)\ ((rel)-> reloptkind = = RELOPT_BASEREL | |\ (rel)-> reloptkind = = RELOPT_OTHER_MEMBER_REL) / * Is the given relation a join relation? * / # define IS_JOIN_REL (rel)\ ((rel)-> reloptkind = = RELOPT_JOINREL | |\ (rel)-> reloptkind = = RELOPT_OTHER_JOINREL) / * Is the given relation an upper relation? * / # define IS_UPPER_REL (rel)\ ((rel)-> reloptkind = = RELOPT_UPPER_REL | |\ (rel)-> reloptkind = = RELOPT_OTHER_UPPER_REL) / * Is the given relation an "other" relation? * / # define IS_OTHER_REL (rel)\ (rel)-> reloptkind = = RELOPT_OTHER_MEMBER_REL |\ (rel)-> reloptkind = = RELOPT_OTHER_JOINREL | |\ (rel)-> reloptkind = = RELOPT_OTHER_UPPER_REL) typedef struct RelOptInfo {NodeTag type | / / Node identifies RelOptKind reloptkind;//RelOpt type / * all relations included in this RelOptInfo * / Relids relids; / * Relids (rtindex) collection set of base relids (rangetable indexes) * / * size estimates generated by planner * / double rows; / * estimated number of tuples estimated number of result tuples * / * per-relation planner control flags * / bool consider_startup / * do you consider the startup cost? Yes, do you need to keep the path keep cheap-startup-cost paths with low startup cost? * / bool consider_param_startup; / * do you want to consider parameterization? Does the path ditto, for parameterized paths? * / bool consider_parallel; / * consider parallel processing path consider parallel paths? * / * default result targetlist for Paths scanning this relation * / struct PathTarget * reltarget; / * when scanning the Relation, the default result list of Vars/Exprs, cost, width * / * materialization information * / List * pathlist; / * access path list Path structures * / List * ppilist / * ParamPathInfos used in pathlist using parameterized paths in the path list * / List * partial_pathlist; / * partial Paths * / struct Path * cheapest_startup_path;// the lowest startup path struct Path * cheapest_total_path;// the lowest overall path struct Path * cheapest_unique_path;// the path List * cheapest_parameterized_paths with the lowest cost to get a unique value / / the lowest cost parameterization? Path linked list / * parameterization information needed for both base rels and join rels * / * (see also lateral_vars and lateral_referencers) * / Relids direct_lateral_relids; / * uses explicit syntax and depends on Relids rels directly laterally referenced * / Relids lateral_relids The data structure Index relid; / * Relation ID * / Oid reltablespace; / * tablespace containing tablespace * / RTEKind rtekind; / * base table used in / * minimum parameterization of rel * / * information about a base rel (not set for join rels!) * / reloptkind=RELOPT_BASEREL? Subquery? Or functions and so on? RELATION, SUBQUERY, FUNCTION, etc * / AttrNumber min_attr / * smallest attribute number smallest attrno of rel (often parse- > targetList!) * qp_callback is a function to compute query_pathkeys once it's safe to do so * qp_extra is optional extra data to pass to qp_callback * * root is the plan information / tlist is the projection column * qp_callback is the function for calculating query_pathkeys / qp_extra is the function passed to qp_callback * * Note: the PlannerInfo node also includes a query_pathkeys field Which * tells query_planner the sort order that is desired in the final output * plan. This value is * not* available at call time, but is computed by * qp_callback once we have completed merging the query's equivalence classes. * (We cannot construct canonical pathkeys until that's done.) * / RelOptInfo * query_planner (PlannerInfo * root, List * tlist, query_pathkeys_callback qp_callback, void * qp_extra) {Query * parse = root- > index double total_pages of parse;// query tree List * joinlist; RelOptInfo * final_rel;// result Index rti;//RTE / / Total number of pages / * * If the query has an empty join tree, then it's something easy like * "SELECT 2 # 2;" or "INSERT. VALUES () ". Fall through quickly. * / if (parse- > jointree- > fromlist = = NIL) / / simple SQL, no FROM/WHERE statement {/ * We need a dummy joinrel to describe the empty set of baserels * / final_rel = build_empty_join_rel (root); / / create the returned result / * * If query allows parallelism in general, check whether the quals are * parallel-restricted. (We need not check final_rel- > reltarget * because it's empty at this point. Anything parallel-restricted in * the query tlist will be dealt with later.) * / if (root- > glob- > parallelModeOK) / / parallel mode? Final_rel- > consider_parallel = is_parallel_safe (root, parse- > jointree- > quals) / * The only path for it is a trivial Result path * / add_path (final_rel, (Path *) create_result_path (root, final_rel, final_rel- > reltarget, (List *) parse- > jointree- > quals)) / / add an access path / * Select cheapest path (pretty easy in this case...) * / set_cheapest (final_rel); / / Select the best access path / * * We still are required to call qp_callback, in case it's something * like "SELECT 2, ORDER BY 1". * / root- > canon_pathkeys = NIL; (* qp_callback) (root, qp_extra); / / callback function return final_rel;// returns} / / other codes.}

Add_path/set_cheapest

We will introduce it later.

Create_result_path

/ * * create_result_path * Creates a path representing a Result-and-nothing-else plan. * This is only used for degenerate cases, such as a query with an empty * jointree. * / ResultPath * create_result_path (PlannerInfo * root, RelOptInfo * rel, PathTarget * target, List * resconstantqual) {ResultPath * pathnode = makeNode (ResultPath); / / result pathnode- > path.pathtype = Tunable ResultX / scan path type pathnode- > path.parent = rel;// path partentbuild_empty_join_rel pathnode- > path.pathtarget = target;// target column pathnode- > path.param_info = NULL / * there are no other rels... * / pathnode- > path.parallel_aware = false; pathnode- > path.parallel_safe = rel- > consider_parallel; pathnode- > path.parallel_workers = 0 pathnode- / parallel workers number, set to 0 pathnode- > path.pathkeys = NIL;// pathnode- > quals = resconstantqual;// expression / * Hardly worth defining a cost_result () function. Just do it * / pathnode- > path.rows = 1 pathnode- > path.startup_cost = target- > cost.startup; pathnode- > path.total_cost = target- > cost.startup + cpu_tuple_cost + target- > cost.per_tuple; / * * Add cost of qual, if any-but we ignore its selectivity, since our * rowcount estimate should be 1 no matter what the qual is. * / if (resconstantqual) {QualCost qual_cost; cost_qual_eval (& qual_cost, resconstantqual, root); / * resconstantqual is evaluated once at startup * / pathnode- > path.startup_cost + = qual_cost.startup + qual_cost.per_tuple; pathnode- > path.total_cost + = qual_cost.startup + qual_cost.per_tuple;} return pathnode;}

Build_empty_join_rel

/ * * build_empty_join_rel * Build a dummy join relation describing an empty set of base rels. * * This is used for queries with empty FROM clauses, such as "SELECT 2" or * "INSERT INTO foo VALUES (...)". We don't try very hard to make the empty * joinrel completely valid, since no real planning will be done with it-* we just need it to carry a simple Result path out of query_planner (). * / RelOptInfo * build_empty_join_rel (PlannerInfo * root) {RelOptInfo * joinrel; / * The dummy join relation should be the only one... * / Assert (root- > join_rel_list = = NIL); joinrel = makeNode (RelOptInfo); joinrel- > reloptkind = RELOPT_JOINREL; joinrel- > relids = NULL; / * empty set * / joinrel- > rows = 1; / * we produce one row for such cases * / joinrel- > rtekind = RTE_JOIN Joinrel- > reltarget = create_empty_pathtarget (); root- > join_rel_list = lappend (root- > join_rel_list, joinrel); return joinrel;} 3. Tracking analysis (gdb) b query_plannerBreakpoint 1 at 0x76942c: file planmain.c, line 57. (gdb) cContinuing.Breakpoint 1, query_planner (root=0x275c878, tlist=0x277fd78, qp_callback=0x76e97d, qp_extra=0x7ffdd435d490) at planmain.c:5757 Query * parse = root- > parse (gdb) N67 if (parse- > jointree- > fromlist = = NIL) (gdb) 70 final_rel = build_empty_join_rel (root) (gdb) 78 if (root- > glob- > parallelModeOK) # empty RELOPT_JOINREL (gdb) p * final_rel$4 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x0, rows = 1, consider_startup = false, consider_param_startup = false, consider_parallel = false, reltarget = 0x277fda8, pathlist = 0x0, ppilist = 0x0, partial_pathlist = 0x0, cheapest_startup_path = 0x0, cheapest_total_path = 0x0, cheapest_unique_path = 0x0, cheapest_parameterized_paths = cheapest_parameterized_paths Direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 0, reltablespace = 0, rtekind = RTE_JOIN, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0, lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0, subroot = 0x0, subplan_params = 0x0, rel_parallel_workers = 0, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0, fdw_private = fdw_private Unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0, baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x0, has_eclass_joins = false, top_parent_relids = 0x0, part_scheme = 0x0, nparts = 0, boundinfo = 0x0, partition_qual = 0x0, part_rels = 0x0, partexprs = 0x0, nullable_partexprs = 0x0, partitioned_child_rels = 0x0}. (gdb) stepadd_path (parent_rel=0x275cc88 New_path=0x275c498) at pathnode.c:424424 bool accept_new = true / * unless we find a superior old path * / # path (ResultPath) (gdb) p * new_path$6 = {type = T_ResultPath, pathtype = T_Result, parent = 0x275cc88, pathtarget = 0x277fda8, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0, total_cost = 0, pathkeys = 0x0} (gdb) finishRun till exit from # 0 add_path (parent_rel=0x275cc88, new_path=0x275c498) at pathnode.c:425query_planner (root=0x275c878, tlist=0x277fd78) Qp_callback=0x76e97d, qp_extra=0x7ffdd435d490) at planmain.c:8989 set_cheapest (final_rel) ... 98 return final_rel (gdb) 267} # return value (gdb) p * final_rel$8 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x0, rows = 1, consider_startup = false, consider_param_startup = false, consider_parallel = true, reltarget = 0x277fda8, pathlist = 0x277fe68, ppilist = 0x0, partial_pathlist = 0x0, cheapest_startup_path = 0x275c498, cheapest_total_path = 0x275c498, cheapest_unique_path = 0x0, cheapest_parameterized_paths = 0x277feb8, direct_lateral_relids = 0x0, lateral_relids = 0x0, 0x0 = 0 Reltablespace = 0, rtekind = RTE_JOIN, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0, lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0, subroot = 0x0, subplan_params = 0x0, rel_parallel_workers = 0, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0, fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, 0x0 = non_unique_for_rels Baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x0, has_eclass_joins = false, top_parent_relids = 0x0, part_scheme = 0x0, nparts = 0, boundinfo = 0x0, partition_qual = 0x0, part_rels = 0x0, partexprs = 0x0, nullable_partexprs = 0x0, partitioned_child_rels = 0x0} (gdb) p * final_rel- > cheapest_total_path$9 = {type = T_ResultPath, pathtype = T_Result, parent = 0x275cc88, pathtarget = 0x277fda8, param_info = param_info Parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0, total_cost = 0.01, pathkeys = 0x0} (gdb) # DONE! At this point, the study of "processing logic analysis of query_planner functions in PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.