先前的章节已介绍了函数query_planner中子函数remove_useless_joins、reduce_unique_semijoins和add_placeholders_to_base_rels的主要实现逻辑,本节继续介绍create_lateral_join_info、match_foreign_keys_to_quals和extract_restriction_or_clauses的实现逻辑。
query_planner代码片段:
//...
create_lateral_join_info(root);//创建Lateral连接信息
match_foreign_keys_to_quals(root);//匹配外键信息
extract_restriction_or_clauses(root);//在OR语句中抽取约束条件
total_pages = 0;
for (rti = 1; rti < root->simple_rel_array_size; rti++)//计算总pages
{
RelOptInfo *brel = root->simple_rel_array[rti];
if (brel == NULL)
continue;
Assert(brel->relid == rti);
if (IS_SIMPLE_REL(brel))
total_pages += (double) brel->pages;
}
root->total_table_pages = total_pages;//赋值
//...
一、数据结构
RelOptInfo
RelOptInfo中,与LATERAL相关的数据结构
typedef struct RelOptInfo
{
NodeTag type;//节点标识
RelOptKind reloptkind;//RelOpt类型
//...
Relids direct_lateral_relids;
Relids lateral_relids;
//...
List *lateral_vars;
Relids lateral_referencers;
//...
} RelOptInfo;
二、源码解读
create_lateral_join_info
PG在提供LATERAL语法之前,假定所有的子查询都可以独立存在,不能互相引用属性或者引用上层的属性,为了可以引用其他或上层的属性,需要在子查询前面显式指定LATERAL关键字.
比如以下的SQL语句,不显式指定LATERAL关键字无法正常运行:
testdb=# select a.*,b.grbh,b.je
testdb-# from t_dwxx a,(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
testdb-# where a.dwbh = '1001'
testdb-# order by b.dwbh;
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 2: ... from t_grxx t1 inner join t_jfxx t2 on t1.dwbh = a.dwbh and...
^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
在子查询前显式指定LATERAL后,可以正常运行:
testdb=# select a.*,b.grbh,b.je
testdb-# 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
testdb-# where a.dwbh = '1001'
testdb-# order by b.dwbh;
dwmc | dwbh | dwdz | grbh | je
-----------+------+--------------------+------+-------
X有限公司 | 1001 | 广东省广州市荔湾区 | 901 | 401.3
X有限公司 | 1001 | 广东省广州市荔湾区 | 901 | 401.3
X有限公司 | 1001 | 广东省广州市荔湾区 | 901 | 401.3
如函数注释所描述的,create_lateral_join_info函数的作用是填充RelOptInfo中的相关四个变量,"Fill in the per-base-relation direct_lateral_relids, lateral_relids和and lateral_referencers sets"
源代码如下:
void
create_lateral_join_info(PlannerInfo *root)
{
bool found_laterals = false;
Index rti;
ListCell *lc;
if (!root->hasLateralRTEs)//是否存在LateralRTE
return;
for (rti = 1; rti < root->simple_rel_array_size; rti++)//遍历
{
RelOptInfo *brel = root->simple_rel_array[rti];
Relids lateral_relids;
if (brel == NULL)
continue;
Assert(brel->relid == rti);
if (brel->reloptkind != RELOPT_BASEREL)
continue;
lateral_relids = NULL;
foreach(lc, brel->lateral_vars)
{
Node *node = (Node *) lfirst(lc);
if (IsA(node, Var))
{
Var *var = (Var *) node;
found_laterals = true;
lateral_relids = bms_add_member(lateral_relids,
var->varno);
}
else if (IsA(node, PlaceHolderVar))
{
PlaceHolderVar *phv = (PlaceHolderVar *) node;
PlaceHolderInfo *phinfo = find_placeholder_info(root, phv,
false);
found_laterals = true;
lateral_relids = bms_add_members(lateral_relids,
phinfo->ph_eval_at);
}
else
Assert(false);
}
brel->direct_lateral_relids = lateral_relids;
brel->lateral_relids = bms_copy(lateral_relids);
}
foreach(lc, root->placeholder_list)
{
PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc);
Relids eval_at = phinfo->ph_eval_at;
int varno;
if (phinfo->ph_lateral == NULL)
continue;
found_laterals = true;
if (bms_get_singleton_member(eval_at, &varno))
{
RelOptInfo *brel = find_base_rel(root, varno);
brel->direct_lateral_relids =
bms_add_members(brel->direct_lateral_relids,
phinfo->ph_lateral);
brel->lateral_relids =
bms_add_members(brel->lateral_relids,
phinfo->ph_lateral);
}
else
{
varno = -1;
while ((varno = bms_next_member(eval_at, varno)) >= 0)
{
RelOptInfo *brel = find_base_rel(root, varno);
brel->lateral_relids = bms_add_members(brel->lateral_relids,
phinfo->ph_lateral);
}
}
}
if (!found_laterals)
{
root->hasLateralRTEs = false;
return;
}
for (rti = 1; rti < root->simple_rel_array_size; rti++)
{
RelOptInfo *brel = root->simple_rel_array[rti];
Relids outer_lateral_relids;
Index rti2;
if (brel == NULL || brel->reloptkind != RELOPT_BASEREL)
continue;
outer_lateral_relids = brel->lateral_relids;
if (outer_lateral_relids == NULL)
continue;
for (rti2 = 1; rti2 < root->simple_rel_array_size; rti2++)
{
RelOptInfo *brel2 = root->simple_rel_array[rti2];
if (brel2 == NULL || brel2->reloptkind != RELOPT_BASEREL)
continue;
if (bms_is_member(rti, brel2->lateral_relids))
brel2->lateral_relids = bms_add_members(brel2->lateral_relids,
outer_lateral_relids);
}
}
for (rti = 1; rti < root->simple_rel_array_size; rti++)
{
RelOptInfo *brel = root->simple_rel_array[rti];
Relids lateral_relids;
int rti2;
if (brel == NULL || brel->reloptkind != RELOPT_BASEREL)
continue;
lateral_relids = brel->lateral_relids;
if (lateral_relids == NULL)
continue;
Assert(!bms_is_empty(lateral_relids));
Assert(!bms_is_member(rti, lateral_relids));
rti2 = -1;
while ((rti2 = bms_next_member(lateral_relids, rti2)) >= 0)
{
RelOptInfo *brel2 = root->simple_rel_array[rti2];
Assert(brel2 != NULL && brel2->reloptkind == RELOPT_BASEREL);
brel2->lateral_referencers =
bms_add_member(brel2->lateral_referencers, rti);
}
}
for (rti = 1; rti < root->simple_rel_array_size; rti++)
{
RelOptInfo *brel = root->simple_rel_array[rti];
RangeTblEntry *brte = root->simple_rte_array[rti];
if (brel == NULL || !IS_SIMPLE_REL(brel))
continue;
Assert(brte);
if (brel->reloptkind == RELOPT_OTHER_MEMBER_REL &&
(brte->rtekind != RTE_RELATION ||
brte->relkind != RELKIND_PARTITIONED_TABLE))
continue;
if (brte->inh)
{
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
RelOptInfo *childrel;
if (appinfo->parent_relid != rti)
continue;
childrel = root->simple_rel_array[appinfo->child_relid];
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
Assert(childrel->direct_lateral_relids == NULL);
childrel->direct_lateral_relids = brel->direct_lateral_relids;
Assert(childrel->lateral_relids == NULL);
childrel->lateral_relids = brel->lateral_relids;
Assert(childrel->lateral_referencers == NULL);
childrel->lateral_referencers = brel->lateral_referencers;
}
}
}
}
跟踪分析:
(gdb) b planmain.c:173
Breakpoint 1 at 0x76961a: file planmain.c, line 173.
(gdb) c
Continuing.
Breakpoint 1, query_planner (root=0x1702b80, tlist=0x174a870, qp_callback=0x76e97d <standard_qp_callback>,
qp_extra=0x7ffd35e059c0) at planmain.c:177
...
(gdb)
212 create_lateral_join_info(root);
查看root变量:
(gdb) p *root
$11 = {..., hasLateralRTEs = false, ...}
经过处理后,LATERAL已经消失(hasLateralRTEs = false),不需要进行处理.
match_foreign_keys_to_quals
这是外键相关的处理,等价类与外键约束进行匹配并加入到条件语句(quals)中
void
match_foreign_keys_to_quals(PlannerInfo *root)
{
List *newlist = NIL;
ListCell *lc;
foreach(lc, root->fkey_list)
{
ForeignKeyOptInfo *fkinfo = (ForeignKeyOptInfo *) lfirst(lc);
RelOptInfo *con_rel;
RelOptInfo *ref_rel;
int colno;
if (fkinfo->con_relid >= root->simple_rel_array_size ||
fkinfo->ref_relid >= root->simple_rel_array_size)
continue;
con_rel = root->simple_rel_array[fkinfo->con_relid];
if (con_rel == NULL)
continue;
ref_rel = root->simple_rel_array[fkinfo->ref_relid];
if (ref_rel == NULL)
continue;
if (con_rel->reloptkind != RELOPT_BASEREL ||
ref_rel->reloptkind != RELOPT_BASEREL)
continue;
for (colno = 0; colno < fkinfo->nkeys; colno++)
{
AttrNumber con_attno,
ref_attno;
Oid fpeqop;
ListCell *lc2;
fkinfo->eclass[colno] = match_eclasses_to_foreign_key_col(root,
fkinfo,
colno);
if (fkinfo->eclass[colno] != NULL)
{
fkinfo->nmatched_ec++;
continue;
}
con_attno = fkinfo->conkey[colno];
ref_attno = fkinfo->confkey[colno];
fpeqop = InvalidOid;
foreach(lc2, con_rel->joininfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc2);
OpExpr *clause = (OpExpr *) rinfo->clause;
Var *leftvar;
Var *rightvar;
if (rinfo->outerjoin_delayed)
continue;
if (!IsA(clause, OpExpr) ||
list_length(clause->args) != 2)
continue;
leftvar = (Var *) get_leftop((Expr *) clause);
rightvar = (Var *) get_rightop((Expr *) clause);
while (leftvar && IsA(leftvar, RelabelType))
leftvar = (Var *) ((RelabelType *) leftvar)->arg;
if (!(leftvar && IsA(leftvar, Var)))
continue;
while (rightvar && IsA(rightvar, RelabelType))
rightvar = (Var *) ((RelabelType *) rightvar)->arg;
if (!(rightvar && IsA(rightvar, Var)))
continue;
if (fkinfo->ref_relid == leftvar->varno &&
ref_attno == leftvar->varattno &&
fkinfo->con_relid == rightvar->varno &&
con_attno == rightvar->varattno)
{
if (clause->opno == fkinfo->conpfeqop[colno])
{
fkinfo->rinfos[colno] = lappend(fkinfo->rinfos[colno],
rinfo);
fkinfo->nmatched_ri++;
}
}
else if (fkinfo->ref_relid == rightvar->varno &&
ref_attno == rightvar->varattno &&
fkinfo->con_relid == leftvar->varno &&
con_attno == leftvar->varattno)
{
if (!OidIsValid(fpeqop))
fpeqop = get_commutator(fkinfo->conpfeqop[colno]);
if (clause->opno == fpeqop)
{
fkinfo->rinfos[colno] = lappend(fkinfo->rinfos[colno],
rinfo);
fkinfo->nmatched_ri++;
}
}
}
if (fkinfo->rinfos[colno])
fkinfo->nmatched_rcols++;
}
if ((fkinfo->nmatched_ec + fkinfo->nmatched_rcols) == fkinfo->nkeys)
newlist = lappend(newlist, fkinfo);
}
root->fkey_list = newlist;
}
extract_restriction_or_clauses
检查join连接条件的OR-of-AND语句,如存在有用的OR约束条件,则提取出来.
如代码注释所描述,((a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45)),可以提取条件(a.x = 42 OR a.x = 44) AND (b.y = 43 OR b.z = 45),提取这些条件的目的是为了在连接前能把这些条件下推到关系中,减少参与连接运算的元组数量.
比如:
testdb=# explain verbose select t1.*
from t_dwxx t1 inner join t_grxx t2
on (t1.dwbh = '1001' and t2.grbh = '901') OR (t1.dwbh = '1002' and t2.grbh = '902');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
--------------------------------------
Nested Loop (cost=0.00..17.23 rows=5 width=474)
Output: t1.dwmc, t1.dwbh, t1.dwdz
Join Filter: ((((t1.dwbh)::text = '1001'::text) AND ((t2.grbh)::text = '901'::text)) OR (((t1.dwbh)::text = '1002'::text)
AND ((t2.grbh)::text = '902'::text)))
-> Seq Scan on public.t_grxx t2 (cost=0.00..16.00 rows=4 width=38)
Output: t2.dwbh, t2.grbh, t2.xm, t2.xb, t2.nl
Filter: (((t2.grbh)::text = '901'::text) OR ((t2.grbh)::text = '902'::text))
-> Materialize (cost=0.00..1.05 rows=2 width=474)
Output: t1.dwmc, t1.dwbh, t1.dwdz
-> Seq Scan on public.t_dwxx t1 (cost=0.00..1.04 rows=2 width=474)
Output: t1.dwmc, t1.dwbh, t1.dwdz
Filter: (((t1.dwbh)::text = '1001'::text) OR ((t1.dwbh)::text = '1002'::text))
(11 rows)
可以看到,t1.dwbh = '1001' OR t1.dwbh = '1002'和t2.grbh = '901' OR t2.grbh = '902'在连接前下推到数据表扫描作为过滤条件.
void
extract_restriction_or_clauses(PlannerInfo *root)
{
Index rti;
for (rti = 1; rti < root->simple_rel_array_size; rti++)
{
RelOptInfo *rel = root->simple_rel_array[rti];
ListCell *lc;
if (rel == NULL)
continue;
Assert(rel->relid == rti);
if (rel->reloptkind != RELOPT_BASEREL)
continue;
foreach(lc, rel->joininfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
if (restriction_is_or_clause(rinfo) &&
join_clause_is_movable_to(rinfo, rel) &&
rinfo->norm_selec <= 1)
{
Expr *orclause = extract_or_clause(rinfo, rel);
if (orclause)
consider_new_or_clause(root, rel, orclause, rinfo);
}
}
}
}
三、参考资料
planmain.c
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
历年真题答案解析
备考技巧名师总结
高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756
相关文章
发现更多好内容- 如何使用 Java 编写一个 circular 类？(怎么用java编写一个circular类)
- 在 Java 中如何安全地进行 SQL 拼接？(Java中怎么安全的进行SQL拼接)
- Java 的 enum 有哪些需要注意的事项？(java的enum的注意事项)
- 深入解析：如何有效提升PHP数据类型的转换效率
- 如何在 Java 中实现轮询？(java如何实现轮询)
- 如何在 Java 中获取当前时间戳？(java怎么获取当前时间戳)
- 如何简化 Java 的 DAO 层开发流程？(Java的DAO层怎样简化开发流程)
- 深入解析PHP中数据类型的转换技巧
- 在处理速度方面，C++与 Java 究竟谁更快？(c++与java处理速度哪个更快)
- Java Zuul 如何在认证授权中进行应用？(java zuul在认证授权中的应用)
猜你喜欢
AI推送时光机 咦！没有更多了？去看看其它编程学习网 内容吧
