本篇内容介绍了“PostgreSQL中函数reconsider_outer_join_clauses的主要实现逻辑是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
query_planner代码片段:
//...
build_base_rel_tlists(root, tlist);//构建"base rels"的投影列
find_placeholders_in_jointree(root);//处理jointree中的PHI
find_lateral_references(root);//处理jointree中Lateral依赖
joinlist = deconstruct_jointree(root);//分解jointree
reconsider_outer_join_clauses(root);//已创建等价类,那么需要重新考虑被下推后处理的外连接表达式
generate_base_implied_equalities(root);//等价类构建后,生成因此外加的约束语句
//...一、重要的数据结构
RelOptInfo
与上节一样,RelOptInfo结构体贯彻逻辑优化和物理优化过程的始终,需不时Review.
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;二、源码解读
reconsider_outer_join_clauses函数
该函数遍历优化器信息(PlannerInfo)中的外连接子句(left_join_clauses),把条件分发到合适的地方,其中限制条件(Where子句中的条件)分发到RelOptInfo->baserestrictinfo中,连接条件(连接语句中的条件ON XX)分发到joininfo中
void
reconsider_outer_join_clauses(PlannerInfo *root)
{
bool found;
ListCell *cell;
ListCell *prev;
ListCell *next;
do
{
found = false;
prev = NULL;
for (cell = list_head(root->left_join_clauses); cell; cell = next)//遍历left_join_clauses
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
next = lnext(cell);
if (reconsider_outer_join_clause(root, rinfo, true))
{
found = true;
root->left_join_clauses =
list_delete_cell(root->left_join_clauses, cell, prev);//如可以,则去掉连接条件(移到约束条件中)
rinfo->norm_selec = 2.0;
rinfo->outer_selec = 1.0;
distribute_restrictinfo_to_rels(root, rinfo);//分发到RelOptInfo中
}
else
prev = cell;
}
prev = NULL;
for (cell = list_head(root->right_join_clauses); cell; cell = next)//处理右连接
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
next = lnext(cell);
if (reconsider_outer_join_clause(root, rinfo, false))
{
found = true;
root->right_join_clauses =
list_delete_cell(root->right_join_clauses, cell, prev);
rinfo->norm_selec = 2.0;
rinfo->outer_selec = 1.0;
distribute_restrictinfo_to_rels(root, rinfo);
}
else
prev = cell;
}
prev = NULL;
for (cell = list_head(root->full_join_clauses); cell; cell = next)//全连接
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
next = lnext(cell);
if (reconsider_full_join_clause(root, rinfo))
{
found = true;
root->full_join_clauses =
list_delete_cell(root->full_join_clauses, cell, prev);
rinfo->norm_selec = 2.0;
rinfo->outer_selec = 1.0;
distribute_restrictinfo_to_rels(root, rinfo);
}
else
prev = cell;
}
} while (found);
//处理连接条件链表中余下的条件
foreach(cell, root->left_join_clauses)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
distribute_restrictinfo_to_rels(root, rinfo);
}
foreach(cell, root->right_join_clauses)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
distribute_restrictinfo_to_rels(root, rinfo);
}
foreach(cell, root->full_join_clauses)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
distribute_restrictinfo_to_rels(root, rinfo);
}
}
static bool
reconsider_outer_join_clause(PlannerInfo *root, RestrictInfo *rinfo,
bool outer_on_left)
{
Expr *outervar,
*innervar;
Oid opno,
collation,
left_type,
right_type,
inner_datatype;
Relids inner_relids,
inner_nullable_relids;
ListCell *lc1;
Assert(is_opclause(rinfo->clause));
opno = ((OpExpr *) rinfo->clause)->opno;
collation = ((OpExpr *) rinfo->clause)->inputcollid;
if (rinfo->outerjoin_delayed && !op_strict(opno))
return false;
op_input_types(opno, &left_type, &right_type);
if (outer_on_left)
{
outervar = (Expr *) get_leftop(rinfo->clause);
innervar = (Expr *) get_rightop(rinfo->clause);
inner_datatype = right_type;
inner_relids = rinfo->right_relids;
}
else
{
outervar = (Expr *) get_rightop(rinfo->clause);
innervar = (Expr *) get_leftop(rinfo->clause);
inner_datatype = left_type;
inner_relids = rinfo->left_relids;
}
inner_nullable_relids = bms_intersect(inner_relids,
rinfo->nullable_relids);
foreach(lc1, root->eq_classes)//遍历等价类
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
bool match;
ListCell *lc2;
if (!cur_ec->ec_has_const)
continue;
if (cur_ec->ec_has_volatile)
continue;
if (collation != cur_ec->ec_collation)
continue;
if (!equal(rinfo->mergeopfamilies, cur_ec->ec_opfamilies))
continue;
match = false;
foreach(lc2, cur_ec->ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Assert(!cur_em->em_is_child);
if (equal(outervar, cur_em->em_expr))
{
match = true;
break;
}
}
if (!match)
continue;
match = false;
foreach(lc2, cur_ec->ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *newrinfo;
if (!cur_em->em_is_const)
continue;
eq_op = select_equality_operator(cur_ec,
inner_datatype,
cur_em->em_datatype);
if (!OidIsValid(eq_op))
continue;
newrinfo = build_implied_join_equality(eq_op,
cur_ec->ec_collation,
innervar,
cur_em->em_expr,
bms_copy(inner_relids),
bms_copy(inner_nullable_relids),
cur_ec->ec_min_security);
if (process_equivalence(root, &newrinfo, true))
match = true;
}
if (match)
return true;
else
break;
}
return false;
}generate_base_implied_equalities函数
该函数遍历所有的等价类,找出一个隐含的条件然后分发到RelOptInfo中,这样做的目的是为了在连接(join)前过滤元组,减少参与运算的元组数量.
void
generate_base_implied_equalities(PlannerInfo *root)
{
ListCell *lc;
Index rti;
foreach(lc, root->eq_classes)//遍历等价类
{
EquivalenceClass *ec = (EquivalenceClass *) lfirst(lc);
Assert(ec->ec_merged == NULL);
Assert(!ec->ec_broken);
if (list_length(ec->ec_members) <= 1)//小于1个成员,无需处理类
continue;
if (ec->ec_has_const)//有常量
generate_base_implied_equalities_const(root, ec);
else//无常量
generate_base_implied_equalities_no_const(root, ec);
if (ec->ec_broken)//处理失败个案
generate_base_implied_equalities_broken(root, ec);
}
for (rti = 1; rti < root->simple_rel_array_size; rti++)//设置标记
{
RelOptInfo *brel = root->simple_rel_array[rti];
if (brel == NULL)
continue;
brel->has_eclass_joins = has_relevant_eclass_joinclause(root, brel);
}
}
static void
generate_base_implied_equalities_const(PlannerInfo *root,
EquivalenceClass *ec)
{
EquivalenceMember *const_em = NULL;
ListCell *lc;
if (list_length(ec->ec_members) == 2 &&
list_length(ec->ec_sources) == 1)
{
RestrictInfo *restrictinfo = (RestrictInfo *) linitial(ec->ec_sources);
if (bms_membership(restrictinfo->required_relids) != BMS_MULTIPLE)
{
distribute_restrictinfo_to_rels(root, restrictinfo);
return;
}
}
foreach(lc, ec->ec_members)//获取常量Member
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
if (cur_em->em_is_const)
{
const_em = cur_em;
if (IsA(cur_em->em_expr, Const))
break;
}
}
Assert(const_em != NULL);
foreach(lc, ec->ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
Oid eq_op;
Assert(!cur_em->em_is_child);
if (cur_em == const_em)
continue;
eq_op = select_equality_operator(ec,
cur_em->em_datatype,
const_em->em_datatype);
if (!OidIsValid(eq_op))
{
ec->ec_broken = true;
break;
}
process_implied_equality(root, eq_op, ec->ec_collation,
cur_em->em_expr, const_em->em_expr,
bms_copy(ec->ec_relids),
bms_union(cur_em->em_nullable_relids,
const_em->em_nullable_relids),
ec->ec_min_security,
ec->ec_below_outer_join,
cur_em->em_is_const);//下推条件
}
}
static void
generate_base_implied_equalities_no_const(PlannerInfo *root,
EquivalenceClass *ec)
{
EquivalenceMember **prev_ems;
ListCell *lc;
prev_ems = (EquivalenceMember **)
palloc0(root->simple_rel_array_size * sizeof(EquivalenceMember *));
foreach(lc, ec->ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
int relid;
Assert(!cur_em->em_is_child);
if (!bms_get_singleton_member(cur_em->em_relids, &relid))
continue;
Assert(relid < root->simple_rel_array_size);
if (prev_ems[relid] != NULL)
{
EquivalenceMember *prev_em = prev_ems[relid];
Oid eq_op;
eq_op = select_equality_operator(ec,
prev_em->em_datatype,
cur_em->em_datatype);
if (!OidIsValid(eq_op))
{
ec->ec_broken = true;
break;
}
process_implied_equality(root, eq_op, ec->ec_collation,
prev_em->em_expr, cur_em->em_expr,
bms_copy(ec->ec_relids),
bms_union(prev_em->em_nullable_relids,
cur_em->em_nullable_relids),
ec->ec_min_security,
ec->ec_below_outer_join,
false);
}
prev_ems[relid] = cur_em;
}
pfree(prev_ems);
foreach(lc, ec->ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
List *vars = pull_var_clause((Node *) cur_em->em_expr,
PVC_RECURSE_AGGREGATES |
PVC_RECURSE_WINDOWFUNCS |
PVC_INCLUDE_PLACEHOLDERS);
add_vars_to_targetlist(root, vars, ec->ec_relids, false);
list_free(vars);
}
}
static void
generate_base_implied_equalities_broken(PlannerInfo *root,
EquivalenceClass *ec)
{
ListCell *lc;
foreach(lc, ec->ec_sources)
{
RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(lc);
if (ec->ec_has_const ||
bms_membership(restrictinfo->required_relids) != BMS_MULTIPLE)
distribute_restrictinfo_to_rels(root, restrictinfo);
}
}
void
process_implied_equality(PlannerInfo *root,
Oid opno,
Oid collation,
Expr *item1,
Expr *item2,
Relids qualscope,
Relids nullable_relids,
Index security_level,
bool below_outer_join,
bool both_const)
{
Expr *clause;
clause = make_opclause(opno,
BOOLOID,
false,
copyObject(item1),
copyObject(item2),
InvalidOid,
collation);//构造条件表达式
if (both_const)//
{
clause = (Expr *) eval_const_expressions(root, (Node *) clause);
if (clause && IsA(clause, Const))
{
Const *cclause = (Const *) clause;
Assert(cclause->consttype == BOOLOID);
if (!cclause->constisnull && DatumGetBool(cclause->constvalue))
return;
}
}
distribute_qual_to_rels(root, (Node *) clause,
true, below_outer_join, JOIN_INNER,
security_level,
qualscope, NULL, NULL, nullable_relids,
NULL);//分发条件至RelOptInfo
}三、跟踪分析
测试脚本:
testdb=# explain verbose select t1.dwbh,t2.grbh
testdb-# from t_dwxx t1 left join t_grxx t2 on t1.dwbh = t2.dwbh and t2.dwbh = '1001'
testdb-# order by t2.dwbh;
QUERY PLAN
-----------------------------------------------------------------------------------
Sort (cost=19.16..19.56 rows=160 width=114)
Output: t1.dwbh, t2.grbh, t2.dwbh
Sort Key: t2.dwbh
-> Hash Left Join (cost=1.09..13.30 rows=160 width=114)
Output: t1.dwbh, t2.grbh, t2.dwbh
Hash Cond: ((t1.dwbh)::text = (t2.dwbh)::text)
-> Seq Scan on public.t_dwxx t1 (cost=0.00..11.60 rows=160 width=38)
Output: t1.dwmc, t1.dwbh, t1.dwdz
-> Hash (cost=1.07..1.07 rows=1 width=76)
Output: t2.grbh, t2.dwbh
-> Seq Scan on public.t_grxx t2 (cost=0.00..1.07 rows=1 width=76)
Output: t2.grbh, t2.dwbh
Filter: ((t2.dwbh)::text = '1001'::text)
(13 rows)跟踪分析,启动gdb
(gdb) b planmain.c:161
Breakpoint 1 at 0x76958b: file planmain.c, line 161.
(gdb) c
Continuing.
Breakpoint 1, query_planner (root=0x2c92a88, tlist=0x2c5f048, qp_callback=0x76e906 <standard_qp_callback>,
qp_extra=0x7fffed6e9c10) at planmain.c:163
warning: Source file is more recent than executable.
163 reconsider_outer_join_clauses(root);调用前检查root(PlannerInfo)->simple_rel_array数组的内存结构,可以看到baserestrictinfo和joininfo均为NULL
(gdb) p *root->simple_rel_array[1]
$2 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2c5fdd0, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = false, reltarget = 0x2c5fde8, 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 = 0x2c5fe38, attr_widths = 0x2c5fec8,
lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x2c60160, statlist = 0x0, pages = 10, tuples = 160,
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}
(gdb) p *root->simple_rel_array[2]
$3 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2c60860, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = false, reltarget = 0x2c60878, 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 = 2, reltablespace = 0,
rtekind = RTE_RELATION, min_attr = -7, max_attr = 5, attr_needed = 0x2c608c8, attr_widths = 0x2c60958,
lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 1, tuples = 6, 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}
(gdb)调用reconsider_outer_join_clauses,注意joininfo,填入了相应的数据
(gdb) p *root->simple_rel_array[1]
$4 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2c5fdd0, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = false, reltarget = 0x2c5fde8, 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 = 0x2c5fe38, attr_widths = 0x2c5fec8,
lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x2c60160, statlist = 0x0, pages = 10, tuples = 160,
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 = 0x2c61780, 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}
(gdb) p *root->simple_rel_array[2]
$5 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2c60860, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = false, reltarget = 0x2c60878, 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 = 2, reltablespace = 0,
rtekind = RTE_RELATION, min_attr = -7, max_attr = 5, attr_needed = 0x2c608c8, attr_widths = 0x2c60958,
lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 1, tuples = 6, 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 = 0x2c617d0,
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}调用generate_base_implied_equalities,注意root->simple_rel_array[2]->baserestrictinfo,条件已下推至限制条件(原为连接条件)
(gdb) p *root->simple_rel_array[2]
$7 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2c60860, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = false, reltarget = 0x2c60878, 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 = 2, reltablespace = 0,
rtekind = RTE_RELATION, min_attr = -7, max_attr = 5, attr_needed = 0x2c608c8, attr_widths = 0x2c60958,
lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 1, tuples = 6, 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 = 0x2c61820,
baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x2c617d0,
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}“PostgreSQL中函数reconsider_outer_join_clauses的主要实现逻辑是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
