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

This article mainly explains "what is the function of create_index_path function in PostgreSQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the function of create_index_path in PostgreSQL"?

The subfunction create_index_path in the function build_index_paths implements the main logic for estimating the cost of index scanning.

I. data structure

IndexOptInfo

Review the information structure of IndexOptInfo index

Typedef struct IndexOptInfo {NodeTag type; Oid indexoid; / * Index OID,OID of the index relation * / Oid reltablespace; / * Index tablespace, tablespace of index (not table) * / RelOptInfo * rel; / * pointer to Relation, back-link to index's table * / / * index-size statistics (from pg_class and elsewhere) * / BlockNumber pages / * Index pages,number of disk pages in index * / double tuples; / * Index tuple, number of index tuples in index * / int tree_height; / * index height, index tree height, or-1 if unknown * / / * index descriptor information * / int ncolumns; / * columns of index, number of columns in index * / int nkeycolumns / * key columns of the index, number of key columns in index * / int * indexkeys; / * column numbers of index's attributes both * key and included columns, or 0 * / Oid * indexcollations; / * OIDs of collations of index columns * / Oid * opfamily; / * OIDs of operator families for columns * / Oid * opcintype / * OIDs of opclass declared input data types * / Oid * sortopfamily; / * OIDs of btree opfamilies, if orderable * / bool * reverse_sort; / * reverse order? is sort order descending? * / bool * nulls_first; / * NULLs priority? do NULLs come first in the sort order? * / bool * canreturn / * Index columns can be returned via Index-Only Scan? which index cols can be returned in an * index-only scan? * / Oid relam; / * access method OID,OID of the access method (in pg_am) * / List * indexprs / * non-simple index column expression linked list, such as functional index, predicate list of expressions for non-simple index columns * / List * indpred; / * partial index, predicate if a partial index, else NIL * / List * indextlist; / * index list (TargetEntry structure body linked list), targetlist representing index columns * / List * indrestrictinfo / * baserestrictinfo list of parent relationships, * does not contain all conditions implied by index predicates * (unless it is a target rel Please see the notes in check_index_predicates (), * parent relation's baserestrictinfo * list, less any conditions implied by * the index's predicate (unless it's a * target rel) See comments in * check_index_predicates () * / bool predOK / * True. If the index predicate meets the query requirements, whether true if index predicate matches query * / bool unique; / * is the unique index, and whether the true if a unique index * / bool immediate; / * uniqueness check takes effect immediately, is uniqueness enforced immediately? * / bool hypothetical / * whether virtual index, true if index doesn't really exist * / / * Remaining fields are copied from the index AM's API struct: * / AM (access method) API information copied from Index Relation bool amcanorderbyop; / * does AM support orderby operator result? * / bool amoptionalkey; / * can query omit key for the first column? * / bool amsearcharray / * can AM handle ScalarArrayOpExpr quals? * / bool amsearchnulls; / * can AM search for NULL/NOT NULL entries? * / bool amhasgettuple; / * does AM have amgettuple interface? * / bool amhasgetbitmap; / * does AM have amgetbitmap interface? * / bool amcanparallel; / * does AM support parallel scan? * / / * Rather than include amapi.h here, we declare amcostestimate like this * / void (* amcostestimate) / * estimate function of access method, AM's cost estimator * /} IndexOptInfo

Cost correlation

Note: the parameter values actually used are defined through the system configuration file, not the constant definition here!

Typedef double Cost / * execution cost (in page-access units) * / / * defaults for costsize.c's Cost parameters * / / * NB: cost-estimation code should use the variables, not these constants! * / / * Note: the actual value is defined by the system configuration file, not the constant definition here! * / / * If you change these Update backend/utils/misc/postgresql.sample.conf * / # define DEFAULT_SEQ_PAGE_COST 1.0 / / the cost of sequential scanning page # define DEFAULT_RANDOM_PAGE_COST 4.0 / / the cost of random scanning page # define DEFAULT_CPU_TUPLE_COST 0.005 / / the CPU cost of processing a tuple # define DEFAULT_CPU_INDEX_TUPLE_COST 0.005 / / processing an index tuple CPU cost # define DEFAULT_CPU_OPERATOR_COST 0.0025 / / CPU cost of performing an operation or function # define DEFAULT_PARALLEL_TUPLE_COST 0.0025 / / parallel execution The cost of transferring a tuple from one worker to another worker # define DEFAULT_PARALLEL_SETUP_COST 1000.0 / / the cost of building a parallel execution environment # define DEFAULT_EFFECTIVE_CACHE_SIZE 524288 / * previously described, measured in pages * / double seq_page_cost = DEFAULT_SEQ_PAGE_COST Double random_page_cost = DEFAULT_RANDOM_PAGE_COST; double cpu_tuple_cost = DEFAULT_CPU_TUPLE_COST; double cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST; double cpu_operator_cost = DEFAULT_CPU_OPERATOR_COST; double parallel_tuple_cost = DEFAULT_PARALLEL_TUPLE_COST; double parallel_setup_cost = DEFAULT_PARALLEL_SETUP_COST; int effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE Cost disable_cost = 1.0e10 worker banner 1 followed by 10 zeros, by setting a huge cost, let the optimizer automatically abandon this path, int max_parallel_workers_per_gather = 2 worker used by gather / number 2, source code interpretation

Create_index_path

This function creates the index scan path node, in which the function cost_index is called to calculate the index scan cost.

/ /-create_index_path / * * create_index_path * Creates a path node for an index scan * create an index scan path node * * 'index' is a usable index. * 'indexclauses' is a list of RestrictInfo nodes representing clauses * to be used as index qual conditions in the scan. * 'indexclausecols' is an integer list of index column numbers (zero based) * the indexclauses can be used with. * 'indexorderbys' is a list of bare expressions (no RestrictInfos) * to be used as index ordering operators in the scan. * 'indexorderbycols' is an integer list of index column numbers (zero based) * the ordering operators can be used with. * 'pathkeys' describes the ordering of the path. * 'indexscandir' is ForwardScanDirection or BackwardScanDirection * for an ordered index, or NoMovementScanDirection for * an unordered index. * 'indexonly' is true if an index-only scan is wanted. * 'required_outer' is the set of outer relids for a parameterized path. * 'loop_count' is the number of repetitions of the indexscan to factor into * estimates of caching behavior. * 'partial_path' is true if constructing a parallel index scan path. * * Returns the new path node. * / IndexPath * create_index_path (PlannerInfo * root,// optimizer information IndexOptInfo * index,// index information List * indexclauses,// index constraint linked list List * indexclausecols,// index constraint column numbered linked list, corresponding to indexclauses one-to-one List * indexorderbys / / ORDER BY original expression linked list List * indexorderbycols,//ORDER BY column numbered linked list List * pathkeys,// sort path key ScanDirection indexscandir,// scan direction bool indexonly,// pure index scan? The external Relids double loop_count,// that Relids required_outer,// depends on is used to estimate the number of cache repeats bool partial_path) / whether parallel index scanning {IndexPath * pathnode = makeNode (IndexPath); / / build node RelOptInfo * rel = index- > Rel List * indexquals, * indexqualcols corresponding to the rel;// index Pathnode- > path.pathtype = indexonly? T_IndexOnlyScan: pathnode- > path.parent = rel;//Relation pathnode- > path.pathtarget = rel- > reltarget;// path final projection column pathnode- > path.param_info = get_baserel_parampathinfo (root, rel, required_outer); / / Parametric Information pathnode- > path.parallel_aware = false / / pathnode- > path.parallel_safe = rel- > whether the consider_parallel;// is parallel pathnode- > path.parallel_workers = 0 number of workers pathnode- > path.pathkeys = pathkeys / / sort path key / * Convert clauses to the executor can handle * / / conversion condition clause (clauses) is the executable index expression (indexquals) expand_indexqual_conditions (index, indexclauses, indexclausecols, & indexquals, & indexqualcols); / * fill in the path node information, Fill in the pathnode * / pathnode- > indexinfo = index; pathnode- > indexclauses = indexclauses Pathnode- > indexquals = indexquals; pathnode- > indexqualcols = indexqualcols; pathnode- > indexorderbys = indexorderbys; pathnode- > indexorderbycols = indexorderbycols; pathnode- > indexscandir = indexscandir; cost_index (pathnode, root, loop_count, partial_path); / / estimated cost return pathnode } /-- expand_indexqual_conditions / * * expand_indexqual_conditions * Given a list of RestrictInfo nodes, produce a list of directly usable * indexqual clauses. * given a RestrictInfo node (constraint), generate a directly available index expression clause * * Standard qual clauses (those in the index's opfamily) are passed through * unchanged. Boolean clauses and "special" index operators are expanded * into clauses that the indexscan machinery will know what to do with. * RowCompare clauses are simplified if necessary to create a clause that is * fully checkable by the index. * Standard conditional clauses (in index opfamily) can be used without modification. The * Boolean clause and the "special" index operator are extended to clauses that can be processed by the index scan executor. * if necessary, the RowCompare clause is simplified to a clause that can be fully checked by the index. * * In addition to the expressions themselves, there are auxiliary lists * of the index column numbers that the clauses are meant to be used with; * we generate an updated column number list for the result. (This is not * the identical list because one input clause sometimes produces more than * one output clause.) * in addition to the expression itself, there is an auxiliary linked list of index column numbers that will be used by these clauses. These column numbers * will be updated for result return (not the same linked list, because an input clause sometimes produces multiple output clauses.) . * The input clauses are sorted by column number, and so the output is too. * (This is depended on in various places in both planner and executor.) * input clauses are sorted by column number, and so are output clauses. * / void expand_indexqual_conditions (IndexOptInfo * index, List * indexclauses, List * indexclausecols, List * * indexquals_p, List * * indexqualcols_p) {List * indexquals = NIL; List * indexqualcols = NIL; ListCell * lcc, * lci Forboth (lcc, indexclauses, lci, indexclausecols) / / scan indexed clause linked list and matching column numbers {RestrictInfo * rinfo = (RestrictInfo *) lfirst (lcc); int indexcol = lfirst_int (lci); Expr * clause = rinfo- > clause;// conditional clause Oid curFamily; Oid curCollation; Assert (indexcol

< index->

Nkeycolumns); curFamily = index- > opfamily [indexcol]; / / opfamily curCollation = index- > indexcollations [indexcol] of the index column; / / collation / * First check for boolean cases * / if (IsBooleanOpfamily (curFamily)) / / Boolean {Expr * boolqual Boolqual = expand_boolean_index_clause ((Node *) clause, indexcol, index) / / Boolean expression if (boolqual) {indexquals = lappend (indexquals, make_simple_restrictinfo (boolqual)); / / add indexqualcols = lappend_int (indexqualcols, indexcol) to the result; / / column number continue }} / * * Else it must be an opclause (usual case), ScalarArrayOp, * RowCompare, or NullTest * / if (is_opclause (clause)) / / ordinary operator clause {indexquals = list_concat (indexquals, expand_indexqual_opclause (rinfo) CurFamily, curCollation)) / / merge into the result linked list / * expand_indexqual_opclause can produce multiple clauses * / while (list_length (indexqualcols))

< list_length(indexquals)) indexqualcols = lappend_int(indexqualcols, indexcol); } else if (IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr { /* no extra work at this time */ indexquals = lappend(indexquals, rinfo); indexqualcols = lappend_int(indexqualcols, indexcol); } else if (IsA(clause, RowCompareExpr))//RowCompareExpr { indexquals = lappend(indexquals, expand_indexqual_rowcompare(rinfo, index, indexcol)); indexqualcols = lappend_int(indexqualcols, indexcol); } else if (IsA(clause, NullTest))//NullTest { Assert(index->

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.