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

< 100where a.c1=f.c1 and b.c1=c.c1 and c.c1 = d.c1 and d.c1 = e.c1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Nested Loop (cost=101.17..2218.24 rows=2 width=42) Output: a.c1, a.c2, b.c1, c.c2, d.c2, e.c1, f.c2 Join Filter: (a.c1 = b.c1) ->

Hash Join (cost=3.25..196.75 rows=100 width=22) Output: a.c1, a.c2, c.c2, c.c1 Hash Cond: (c.c1 = a.c1)-> Seq Scan on public.c (cost=0.00..155.00 rows=10000 width=12) Output: c.c1, c.c2-> Hash (cost=2.00..2.00 rows=100 width=10) Output: a.c1 A.c2-> Seq Scan on public.a (cost=0.00..2.00 rows=100 width=10) Output: a.c1, a.c2-> Materialize (cost=97.92..2014.00 rows=5 width=32) Output: b.c1, d.c2, d.c1, e.c1, f.c2, f.c1-> Hash Join (cost=97.92..2013.97 rows=5 width=32) Output: b.c1 D.c2, d.c1, e.c1, f.c2, f.c1 Hash Cond: (f.c1 = b.c1)-> Seq Scan on public.f (cost=0.00..1541.00 rows=100000 width=13) Output: f.c1, f.c2-> Hash (cost=97.86..97.86 rows=5 width=19) Output: b.c1, d.c2 D.c1, e.c1-> Hash Join (cost=78.10..97.86 rows=5 width=19) Output: b.c1, d.c2, d.c1 E.c1 Hash Cond: (b.c1 = e.c1)-> Seq Scan on public.b (cost=0.00..16.00 rows=1000 width=4) Output: b.c1 B.c2-> Hash (cost=78.04..78.04 rows=5 width=15) Output: d.c2, d.c1, e.c1-> Hash Join (cost=73.24..78.04 rows=5 width=15) Output: d.c2, d.c1 E.c1 Hash Cond: (d.c1 = e.c1)-> Seq Scan on public.d (cost=0.00..4.00 rows=200 width=11) Output: d.c1 D.c2-> Hash (cost=72.00..72.00 rows=99 width=4) Output: e.c1-> Seq Scan on public.e (cost=0.00..72.00 rows=99 width=4) Output: e.c1 Filter: (e.c1

< 100)(38 rows) 测试SQL语句的连接关系:a-b,a-f,b-c,c-d,d-e,e-f 注:根据先前章节的知识,该SQL语句存在等价类{a.c1 b.c1 c.c1 d.c1 e.c1 f.c1} 启动gdb跟踪 (gdb) b join_search_one_levelBreakpoint 1 at 0x755667: file joinrels.c, line 67.(gdb) cContinuing.Breakpoint 1, join_search_one_level (root=0x3006e28, level=2) at joinrels.c:6767 List **joinrels = root->

Join_rel_level

View optimizer information (root)

(gdb) p * root$13 = {type = T_PlannerInfo, parse = 0x2fa3410, glob = 0x3008578, query_level = 1, parent_root = 0x0, plan_params = 0x0, outer_params = 0x0, simple_rel_array = 0x2f510e8, simple_rel_array_size = 9, simple_rte_array = 0x2f51178, all_baserels = 0x2f53dd8, nullable_baserels = 0x0, join_rel_list = 0x2fcb5c8, join_rel_hash = 0x0, join_rel_level = 0x2fcafe8, join_cur_level = 2, init_plans = 0x0 Cte_plan_ids = 0x0, multiexpr_params = 0x0, eq_classes = 0x2f52cb8, canon_pathkeys = 0x2fcb718, left_join_clauses = 0x0, right_join_clauses = 0x0, full_join_clauses = 0x0, join_info_list = 0x0, append_rel_list = 0x0, rowMarks = 0x0, placeholder_list = 0x0, fkey_list = 0x0, query_pathkeys = 0x0, group_pathkeys = 0x0, window_pathkeys = 0x0, distinct_pathkeys = 0x0, sort_pathkeys = 0x0, part_schemes = 0x0, initial_rels = initial_rels Upper_rels = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, upper_targets = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, processed_tlist = 0x2f4f718, grouping_map = 0x0, minmax_aggs = 0x0, planner_cxt = 0x2e87040, total_table_pages = 627, tuple_fraction = 0, limit_tuples =-1, qual_security_level = 0, inhTargetKind = INHKIND_NONE, hasJoinRTEs = true, hasLateralRTEs = false, hasDeletedRTEs = false, false = hasHavingQual, hasHavingQual = false, false = hasHavingQual Wt_param_id =-1, non_recursive_path = 0x0, curOuterRels = 0x0, curOuterParams = 0x0, join_search_private = 0x0, partColsUpdated = false}

Root- > simple_rel_array_size=9, there are 9 elements in the array. From 1 to 8 (the subscript 0 is useless), they are 1-> RTE_RELATION/16775,2- > RTE_RELATION/16778,3- > RTE_JOIN,4- > RTE_RELATION/16781,5- > RTE_RELATION/16784,6- > RTE_RELATION/16787,7- > RTE_RELATION/16790,8- > RTE_JOIN.

Oid | relname-+-16775 | a-> 1 16778 | b-> 2 16781 | c-> 4 16784 | d-> 5 16787 | e-> 6 16790 | f-> 7 (6 rows)

Enter the join_search_one_level function, level=2, and start iterating through joinrels

(gdb) n74 root- > join_cur_level = level; (gdb) 83 foreach (r, joinrels [level-1]) (gdb) n85 RelOptInfo * old_rel = (RelOptInfo *) lfirst (r) (gdb) 87 if (old_rel- > joininfo! = NIL | | old_rel- > has_eclass_joins | | (gdb) 105 if (level = = 2) / * consider remaining initial rels * / (gdb) 106 other_rels = lnext (r); (gdb) 110 make_rels_by_clause_joins (root)

[level=2] enter the make_rels_by_clause_joins function

(gdb) stepmake_rels_by_clause_joins (root=0x3006e28, old_rel=0x3008258, other_rels=0x2fcaf48) at joinrels.c:280280 for_each_cell (l, other_rels)

[level=2] because there is an equivalent class {a.c1 b.c1 c.c1 d.c1 e.c1 f.c1}, this step constructs a new relationship between pairwise connections, ab,ac,ad,ae,af,bc,bd,...

(gdb) n282 RelOptInfo * other_rel = (RelOptInfo *) lfirst (l) (gdb) 284 if (! bms_overlap (old_rel- > relids, other_rel- > relids) & & (gdb) 285 (have_relevant_joinclause (root, old_rel, other_rel) | | (gdb) 284 if (! bms_overlap (old_rel- > relids, other_rel- > relids) & & (gdb) 288 (void) make_join_rel (root, old_rel, other_rel) (gdb) n280 for_each_cell (l, other_rels)

[level=2] after calling the make_join_rel function, look at root- > join_rel_level [2], relids=6=2+4, which is the connection of No. 1 (relation a) and No. 2 (relation b) RTE.

(gdb) p * root- > join_rel_level [2] $6 = {type = T_List, length = 1, head = 0x2fcb5f8, tail = 0x2fcb5f8} (gdb) p * (Node *) root- > join_rel_level [2]-> head- > data.ptr_value$7 = {type = T_RelOptInfo} (gdb) p * (RelOptInfo *) root- > join_rel_level [2]-> head- > data.ptr_value$8 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x2fcb050, rows = 100, consider_startup = false Consider_param_startup = false, consider_parallel = true, reltarget = 0x2fcb068, pathlist = 0x2fcba08, ppilist = 0x0, partial_pathlist = 0x0, cheapest_startup_path = 0x0, cheapest_total_path = 0x0, cheapest_unique_path = 0x0, cheapest_parameterized_paths = 0x0, 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 =-1, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0, fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0, baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 4294967295, joininfo = 0x0, has_eclass_joins = true 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) set $tmp= (RelOptInfo *) root- > join_rel_level [2]-> head- > data.ptr_value (gdb) p * $tmp- > relids- > words$10 = 6

[level=2] continue the cycle, and the next groups are ac,ad,ae,af

(gdb) p * $tmp- > relids- > words$12 = 18 Universe 34 * 66 Universe 130

[level=2] complete the pairwise connection of relation a

(gdb) n291} (gdb) join_search_one_level (root=0x3006e28, level=2) at joinrels.c:8989 {(gdb) n83 foreach (r, joinrels [level-1])

[level=2] similarly, when dealing with b/c/d/e/f, the two pairs form a connection, and there are a total of 15 combinations (6 / (2) * (6-2)!))

(gdb) 83 foreach (r, joinrels [level-1]) (gdb) 142for (k = 2 root-; KTH +) (gdb) p * root- > join_rel_level [2] $44 = {type = T_List, length = 15, head = 0x2fcb5f8, tail = 0x2fd7f78}

[level=2] complete the call to level=2. The relids combination of level2 includes 1, 2, 4, 1, 5, 5, 6, 6, 7, 4, 4, 5, 7, 6, 7, 6, 5, 6, 7, 5, 6, 7, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 5, 6, 6, 6, 6, 6, 5, 6, 6, 5, 6, 6, 6, 5, 6, 6, 5, 6, 6, 5, 6, 6, 5, 6, 6, 5, 6, 6, 6, 6, 6, 5, 6, 6, 5, 6, 5, 6, 5, 6, 5, 6

(gdb) standard_join_search (root=0x3006e28, levels_needed=6, initial_rels=0x2fcaf18) at allpaths.c:27572757 foreach (lc, root- > join_rel_ level [lev])

Start the call to level=3

(gdb) cContinuing.Breakpoint 1, join_search_one_level (root=0x3006e28, level=3) at joinrels.c:6767 List * * joinrels = root- > join_rel_level

[level=3] traverses the RelOptInfo of level=2 (a new relationship formed by a pairwise connection)

(gdb) 83 foreach (r, joinrels [level-1])

[level=3] unlike level=2, choose the initial RelOptInfo to connect instead of the sibling rels

(gdb) 108 other_rels = list_head (joinrels [1])

[level=3] completes the first round of the cycle, root- > join_rel_level [3] there are 4 Node (RelOptInfo) in the linked list, and their relids are 22, 38, 70, 134, that is, 1, 2, 4, 4, 5, 5, 5, 2, 6, 6, 1, 2, 2, 7.

(gdb) p * ((RelOptInfo *) root- > join_rel_level [3]-> head- > data.ptr_value)-> relids- > words$55 = 22 (gdb) p * ((RelOptInfo *) root- > join_rel_level [3]-> head- > next- > data.ptr_value)-> relids- > words$56 = 38 (gdb) p * ((RelOptInfo *) root- > join_rel_level [3]-> head- > next- > next- > data.ptr_value)-> relids- > words$57 = 70 (gdb) p * (RelOptInfo *) ) root- > join_rel_level [3]-> head- > next- > data.ptr_value)-> relids- > words$58 = 134

[level=3] after completing all the cycles, root- > join_rel_level [3] forms a total of 20 connected relids combinations (please refer to the calculation of the mathematical combination), including 1, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 7, 7.

(gdb) p * root- > join_rel_level [3] $68 = {type = T_List, length = 20, head = 0x2fd90d8, tail = 0x2f7f248}

[level=3] try bushy plans, fail to meet the requirements, exit the loop

(gdb) 144int other_level = level-k; (gdb) 150if (k > other_level) 150if (k > other_level) (gdb) n151 break

[level=3] finish calling level=3 and start level 4 call

(gdb) standard_join_search (root=0x3006e28, levels_needed=6, initial_rels=0x2fcaf18) at allpaths.c:27572757 foreach (lc, root- > join_rel_ level [lev]) (gdb) cContinuing.Breakpoint 1, join_search_one_level (root=0x3006e28, level=4) at joinrels.c:6767 List * * joinrels = root- > join_rel_level

[level=4] complete the first round of circular calls, see root- > join_rel_level [4]. The relids is 54 Universe 86x150, that is, 1, 2, 4, 5, 5, 5, 2, 4, 6, 4, 4, 7.

.. 89 {(gdb) 83 foreach (r, joinrels [level-1]) (gdb) p * root- > join_rel_level [4] $69 = {type = T_List, length = 3, head = 0x2f838e0 Tail = 0x30654d8} (gdb) p * ((RelOptInfo *) root- > join_rel_level [4]-> head- > data.ptr_value)-> relids- > words$70 = 54 (gdb) p * ((RelOptInfo *) root- > join_rel_level [4]-> head- > next- > data.ptr_value)-> relids- > words$71 = 86 (gdb) p * ((RelOptInfo *) root- > join_rel_level [4]-> head- > next- > next- > data.ptr_value)-> relids- > words$72 = 150

[level=4] all the cyclic root- > join_rel_level [4] form a connected relids combination, with a total of 15

(gdb) b joinrels.c:142Breakpoint 2 at 0x75576a: file joinrels.c, line 142s. (gdb) cContinuing.Breakpoint 2, join_search_one_level (root=0x3006e28, level=4) at joinrels.c:142142 for (k = 2 level=4; kryptonite +) (gdb) p * root- > join_rel_level [4] $73 = {type = T_List, length = 15, head = 0x2f838e0, tail = 0x307bd78}

[level=4] try bushy plans

... (gdb) p other_level$75 74 = 2 (gdb) p other_level$75 = 2.

[level=4] traversing k-level relations, k=other_level, rel at the same level, pairwise combinations, that is, 1x 2m 3m 4, etc., try to connect pairwise pairs.

(gdb) n153 foreach (r, joiners [k])... (gdb) 168 if (k = = other_level)

[level=4] such as the relationship between relids=6 and relids=48

If (! bms_overlap (old_rel- > relids, new_rel- > relids)) (gdb) 184 if (have_relevant_joinclause (root, old_rel, new_rel) | | (gdb) p * old_rel- > relids- > words$78 = 6 (gdb) p * new_rel- > relids- > words$79 = 48

[level=4] constructs a new relationship, but it cannot be formed through a legal connection or already exists, so it has no effect on root- > join_rel_level [4] (15 Node before and after the call)

(gdb) n187 (void) make_join_rel (root, old_rel, new_rel); (gdb) 173for_each_cell (R2, other_rels) (gdb) p * root- > join_rel_level [4] $80 = {type = T_List, length = 15, head = 0x2f838e0, tail = 0x307bd78}

[level=4] complete bushy plans,root- > join_rel_level [4] the number of elements has not changed

(gdb) cContinuing.Breakpoint 3, join_search_one_level (root=0x3006e28, level=4) at joinrels.c:213213 if (joinrels [level] = = NIL) (gdb) p * root- > join_rel_level [4] $82 = {type = T_List, length = 15, head = 0x2f838e0, tail = 0x307bd78}

[level=5] enter the level=5 call

(gdb) cContinuing.Breakpoint 1, join_search_one_level (root=0x3006e28, level=5) at joinrels.c:6767 List * * joinrels = root- > join_rel_level

[level=5] complete the first round of circular calls, see root- > join_rel_level [5], and the relids is 1180.182, that is, 1182.4x67, respectively.

(gdb) p * root- > join_rel_level [5] $83 = {type = T_List, length = 2, head = 0x30931d0, tail = 0x3093dc8} (gdb) p * ((RelOptInfo *) root- > join_rel_level [5]-> head- > data.ptr_value)-> relids- > words$85 = 118 (gdb) p * ((RelOptInfo *) root- > join_rel_level [5]-> head- > next- > data.ptr_value)-> relids- > words$86 = 182

[level=5] all the cyclic root- > join_rel_level [5] form a connected relids combination, with a total of 6

(gdb) p * root- > join_rel_level [5] $87 = {type = T_List, length = 6, head = 0x30931d0, tail = 0x309d188}

[level=5] try bushy plans, that is, the relationship generated by 2 rels connections, the relationship generated by join 3 rels connections

Complete the call

(gdb) cContinuing.Breakpoint 3, join_search_one_level (root=0x3006e28, level=5) at joinrels.c:213213 if (joinrels [level] = = NIL) (gdb) p * root- > join_rel_level [5] $91 = {type = T_List, length = 6, head = 0x30931d0, tail = 0x309d188}

[level=6] enter the level=6 call

(gdb) cContinuing.Breakpoint 1, join_search_one_level (root=0x3006e28, level=6) at joinrels.c:6767 List * * joinrels = root- > join_rel_level

[level=6] A new relationship is formed after connecting to the rels of level=1

(gdb) cContinuing.Breakpoint 2, join_search_one_level (root=0x3006e28, level=6) at joinrels.c:142142 for (k = 2; gdb +) p * root- > join_rel_level [6] $92 = {type = T_List, length = 1, head = 0x3104cf8, tail = 0x3104cf8}

[level=6] try bushy plans, that is, the relationship generated by 2 rels connections join the relationship generated by 4 rels connections & 3 join 3

Complete the call and generate the result linked list of level=6

(gdb) cContinuing.Breakpoint 3, join_search_one_level (root=0x3006e28, level=6) at joinrels.c:213213 if (joinrels [level] = = NIL) (gdb) p * root- > join_rel_level [6] $93 = {type = T_List, length = 1, head = 0x3104cf8, tail = 0x3104cf8} (gdb) p * (RelOptInfo *) root- > join_rel_level [6]-> head- > data.ptr_value$94 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x3099a80, rows = 2, consider_startup = false Consider_param_startup = false, consider_parallel = true, reltarget = 0x3104a08, pathlist = 0x3104ec0, ppilist = 0x0, partial_pathlist = 0x0, cheapest_startup_path = 0x0, cheapest_total_path = 0x0, cheapest_unique_path = 0x0, cheapest_parameterized_paths = 0x0, 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 =-1, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0, fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0, baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 4294967295, joininfo = 0x0, has_eclass_joins = false Top_parent_relids = 0x0, part_scheme = 0x0, nparts = 0, boundinfo = 0x0, partit

[level=6] View access path

(gdb) set $roi= (RelOptInfo *) root- > join_rel_level [6]-> head- > data.ptr_value (gdb) p * $roi- > pathlist$97 = {type = T_List, length = 1, head = 0x3104ea0, tail = 0x3104ea0} (gdb) p * (Node *) $roi- > pathlist- > head- > data.ptr_value$98 = {type = T_NestPath} (gdb) p * (NestPath *) $roi- > pathlist- > head- > data.ptr_value$99 = {path = {type = T_NestPath, pathtype = pathtype, T_NestLoop = T_NestLoop, T_NestLoop = T_NestLoop Param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 2, startup_cost = 101.1725, total_cost = 2218.23500000001, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false, outerjoinpath = 0x2fccd80, innerjoinpath = 0x3107820, joinrestrictinfo = 0x3107ae0}

The innerjoinpath of the path (the path that constructs the connection inner relationship) and the outerjoinpath (the path that constructs the connection outer relationship)

(gdb) p * $np- > innerjoinpath$109 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x3077c70, pathtarget = 0x3077e80, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 5, startup_cost = 97.922499999999999, total_cost = 2013.997499999999, pathkeys = 0x0} (gdb) p * $np- > outerjoinpath$110 = {type = T_HashPath, pathtype = T_HashJoin, parent = 0x2f54050, pathtarget = 0x2fcbf88, param_info = 0x0, parallel_aware = parallel_aware, parallel_aware = parallel_aware Parallel_workers = 0, rows = 100, startup_cost = 3.25, total_cost = 196.75, pathkeys = 0x0} so far The study on "which is the realization function of constructing connection path using dynamic programming algorithm 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.