本篇内容介绍了“postgresql中哪个函数创建两个rels连接所生成的RelOptInfo”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧
本篇内容介绍了“postgresql中哪个函数创建两个rels连接所生成的RelOptInfo”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
RelOptInfo
typedef enum RelOptKind
{
RELOPT_BASEREL,//基本关系(如基表/子查询等)
RELOPT_JOINREL,//连接产生的关系,要注意的是通过连接等方式产生的结果亦可以视为关系
RELOPT_OTHER_MEMBER_REL,
RELOPT_OTHER_JOINREL,
RELOPT_UPPER_REL,//上层的关系
RELOPT_OTHER_UPPER_REL,
RELOPT_DEADREL
} RelOptKind;
#define IS_SIMPLE_REL(rel) \
((rel)->reloptkind == RELOPT_BASEREL || \
(rel)->reloptkind == RELOPT_OTHER_MEMBER_REL)
#define IS_JOIN_REL(rel) \
((rel)->reloptkind == RELOPT_JOINREL || \
(rel)->reloptkind == RELOPT_OTHER_JOINREL)
#define IS_UPPER_REL(rel) \
((rel)->reloptkind == RELOPT_UPPER_REL || \
(rel)->reloptkind == RELOPT_OTHER_UPPER_REL)
#define IS_OTHER_REL(rel) \
((rel)->reloptkind == RELOPT_OTHER_MEMBER_REL || \
(rel)->reloptkind == RELOPT_OTHER_JOINREL || \
(rel)->reloptkind == RELOPT_OTHER_UPPER_REL)
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;join_search_one_level->...(如make_rels_by_clause_joins)->make_join_rel函数创建两个rels连接所生成的RelOptInfo,并创建访问路径添加到RelOptInfo的pathlist链表中。这里重点介绍make_join_rel函数中的build_join_rel函数,populate_joinrel_with_paths函数下一小节再行介绍.
//---------------------------------------------------- make_join_rel
RelOptInfo *
make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
{
Relids joinrelids;
SpecialJoinInfo *sjinfo;
bool reversed;
SpecialJoinInfo sjinfo_data;
RelOptInfo *joinrel;
List *restrictlist;
Assert(!bms_overlap(rel1->relids, rel2->relids));//两者无交接
joinrelids = bms_uNIOn(rel1->relids, rel2->relids);//两个rel涉及的rels
if (!join_is_legal(root, rel1, rel2, joinrelids,
&sjinfo, &reversed))//是否非法
{
bms_free(joinrelids);
return NULL;//返回
}
if (reversed)//位置是否调换
{
RelOptInfo *trel = rel1;
rel1 = rel2;
rel2 = trel;
}
if (sjinfo == NULL)
{
sjinfo = &sjinfo_data;
sjinfo->type = T_SpecialJoinInfo;
sjinfo->min_lefthand = rel1->relids;
sjinfo->min_righthand = rel2->relids;
sjinfo->syn_lefthand = rel1->relids;
sjinfo->syn_righthand = rel2->relids;
sjinfo->jointype = JOIN_INNER;
sjinfo->lhs_strict = false;
sjinfo->delay_upper_joins = false;
sjinfo->semi_can_btree = false;
sjinfo->semi_can_hash = false;
sjinfo->semi_operators = NIL;
sjinfo->semi_rhs_exprs = NIL;
}
joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo,
&restrictlist);
if (is_dummy_rel(joinrel))
{
bms_free(joinrelids);
return joinrel;
}
//为连接生成的新关系构造访问路径
populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo,
restrictlist);
bms_free(joinrelids);//释放资源
return joinrel;//返回joinrel
}
//-------------------------------------------------------------------- build_join_rel
RelOptInfo *
build_join_rel(PlannerInfo *root,
Relids joinrelids,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel,
SpecialJoinInfo *sjinfo,
List **restrictlist_ptr)
{
RelOptInfo *joinrel;
List *restrictlist;
Assert(!IS_OTHER_REL(outer_rel) && !IS_OTHER_REL(inner_rel));
joinrel = find_join_rel(root, joinrelids);
if (joinrel)//已存在
{
if (restrictlist_ptr)
*restrictlist_ptr = build_joinrel_restrictlist(root,
joinrel,
outer_rel,
inner_rel);//如已存在约束条件,则找出相应的信息即可
return joinrel;//返回
}
joinrel = makeNode(RelOptInfo);
joinrel->reloptkind = RELOPT_JOINREL;
joinrel->relids = bms_copy(joinrelids);
joinrel->rows = 0;
joinrel->consider_startup = (root->tuple_fraction > 0);
joinrel->consider_param_startup = false;
joinrel->consider_parallel = false;
joinrel->reltarget = create_empty_pathtarget();
joinrel->pathlist = NIL;
joinrel->ppilist = NIL;
joinrel->partial_pathlist = NIL;
joinrel->cheapest_startup_path = NULL;
joinrel->cheapest_total_path = NULL;
joinrel->cheapest_unique_path = NULL;
joinrel->cheapest_parameterized_paths = NIL;
joinrel->direct_lateral_relids =
bms_union(outer_rel->direct_lateral_relids,
inner_rel->direct_lateral_relids);
joinrel->lateral_relids = min_join_parameterization(root, joinrel->relids,
outer_rel, inner_rel);
joinrel->relid = 0;
joinrel->rtekind = RTE_JOIN;//RTE_JOIN
joinrel->min_attr = 0;
joinrel->max_attr = 0;
joinrel->attr_needed = NULL;
joinrel->attr_widths = NULL;
joinrel->lateral_vars = NIL;
joinrel->lateral_referencers = NULL;
joinrel->indexlist = NIL;
joinrel->statlist = NIL;
joinrel->pages = 0;
joinrel->tuples = 0;
joinrel->allvisfrac = 0;
joinrel->subroot = NULL;
joinrel->subplan_params = NIL;
joinrel->rel_parallel_workers = -1;
joinrel->serverid = InvalidOid;
joinrel->userid = InvalidOid;
joinrel->useridiscurrent = false;
joinrel->fdwroutine = NULL;
joinrel->fdw_private = NULL;
joinrel->unique_for_rels = NIL;
joinrel->non_unique_for_rels = NIL;
joinrel->baserestrictinfo = NIL;
joinrel->baserestrictcost.startup = 0;
joinrel->baserestrictcost.per_tuple = 0;
joinrel->baserestrict_min_security = UINT_MAX;
joinrel->joininfo = NIL;
joinrel->has_eclass_joins = false;
joinrel->consider_partitionwise_join = false;
joinrel->top_parent_relids = NULL;
joinrel->part_scheme = NULL;
joinrel->nparts = 0;
joinrel->boundinfo = NULL;
joinrel->partition_qual = NIL;
joinrel->part_rels = NULL;
joinrel->partexprs = NULL;
joinrel->nullable_partexprs = NULL;
joinrel->partitioned_child_rels = NIL;
set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
build_joinrel_tlist(root, joinrel, outer_rel);//连接外表
build_joinrel_tlist(root, joinrel, inner_rel);//连接内表
add_placeholders_to_joinrel(root, joinrel, outer_rel, inner_rel);//添加PHV
joinrel->direct_lateral_relids =
bms_del_members(joinrel->direct_lateral_relids, joinrel->relids);
if (bms_is_empty(joinrel->direct_lateral_relids))
joinrel->direct_lateral_relids = NULL;
restrictlist = build_joinrel_restrictlist(root, joinrel,
outer_rel, inner_rel);//构建限制条件链表
if (restrictlist_ptr)
*restrictlist_ptr = restrictlist;
build_joinrel_joinlist(joinrel, outer_rel, inner_rel);//构建连接条件链表
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
sjinfo->jointype);
set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
sjinfo, restrictlist);
if (inner_rel->consider_parallel && outer_rel->consider_parallel &&
is_parallel_safe(root, (Node *) restrictlist) &&
is_parallel_safe(root, (Node *) joinrel->reltarget->exprs))
joinrel->consider_parallel = true;
add_join_rel(root, joinrel);//添加到优化器信息中
if (root->join_rel_level)
{
Assert(root->join_cur_level > 0);
Assert(root->join_cur_level <= bms_num_members(joinrel->relids));
root->join_rel_level[root->join_cur_level] =
lappend(root->join_rel_level[root->join_cur_level], joinrel);//加入到链表中
}
return joinrel;
}
//----------------------------------------------- find_join_rel
RelOptInfo *
find_join_rel(PlannerInfo *root, Relids relids)
{
if (!root->join_rel_hash && list_length(root->join_rel_list) > 32)
build_join_rel_hash(root);
if (root->join_rel_hash)//hash
{
Relids hashkey = relids;
JoinHashEntry *hentry;
hentry = (JoinHashEntry *) hash_search(root->join_rel_hash,
&hashkey,
HASH_FIND,
NULL);
if (hentry)
return hentry->join_rel;
}
else//线性
{
ListCell *l;
foreach(l, root->join_rel_list)
{
RelOptInfo *rel = (RelOptInfo *) lfirst(l);
if (bms_equal(rel->relids, relids))
return rel;
}
}
return NULL;
}
//----------------------------------------------- build_joinrel_restrictlist
static List *
build_joinrel_restrictlist(PlannerInfo *root,
RelOptInfo *joinrel,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel)
{
List *result;
result = subbuild_joinrel_restrictlist(joinrel, outer_rel->joininfo, NIL);
result = subbuild_joinrel_restrictlist(joinrel, inner_rel->joininfo, result);
result = list_concat(result,
generate_join_implied_equalities(root,
joinrel->relids,
outer_rel->relids,
inner_rel));
return result;
}
static void
build_joinrel_joinlist(RelOptInfo *joinrel,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel)
{
List *result;
result = subbuild_joinrel_joinlist(joinrel, outer_rel->joininfo, NIL);
result = subbuild_joinrel_joinlist(joinrel, inner_rel->joininfo, result);
joinrel->joininfo = result;
}测试表和数据继续沿用上一节创建的表和数据，使用的sql语句如下:
testdb=# explain verbose select a.*,b.c1,c.c2,d.c2,e.c1,f.c2
from a inner join b on a.c1=b.c1,c,d,e inner join f on e.c1 = f.c1 and e.c1 < 100
where a.c1=f.c1 and b.c1=c.c1 and c.c1 = d.c1 and d.c1 = e.c1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Nested Loop (cost=101.17..2218.24 rows=2 width=42)
Output: a.c1, a.c2, b.c1, c.c2, d.c2, e.c1, f.c2
Join Filter: (a.c1 = b.c1)
-> Hash Join (cost=3.25..196.75 rows=100 width=22)
Output: a.c1, a.c2, c.c2, c.c1
Hash Cond: (c.c1 = a.c1)
-> Seq Scan on public.c (cost=0.00..155.00 rows=10000 width=12)
Output: c.c1, c.c2
-> Hash (cost=2.00..2.00 rows=100 width=10)
Output: a.c1, a.c2
-> Seq Scan on public.a (cost=0.00..2.00 rows=100 width=10)
Output: a.c1, a.c2
-> Materialize (cost=97.92..2014.00 rows=5 width=32)
Output: b.c1, d.c2, d.c1, e.c1, f.c2, f.c1
-> Hash Join (cost=97.92..2013.97 rows=5 width=32)
Output: b.c1, d.c2, d.c1, e.c1, f.c2, f.c1
Hash Cond: (f.c1 = b.c1)
-> Seq Scan on public.f (cost=0.00..1541.00 rows=100000 width=13)
Output: f.c1, f.c2
-> Hash (cost=97.86..97.86 rows=5 width=19)
Output: b.c1, d.c2, d.c1, e.c1
-> Hash Join (cost=78.10..97.86 rows=5 width=19)
Output: b.c1, d.c2, d.c1, e.c1
Hash Cond: (b.c1 = e.c1)
-> Seq Scan on public.b (cost=0.00..16.00 rows=1000 width=4)
Output: b.c1, b.c2
-> Hash (cost=78.04..78.04 rows=5 width=15)
Output: d.c2, d.c1, e.c1
-> Hash Join (cost=73.24..78.04 rows=5 width=15)
Output: d.c2, d.c1, e.c1
Hash Cond: (d.c1 = e.c1)
-> Seq Scan on public.d (cost=0.00..4.00 rows=200 width=11)
Output: d.c1, d.c2
-> Hash (cost=72.00..72.00 rows=99 width=4)
Output: e.c1
-> Seq Scan on public.e (cost=0.00..72.00 rows=99 width=4)
Output: e.c1
Filter: (e.c1 < 100)
(38 rows)优化器选择了2 rels + 4 rels的连接模式,跟踪重点考察bushy plans的执行情况.
启动gdb,设置断点,只考察level=6的情况
(gdb) b join_search_one_level
Breakpoint 2 at 0x7b0289: file joinrels.c, line 67.
(gdb) c
Continuing.
...
(gdb) c
Continuing.
Breakpoint 2, join_search_one_level (root=0x241ca38, level=6) at joinrels.c:67
67 List **joinrels = root->join_rel_level;完成5(rels)+1(rels)的调用
(gdb) b joinrels.c:142
Breakpoint 3 at 0x7b03c4: file joinrels.c, line 142.
(gdb) c
Continuing.
Breakpoint 3, join_search_one_level (root=0x241ca38, level=6) at joinrels.c:142
142 for (k = 2;; k++)查看root->join_rel_level[6]
(gdb) p *root->join_rel_level[6]
$1 = {type = T_List, length = 1, head = 0x24c8468, tail = 0x24c8468}查看该链表中的RelOptInfo
(gdb) set $roi=(RelOptInfo *)root->join_rel_level[6]->head->data.ptr_value
(gdb) p *$roi
$3 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x1eb8330, rows = 2, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x1f25ac8, pathlist = 0x1f25f80, 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 = 0, reltablespace = 0,
rtekind = RTE_JOIN, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0, lateral_vars = 0x0,
lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, 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 = 0x0, baserestrictcost = {
startup = 0, per_tuple = 0}, baserestrict_min_security = 4294967295, 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}查看该RelOptInfo的pathlist
(gdb) p *$roi->pathlist
$4 = {type = T_List, length = 1, head = 0x1f25f60, tail = 0x1f25f60}
(gdb) p *(Node *)$roi->pathlist->head->data.ptr_value
$5 = {type = T_NestPath}
(gdb) set $np=(NestPath *)$roi->pathlist->head->data.ptr_value
(gdb) p *(NestPath *)$np
$5 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x1f258b8, pathtarget = 0x1f25ac8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 2, startup_cost = 290.57499999999999,
total_cost = 2216.1374999999998, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x1f07c00, innerjoinpath = 0x1f27c40, joinrestrictinfo = 0x1f27e60}查看该连接的外表和内部访问路径
(gdb) p *$np->outerjoinpath
$6 = {type = T_Path, pathtype = T_SeqScan, parent = 0x1e228e8, pathtarget = 0x1f04bc0, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100, startup_cost = 0, total_cost = 2,
pathkeys = 0x0}
(gdb) p *$np->innerjoinpath
$7 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x1ebb538, pathtarget = 0x1ebb748, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 5, startup_cost = 290.57499999999999,
total_cost = 2206.6500000000001, pathkeys = 0x0}下面开始尝试bushy plans,即（2/4 rels+ 4/2 rels)或(3 rels + 3 rels)模式,重点考察ac + bdef这种组合
(gdb) b joinrels.c:156
Breakpoint 3 at 0x7557df: file joinrels.c, line 156.
(gdb) c
Continuing.
Breakpoint 3, join_search_one_level (root=0x1e214b8, level=6) at joinrels.c:164
164 if (old_rel->joininfo == NIL && !old_rel->has_eclass_joins &&
(gdb) p *old_rel->relids->Words
$13 = 18进入make_join_rel函数
173 for_each_cell(r2, other_rels)
(gdb)
175 RelOptInfo *new_rel = (RelOptInfo *) lfirst(r2);
(gdb)
177 if (!bms_overlap(old_rel->relids, new_rel->relids))
(gdb)
184 if (have_relevant_joinclause(root, old_rel, new_rel) ||
(gdb)
187 (void) make_join_rel(root, old_rel, new_rel);
(gdb) step
make_join_rel (root=0x1e214b8, rel1=0x1f079f0, rel2=0x1e96520) at joinrels.c:681
681 joinrelids = bms_union(rel1->relids, rel2->relids);进入build_join_rel函数,相应的RelOptInfo已存在,返回
(gdb)
728 joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo,
(gdb) step
build_join_rel (root=0x1e214b8, joinrelids=0x1e401d8, outer_rel=0x1f079f0, inner_rel=0x1e96520, sjinfo=0x7fff247e18a0,
restrictlist_ptr=0x7fff247e1898) at relnode.c:498
498 joinrel = find_join_rel(root, joinrelids);
500 if (joinrel)
(gdb) n
506 if (restrictlist_ptr)
(gdb)
507 *restrictlist_ptr = build_joinrel_restrictlist(root,
(gdb)
511 return joinrel;执行populate_joinrel_with_paths,该函数执行后再次查看外表和内部访问路径,变成了HashPath + MaterialPath的组合,具体的变化,下一节再行介绍.
...
(gdb)
742 populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo,
(gdb) n
745 bms_free(joinrelids);
(gdb) set $roi=(RelOptInfo *)root->join_rel_level[6]->head->data.ptr_value
(gdb) set $np=(NestPath *)$roi->pathlist->head->data.ptr_value
(gdb) p *$np->outerjoinpath
$30 = {type = T_HashPath, pathtype = T_HashJoin, parent = 0x1f079f0, pathtarget = 0x1e41128, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100, startup_cost = 3.25, total_cost = 196.75,
pathkeys = 0x0}
(gdb) p *$np->innerjoinpath
$31 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x1e96520, pathtarget = 0x1e96730, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 5, startup_cost = 97.962499999999991,
total_cost = 2014.0375000000001, pathkeys = 0x0}“PostgreSQL中哪个函数创建两个rels连接所生成的RelOptInfo”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！
--结束END--
本文标题: PostgreSQL中哪个函数创建两个rels连接所生成的RelOptInfo
本文链接: https://lsjlt.com/news/64817.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0