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

Nkeycolumns; indexcol++) {if (match_clause_to_indexcol (index, indexcol, rinfo)) {clauseset- > indexclauses [indexcol] = list_append_unique_ptr (clauseset- > indexclauses [indexcol], rinfo) / / assign clauseset- > nonempty = true;// set the tag return;} / /-match_clause_to_indexcol / * * match_clause_to_indexcol () * Determines whether a restriction clause matches a column of an index. * determine whether the constraint matches a column in the index * * To match an index normally, the clause: * generally speaking, to match an index, the clause must: * (1) must be in the form (indexkey op const) or (const op indexkey) * and * satisfies the format: (index key operator constant) or (constant operator index key), and * (2) must contain an operator which is in the same family as the index * operator for this column, or is a "special" operator as recognized * by match_special_index_operator () * and * contains an operator with the same family as the index column, or a special operator identified by the match_special_index_operator method * (3) must match the collation of the index, if collation is relevant. * matches the collation collation of the index * * Our definition of "const" is exceedingly liberal: we allow anything that * doesn't involve a volatile function or a Var of the index's relation. * In particular, Vars belonging to other relations of the query are * accepted here, since a clause of that form can be used in a * parameterized indexscan. It's the responsibility of higher code levels * to manage restriction and join clauses appropriately. * here the definition of the "const" constant is very free: except for the Var of variable functions or index relations, it is regarded as "const" * because of the possibility of parameterized index scanning, Vars belonging to other Relations in the query can also appear here. * the code that calls this function is responsible for managing appropriate constraints and connection conditions. * * Note: we do need to check for Vars of the index's relation on the * "const" side of the clause, since clauses like (a.f1 OP (b.f2 OP a.f3)) * are not processable by a parameterized indexscan on a.f1, whereas * something like (a.f1 OP (b.f2 OP c.f3)) is. * Note: the Vars of the index relationship needs to be checked in the const section of the clause, because clauses * such as (a.f1 OP (b.f2 OP a.f3)) cannot be processed by parameterized index scanning on a * * Presently, the executor can only deal with indexquals that have the * indexkey on the left, so we can only use clauses that have the indexkey * on the right if we can commute the clause to put the key on the left. * We do not actually do the commuting here, but we check whether a * suitable commutator operator is available. * so far, executors can only handle index expressions with index keys on the left, so they can only use conditional expressions that can * transform index keys to the left. No transformation is performed in this function, but the corresponding check is performed. * If the index has a collation, the clause must have the same collation. * For collation-less indexes, we assume it doesn't matter; this is * necessary for cases like "hstore? text", wherein hstore's operators * don't care about collation but the clause will get marked with a * collation anyway because of the text argument. (This logic is * embodied in the macro IndexCollMatchesExprColl.) * if the index contains collation (collation), the conditional clause must contain the same collation. * for indexes without collation, it is assumed that collation has no effect. * * It is also possible to match RowCompareExpr clauses to indexes (but * currently, only btree indexes handle this) In this routine we will * report a match if the first column of the row comparison matches the * target index column. This is sufficient to guarantee that some index * condition can be constructed from the RowCompareExpr-whether the * remaining columns match the index too is considered in * adjust_rowcompare_for_index (). * RowCompareExpr may match the index, and during this process, if the first column of the row comparison * matches the target index, it can be considered a match. * * It is also possible to match ScalarArrayOpExpr clauses to indexes, when * the clause is of the form "indexkey op ANY (arrayconst)". * it is also possible to match ScalarArrayOpExpr * if the format of the clause is "indexkey op ANY (arrayconst)". * * For boolean indexes, it is also possible to match the clause directly * to the indexkey; or perhaps the clause is (NOT indexkey) * for Boolean indexes, you can directly match the index key * * input parameter: * 'index' is the index of interest. * Index being processed by index- * 'indexcol' is a column number of' index' (counting from 0). * indexcol- index column (from 0) * 'rinfo' is the clause to be tested (as a RestrictInfo node). * rinfo-RestrictInfo Node * * Returns true if the clause can be used with this index key. * if the index can be used, return T * * NOTE: returns false if clause is an OR or AND clause; it is the * responsibility of higher-level routines to cope with those. * Note: if the conditional statement is an OR/AND statement, F is returned, and the upper processing logic handles * / static bool match_clause_to_indexcol (IndexOptInfo * index, int indexcol, RestrictInfo * rinfo) {Expr * clause = rinfo- > clause;// conditional statement Index index_relid = index- > rel- > relid. / / Relid Oid opfamily;// operator type of Index Oid idxcollation;// index collation Node * leftop,// left node * rightop;// right node Relids left_relids;// left node related Relids Relids right_relids;// right node related Relids Oid expr_op / / whether the Oid bool plain_op;// of the Oid Oid expr_coll;// expression of the expression operator Collation is the Plain operator Assert (indexcol

Nkeycolumns); opfamily = index- > opfamily [indexcol]; / / get operator type idxcollation = index- > indexcollations [indexcol]; / / get index collation / * First check for boolean-index cases. * / if (IsBooleanOpfamily (opfamily)) / / whether the Boolean class {if (match_boolean_index_clause ((Node *) clause, indexcol, index)) / / whether to match return true;// if matching, return T} / * * Clause must be a binary opclause, or possibly a ScalarArrayOpExpr * (which is always binary, by definition). Or it could be a * RowCompareExpr, which we pass off to match_rowcompare_to_indexcol (). * Or, if the index supports it, we can handle IS NULL/NOT NULL clauses. * / if (is_opclause (clause)) / / OpExpr {leftop = get_leftop (clause); rightop = get_rightop (clause); if (! leftop | |! rightop) return false; left_relids = rinfo- > left_relids; right_relids = rinfo- > right_relids; expr_op = (OpExpr *) clause)-> opno; expr_coll = ((OpExpr *) clause)-> inputcollid Plain_op = true;} else if (clause & & IsA (clause, ScalarArrayOpExpr)) / / ScalarArrayOpExpr {ScalarArrayOpExpr * saop = (ScalarArrayOpExpr *) clause; / * We only accept ANY clauses, not ALL * / if (! saop- > useOr) return false; leftop = (Node *) linitial (saop- > args); rightop = (Node *) lsecond (saop- > args); left_relids = NULL / * not actually needed * / right_relids = pull_varnos (rightop); expr_op = saop- > opno; expr_coll = saop- > inputcollid; plain_op = false } else if (clause & & IsA (clause, RowCompareExpr) / / RowCompareExpr {return match_rowcompare_to_indexcol (index, indexcol, opfamily, idxcollation, (RowCompareExpr *) clause) } else if (index- > amsearchnulls & & IsA (clause, NullTest)) / / NullTest {NullTest * nt = (NullTest *) clause; if (! nt- > argisrow & & match_index_to_operand ((Node *) nt- > arg, indexcol, index) return true; return false;} else return false / * * Check for clauses of the form: (indexkey operator constant) or * (constant operator indexkey) See above notes about const-ness. * (indexkey operator constant) and (constant operator indexkey) format statements * / processing: (indexkey operator constant) if (match_index_to_operand (leftop, indexcol, index) & &! bms_is_member (index_relid, right_relids) & &! contain_volatile_functions (rightop)) {if (IndexCollMatchesExprColl (idxcollation, expr_coll) & & is_indexable_operator (expr_op) Opfamily, true) / / collation & operator type matching return true / / return T / * * If we didn't find a member of the index's opfamily, see whether it * is a "special" indexable operator. * / if (plain_op & & match_special_index_operator (clause, opfamily, idxcollation, true)) / / Plain operation & special operator, returns T return true; return false / / otherwise, return F} / / constant operator indexkey if (plain_op & & match_index_to_operand (rightop, indexcol, index) & &! bms_is_member (index_relid, left_relids) & &! contain_volatile_functions (leftop)) {if (IndexCollMatchesExprColl (idxcollation, expr_coll) & & is_indexable_operator (expr_op, opfamily) False)) return true / * If we didn't find a member of the index's opfamily, see whether it * is a "special" indexable operator. * / if (match_special_index_operator (clause, opfamily, idxcollation, false)) return true; return false;} return false;} 3. Tracking analysis

The test script is as follows

Select a.from t_grxx from t_grxx b.grbhjje from t_dwxx a, lateral (select t1.dwbhrecedict1.grbhrecoveryt2.je from t_grxx T1 inner join t_jfxx T2 on t1.dwbh = a.dwbh and t1.grbh = t2.grbh) bwhere a.dwbh = '1001'order by b.dwbh

Note: according to the previous analysis, there are equivalent classes {t_dwxx.dwbh t_grxx.dwbh '1001'} and {t_grxx.grbh t_jfxx.grbh} in SQL statements, which are constructed using equivalence classes when constructing the index access path of t_grxx.

When you start gdb, the first RelOptInfo (corresponding to t_dwxx) has three Index, the second RelOptInfo (corresponding to t_grxx) has two Index (indexes on dwbh and grbh, respectively), and the third RelOptInfo (corresponding to t_jfxx) has one Index (index on grbh). This section takes t_jfxx and t_grxx as examples for tracking analysis.

(gdb) cContinuing.Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:242242 if (rel- > indexlist = = NIL) (gdb) p * (IndexOptInfo *) rel- > indexlist- > head- > data.ptr_value$38 = {type = T_IndexOptInfo, indexoid = 16750, reltablespace = 0, rel=0x2729530, pages = 276, tuples = 100000, tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729998, indexcollations = 0x27299b0, opfamily = 0x27299c8, opcintype = 0x27299e0, sortopfamily = 0x27299c8, reverse_sort = reverse_sort, 0x2729a10 = 0x2729a10 Canreturn = 0x27299f8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2729ae0, indrestrictinfo = 0x0, predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true, amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true, amcostestimate = 0x94f0ad}

The input information is known as root (PlannerInfo) and rel (RelOptInfo). First perform an index traversal loop

(gdb) cContinuing.Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:242242 if (rel- > indexlist = = NIL) (gdb) p * (IndexOptInfo *) rel- > indexlist- > head- > data.ptr_value$38 = {type = T_IndexOptInfo, indexoid = 16750, reltablespace = 0, rel=0x2729530, pages = 276, tuples = 100000, tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729998, indexcollations = 0x27299b0, opfamily = 0x27299c8, opcintype = 0x27299e0, sortopfamily = 0x27299c8, reverse_sort = reverse_sort, 0x2729a10 = 0x2729a10 Canreturn = 0x27299f8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2729ae0, indrestrictinfo = 0x0, predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true, amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true, amcostestimate = 0x94f0ad}

Query data dictionary pg_class,oid=16750 the corresponding index is idx_t_jfxx_grbh

Testdb=# select relname from pg_class where oid=16750; relname-idx_t_jfxx_grbh (1 row)

Call match_restriction_clauses_to_index and match_join_clauses_to_index, and the collection of clauses is NULL

(gdb) match_restriction_clauses_to_index (rel=0x2729530, index=0x2729888, clauseset=0x7fff69cf0890) at indxpath.c:21172117} (gdb) create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:275275 get_index_paths (root, rel, index, & rclauseset, (gdb) 284 MemSet (& jclauseset, 0, sizeof (jclauseset)); (gdb) 285 match_join_clauses_to_index (root, rel, index, (gdb) 292 MemSet (& eclauseset, 0, sizeof (eclauseset)) (gdb) 293 match_eclass_clauses_to_index (root, index, (gdb) p rclauseset$2 = {nonempty = false, indexclauses = {0x0}} (gdb) p joinorclauses$3 = (List *) 0x0 (gdb) p jclauseset$4 = {nonempty = false, indexclauses = {0x0}}

Enter match_eclass_clauses_to_index

268 match_restriction_clauses_to_index (rel, index, & rclauseset); (gdb) stepmatch_restriction_clauses_to_index (rel=0x2724c88, index=0x27254d8, clauseset=0x7fff69cf0890) at indxpath.c:21162116 match_clauses_to_index (index, index- > indrestrictinfo, clauseset)

Enter generate_implied_equalities_for_column

(gdb) stepgenerate_implied_equalities_for_column (root=0x2714c50, rel=0x2729530, callback=0x7509b0, callback_arg=0x7fff69cf0620, prohibited_rels=0x0) at equivclass.c:22192219 List * result = NIL

Equivalence class information

... 2235 EquivalenceClass * cur_ec = (EquivalenceClass *) lfirst (lc1) (gdb) 2243 if (cur_ec- > ec_has_const | | list_length (cur_ec- > ec_members) ec_members) (gdb) p * cur_em$7 = {type = T_EquivalenceMember, em_expr = 0x2722890, em_relids = 0x272a238, em_nullable_relids = 0x0, em_is_const = false, em_is_child = false Em_datatype = 25} (gdb) p * cur_em- > em_expr$8 = {type = T_RelabelType} (gdb) p * (RelabelType *) cur_em- > em_expr$9 = {xpr = {type = T_RelabelType}, arg = 0x2722840, resulttype = 25, resulttypmod =-1, resultcollid = 100, relabelformat = COERCE_IMPLICIT_CAST Location =-1} (gdb) p * ((RelabelType *) cur_em- > em_expr)-> arg$10 = {type = T_Var} (gdb) p * (Var *) ((RelabelType *) cur_em- > em_expr)-> arg$11 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0, varnoold = 4, varoattno = 1, location = 0}

Iterate over the members of the equivalence class again to get the first constraint (t_jfxx.grbh=t_grxx.grbh)

(gdb) n2314 rinfo = create_join_clause (root, cur_ec, eq_op, (gdb) 2318 result = lappend (result, rinfo) (gdb) p * rinfo$18 = {type = T_RestrictInfo, clause = 0x272d910, is_pushed_down = true, outerjoin_delayed = false, can_join = true, pseudoconstant = false, leakproof = false, security_level = 0, clause_relids = 0x272db10, required_relids = 0x272d5f0, outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x272dae0, right_relids = 0x272daf8, orclause = 0x0, parent_ec = 0x272a340, eval_cost = {startup = 0, per_tuple = 0.00250000000000001}, per_tuple =-1 Outer_selec =-1, mergeopfamilies = 0x272db48, left_ec = 0x272a340, right_ec = 0x272a340, left_em = 0x272a4d8, right_em = 0x272a420, scansel_cache = 0x0, outer_is_left = false, hashjoinoperator = 98, left_bucketsize =-1, right_bucketsize =-1, left_mcvfreq =-1 Right_mcvfreq =-1} (gdb) set $tmp1= (RelabelType *) ((OpExpr *) rinfo- > clause)-> args- > head- > data.ptr_value (gdb) set $tmp2= (RelabelType *) ((OpExpr *) rinfo- > clause)-> args- > head- > next- > data.ptr_value (gdb) p * (Var *) $tmp1- > arg$31 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0, varnoold = 4, varnoold = 1 Location = 168} (gdb) p * (Var *) $tmp2- > arg$32 = {xpr = {type = T_Var}, varno = 3, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0, varnoold = 3, varoattno = 2, location = 158}

Get the result and return to match_eclass_clauses_to_index

2281 foreach (lc2, cur_ec- > ec_members) (gdb) 2326 if (result) (gdb) 2327 break; (gdb) 2330 return result; (gdb) 2331} (gdb) match_eclass_clauses_to_index (root=0x2714c50, index=0x2729888, clauseset=0x7fff69cf0670) at indxpath.c:21842184 match_clauses_to_index (index, clauses, clauseset);

Let's take a look at the index on t_grxx.dwbh as an example to analyze match_clause_to_index

(gdb) cContinuing.Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2728c38) at indxpath.c:242242 if (rel- > indexlist = = NIL) (gdb) p * (IndexOptInfo *) rel- > indexlist- > head- > data.ptr_value$39 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel=0x2728c38, pages = 276, tuples = 100000, tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729378, indexcollations = 0x2729390, opfamily = 0x27293a8, opcintype = 0x27293c0, sortopfamily = 0x27293a8, reverse_sort = reverse_sort, 0x27293f0 = 0x27293f0 Canreturn = 0x27293d8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x27294e0, indrestrictinfo = 0x272b040, predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true, amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true, amcostestimate = 0x94f0ad}

Oid=16752, and the corresponding object is idx_t_grxx_dwbh

Testdb=# select relname from pg_class where oid=16752; relname-idx_t_grxx_dwbh (1 row)

Enter the IndexOptInfo loop. The IndexOptInfo corresponding to the first element is idx_t_grxx_dwbh.

249 foreach (lc, rel- > indexlist) (gdb) p * rel- > indexlist$40 = {type = T_List, length = 2, head = 0x2729510, tail = 0x2729218} (gdb) p * (IndexOptInfo *) rel- > indexlist- > head- > data- > ptr_value$42 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x2728c38, pages = 276, tuples = 100000, tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729378, indexcollations = 0x2729390, 0x2729390, opfamily = opfamily, 0x27293a8 = 0x27293a8, 0x27293a8 = 0x27293a8 Nulls_first = 0x2729408, canreturn = 0x27293d8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x27294e0, indrestrictinfo = 0x272b040, predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true, amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true, amcostestimate = amcostestimate}

Trot all the way into match_clause_to_indexcol

(gdb) stepmatch_clause_to_indexcol (index=0x2729268, indexcol=0, rinfo=0x272ae58) at indxpath.c:23302330 Expr * clause = rinfo- > clause; (gdb) n2331 Index index_relid = index- > rel- > relid; (gdb) n2344 opfamily = index- > opfamily [indexcol]; (gdb) 2345 idxcollation = index- > indexcollations [indexcol]; (gdb) p index_relid$47 = 3 (gdb) p opfamily$48 = 1994 (gdb)

Query data dictionary according to opfamily

Testdb=# select * from pg_opfamily where oid=1994; opfmethod | opfname | opfnamespace | opfowner-+-403 | text_ops | 11 | 10 (1 row)-indexed access method (btree) testdb=# select * from pg_am where oid=403 Amname | amhandler | amtype-+-btree | bthandler | I (1 row)

Next, enter the is_opclause judgment branch.

(gdb) p idxcollation$49 = 100 (gdb) n2360 if (is_opclause (clause)) (gdb) 2362 leftop = get_leftop (clause); (gdb) 2363 rightop = get_rightop (clause); (gdb) 2364 if (! leftop | |! rightop) (gdb) p * leftop$50 = {type = T_RelabelType} (gdb) p * rightop$51 = {type = T_Const}

The restriction condition is pushed back to form the constraint condition t_grxx.dwbh = '1001'.

# Var:t_grxx.dwbh (gdb) p * (RelabelType *) leftop$56 = {xpr = {type = T_RelabelType}, arg = 0x272ad80, resulttype = 25, resulttypmod =-1, resultcollid = 100, relabelformat = COERCE_IMPLICIT_CAST, location =-1} # constant: '1001' (gdb) p * (Const *) rightop$57 = {xpr = {type = T_Const}, consttype = 25, consttypmod =-1, constcollid = 100, constlen =-1, constvalue = 41069848, constisnull = false, constbyval = false, location = 194}

Execute the relevant judgment and return to T

(gdb) n2366 left_relids = rinfo- > left_relids; (gdb) 2367 right_relids = rinfo- > right_relids; (gdb) 2368 expr_op = ((OpExpr *) clause)-> opno; (gdb) 2369 expr_coll = ((OpExpr *) clause)-> inputcollid; (gdb) 2370 plain_op = true (gdb) 2409 if (match_index_to_operand (leftop, indexcol, index) & & (gdb) 2410! bms_is_member (index_relid, right_relids) & & (gdb) 2409 if (match_index_to_operand (leftop, indexcol, index) & & (gdb) 2411! contain_volatile_functions (rightop) 2410! bms_is_member (index_relid, right_relids) & & (gdb) 2413 if (IndexCollMatchesExprColl (idxcollation) Expr_coll) & & (gdb) 2414 is_indexable_operator (expr_op, opfamily, true) (gdb) 2413 if (IndexCollMatchesExprColl (idxcollation, expr_coll) & & (gdb) 2415 return true

Assign a value to the clauseset variable

(gdb) match_clause_to_index (index=0x2729268, rinfo=0x272ae58, clauseset=0x7fff69cf0890) at indxpath.c:22552255 list_append_unique_ptr (clauseset- > indexclauses [indexcol], (gdb) 2254 clauseset- > indexclauses [indexcol] = (gdb) 2257 clauseset- > nonempty = true; (gdb) 2258 return; (gdb) 2261}

Return to match_clauses_to_index

(gdb) match_clauses_to_index (index=0x2729268, clauses=0x272b040, clauseset=0x7fff69cf0890) at indxpath.c:22002200 foreach (lc, clauses) so far, the study of "what is the function of set_base_rel_pathlists function in PostgreSQL" is over. I hope to be able to solve everyone's 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.