这篇文章主要介绍“PostgreSQL中set_base_rel_pathlists函数有什么作用”，在日常操作中，相信很多人在PostgreSQL中set_base_rel_pathlists函数有什么作用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中set_base_rel_pathlists函数有什么作用”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
set_base_rel_pathlists函数的目的是为每一个base rel找出所有可用的访问路径(包括顺序扫描和所有可用的索引)，每一个可用的路径都会添加到pathlist链表中。
一、数据结构
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;
//FDW相关信息
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;
//是否尝试partitionwise连接,这是PG 11的一个新特性.
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;Cost相关
注意:实际使用的参数值通过系统配置文件定义,而不是这里的常量定义!
typedef struct QualCost
{
Cost startup;
Cost per_tuple;
} QualCost;
typedef double Cost;
#define DEFAULT_SEQ_PAGE_COST 1.0 //顺序扫描page的成本
#define DEFAULT_RANDOM_PAGE_COST 4.0 //随机扫描page的成本
#define DEFAULT_CPU_TUPLE_COST 0.01 //处理一个元组的CPU成本
#define DEFAULT_CPU_INDEX_TUPLE_COST 0.005 //处理一个索引元组的CPU成本
#define DEFAULT_CPU_OPERATOR_COST 0.0025 //执行一次操作或函数的CPU成本
#define DEFAULT_PARALLEL_TUPLE_COST 0.1 //并行执行,从一个worker传输一个元组到另一个worker的成本
#define DEFAULT_PARALLEL_SETUP_COST 1000.0 //构建并行执行环境的成本
#define DEFAULT_EFFECTIVE_CACHE_SIZE 524288
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;//1后面10个0,通过设置一个巨大的成本,让优化器自动放弃此路径
int max_parallel_workers_per_gather = 2;//每次gather使用的worker数IndexClauseSet
用于收集匹配索引的的条件语句
typedef struct
{
bool nonempty;
List *indexclauses[INDEX_MAX_KEYS];
} IndexClauseSet;二、源码解读
set_base_rel_pathlists函数遍历RelOptInfo数组,为每一个Rel构造访问路径,先前已介绍了顺序扫描的成本估算,本节介绍索引扫描的成本估算(函数:create_index_paths),通过调用set_plain_rel_pathlist->create_index_paths函数实现.
static void
set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
Relids required_outer;
//...
create_index_paths(root, rel);
create_tidscan_paths(root, rel);
}create_index_paths
create_index_paths函数生成Relation所有可能被选中的索引访问路径,详见源码注释.
void
create_index_paths(PlannerInfo *root, RelOptInfo *rel)
{
List *indexpaths;//索引访问路径链表
List *bitindexpaths;//
List *bitjoinpaths;
List *joinorclauses;
IndexClauseSet rclauseset;
IndexClauseSet jclauseset;
IndexClauseSet eclauseset;
ListCell *lc;
if (rel->indexlist == NIL)//不存在索引,退出
return;
bitindexpaths = bitjoinpaths = joinorclauses = NIL;//初始赋值
foreach(lc, rel->indexlist)//遍历索引链表
{
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);//索引信息
Assert(index->ncolumns <= INDEX_MAX_KEYS);
if (index->indpred != NIL && !index->predOK)//部分索引,而且不能使用,不使用此索引
continue;
MemSet(&rclauseset, 0, sizeof(rclauseset));
match_restriction_clauses_to_index(rel, index, &rclauseset);
get_index_paths(root, rel, index, &rclauseset,
&bitindexpaths);
MemSet(&jclauseset, 0, sizeof(jclauseset));
match_join_clauses_to_index(root, rel, index,
&jclauseset, &joinorclauses);
MemSet(&eclauseset, 0, sizeof(eclauseset));
match_eclass_clauses_to_index(root, index,
&eclauseset);
if (jclauseset.nonempty || eclauseset.nonempty)
consider_index_join_clauses(root, rel, index,
&rclauseset,
&jclauseset,
&eclauseset,
&bitjoinpaths);
}
indexpaths = generate_bitmap_or_paths(root, rel,
rel->baserestrictinfo, NIL);
bitindexpaths = list_concat(bitindexpaths, indexpaths);//合并到bitindexpaths链表中
indexpaths = generate_bitmap_or_paths(root, rel,
joinorclauses, rel->baserestrictinfo);
bitjoinpaths = list_concat(bitjoinpaths, indexpaths);//合并到bitjoinpaths链表中
if (bitindexpaths != NIL)//存在位图索引访问路径
{
Path *bitmapqual;//访问路径
BitmapHeapPath *bpath;//BitmapHeapPath访问路径
bitmapqual = choose_bitmap_and(root, rel, bitindexpaths);//位图表达式路径
bpath = create_bitmap_heap_path(root, rel, bitmapqual,
rel->lateral_relids, 1.0, 0);//BitmapHeapPath访问路径
add_path(rel, (Path *) bpath);//添加到RelOptInfo中
if (rel->consider_parallel && rel->lateral_relids == NULL)
create_partial_bitmap_paths(root, rel, bitmapqual);//创建并行访问路径
}
if (bitjoinpaths != NIL)//bitjoinpaths位图连接访问路径
{
List *path_outer;//依赖的外部Relids链表
List *all_path_outers;//依赖的外部路径Relids链表
ListCell *lc;//临时变量
path_outer = all_path_outers = NIL;//初始化变量
foreach(lc, bitjoinpaths)//遍历bitjoinpaths
{
Path *path = (Path *) lfirst(lc);//访问路径
Relids required_outer;//依赖的外部Relids
required_outer = get_bitmap_tree_required_outer(path);//
path_outer = lappend(path_outer, required_outer);//添加到链表中
if (!bms_equal_any(required_outer, all_path_outers))//不等,则添加到all_path_outers中
all_path_outers = lappend(all_path_outers, required_outer);
}
//对每一个唯一的参数化集合进行处理
foreach(lc, all_path_outers)//遍历all_path_outers
{
Relids max_outers = (Relids) lfirst(lc);
List *this_path_set;
Path *bitmapqual;
Relids required_outer;
double loop_count;
BitmapHeapPath *bpath;
ListCell *lcp;
ListCell *lco;
this_path_set = NIL;
forboth(lcp, bitjoinpaths, lco, path_outer)//遍历
{
Path *path = (Path *) lfirst(lcp);
Relids p_outers = (Relids) lfirst(lco);
if (bms_is_subset(p_outers, max_outers))//无需依赖其他Relids,添加到this_path_set中
this_path_set = lappend(this_path_set, path);
}
this_path_set = list_concat(this_path_set, bitindexpaths);//合并bitindexpaths访问路径
bitmapqual = choose_bitmap_and(root, rel, this_path_set);//为此参数化处理选择最好的AND组合
required_outer = get_bitmap_tree_required_outer(bitmapqual);
loop_count = get_loop_count(root, rel->relid, required_outer);
bpath = create_bitmap_heap_path(root, rel, bitmapqual,
required_outer, loop_count, 0);//创建索引访问路径
add_path(rel, (Path *) bpath);
}
}
}match_XXX
match_restriction_clauses_to_index函数验证限制条件是否与Index匹配,匹配的条件添加到clauseset中.
match_join_clauses_to_index函数验证连接条件是否与Index匹配,同样的,匹配的条件添加到clauseset中.
match_eclass_clauses_to_index函数验证EC连接条件是否与Index匹配,匹配的条件添加到clauseset中.
//--------------------------------------------------- match_restriction_clauses_to_index
static void
match_restriction_clauses_to_index(RelOptInfo *rel, IndexOptInfo *index,
IndexClauseSet *clauseset)
{
//忽略部分(条件)索引,直接调用match_clauses_to_index
match_clauses_to_index(index, index->indrestrictinfo, clauseset);
}
//------------------------------- match_clauses_to_index
static void
match_clauses_to_index(IndexOptInfo *index,
List *clauses,
IndexClauseSet *clauseset)
{
ListCell *lc;//临时变量
foreach(lc, clauses)//遍历限制条件
{
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
match_clause_to_index(index, rinfo, clauseset);
}
}
//--------------------------------------------------- match_join_clauses_to_index
static void
match_join_clauses_to_index(PlannerInfo *root,
RelOptInfo *rel, IndexOptInfo *index,
IndexClauseSet *clauseset,
List **joinorclauses)
{
ListCell *lc;//临时变量
foreach(lc, rel->joininfo)//遍历连接条件
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
if (!join_clause_is_movable_to(rinfo, rel))
continue;
if (restriction_is_or_clause(rinfo))
*joinorclauses = lappend(*joinorclauses, rinfo);
else
match_clause_to_index(index, rinfo, clauseset);
}
}
//--------------------------------------------------- match_eclass_clauses_to_index
static void
match_eclass_clauses_to_index(PlannerInfo *root, IndexOptInfo *index,
IndexClauseSet *clauseset)
{
int indexcol;
if (!index->rel->has_eclass_joins)//没有ECs,返回
return;
for (indexcol = 0; indexcol < index->nkeycolumns; indexcol++)//遍历索引列
{
ec_member_matches_arg arg;
List *clauses;
//生成条件子句链表
arg.index = index;
arg.indexcol = indexcol;
clauses = generate_implied_equalities_for_column(root,
index->rel,
ec_member_matches_indexcol,
(void *) &arg,
index->rel->lateral_referencers);
match_clauses_to_index(index, clauses, clauseset);
}
}
//---------------------------- generate_implied_equalities_for_column
List *
generate_implied_equalities_for_column(PlannerInfo *root,
RelOptInfo *rel,
ec_matches_callback_type callback,
void *callback_arg,
Relids prohibited_rels)
{
List *result = NIL;//结果链表
bool is_child_rel = (rel->reloptkind == RELOPT_OTHER_MEMBER_REL);//是否子Relation
Relids parent_relids;//父Relids
ListCell *lc1;//变量
Assert(IS_SIMPLE_REL(rel));
if (is_child_rel)
parent_relids = find_childrel_parents(root, rel);
else
parent_relids = NULL;
foreach(lc1, root->eq_classes)//遍历EC
{
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);//当前的EC
EquivalenceMember *cur_em;//EC成员
ListCell *lc2;//链表成员
if (cur_ec->ec_has_const || list_length(cur_ec->ec_members) <= 1)
continue;
if (!is_child_rel &&
!bms_is_subset(rel->relids, cur_ec->ec_relids))
continue;
cur_em = NULL;
foreach(lc2, cur_ec->ec_members)//遍历EC的成员
{
cur_em = (EquivalenceMember *) lfirst(lc2);//当前成员
if (bms_equal(cur_em->em_relids, rel->relids) &&
callback(root, rel, cur_ec, cur_em, callback_arg))//调用ec_member_matches_indexcol函数
break;//找到匹配的成员,跳出
cur_em = NULL;
}
if (!cur_em)
continue;
foreach(lc2, cur_ec->ec_members)
{
EquivalenceMember *other_em = (EquivalenceMember *) lfirst(lc2);
Oid eq_op;
RestrictInfo *rinfo;
if (other_em->em_is_child)//
continue;
if (other_em == cur_em ||
bms_overlap(other_em->em_relids, rel->relids))//过滤cur_em
continue;
if (bms_overlap(other_em->em_relids, prohibited_rels))
continue;
if (is_child_rel &&
bms_overlap(parent_relids, other_em->em_relids))
continue;
eq_op = select_equality_operator(cur_ec,
cur_em->em_datatype,
other_em->em_datatype);
if (!OidIsValid(eq_op))
continue;
rinfo = create_join_clause(root, cur_ec, eq_op,
cur_em, other_em,
cur_ec);//创建连接条件语句
result = lappend(result, rinfo);
}
if (result)
break;
}
return result;
}
//---------------------------- match_clause_to_index
static void
match_clause_to_index(IndexOptInfo *index,
RestrictInfo *rinfo,
IndexClauseSet *clauseset)
{
int indexcol;
if (rinfo->pseudoconstant)
return;
if (!restriction_is_securely_promotable(rinfo, index->rel))
return;
for (indexcol = 0; indexcol < index->nkeycolumns; indexcol++)
{
if (match_clause_to_indexcol(index,
indexcol,
rinfo))
{
clauseset->indexclauses[indexcol] =
list_append_unique_ptr(clauseset->indexclauses[indexcol],
rinfo);//赋值
clauseset->nonempty = true;//设置标记
return;
}
}
}
//------------------- match_clause_to_indexcol
static bool
match_clause_to_indexcol(IndexOptInfo *index,
int indexcol,
RestrictInfo *rinfo)
{
Expr *clause = rinfo->clause;//条件语句
Index index_relid = index->rel->relid;//Index的Relid
Oid opfamily;//操作符种类
Oid idxcollation;//索引排序规则
Node *leftop,//左节点
*rightop;//右节点
Relids left_relids;//左节点相关Relids
Relids right_relids;//右节点相关Relids
Oid expr_op;//表达式操作符的Oid
Oid expr_coll;//表达式Collation的Oid
bool plain_op;//是否Plain操作符
Assert(indexcol < index->nkeycolumns);
opfamily = index->opfamily[indexcol];//获取操作符种类
idxcollation = index->indexcollations[indexcol];//获取索引排序规则
if (IsBooleanOpfamily(opfamily))//是否布尔类
{
if (match_boolean_index_clause((Node *) clause, indexcol, index))//是否匹配
return true;//如匹配,返回T
}
if (is_opclause(clause))//OpExpr
{
leftop = get_leftop(clause);
rightop = get_rightop(clause);
if (!leftop || !rightop)
return false;
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
expr_op = ((OpExpr *) clause)->opno;
expr_coll = ((OpExpr *) clause)->inputcollid;
plain_op = true;
}
else if (clause && IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr
{
ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause;
if (!saop->useOr)
return false;
leftop = (Node *) linitial(saop->args);
rightop = (Node *) lsecond(saop->args);
left_relids = NULL;
right_relids = pull_varnos(rightop);
expr_op = saop->opno;
expr_coll = saop->inputcollid;
plain_op = false;
}
else if (clause && IsA(clause, RowCompareExpr))//RowCompareExpr
{
return match_rowcompare_to_indexcol(index, indexcol,
opfamily, idxcollation,
(RowCompareExpr *) clause);
}
else if (index->amsearchnulls && IsA(clause, NullTest))//NullTest
{
NullTest *nt = (NullTest *) clause;
if (!nt->argisrow &&
match_index_to_operand((Node *) nt->arg, indexcol, index))
return true;
return false;
}
else
return false;
//处理:(indexkey operator constant)
if (match_index_to_operand(leftop, indexcol, index) &&
!bms_is_member(index_relid, right_relids) &&
!contain_volatile_functions(rightop))
{
if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
is_indexable_operator(expr_op, opfamily, true))//排序规则&操作符种类匹配
return true;//返回T
if (plain_op &&
match_special_index_operator(clause, opfamily,
idxcollation, true))//Plain操作&特殊操作符,返回T
return true;
return false;//否则,返回F
}
//处理(constant operator indexkey)
if (plain_op &&
match_index_to_operand(rightop, indexcol, index) &&
!bms_is_member(index_relid, left_relids) &&
!contain_volatile_functions(leftop))
{
if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
is_indexable_operator(expr_op, opfamily, false))
return true;
if (match_special_index_operator(clause, opfamily,
idxcollation, false))
return true;
return false;
}
return false;
}三、跟踪分析
测试脚本如下
select a.*,b.grbh,b.je
from t_dwxx a,
lateral (select t1.dwbh,t1.grbh,t2.je
from t_grxx t1
inner join t_jfxx t2 on t1.dwbh = a.dwbh and t1.grbh = t2.grbh) b
where a.dwbh = '1001'
order by b.dwbh;注意:按先前的分析,SQL语句存在等价类{t_dwxx.dwbh t_grxx.dwbh '1001'}和{t_grxx.grbh t_jfxx.grbh},在构造t_grxx的索引访问路径时,使用等价类构造.
启动gdb,第一个RelOptInfo(对应t_dwxx)有3个Index,第二个RelOptInfo(对应t_grxx)有2个Index(分别是在dwbh和grbh上的索引),第三个RelOptInfo(对应t_jfxx)有1个Index(grbh上的索引),本节以t_jfxx和t_grxx为例进行跟踪分析
...
(gdb) c
Continuing.
Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:242
242 if (rel->indexlist == NIL)
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data.ptr_value
$38 = {type = T_IndexOptInfo, indexoid = 16750, reltablespace = 0, rel = 0x2729530, pages = 276, tuples = 100000,
tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729998, indexcollations = 0x27299b0, opfamily = 0x27299c8,
opcintype = 0x27299e0, sortopfamily = 0x27299c8, reverse_sort = 0x2729a10, nulls_first = 0x2729a28,
canreturn = 0x27299f8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2729ae0, indrestrictinfo = 0x0,
predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true,
amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true,
amcostestimate = 0x94f0ad <btcostestimate>}输入信息是已熟知的root(PlannerInfo)和rel(RelOptInfo).首先进行索引遍历循环
(gdb) c
Continuing.
Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:242
242 if (rel->indexlist == NIL)
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data.ptr_value
$38 = {type = T_IndexOptInfo, indexoid = 16750, reltablespace = 0, rel = 0x2729530, pages = 276, tuples = 100000,
tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729998, indexcollations = 0x27299b0, opfamily = 0x27299c8,
opcintype = 0x27299e0, sortopfamily = 0x27299c8, reverse_sort = 0x2729a10, nulls_first = 0x2729a28,
canreturn = 0x27299f8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2729ae0, indrestrictinfo = 0x0,
predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true,
amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true,
amcostestimate = 0x94f0ad <btcostestimate>}查询数据字典pg_class,oid=16750相应的索引是idx_t_jfxx_grbh
testdb=# select relname from pg_class where oid=16750;
relname
-----------------
idx_t_jfxx_grbh
(1 row)调用match_restriction_clauses_to_index和match_join_clauses_to_index,子句集合均为NULL
(gdb)
match_restriction_clauses_to_index (rel=0x2729530, index=0x2729888, clauseset=0x7fff69cf0890) at indxpath.c:2117
2117 }
(gdb)
create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:275
275 get_index_paths(root, rel, index, &rclauseset,
(gdb)
284 MemSet(&jclauseset, 0, sizeof(jclauseset));
(gdb)
285 match_join_clauses_to_index(root, rel, index,
(gdb)
292 MemSet(&eclauseset, 0, sizeof(eclauseset));
(gdb)
293 match_eclass_clauses_to_index(root, index,
(gdb) p rclauseset
$2 = {nonempty = false, indexclauses = {0x0 <repeats 32 times>}}
(gdb) p joinorclauses
$3 = (List *) 0x0
(gdb) p jclauseset
$4 = {nonempty = false, indexclauses = {0x0 <repeats 32 times>}}进入match_eclass_clauses_to_index
...
268 match_restriction_clauses_to_index(rel, index, &rclauseset);
(gdb) step
match_restriction_clauses_to_index (rel=0x2724c88, index=0x27254d8, clauseset=0x7fff69cf0890) at indxpath.c:2116
2116 match_clauses_to_index(index, index->indrestrictinfo, clauseset);进入generate_implied_equalities_for_column
...
(gdb) step
generate_implied_equalities_for_column (root=0x2714c50, rel=0x2729530, callback=0x7509b0 <ec_member_matches_indexcol>,
callback_arg=0x7fff69cf0620, prohibited_rels=0x0) at equivclass.c:2219
2219 List *result = NIL;等价类信息
...
2235 EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
(gdb)
2243 if (cur_ec->ec_has_const || list_length(cur_ec->ec_members) <= 1)
(gdb) p *cur_ec
$6 = {type = T_EquivalenceClass, ec_opfamilies = 0x272a268, ec_collation = 100, ec_members = 0x272a4a8,
ec_sources = 0x272a3f0, ec_derives = 0x272d2f0, ec_relids = 0x272a470, ec_has_const = false, ec_has_volatile = false,
ec_below_outer_join = false, ec_broken = false, ec_sortref = 0, ec_min_security = 0, ec_max_security = 0, ec_merged = 0x0}遍历EC的成员后,cur_em不为NULL,查看cur_em内存结构(匹配的成员,即t_jfxx.grbh)
2281 foreach(lc2, cur_ec->ec_members)
(gdb) p *cur_em
$7 = {type = T_EquivalenceMember, em_expr = 0x2722890, em_relids = 0x272a238, em_nullable_relids = 0x0,
em_is_const = false, em_is_child = false, em_datatype = 25}
(gdb) p *cur_em->em_expr
$8 = {type = T_RelabelType}
(gdb) p *(RelabelType *)cur_em->em_expr
$9 = {xpr = {type = T_RelabelType}, arg = 0x2722840, resulttype = 25, resulttypmod = -1, resultcollid = 100,
relabelformat = COERCE_IMPLICIT_CAST, location = -1}
(gdb) p *((RelabelType *)cur_em->em_expr)->arg
$10 = {type = T_Var}
(gdb) p *(Var *)((RelabelType *)cur_em->em_expr)->arg
$11 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 4, varoattno = 1, location = 168}再次遍历等价类的成员,得到第一个约束条件(t_jfxx.grbh=t_grxx.grbh)
(gdb) n
2314 rinfo = create_join_clause(root, cur_ec, eq_op,
(gdb)
2318 result = lappend(result, rinfo);
(gdb) p *rinfo
$18 = {type = T_RestrictInfo, clause = 0x272d910, is_pushed_down = true, outerjoin_delayed = false, can_join = true,
pseudoconstant = false, leakproof = false, security_level = 0, clause_relids = 0x272db10, required_relids = 0x272d5f0,
outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x272dae0, right_relids = 0x272daf8, orclause = 0x0,
parent_ec = 0x272a340, eval_cost = {startup = 0, per_tuple = 0.0025000000000000001}, norm_selec = -1, outer_selec = -1,
mergeopfamilies = 0x272db48, left_ec = 0x272a340, right_ec = 0x272a340, left_em = 0x272a4d8, right_em = 0x272a420,
scansel_cache = 0x0, outer_is_left = false, hashjoinoperator = 98, left_bucketsize = -1, right_bucketsize = -1,
left_mcvfreq = -1, right_mcvfreq = -1}
(gdb) set $tmp1=(RelabelType *)((OpExpr *)rinfo->clause)->args->head->data.ptr_value
(gdb) set $tmp2=(RelabelType *)((OpExpr *)rinfo->clause)->args->head->next->data.ptr_value
(gdb) p *(Var *)$tmp1->arg
$31 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 4, varoattno = 1, location = 168}
(gdb) p *(Var *)$tmp2->arg
$32 = {xpr = {type = T_Var}, varno = 3, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 3, varoattno = 2, location = 158}获得了结果,返回到match_eclass_clauses_to_index
2281 foreach(lc2, cur_ec->ec_members)
(gdb)
2326 if (result)
(gdb)
2327 break;
(gdb)
2330 return result;
(gdb)
2331 }
(gdb)
match_eclass_clauses_to_index (root=0x2714c50, index=0x2729888, clauseset=0x7fff69cf0670) at indxpath.c:2184
2184 match_clauses_to_index(index, clauses, clauseset);
...下面再考察t_grxx.dwbh上的索引为例,分析match_clause_to_index
(gdb) c
Continuing.
Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2728c38) at indxpath.c:242
242 if (rel->indexlist == NIL)
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data.ptr_value
$39 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x2728c38, pages = 276, tuples = 100000,
tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729378, indexcollations = 0x2729390, opfamily = 0x27293a8,
opcintype = 0x27293c0, sortopfamily = 0x27293a8, reverse_sort = 0x27293f0, nulls_first = 0x2729408,
canreturn = 0x27293d8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x27294e0, indrestrictinfo = 0x272b040,
predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true,
amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true,
amcostestimate = 0x94f0ad <btcostestimate>}oid=16752,对应的object为idx_t_grxx_dwbh
testdb=# select relname from pg_class where oid=16752;
relname
-----------------
idx_t_grxx_dwbh
(1 row)进入IndexOptInfo循环,第一个元素对应的IndexOptInfo为idx_t_grxx_dwbh
249 foreach(lc, rel->indexlist)
(gdb) p *rel->indexlist
$40 = {type = T_List, length = 2, head = 0x2729510, tail = 0x2729218}
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data->ptr_value
$42 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x2728c38, pages = 276, tuples = 100000,
tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729378, indexcollations = 0x2729390, opfamily = 0x27293a8,
opcintype = 0x27293c0, sortopfamily = 0x27293a8, reverse_sort = 0x27293f0, nulls_first = 0x2729408,
canreturn = 0x27293d8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x27294e0, indrestrictinfo = 0x272b040,
predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true,
amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true,
amcostestimate = 0x94f0ad <btcostestimate>}一路小跑,进入match_clause_to_indexcol
...
(gdb) step
match_clause_to_indexcol (index=0x2729268, indexcol=0, rinfo=0x272ae58) at indxpath.c:2330
2330 Expr *clause = rinfo->clause;
(gdb) n
2331 Index index_relid = index->rel->relid;
(gdb) n
2344 opfamily = index->opfamily[indexcol];
(gdb)
2345 idxcollation = index->indexcollations[indexcol];
(gdb) p index_relid
$47 = 3
(gdb) p opfamily
$48 = 1994
(gdb)根据opfamily查询数据字典
testdb=# select * from pg_opfamily where oid=1994;
opfmethod | opfname | opfnamespace | opfowner
-----------+----------+--------------+----------
403 | text_ops | 11 | 10
(1 row)
-- 索引访问方法(btree)
testdb=# select * from pg_am where oid=403;
amname | amhandler | amtype
--------+-----------+--------
btree | bthandler | i
(1 row)下面进入is_opclause判断分支
(gdb) p idxcollation
$49 = 100
(gdb) n
2360 if (is_opclause(clause))
(gdb)
2362 leftop = get_leftop(clause);
(gdb)
2363 rightop = get_rightop(clause);
(gdb)
2364 if (!leftop || !rightop)
(gdb) p *leftop
$50 = {type = T_RelabelType}
(gdb) p *rightop
$51 = {type = T_Const}限制条件下推后,形成限制条件t_grxx.dwbh = '1001'
#Var:t_grxx.dwbh
(gdb) p *(RelabelType *)leftop
$56 = {xpr = {type = T_RelabelType}, arg = 0x272ad80, resulttype = 25, resulttypmod = -1, resultcollid = 100,
relabelformat = COERCE_IMPLICIT_CAST, location = -1}
#常量:'1001'
(gdb) p *(Const *)rightop
$57 = {xpr = {type = T_Const}, consttype = 25, consttypmod = -1, constcollid = 100, constlen = -1, constvalue = 41069848,
constisnull = false, constbyval = false, location = 194}执行相关判断,返回T
(gdb) n
2366 left_relids = rinfo->left_relids;
(gdb)
2367 right_relids = rinfo->right_relids;
(gdb)
2368 expr_op = ((OpExpr *) clause)->opno;
(gdb)
2369 expr_coll = ((OpExpr *) clause)->inputcollid;
(gdb)
2370 plain_op = true;
(gdb)
2409 if (match_index_to_operand(leftop, indexcol, index) &&
(gdb)
2410 !bms_is_member(index_relid, right_relids) &&
(gdb)
2409 if (match_index_to_operand(leftop, indexcol, index) &&
(gdb)
2411 !contain_volatile_functions(rightop))
(gdb)
2410 !bms_is_member(index_relid, right_relids) &&
(gdb)
2413 if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
(gdb)
2414 is_indexable_operator(expr_op, opfamily, true))
(gdb)
2413 if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
(gdb)
2415 return true;给clauseset变量赋值
(gdb)
match_clause_to_index (index=0x2729268, rinfo=0x272ae58, clauseset=0x7fff69cf0890) at indxpath.c:2255
2255 list_append_unique_ptr(clauseset->indexclauses[indexcol],
(gdb)
2254 clauseset->indexclauses[indexcol] =
(gdb)
2257 clauseset->nonempty = true;
(gdb)
2258 return;
(gdb)
2261 }返回到match_clauses_to_index
(gdb)
match_clauses_to_index (index=0x2729268, clauses=0x272b040, clauseset=0x7fff69cf0890) at indxpath.c:2200
2200 foreach(lc, clauses)到此，关于“PostgreSQL中set_base_rel_pathlists函数有什么作用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
