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

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

I. data structure

IndexClauseSet

Conditional statements used to collect matching indexes

/ * Data structure for collecting qual clauses that match an index * / typedef struct {bool nonempty; / * True if lists are not all empty * / / * Lists of RestrictInfos, one per index column * / List * indexclauses [index _ MAX_KEYS];} IndexClauseSet; II, source code interpretation

Get_index_paths

The get_index_paths function constructs an index access path (IndexPath) based on a given index and index conditional clause.

/ /-get_index_paths / * * get_index_paths * Given an index and a set of index clauses for it, construct IndexPaths * given index and index condition clause, construct index access path (IndexPaths). * * Plain indexpaths are sent directly to add_path, while potential * bitmap indexpaths are added to * bitindexpaths for later processing. * the Plain index access path is directly passed into the function add_path as a parameter, and the potential bitmap index path * is added to the bitindexpaths for later processing. * * This is a fairly simple frontend to build_index_paths (). Its reason for * existence is mainly to handle ScalarArrayOpExpr quals properly. If the * index AM supports them natively, we should just include them in simple * index paths. If not, we should exclude them while building simple index * paths, and then make a separate attempt to include them in bitmap paths. * Furthermore, we should consider excluding lower-order ScalarArrayOpExpr * quals so as to create ordered paths. * this function simply constructs the parameters needed by build_index_paths and calls them. The reason for the existence of this function is the correct * processing of ScalarArrayOpExpr expressions. * / static void get_index_paths (PlannerInfo * root, RelOptInfo * rel, IndexOptInfo * index, IndexClauseSet * clauses, List * * bitindexpaths) {List * indexpaths; bool skip_nonnative_saop = false; bool skip_lower_saop = false; ListCell * lc; / * * Build simple indexpaths using the clauses. Allow ScalarArrayOpExpr * clauses only if the index AM supports them natively, and skip any such * clauses for index columns after the first (so that we produce ordered * paths if possible). * / indexpaths = build_index_paths (root, rel, index, clauses, index- > predOK, ST_ANYSCAN, & skip_nonnative_saop & skip_lower_saop) / / call the build_index_paths function / * * If we skipped any lower-order ScalarArrayOpExprs on an index with an AM * that supports them, then try again including those clauses. This will * produce paths with more selectivity but no ordering. * / if (skip_lower_saop) {indexpaths = list_concat (indexpaths, build_index_paths (root, rel, index, clauses, index- > predOK) ST_ANYSCAN, & skip_nonnative_saop, NULL)) } / * Submit all the ones that can form plain IndexScan plans to add_path. A * plain IndexPath can represent either a plain IndexScan or an * IndexOnlyScan, but for our purposes here that distinction does not * matter. However, some of the indexes might support only bitmap scans, * and those we mustn't submit to add_path here.) * call add_path * (plain IndexPath can be regular index scan or IndexOnlyScan) * * Also, pick out the ones that are usable as bitmap scans one by one of the access paths that can form the Plain index scan plan as parameters. For that, we * must discard indexes that don't support bitmap scans, and we also are * only interested in paths that have some selectivity; we should discard * anything that was generated solely for ordering purposes. * find out the index available for bitmap scanning * / foreach (lc, indexpaths) / / traverse the access path {IndexPath * ipath = (IndexPath *) lfirst (lc); if (index- > amhasgettuple) add_path (rel, (Path *) ipath); / / call add_path if (index- > amhasgetbitmap & & (ipath- > path.pathkeys = = NIL | ipath- > indexselectivity)

< 1.0)) *bitindexpaths = lappend(*bitindexpaths, ipath);//如可以,添加到位图索引扫描路径链表中 } /* * If there were ScalarArrayOpExpr clauses that the index can't handle * natively, generate bitmap scan paths relying on executor-managed * ScalarArrayOpExpr. */ if (skip_nonnative_saop) { indexpaths = build_index_paths(root, rel, index, clauses, false, ST_BITMAPSCAN, NULL, NULL); *bitindexpaths = list_concat(*bitindexpaths, indexpaths); } } //----------------------------------------------------------- build_index_paths /* * build_index_paths * Given an index and a set of index clauses for it, construct zero * or more IndexPaths. It also constructs zero or more partial IndexPaths. * 给定索引和该索引的条件,构造0-N个索引访问路径或partial索引访问路径(用于并行) * * We return a list of paths because (1) this routine checks some cases * that should cause us to not generate any IndexPath, and (2) in some * cases we want to consider both a forward and a backward scan, so as * to obtain both sort orders. Note that the paths are just returned * to the caller and not immediately fed to add_path(). * 函数返回访问路径链表:(1)执行过程中的检查会导致产生不了索引访问路径 * (2)在某些情况下，同时考虑正向/反向扫描，以便获得两种排序顺序。 * 注意:访问路径返回给调用方,不会马上反馈到函数add_path中 * * At top level, useful_predicate should be exactly the index's predOK flag * (ie, true if it has a predicate that was proven from the restriction * clauses). When working on an arm of an OR clause, useful_predicate * should be true if the predicate required the current OR list to be proven. * Note that this routine should never be called at all if the index has an * unprovable predicate. * 在顶层,useful_predicate标记应与索引的predOK标记一致.在操作OR自己的arm(?)时, * 如果谓词需要当前的OR链表证明,则useful_predicate应为T. * 注意:如果索引有一个未经验证的谓词,则该例程不能被调用. * * scantype indicates whether we want to create plain indexscans, bitmap * indexscans, or both. When it's ST_BITMAPSCAN, we will not consider * index ordering while deciding if a Path is worth generating. * scantype:提示是否创建plain/bitmap或者两者兼顾的索引扫描. * 如该参数值为ST_BITMAPSCAN,则在决定访问路径是否产生时不会考虑使用索引排序 * * If skip_nonnative_saop is non-NULL, we ignore ScalarArrayOpExpr clauses * unless the index AM supports them directly, and we set *skip_nonnative_saop * to true if we found any such clauses (caller must initialize the variable * to false). If it's NULL, we do not ignore ScalarArrayOpExpr clauses. * skip_nonnative_saop: * 如为NOT NULL,除非索引的访问方法(AM)直接支持,否则会忽略 * ScalarArrayOpExpr子句,如支持,则更新skip_nonnative_saop标记为T. * 如为NULL,不能忽略ScalarArrayOpExpr子句. * * If skip_lower_saop is non-NULL, we ignore ScalarArrayOpExpr clauses for * non-first index columns, and we set *skip_lower_saop to true if we found * any such clauses (caller must initialize the variable to false). If it's * NULL, we do not ignore non-first ScalarArrayOpExpr clauses, but they will * result in considering the scan's output to be unordered. * skip_lower_saop: * 如为NOT NULL,ScalarArrayOpExpr子句中的首列不是索引列,则忽略之, * 同时如果找到相应的子句,则设置skip_lower_saop标记为T. * 如为NULL,除首个ScalarArrayOpExpr子句外,其他子句不能被忽略,但输出时不作排序 * * 输入/输出参数: * 'rel' is the index's heap relation * rel-相应的RelOptInfo * 'index' is the index for which we want to generate paths * index-相应的索引IndexOptInfo * 'clauses' is the collection of indexable clauses (RestrictInfo nodes) * clauses-RestrictInfo节点集合 * 'useful_predicate' indicates whether the index has a useful predicate * useful_predicate-提示索引是否有可用的谓词 * 'scantype' indicates whether we need plain or bitmap scan support * scantype-扫描类型,提示是否需要plain/bitmap扫描支持 * 'skip_nonnative_saop' indicates whether to accept SAOP if index AM doesn't * skip_nonnative_saop-提示是否接受SAOP * 'skip_lower_saop' indicates whether to accept non-first-column SAOP * skip_lower_saop-提示是否接受非首列SAOP */ static List * build_index_paths(PlannerInfo *root, RelOptInfo *rel, IndexOptInfo *index, IndexClauseSet *clauses, bool useful_predicate, ScanTypeControl scantype, bool *skip_nonnative_saop, bool *skip_lower_saop) { List *result = NIL; IndexPath *ipath; List *index_clauses; List *clause_columns; Relids outer_relids; double loop_count; List *orderbyclauses; List *orderbyclausecols; List *index_pathkeys; List *useful_pathkeys; bool found_lower_saop_clause; bool pathkeys_possibly_useful; bool index_is_ordered; bool index_only_scan; int indexcol; /* * Check that index supports the desired scan type(s) */ switch (scantype) { case ST_INDEXSCAN: if (!index->

Amhasgettuple) return NIL; break; case ST_BITMAPSCAN: if (! index- > amhasgetbitmap) return NIL; break; case ST_ANYSCAN: / * either or both are OK * / break;} / * * 1.Collect the index clauses into a single list. * 1. Collect index clauses into a separate linked list * * We build a list of RestrictInfo nodes for clauses to be used with this * index, along with an integer list of the index column numbers (zero * based) that each clause should be used with. The clauses are ordered * by index key, so that the column numbers form a nondecreasing sequence. * (This order is depended on by btree and possibly other places.) The * lists can be empty, if the index AM allows that. * We built a linked list of RestrictInfo nodes for the clauses to be used with this index, * and an integer list of index column numbers (starting at 0) that each clause should use with it. * clauses are sorted by index key, so the column number forms a non-decreasing sequence. * (this sort depends on BTree and other places where possible). * the linked list can be empty if the index access method (AM) allows. * found_lower_saop_clause is set true if we accept a ScalarArrayOpExpr * index clause for a non-first index column. This prevents us from * assuming that the scan result is ordered. (Actually, the result is * still ordered if there are equality constraints for all earlier * columns, but it seems too expensive and non-modular for this code to be * aware of that refinement.) * * We also build a Relids set showing which outer rels are required by the * selected clauses. Any lateral_relids are included in that, but not * otherwise accounted for. * create a Relids collection of external rels on which the selected clause depends, including lateral_relids. * / index_clauses = NIL; clause_columns = NIL; found_lower_saop_clause = false; outer_relids = bms_copy (rel- > lateral_relids); for (indexcol = 0; indexcol)

< index->

< index->

Ncolumns; indexcol++) (gdb) p outer_relids$20 = (Relids) 0x0 (gdb) n927 foreach (lc, clauses- > indexclus]) (gdb) p indexcol$21 = 0 (gdb) n#rinfo constraint: t_dwxx.dwbh='1001'929 RestrictInfo * rinfo = (RestrictInfo *) lfirst (lc); (gdb) 931 if (IsA (rinfo- > clause, ScalarArrayOpExpr) (gdb))

The main logic of Step 1:

(gdb) n955 index_clauses = lappend (index_clauses, rinfo); (gdb) p * index_clausesCannot access memory at address 0x0 (gdb) n956 clause_columns = lappend_int (clause_columns, indexcol); (gdb) 958 rinfo- > clause_relids); (gdb) 957 outer_relids = bms_add_members (outer_relids)

After the completion of Step 1:

(gdb) p * outer_relids$23 = {nwords = 1, words = 0x27177fc} (gdb) p * index_clauses$26 = {type = T_List, length = 1, head = 0x2717758, tail = 0x2717758} (gdb) p outer_relids- > words [0] $27 = 0-> no external Relids (gdb) p * clause_columns$31 = {type = T_IntList, length = 1, head = 0x27177a8, tail = 0x27177a8} p clause_columns- > head- > > 0-> column array number is 0

Number of cycles:

(gdb) p loop_count$33 = 1

Step 2: calculate the path key, if any, that describes the sort of index, and check how many are useful for the query, if any.

... (gdb) p pathkeys_possibly_useful$35 = true (gdb) p index_is_ordered$36 = true

Create a forward scan sort key

(gdb) n994 index_pathkeys = build_index_pathkeys (root, index, (gdb) p * index_pathkeysCannot access memory at address 0x0-- > No sorting required

Step 3: check if only indexes need to be scanned

(gdb) p index_only_scan$37 = false-- > No way!

Step 4: generate index scan path

Call the function create_index_path (described in the next section)

1036 ipath = create_index_path (root, index, (gdb) 1049 result = lappend (result, ipath) (gdb) p * ipath$38 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x27041b0, pathtarget = 0x27134c8, param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0.285000000000003, total_cost = 8.30250000000002, pathkeys = 0x0}, indexinfo = 0x27135b8, indexclauses = 0x2717778, indexquals = 0x27178e8, indexqualcols = 0x2717938, indexorderbys = 0x0, indexorderbycols = 0x0, indexscandir = indexscandir, ForwardScanDirection = 4.29250000000004, ForwardScanDirection = 0.0001}

Step 5: build reverse scan (BackwardScanDirection) path

(gdb) p index_is_ordered$41 = true (gdb) p pathkeys_possibly_useful$42 = true... (gdb) p * index_pathkeysCannot access memory at address 0x0-- > No sorting required

Return the result

1137 return result; (gdb) 1138} at this point, the study of "functional analysis in the cost estimation of PostgreSQL index scanning" 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.