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

This article introduces the relevant knowledge of "case analysis of set_base_rel_sizes function and its sub-functions in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Make_one_rel source code:

RelOptInfo * make_one_rel (PlannerInfo * root, List * joinlist) {RelOptInfo * rel; Index rti; / * * Construct the all_baserels Relids set. * / root- > all_baserels = NULL; for (rti = 1; rti

< root->

< 42". Clauses * are recognized as possible range query components if they are restriction * opclauses whose operators have scalarltsel or a related function as their * restriction selectivity estimator. We pair up clauses of this form that * refer to the same variable. An unpairable clause of this kind is simply * multiplied into the selectivity product in the normal way. But when we * find a pair, we know that the selectivities represent the relative * positions of the low and high bounds within the column's range, so instead * of figuring the selectivity as hisel * losel, we can figure it as hisel + * losel - 1. (To visualize this, see that hisel is the fraction of the range * below the high bound, while losel is the fraction above the low bound; so * hisel can be interpreted directly as a 0..1 value but we need to convert * losel to 1-losel before interpreting it as a value. Then the available * range is 1-losel to hisel. However, this calculation double-excludes * nulls, so really we need hisel + losel + null_frac - 1.) * * 优化器还可以识别范围查询,比如x >

34 AND x

< 42,这类范围查询不能简单的把x >

The selection rate of 34 * times x

< 42的选择率,为方便起见,假定x < 42的选择率为hisel,x < 34的选择率为losel, * 那么计算公式应该为hisel - (1 - losel),即hisel + losel -1,考虑NULL值,则范围查询的选择率 * 为hisel + losel + null_frac - 1 * * If either selectivity is exactly DEFAULT_INEQ_SEL, we forget this equation * and instead use DEFAULT_RANGE_INEQ_SEL. The same applies if the equation * yields an impossible (negative) result. * * 如果任意一个选择性都恰好是DEFAULT_INEQ_SEL，那么我们将忘记这个等式， * 而使用DEFAULT_RANGE_INEQ_SEL。这种情况同样适用于如果等式产生了一个不可能的(负的)结果。 * * A free side-effect is that we can recognize redundant inequalities such * as "x < 4 AND x < 5"; only the tighter constraint will be counted. * * 我们可以识别冗余的不等式,比如x < 4 AND x have_lobound) s1 *= rqlist->

< '70000' and dwbh < '65000'; 执行计划如下: testdb=# explain (analyze true,verbose) select t1.* from t_dwxx t1 where dwbh >

'6000000 'and dwbh

< '70000' and dwbh < '65000'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on public.t_dwxx t1 (cost=134.19..956.12 rows=5482 width=23) (actual time=1.484..2.744 rows=5554 loops=1) Output: dwmc, dwbh, dwdz Recheck Cond: (((t1.dwbh)::text >

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

< '70000'::text) AND ((t1.dwbh)::text < '65000'::text)) Heap Blocks: exact=45 ->

Bitmap Index Scan on idx_dwxx_predicate_dwbh (cost=0.00..132.81 rows=5482 width=0) (actual time=1.467..1.467 rows=5554 loops=1) Index Cond: ((t1.dwbh):: text > '60000'::text) AND ((t1.dwbh):: text

< '70000'::text) AND ((t1.dwbh)::text < '65000'::text)) Planning Time: 0.204 ms Execution Time: 3.288 ms 启动gdb跟踪分析: (gdb) b set_baserel_size_estimatesBreakpoint 1 at 0x747bf5: file costsize.c, line 4302.(gdb) cContinuing.Breakpoint 1, set_baserel_size_estimates (root=0x2686fa8, rel=0x26873b8) at costsize.c:43024302 nrows = rel->

Tuples *

Enter the function clauselist_selectivity:

(gdb) stepclauselist_selectivity (root=0x2686fa8, clauses=0x271f600, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0) at clausesel.c:105105 Selectivity S1 = 1.0. 124 rel = find_single_rel_for_clauses (root, clauses) (gdb) 125 if (rel & & rel- > rtekind = = RTE_RELATION & & rel- > statlist! = NIL) # rel (t_dwxx) (gdb) p * rel$1 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2687728, rows = 0, consider_startup = false, consider_param_startup = false, consider_parallel = true, reltarget = 0x271e228, pathlist = 0x0, ppilist = 0x0, partial_pathlist = 0x0, cheapest_startup_path = 0x0, 0x0 = 0x0 Cheapest_unique_path = 0x0, cheapest_parameterized_paths = 0x0, direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 1, reltablespace = 0, rtekind = RTE_RELATION, min_attr =-7, max_attr = 3, attr_needed = 0x271e278, attr_widths = 0x271e308, lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x271e700, statlist = 0x0, pages = 726, tuples = 100000, allvisfrac = 0, subroot = 0x0, subplan_params = 0x0, rel_parallel_workers =-1, serverid = 0, userid = 0 Useridiscurrent = false, fdwroutine = 0x0, fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x271f600, baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 0, 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 = partitioned_child_rels}

Start the loop process:

152 foreach (l, clauses).

The first conditional statement, after calling clause_selectivity, the selection rate is 0.44.

168s2 = clause_selectivity (root, clause, varRelid, jointype, sjinfo); (gdb) 176 if (IsA (clause, RestrictInfo)) (gdb) p S2 $2 = 0.44045086705202319

Add to the scope condition statement:

... 225 switch (get_oprrest (expr- > opno)) (gdb) 234 addRangeClause (& rqlist, clause, (gdb) 236 break)

The second conditional statement, which has a selection rate of 0.66.. clause_selectivity after calling it, will also be added to the scope conditional statement:

168s2 = clause_selectivity (root, clause, varRelid, jointype, sjinfo); (gdb) 176 if (IsA (clause, RestrictInfo)) (gdb) p s2 $3 = 0.66904390539053915. 225 switch (get_oprrest (expr- > opno)) (gdb) 229 addRangeClause (& rqlist, clause)

The third conditional statement, when clause_selectivity is called, has a selection rate of 0.61... Percience, which is also added to the scope conditional statement:

168s2 = clause_selectivity (root, clause, varRelid, jointype, sjinfo); (gdb) 176if (IsA (clause, RestrictInfo)) (gdb) p s2 $4 = 0.61437297872340435. 225 switch (get_oprrest (expr- > opno)) (gdb) 229 addRangeClause (& rqlist, clause)

End the loop and start processing the scope condition statement:

253 while (rqlist! = NULL) (gdb) n # has both upper and lower limits (gdb) p * rqlist$7 = {next = 0x0, var = 0x271dba8, have_lobound = true, have_hibound = true, lobound = 0.44045086705202319, hibound = 0.61437297872340435}. # calculation formula notes have been introduced (gdb) n274 S2 = rqlist- > hibound + rqlist- > lobound-1.0 (gdb) 277s2 + = nulltestsel (root, IS_NULL, rqlist- > var,# final result (gdb) 325 return S1; (gdb) p S1 $11 = 0.054823845775427538.

Back to the main function:

(gdb) set_baserel_size_estimates (root=0x2686fa8, rel=0x26873b8) at costsize.c:43024302 nrows = rel- > tuples * (gdb) 4309 rel- > rows = clamp_row_est (nrows); (gdb) 4311 cost_qual_eval (& rel- > baserestrictcost, rel- > baserestrictinfo, root); (gdb) 4313 set_rel_width (root, rel); (gdb) p rel- > rows$12 = 5482

The result is 5482, which is where the rows=5482 in the implementation plan comes from.

This is the end of the case study of set_base_rel_sizes function and its subfunctions in PostgreSQL. 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.