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) 优化器选择了2 rels + 4 rels的连接模式,跟踪重点考察bushy plans的执行情况. 启动gdb,设置断点,只考察level=6的情况 (gdb) b join_search_one_levelBreakpoint 2 at 0x7b0289: file joinrels.c, line 67.(gdb) cContinuing....(gdb) cContinuing.Breakpoint 2, join_search_one_level (root=0x241ca38, level=6) at joinrels.c:6767 List **joinrels = root->

Join_rel_level

Complete the call of 5 (rels) + 1 (rels)

(gdb) b joinrels.c:142Breakpoint 3 at 0x7b03c4: file joinrels.c, line 142. (gdb) cContinuing.Breakpoint 3, join_search_one_level (root=0x241ca38, level=6) at joinrels.c:142142 for

Check root- > join_rel_level [6]

(gdb) p * root- > join_rel_level [6] $1 = {type = T_List, length = 1, head = 0x24c8468, tail = 0x24c8468}

View the RelOptInfo in the linked list

(gdb) set $roi= (RelOptInfo *) root- > join_rel_level [6]-> head- > data.ptr_value (gdb) p * $roi$3 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x1eb8330, rows = 2, consider_startup = false, consider_param_startup = false, consider_parallel = true, reltarget = 0x1f25ac8, pathlist = 0x1f25f80, ppilist = 0x0, partial_pathlist = 0x0, cheapest_startup_path = 0x0, cheapest_total_path = 0x0, cheapest_unique_path = 0x0, 0x0 = cheapest_unique_path 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, 0x0 = fdw_private 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, partition_qual = 0x0, part_rels = 0x0, partexprs = 0x0, nullable_partexprs = 0x0, partitioned_child_rels = 0x0}

View the pathlist of the RelOptInfo

(gdb) p * $roi- > pathlist$4 = {type = T_List, length = 1, head = 0x1f25f60, tail = 0x1f25f60} (gdb) p * (Node *) $roi- > pathlist- > head- > data.ptr_value$5 = {type = T_NestPath} (gdb) set $np= (NestPath *) $roi- > pathlist- > head- > data.ptr_value (gdb) p * (NestPath *) $np$5 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x1f258b8, 0x1f258b8 = 0x1f258b8, pathtarget = pathtarget, 0x1f25ac8 = 0x1f25ac8, 0x1f25ac8 = 0x1f25ac8 Parallel_workers = 0, rows = 2, startup_cost = 290.57499999999999, total_cost = 2216.137499999999998, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false, outerjoinpath = 0x1f07c00, innerjoinpath = 0x1f27c40, joinrestrictinfo = 0x1f27e60}

View the appearance and internal access path of the connection

(gdb) p * $np- > outerjoinpath$6 = {type = T_Path, pathtype = T_SeqScan, parent = 0x1e228e8, pathtarget = 0x1f04bc0, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100, startup_cost = 0, total_cost = 2, pathkeys = 0x0} (gdb) p * $np- > innerjoinpath$7 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x1ebb538, pathtarget = 0x1ebb748, param_info = 0x0, 0x0 = parallel_aware, parallel_aware = false, parallel_aware = 0 Rows = 5, startup_cost = 290.5749999999999999, total_cost = 2206.6500000000001, pathkeys = 0x0}

Let's try bushy plans, that is, (2 rels+ 4 rels+ 4 rels) or (3 rels+ 3 rels), focusing on the combination of ac and bdef

(gdb) b joinrels.c:156Breakpoint 3 at 0x7557df: file joinrels.c, (gdb) cContinuing.Breakpoint 3, join_search_one_level (root=0x1e214b8, level=6) at joinrels.c:164164 if (old_rel- > joininfo = = NIL & &! old_rel- > has_eclass_joins & & (gdb) p * old_rel- > relids- > words$13 = 18

Enter the make_join_rel function

173for_each_cell (R2, other_rels) (gdb) 175 RelOptInfo * new_rel = (RelOptInfo *) lfirst (R2) (gdb) 177if (! bms_overlap (old_rel- > relids, new_rel- > relids)) (gdb) 184if (have_relevant_joinclause (root, old_rel, new_rel) | | (gdb) 187( void) make_join_rel (root, old_rel, new_rel) (gdb) stepmake_join_rel (root=0x1e214b8, rel1=0x1f079f0, rel2=0x1e96520) at joinrels.c:681681 joinrelids = bms_union (rel1- > relids, rel2- > relids)

Enter the build_join_rel function, the corresponding RelOptInfo already exists, and return

(gdb) 728 joinrel = build_join_rel (root, joinrelids, rel1, rel2, sjinfo, (gdb) stepbuild_join_rel (root=0x1e214b8, joinrelids=0x1e401d8, outer_rel=0x1f079f0, inner_rel=0x1e96520, sjinfo=0x7fff247e18a0, restrictlist_ptr=0x7fff247e1898) at relnode.c:498498 joinrel = find_join_rel (root, joinrelids); 500 if (joinrel) (gdb) n506 if (restrictlist_ptr) (gdb) 507 * restrictlist_ptr= build_joinrel_restrictlist (root, (gdb) 511 return joinrel

Execute populate_joinrel_with_paths, after the function is executed, check the external and internal access paths again, and become the combination of HashPath + MaterialPath. The specific changes will be described in the next section.

(gdb) 742 populate_joinrel_with_paths (root, rel1, rel2, joinrel, sjinfo, (gdb) n745 bms_free (joinrelids)) (gdb) set $roi= (RelOptInfo *) root- > join_rel_level [6]-> head- > data.ptr_value (gdb) set $np= (NestPath *) $roi- > pathlist- > head- > data.ptr_value (gdb) p * $np- > outerjoinpath$30 = {type = T_HashPath, pathtype = T_HashJoin, parent = 0x1f079f0, pathtarget = 0x1e41128, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100,3.25, startup_cost = 196.75 Pathkeys = 0x0} (gdb) p * $np- > innerjoinpath$31 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x1e96520, pathtarget = 0x1e96730, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 5, startup_cost = 97.962499999999991, total_cost = 2014.0375000000001, pathkeys = 0x0} "RelOptInfo generated by which function in PostgreSQL creates two rels connections" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.