本篇内容介绍了“postgresql中set_base_rel_sizes函数及其子函数案例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况
本篇内容介绍了“postgresql中set_base_rel_sizes函数及其子函数案例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
make_one_rel源代码:
RelOptInfo *
make_one_rel(PlannerInfo *root, List *joinlist)
{
RelOptInfo *rel;
Index rti;
root->all_baserels = NULL;
for (rti = 1; rti < root->simple_rel_array_size; rti++)//遍历RelOptInfo
{
RelOptInfo *brel = root->simple_rel_array[rti];
if (brel == NULL)
continue;
Assert(brel->relid == rti);
if (brel->reloptkind != RELOPT_BASEREL)
continue;
root->all_baserels = bms_add_member(root->all_baserels, brel->relid);//添加到all_baserels遍历中
}
//设置RelOptInfo的consider_param_startup变量,是否考量fast-start plans
set_base_rel_consider_startup(root);
set_base_rel_sizes(root);//估算Relation的Size并且设置consider_parallel标记
set_base_rel_pathlists(root);//生成Relation的扫描(访问)路径
rel = make_rel_from_joinlist(root, joinlist);
Assert(bms_equal(rel->relids, root->all_baserels));
//返回最上层的RelOptInfo
return rel;
}RelOptInfo
typedef struct RelOptInfo
{
nodeTag type;//节点标识
RelOptKind reloptkind;//RelOpt类型
Relids relids;
double rows;
bool consider_startup;
bool consider_param_startup;
bool consider_parallel;
struct PathTarget *reltarget;
List *pathlist;
List *ppilist;
List *partial_pathlist;
struct Path *cheapest_startup_path;//代价最低的启动路径
struct Path *cheapest_total_path;//代价最低的整体路径
struct Path *cheapest_unique_path;//代价最低的获取唯一值的路径
List *cheapest_parameterized_paths;//代价最低的参数化?路径链表
Relids direct_lateral_relids;
Relids lateral_relids;
//reloptkind=RELOPT_BASEREL时使用的数据结构
Index relid;
Oid reltablespace;
RTEKind rtekind;
AttrNumber min_attr;
AttrNumber max_attr;
Relids *attr_needed;
int32 *attr_widths;
List *lateral_vars;
Relids lateral_referencers;
List *indexlist;
List *statlist;
BlockNumber pages;
double tuples;
double allvisfrac;
PlannerInfo *subroot;
List *subplan_params;
int rel_parallel_workers;
//FWD相关信息
Oid serverid;
Oid userid;
bool useridiscurrent;
struct FdwRoutine *fdwroutine;
void *fdw_private;
//已知的,可保证唯一的Relids链表
List *unique_for_rels;
List *non_unique_for_rels;
List *baserestrictinfo;
QualCost baserestrictcost;
Index baserestrict_min_security;
List *joininfo;
bool has_eclass_joins;
bool consider_partitionwise_join;
Relids top_parent_relids;
//分区表使用
PartitionScheme part_scheme;
int nparts;
struct PartitionBoundInfoData *boundinfo;
List *partition_qual;
struct RelOptInfo **part_rels;
List **partexprs;
List **nullable_partexprs;
List *partitioned_child_rels;
} RelOptInfo;make_one_rel函数调用了set_base_rel_sizes,该函数的主要实现逻辑通过调用set_rel_size实现.现重点考察函数set_rel_size中对基础关系进行估算的处理逻辑,即函数set_plain_rel_size的实现逻辑.
set_plain_rel_size
static void
set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
check_index_predicates(root, rel);//验证部分(条件)索引的可用性
set_baserel_size_estimates(root, rel);//标记rel的输出行数/行宽等信息
}set_plain_rel_size->check_index_predicates
如果部分(条件)索引的谓词与查询语句相匹配,则predOK设置为true
比如:
数据表t1(c1 int,c2 varchar(40),...),如存在索引idx_t1_partial_c1,条件为where c1 > 100
查询条件为where c1 > 100(是否支持>200?),那么该谓词与查询条件相匹配
//----------------------------------------- check_index_predicates
void
check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
{
List *clauselist;//条件链表
bool have_partial;//是否包含部分索引
bool is_target_rel;//目标rel?
Relids otherrels;//Relids
ListCell *lc;//临时变量
Assert(IS_SIMPLE_REL(rel));//rel必须是基础关系
have_partial = false;
foreach(lc, rel->indexlist)//遍历index
{
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
index->indrestrictinfo = rel->baserestrictinfo;//设置索引约束条件
if (index->indpred)
have_partial = true;//存在部分索引
}
if (!have_partial)
return;
clauselist = list_copy(rel->baserestrictinfo);//条件语句初始化
foreach(lc, rel->joininfo)//遍历连接条件
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);//条件
if (!join_clause_is_movable_to(rinfo, rel))//条件是否可以下推到Rel中
continue;//不可用,下一个条件
clauselist = lappend(clauselist, rinfo);//可以,则添加到条件语句链表中
}
if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
otherrels = bms_difference(root->all_baserels,
find_childrel_parents(root, rel));//
else
otherrels = bms_difference(root->all_baserels, rel->relids);//获取与rel无关的其他rels
if (!bms_is_empty(otherrels))
clauselist =
list_concat(clauselist,
generate_join_implied_equalities(root,
bms_uNIOn(rel->relids,
otherrels),
otherrels,
rel));//添加到条件语句
is_target_rel = (rel->relid == root->parse->resultRelation ||
get_plan_rowmark(root->rowMarks, rel->relid) != NULL);
foreach(lc, rel->indexlist)//遍历index
{
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
ListCell *lcr;
if (index->indpred == NIL)
continue;
if (!index->predOK)
index->predOK = predicate_implied_by(index->indpred, clauselist,
false);//设置predOK参数
if (is_target_rel)
continue;
index->indrestrictinfo = NIL;
foreach(lcr, rel->baserestrictinfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcr);
if (contain_mutable_functions((Node *) rinfo->clause) ||
!predicate_implied_by(list_make1(rinfo->clause),
index->indpred, false))
index->indrestrictinfo = lappend(index->indrestrictinfo, rinfo);//
}
}
}set_plain_rel_size->set_baserel_size_estimates
函数注释:
//----------------------------------------- set_baserel_size_estimates
源代码:
void
set_baserel_size_estimates(PlannerInfo *root, RelOptInfo *rel)
{
double nrows;
Assert(rel->relid > 0);
nrows = rel->tuples *
clauselist_selectivity(root,
rel->baserestrictinfo,
0,
JOIN_INNER,
NULL);//元组总数*选择率
rel->rows = clamp_row_est(nrows);
cost_qual_eval(&rel->baserestrictcost, rel->baserestrictinfo, root);
set_rel_width(root, rel);
}
//----------------------------------------- clauselist_selectivity
Selectivity
clauselist_selectivity(PlannerInfo *root,
List *clauses,
int varRelid,
JoinType jointype,
SpecialJoinInfo *sjinfo)
{
Selectivity s1 = 1.0;//默认返回值
RelOptInfo *rel;//
Bitmapset *estimatedclauses = NULL;//位图集合
RangeQueryClause *rqlist = NULL;//范围查询语句
ListCell *l;//临时变量
int listidx;
if (list_length(clauses) == 1)
return clause_selectivity(root, (Node *) linitial(clauses),
varRelid, jointype, sjinfo);//单个条件
//如果条件链表中的元素依赖的rel有且只有一个,则返回此rel
rel = find_single_rel_for_clauses(root, clauses);
//应用dependencies_clauselist_selectivity中的可用的条件进行选择率估算
if (rel && rel->rtekind == RTE_RELATION && rel->statlist != NIL)
{
s1 *= dependencies_clauselist_selectivity(root, clauses, varRelid,
jointype, sjinfo, rel,
&estimatedclauses);
}
listidx = -1;
foreach(l, clauses)//遍历
{
Node *clause = (Node *) lfirst(l);//链表中的元素
RestrictInfo *rinfo;
Selectivity s2;
listidx++;
if (bms_is_member(listidx, estimatedclauses))//跳过已处理的条件
continue;
s2 = clause_selectivity(root, clause, varRelid, jointype, sjinfo);//获取条件选择率
if (IsA(clause, RestrictInfo))//条件语句是RestrictInfo类型
{
rinfo = (RestrictInfo *) clause;
if (rinfo->pseudoconstant)//常量
{
s1 = s1 * s2;//直接相乘
continue;
}
clause = (Node *) rinfo->clause;//条件表达式
}
else
rinfo = NULL;//不是RestrictInfo类型,rinfo设置为NULL
if (is_opclause(clause) && list_length(((OpExpr *) clause)->args) == 2)//OpExpr
{
OpExpr *expr = (OpExpr *) clause;//条件语句
bool varonleft = true;
bool ok;
if (rinfo)//rinfo中的条件语句
{
ok = (bms_membership(rinfo->clause_relids) == BMS_SINGLETON) &&
(is_pseudo_constant_clause_relids(lsecond(expr->args),
rinfo->right_relids) ||
(varonleft = false,
is_pseudo_constant_clause_relids(linitial(expr->args),
rinfo->left_relids)));
}
else//裸条件语句
{
ok = (NumRelids(clause) == 1) &&
(is_pseudo_constant_clause(lsecond(expr->args)) ||
(varonleft = false,
is_pseudo_constant_clause(linitial(expr->args))));
}
if (ok)//校验通过
{
switch (get_oprrest(expr->opno))
{
case F_ScalaRLTSEL:
case F_SCALARLESEL:
addRangeClause(&rqlist, clause,
varonleft, true, s2);//范围条件
break;
case F_SCALARGTSEL:
case F_SCALARGESEL:
addRangeClause(&rqlist, clause,
varonleft, false, s2);//范围条件
break;
default:
s1 = s1 * s2;//直接相乘
break;
}
continue;
}
}
s1 = s1 * s2;//直接相乘
}
while (rqlist != NULL)//处理范围条件
{
RangeQueryClause *rqnext;
if (rqlist->have_lobound && rqlist->have_hibound)//存在上下限
{
Selectivity s2;//选择率
if (rqlist->hibound == DEFAULT_INEQ_SEL ||
rqlist->lobound == DEFAULT_INEQ_SEL)//默认值
{
s2 = DEFAULT_RANGE_INEQ_SEL;//默认为DEFAULT_RANGE_INEQ_SEL
}
else
{
s2 = rqlist->hibound + rqlist->lobound - 1.0;//计算公式在注释已解释
s2 += nulltestsel(root, IS_NULL, rqlist->var,
varRelid, jointype, sjinfo);//NULL值
if (s2 <= 0.0)//小于0?
{
if (s2 < -0.01)
{
s2 = DEFAULT_RANGE_INEQ_SEL;//小于﹣1%,默认值
}
else
{
s2 = 1.0e-10;,//否则设置为1的﹣10次方
}
}
}
s1 *= s2;//直接相乘
}
else//只有其中一个限制
{
if (rqlist->have_lobound)
s1 *= rqlist->lobound;//下限
else
s1 *= rqlist->hibound;//上限
}
rqnext = rqlist->next;//释放资源
pfree(rqlist);
rqlist = rqnext;
}
return s1;//返回选择率
}
Selectivity
clause_selectivity(PlannerInfo *root,
Node *clause,
int varRelid,
JoinType jointype,
SpecialJoinInfo *sjinfo)
{
Selectivity s1 = 0.5;
RestrictInfo *rinfo = NULL;
bool cacheable = false;
if (clause == NULL)
return s1;
if (IsA(clause, RestrictInfo))//RestrictInfo
{
rinfo = (RestrictInfo *) clause;
if (rinfo->pseudoconstant)//pseudoconstant,不影响选择率
{
if (!IsA(rinfo->clause, Const))
return (Selectivity) 1.0;//返回1.0
}
if (rinfo->nORM_selec > 1)
return (Selectivity) 1.0;//返回1.0
if (varRelid == 0 ||
bms_is_subset_singleton(rinfo->clause_relids, varRelid))//varRelid为0
{
if (jointype == JOIN_INNER)
{
if (rinfo->norm_selec >= 0)
return rinfo->norm_selec;
}
else
{
if (rinfo->outer_selec >= 0)
return rinfo->outer_selec;
}
cacheable = true;
}
if (rinfo->orclause)
clause = (Node *) rinfo->orclause;
else
clause = (Node *) rinfo->clause;
}
if (IsA(clause, Var))//Var
{
Var *var = (Var *) clause;
if (var->varlevelsup == 0 &&
(varRelid == 0 || varRelid == (int) var->varno))
{
s1 = boolvarsel(root, (Node *) var, varRelid);//bool Var选择率
}
}
else if (IsA(clause, Const))//常量
{
Const *con = (Const *) clause;
s1 = con->constisnull ? 0.0 :
DatumGetBool(con->constvalue) ? 1.0 : 0.0;//常量有效则为1.0,否则为0.0
}
else if (IsA(clause, Param))//参数
{
Node *subst = estimate_expression_value(root, clause);
if (IsA(subst, Const))
{
Const *con = (Const *) subst;
s1 = con->constisnull ? 0.0 :
DatumGetBool(con->constvalue) ? 1.0 : 0.0;
}
else
{
}
}
else if (not_clause(clause))//反选
{
s1 = 1.0 - clause_selectivity(root,
(Node *) get_notclausearg((Expr *) clause),
varRelid,
jointype,
sjinfo);
}
else if (and_clause(clause))//AND语句
{
s1 = clauselist_selectivity(root,
((BoolExpr *) clause)->args,
varRelid,
jointype,
sjinfo);//递归调用
}
else if (or_clause(clause))//OR语句
{
ListCell *arg;
s1 = 0.0;
foreach(arg, ((BoolExpr *) clause)->args)
{
Selectivity s2 = clause_selectivity(root,
(Node *) lfirst(arg),
varRelid,
jointype,
sjinfo);//递归调用
s1 = s1 + s2 - s1 * s2;
}
}
else if (is_opclause(clause) || IsA(clause, DistinctExpr))
{
OpExpr *opclause = (OpExpr *) clause;
Oid opno = opclause->opno;
if (treat_as_join_clause(clause, rinfo, varRelid, sjinfo))
{
s1 = join_selectivity(root, opno,
opclause->args,
opclause->inputcollid,
jointype,
sjinfo);//连接条件
}
else
{
s1 = restriction_selectivity(root, opno,
opclause->args,
opclause->inputcollid,
varRelid);//限制条件
}
if (IsA(clause, DistinctExpr))
s1 = 1.0 - s1;//Distinct?
}
else if (IsA(clause, ScalarArrayOpExpr))//数组
{
s1 = scalararraysel(root,
(ScalarArrayOpExpr *) clause,
treat_as_join_clause(clause, rinfo,
varRelid, sjinfo),
varRelid,
jointype,
sjinfo);
}
else if (IsA(clause, RowCompareExpr))//行对比
{
s1 = rowcomparesel(root,
(RowCompareExpr *) clause,
varRelid,
jointype,
sjinfo);
}
else if (IsA(clause, NullTest))//NullTest
{
s1 = nulltestsel(root,
((NullTest *) clause)->nulltesttype,
(Node *) ((NullTest *) clause)->arg,
varRelid,
jointype,
sjinfo);
}
else if (IsA(clause, BooleanTest))//BooleanTest
{
s1 = booltestsel(root,
((BooleanTest *) clause)->booltesttype,
(Node *) ((BooleanTest *) clause)->arg,
varRelid,
jointype,
sjinfo);
}
else if (IsA(clause, CurrentOfExpr))//CurrentOfExpr
{
CurrentOfExpr *cexpr = (CurrentOfExpr *) clause;
RelOptInfo *crel = find_base_rel(root, cexpr->cvarno);
if (crel->tuples > 0)
s1 = 1.0 / crel->tuples;
}
else if (IsA(clause, RelabelType))//RelabelType
{
s1 = clause_selectivity(root,
(Node *) ((RelabelType *) clause)->arg,
varRelid,
jointype,
sjinfo);
}
else if (IsA(clause, CoerceToDomain))//CoerceToDomain
{
s1 = clause_selectivity(root,
(Node *) ((CoerceToDomain *) clause)->arg,
varRelid,
jointype,
sjinfo);
}
else
{
s1 = boolvarsel(root, clause, varRelid);//默认为bool Var
}
if (cacheable)//缓存?
{
if (jointype == JOIN_INNER)
rinfo->norm_selec = s1;
else
rinfo->outer_selec = s1;
}
#ifdef SELECTIVITY_DEBUG
elog(DEBUG4, "clause_selectivity: s1 %f", s1);
#endif
return s1;
}
static RelOptInfo *
find_single_rel_for_clauses(PlannerInfo *root, List *clauses)
{
int lastrelid = 0;
ListCell *l;
foreach(l, clauses)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
int relid;
if (!IsA(rinfo, RestrictInfo))
return NULL;
if (bms_is_empty(rinfo->clause_relids))
continue;
if (!bms_get_singleton_member(rinfo->clause_relids, &relid))
return NULL;
if (lastrelid == 0)
lastrelid = relid;
else if (relid != lastrelid)
return NULL;
}
if (lastrelid != 0)
return find_base_rel(root, lastrelid);
return NULL;
}
//---------------------------------------------- clamp_row_est
double
clamp_row_est(double nrows)
{
if (nrows <= 1.0)
nrows = 1.0;//小于1,返回1
else
nrows = rint(nrows);//整型
return nrows;
}
//---------------------------------------------- cost_qual_eval
void
cost_qual_eval(QualCost *cost, List *quals, PlannerInfo *root)
{
cost_qual_eval_context context;
ListCell *l;
context.root = root;
context.total.startup = 0;
context.total.per_tuple = 0;
foreach(l, quals)
{
Node *qual = (Node *) lfirst(l);
cost_qual_eval_walker(qual, &context);
}
*cost = context.total;
}
//---------------------------------------------- set_rel_width
static void
set_rel_width(PlannerInfo *root, RelOptInfo *rel)
{
Oid reloid = planner_rt_fetch(rel->relid, root)->relid;
int32 tuple_width = 0;
bool have_wholerow_var = false;
ListCell *lc;
rel->reltarget->cost.startup = 0;
rel->reltarget->cost.per_tuple = 0;
foreach(lc, rel->reltarget->exprs)
{
Node *node = (Node *) lfirst(lc);
if (IsA(node, Var) &&
((Var *) node)->varno == rel->relid)
{
Var *var = (Var *) node;
int ndx;
int32 item_width;
Assert(var->varattno >= rel->min_attr);
Assert(var->varattno <= rel->max_attr);
ndx = var->varattno - rel->min_attr;
if (var->varattno == 0)
{
have_wholerow_var = true;
continue;
}
if (rel->attr_widths[ndx] > 0)
{
tuple_width += rel->attr_widths[ndx];
continue;
}
if (reloid != InvalidOid && var->varattno > 0)
{
item_width = get_attavgwidth(reloid, var->varattno);
if (item_width > 0)
{
rel->attr_widths[ndx] = item_width;
tuple_width += item_width;
continue;
}
}
item_width = get_typavgwidth(var->vartype, var->vartypmod);
Assert(item_width > 0);
rel->attr_widths[ndx] = item_width;
tuple_width += item_width;
}
else if (IsA(node, PlaceHolderVar))
{
PlaceHolderVar *phv = (PlaceHolderVar *) node;
PlaceHolderInfo *phinfo = find_placeholder_info(root, phv, false);
QualCost cost;
tuple_width += phinfo->ph_width;
cost_qual_eval_node(&cost, (Node *) phv->phexpr, root);
rel->reltarget->cost.startup += cost.startup;
rel->reltarget->cost.per_tuple += cost.per_tuple;
}
else
{
int32 item_width;
QualCost cost;
item_width = get_typavgwidth(exprType(node), exprTypmod(node));
Assert(item_width > 0);
tuple_width += item_width;
cost_qual_eval_node(&cost, node, root);
rel->reltarget->cost.startup += cost.startup;
rel->reltarget->cost.per_tuple += cost.per_tuple;
}
}
if (have_wholerow_var)
{
int32 wholerow_width = MAXALIGN(SizeofHeapTupleHeader);
if (reloid != InvalidOid)
{
wholerow_width += get_relation_data_width(reloid,
rel->attr_widths - rel->min_attr);
}
else
{
AttrNumber i;
for (i = 1; i <= rel->max_attr; i++)
wholerow_width += rel->attr_widths[i - rel->min_attr];
}
rel->attr_widths[0 - rel->min_attr] = wholerow_width;
tuple_width += wholerow_width;
}
Assert(tuple_width >= 0);
rel->reltarget->width = tuple_width;
}测试脚本:
单位信息表,插入100,000行数据,dwbh为主键,同时创建函数索引和部分索引
drop table if exists t_dwxx;
create table t_dwxx(dwmc varchar(100),dwbh varchar(20),dwdz varchar(100));
alter table t_dwxx add primary key(dwbh);
create index idx_dwxx_expr on t_dwxx(trim(dwmc));
create index idx_dwxx_predicate_dwbh on t_dwxx(dwbh) where dwbh > '50000';
truncate table t_dwxx;
insert into t_dwxx(dwmc,dwbh,dwdz)
select 'DWMC'||generate_series(1,100000),generate_series(1,100000)||'','DWDZ'||generate_series(1,100000);个人信息表,插入5,000,000行数据,在grbh和dwbh上创建索引
drop table if exists t_grxx;
create table t_grxx(dwbh varchar(10),grbh varchar(10),xm varchar(20),xb varchar(10),nl int);
insert into t_grxx(dwbh,grbh,xm,xb,nl)
select generate_series(1,5000000)/50||'',generate_series(1,5000000),'XM'||generate_series(1,5000000),
(case when (floor(random()*2)=0) then '男' else '女' end),floor(random() * 100 + 1)::int;
create index idx_t_grxx_grbh on t_grxx(grbh);
create index idx_t_dwxx_grbh on t_grxx(dwbh);个人缴费信息表,在grbh上创建索引,多次插入5,000,000行数据
drop table if exists t_jfxx;
create table t_jfxx(grbh varchar(10),ny varchar(10),je float);
-- 根据实际情况,多次执行以下sql
insert into t_jfxx(grbh,ny,je)
select generate_series(1,5000000),
to_char(now()::timestamp - (floor(random()*240+1)||' month')::interval,'yyyymm'),
floor(random()*10000+1);
create index idx_t_jfxx_grbh on t_jfxx(grbh);执行简单的查询SQL:
select t1.* from t_dwxx t1 where dwbh > '60000' and dwbh < '70000' and dwbh < '65000';执行计划如下:
testdb=# explain (analyze true,verbose) select t1.* from t_dwxx t1 where dwbh > '60000' 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'::tex
t))
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=555
4 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_estimates
Breakpoint 1 at 0x747bf5: file costsize.c, line 4302.
(gdb) c
Continuing.
Breakpoint 1, set_baserel_size_estimates (root=0x2686fa8, rel=0x26873b8) at costsize.c:4302
4302 nrows = rel->tuples *进入函数clauselist_selectivity:
(gdb) step
clauselist_selectivity (root=0x2686fa8, clauses=0x271f600, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0) at clausesel.c:105
105 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, cheapest_total_path = 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 = 0x0}开始循环处理:
152 foreach(l, clauses)
...第一个条件语句,调用clause_selectivity后,选择率为0.44...
168 s2 = clause_selectivity(root, clause, varRelid, jointype, sjinfo);
(gdb)
176 if (IsA(clause, RestrictInfo))
(gdb) p s2
$2 = 0.44045086705202319添加到范围条件语句中:
...
225 switch (get_oprrest(expr->opno))
(gdb)
234 addRangeClause(&rqlist, clause,
(gdb)
236 break;第二个条件语句,调用clause_selectivity后,选择率为0.66...,,同样会添加到范围条件语句中:
168 s2 = 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,第三个条件语句,调用clause_selectivity后,选择率为0.61...,,同样会添加到范围条件语句中:
168 s2 = clause_selectivity(root, clause, varRelid, jointype, sjinfo);
(gdb)
176 if (IsA(clause, RestrictInfo))
(gdb) p s2
$4 = 0.61437297872340435
...
225 switch (get_oprrest(expr->opno))
(gdb)
229 addRangeClause(&rqlist, clause,结束循环,开始处理范围条件语句:
253 while (rqlist != NULL)
(gdb) n
#既有上限,也有下限
(gdb) p *rqlist
$7 = {next = 0x0, var = 0x271dba8, have_lobound = true, have_hibound = true, lobound = 0.44045086705202319,
hibound = 0.61437297872340435}
...
#计算公式注释已作介绍
(gdb) n
274 s2 = rqlist->hibound + rqlist->lobound - 1.0;
(gdb)
277 s2 += nulltestsel(root, IS_NULL, rqlist->var,
#最终结果
(gdb)
325 return s1;
(gdb) p s1
$11 = 0.054823845775427538
...回到主函数:
(gdb)
set_baserel_size_estimates (root=0x2686fa8, rel=0x26873b8) at costsize.c:4302
4302 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结果为5482,执行计划中的rows=5482就是这么来的.
“PostgreSQL中set_base_rel_sizes函数及其子函数案例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！
--结束END--
本文标题: PostgreSQL中set_base_rel_sizes函数及其子函数案例分析
本文链接: https://www.lsjlt.com/news/64946.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0