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

This article mainly explains "create_index_paths- > generate_bitmap_or_paths function Analysis in PostgreSQL physical Optimization". The explanation in this article is simple and clear and easy to learn and understand. Please follow the editor's train of thought to study and learn "create_index_paths- > generate_bitmap_or_paths function Analysis in PostgreSQL physical Optimization".

This function looks for OR clauses from the list of conditional clauses and generates BitmapOrPath if found and can be processed.

The following is a sample BitmapOrPath access path:

Testdb=# explain verbose select t1.* from t_dwxx T1 where (dwbh > '10000' and dwbh

< '30000') OR (dwdz between 'DWDZ10000' and 'DWDZ20000');QUERY PLAN --------------------------------------------------------------------------------------------- Bitmap Heap Scan on public.t_dwxx t1 (cost=84.38..216.82 rows=3156 width=20) Output: dwmc, dwbh, dwdz Recheck Cond: ((((t1.dwbh)::text >

'10000'::text) AND ((t1.dwbh):: text

< '30000'::text)) OR (((t1.dwdz)::text >

= 'DWDZ10000'::text) AND ((t1.dwdz):: text BitmapOr (cost=84.38..84.38 rows=3422 width=0)-> BitmapOr-> Bitmap Index Scan on t_dwxx_pkey (cost=0.00..50.52 rows=2223 width=0) Index Cond: ((t1.dwbh):: text >' 10000'::text) AND ((t1.dwbh):: text

< '30000'::text)) ->

< '30000') OR (dwdz between 'DWDZ10000' and 'DWDZ20000'); 启动gdb跟踪 (gdb) b generate_bitmap_or_pathsBreakpoint 1 at 0x74e6c1: file indxpath.c, line 1266.(gdb) cContinuing.Breakpoint 1, generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0) at indxpath.c:12661266 List *result = NIL; 查看输入参数,clauses是链表,只有一个元素(BoolExpr类型,即OR子句);other_clauses为NULL (gdb) p *clauses$1 = {type = T_List, length = 1, head = 0x2aaf118, tail = 0x2aaf118}(gdb) p *(Node *)clauses->

Head- > data.ptr_value$2 = {type = T_RestrictInfo} (gdb) p * (RestrictInfo *) clauses- > head- > data.ptr_value$3 = {type = T_RestrictInfo, clause = 0x2aad818, is_pushed_down = true, outerjoin_delayed = false, can_join = false, pseudoconstant = false, leakproof = false, security_level = 0, clause_relids = 0x2aaf100, required_relids = 0x2aae938, outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x0, right_relids = 0x0, orclause = orclause, orclause = orclause Eval_cost = {startup = 0, per_tuple = 0.01}, norm_selec = 0.31556115090433856, outer_selec =-1, mergeopfamilies = 0x0, left_ec = 0x0, right_ec = 0x0, left_em = 0x0, right_em = 0x0, scansel_cache = 0x0, outer_is_left = false, hashjoinoperator = 0, left_bucketsize =-1, right_bucketsize =-1, left_mcvfreq =-1 Right_mcvfreq =-1} (gdb) p * ((RestrictInfo *) clauses- > head- > data.ptr_value)-> clause$4 = {type = T_BoolExpr} (gdb) set $clause= ((RestrictInfo *) clauses- > head- > data.ptr_value)-> clause (gdb) p * (BoolExpr *) $clause$6 = {xpr = {type = T_BoolExpr}, boolop = OR_EXPR, args = 0x2aad758, location =-1}

Traversing the clausees clause, rinfo- > clause means BoolExpr (OR clause)

... 1276 foreach (lc, clauses) (gdb) 1278 RestrictInfo * rinfo = lfirst_node (RestrictInfo, lc); (gdb) 1284 if (! restriction_is_or_clause (rinfo))

Traversing the parameters of the OR clause

(gdb) 1292 foreach (j, (BoolExpr *) rinfo- > orclause)-> args)

Parameter, and the BoolExpr,boolop operator is AND_EXPR

(gdb) n1294 Node * orarg = (Node *) lfirst (j); (gdb) 1298 if (and_clause (orarg)) (gdb) p * orarg$10 = {type = T_BoolExpr} (gdb) p * (BoolExpr *) orarg$11 = {xpr = {type = T_BoolExpr}, boolop = AND_EXPR, args = 0x2aaea90, location =-1}

Parameters of the AND clause

(gdb) n1300 List * andargs = ((BoolExpr *) orarg)-> args (gdb) 1302 indlist = build_paths_for_OR (root, rel, (gdb) p * andargs$12 = {type = T_List, length = 2, head = 0x2aada78, tail = 0x2aada98} (gdb) p * (Node *) andargs- > head- > data.ptr_value$13 = {type = T_RestrictInfo} (gdb) p * (RestrictInfo *) andargs- > head- > data.ptr_value$14 = {type = T_RestrictInfo, clause = 0x2aace08, is_pushed_down = true, outerjoin_delayed = false, can_join = false, pseudoconstant = pseudoconstant Leakproof = false, security_level = 0, clause_relids = 0x2aaea78, required_relids = 0x2aaea78, outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x2aaea60, right_relids = 0x0, orclause = 0x0, parent_ec = 0x0, eval_cost = {startup = 0, per_tuple = 0.002500000000001}, norm_selec = 0.999900000000001, outer_selec =-1, mergeopfamilies = 0x0, left_ec = 0x0, right_ec = 0x0, left_em = 0x0, right_em = 0x0, scansel_cache = 0x0 Outer_is_left = false, hashjoinoperator = 0, left_bucketsize =-1, right_bucketsize =-1, left_mcvfreq =-1, right_mcvfreq =-1} (gdb) p * ((RestrictInfo *) andargs- > head- > data.ptr_value)-> clause$15 = {type = T_OpExpr} (gdb) set $tmp= ((RestrictInfo *) andargs- > head- > data.ptr_value)-> clause (gdb) p * (OpExpr *) $tmp$16 = {xpr = {type = T_OpExpr}, opno = 666,opfuncid = 742, opresulttype = 16, opretset = false Opcollid = 0, inputcollid = 100, args = 0x2aacd68, location = 39} (gdb) set $tmp2= ((RestrictInfo *) andargs- > head- > next- > data.ptr_value)-> clause (gdb) p * (OpExpr *) $tmp2 $17 = {xpr = {type = T_OpExpr}, opno = 664, opfuncid = 740, opresulttype = 16, opretset = false, opcollid = 0, inputcollid = 100, args = 0x2aacc78, location = 58} (gdb)

Enter the build_paths_for_OR function

(gdb) stepbuild_paths_for_OR (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaea90, other_clauses=0x2aaf598) at indxpath.c:11701170 List * result = NIL

Traversing the index, the first index is idx_dwxx_dwdz

1174 foreach (lc, rel- > indexlist) (gdb) 1176 IndexOptInfo * index = (IndexOptInfo *) lfirst (lc) (gdb) 1182 if (! index- > amhasgetbitmap) (gdb) p * index$18 = {type = T_IndexOptInfo, indexoid = 16753, reltablespace = 0, rel = 0x2aa6658, pages = 40, tuples = 10000, tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2aae590, indexcollations = 0x2aae5a8, opfamily = 0x2aae5c0, opcintype = 0x2aae5d8, sortopfamily = 0x2aae5c0, reverse_sort = 0x2aae608, nulls_first = 0x2aae620, canreturn = 0x2aae5f0, relam = 0x2aae620, canreturn = 0x2aae5f0, relam = 403, indexprs = 0x0, 0x0 = indpred, indpred = 0x0, 0x0 = 0x0, = Immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true, amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true, amcostestimate = 0x94f0ad}-- testdb=# select relname from pg_class where oid=16753 Relname-idx_dwxx_dwdz (1 row)--

Does not match constraints ((dwbh > '10000' and dwbh)

< '30000')),继续下一个索引 1229 if (!clauseset.nonempty && !useful_predicate)(gdb) p clauseset$20 = {nonempty = false, indexclauses = {0x0 }}(gdb) n1230 continue; 下一个索引是idx_dwxx_predicate_dwmc/idx_dwxx_expr,同样不匹配,继续寻找索引,直至索引t_dwxx_pkey (gdb) p *index$23 = {type = T_IndexOptInfo, indexoid = 16738,...1223 match_clauses_to_index(index, clauses, &clauseset);(gdb) 1229 if (!clauseset.nonempty && !useful_predicate)(gdb) p clauseset$24 = {nonempty = true, indexclauses = {0x2aaf638, 0x0 }} 构建索引访问路径 (gdb) 1246 result = list_concat(result, indexpaths);(gdb) p *indexpaths$25 = {type = T_List, length = 1, head = 0x2aafb48, tail = 0x2aafb48}(gdb) p *(Node *)indexpaths->

Head- > data.ptr_value$26 = {type = T_IndexPath} (gdb) p * (IndexPath *) indexpaths- > head- > data.ptr_value$27 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 3156, startup_cost = 0.285000000000003, total_cost = 191.46871600907946, pathkeys = 0x0}, indexinfo = 0x2aa6868, indexclauses = 0x2aaf6a8, indexquals = 0x2aaf898, indexqualcols = indexqualcols, indexqualcols = 0x2aaf8e8 Indexorderbycols = 0x0, indexscandir = ForwardScanDirection, indextotalcost = 50.5150000000001, indexselectivity = 0.22227191011235958}

Go back to the generate_bitmap_or_paths function

1250} (gdb) generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0) at indxpath.c:13071307 indlist = list_concat (indlist

Recursively enter generate_bitmap_or_paths

(gdb) nBreakpoint 1, generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaea90, other_clauses=0x2aaf598) at indxpath.c:12661266 List * result = NIL;# Direct end (gdb) finishRun till exit from # 0 generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaea90, other_clauses=0x2aaf598) at indxpath.c:12660x000000000074e7a0 in generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0) at indxpath.c:13071307 indlist = list_concat (indlist,Value returned is $28 = (List *) 0x0

Complete the first cycle

(gdb) n1329 if (indlist = = NIL) (gdb) n1339 bitmapqual = choose_bitmap_and (root, rel, indlist); (gdb) 1340 pathlist = lappend (pathlist, bitmapqual); (gdb) p * bitmapqual$29 = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 3156, startup_cost = 0.2850000000003, total_cost = 191.46871600907946, pathkeys = 0x0

This is the second AND clause

1292 foreach (j, ((BoolExpr *) rinfo- > orclause)-> args) (gdb) 1294 Node * orarg = (Node *) lfirst (j); (gdb) 1298 if (and_clause (orarg)) (gdb) 1300 List * andargs = ((BoolExpr *) orarg)-> args

Complete the second cycle

(gdb) 1339 bitmapqual = choose_bitmap_and (root, rel, indlist); (gdb) 1340 pathlist = lappend (pathlist, bitmapqual); (gdb) p bitmapqual$33 = (Path *) 0x2aafd78 (gdb) p * bitmapqual$34 = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 3156, startup_cost = 0.285000000000003, total_cost = 148.08735471522883, pathkeys = pathkeys}

End the loop and build the BitmapOrPath

1347 if (pathlist! = NIL) (gdb) 1349 bitmapqual = (Path *) create_bitmap_or_path (root, rel, pathlist)

Enter create_bitmap_or_path and call the function cost_bitmap_or_node to calculate the cost

(gdb) stepcreate_bitmap_or_path (root=0x2aa6248, rel=0x2aa6658, bitmapquals=0x2aafbf8) at pathnode.c:11561156 BitmapOrPath * pathnode = makeNode (BitmapOrPath);... 1178 cost_bitmap_or_node (pathnode, root); (gdb) stepcost_bitmap_or_node (path=0x2ab0278, root=0x2aa6248) at costsize.c:1149...

The calculation results match the information in the execution plan "BitmapOr (cost=84.38..84.38 rows=3422 width=0)"

(gdb) p * path$37 = {path = {type = T_BitmapOrPath, pathtype = T_BitmapOr, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 0, startup_cost = 84.378, total_cost = 84.378, pathkeys = 0x0}, bitmapquals = 0x2aafbf8, bitmapselectivity = 0.34222288270157986}

Back to generate_bitmap_or_paths

(gdb) ncreate_bitmap_or_path (root=0x2aa6248, rel=0x2aa6658, bitmapquals=0x2aafbf8) at pathnode.c:11801180 return pathnode; (gdb) 1181} (gdb) generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0) at indxpath.c:13501350 result = lappend (result, bitmapqual) Thank you for reading, the above is the content of "create_index_paths- > generate_bitmap_or_paths function analysis in PostgreSQL physical optimization". After the study of this article, I believe you have a deeper understanding of the problem of create_index_paths- > generate_bitmap_or_paths function analysis in PostgreSQL physical optimization, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.