做网站的难点是什么,档案网站建设图片,手机软件下载大全,徐州网站建设推广相关 《Postgresql源码#xff08;133#xff09;优化器动态规划生成连接路径的实例分析》 上一篇对路径的生成进行了分析#xff0c;通过make_one_rel最终拿到了一个带着路径的RelOptInfo。本篇针对带volatile函数的排序场景继续分析subquery_planner的后续流程。
subquer… 相关 《Postgresql源码133优化器动态规划生成连接路径的实例分析》 上一篇对路径的生成进行了分析通过make_one_rel最终拿到了一个带着路径的RelOptInfo。本篇针对带volatile函数的排序场景继续分析subquery_planner的后续流程。
subquery_plannergrouping_plannerquery_plannermake_one_rel 上一篇// 后续流程 本篇总结速查
一句话总结带有volatile的投影列会被SORT算子忽略达到先排序在投影计算volatile的效果。
grouping_planner→make_one_rel层层生成path每个path都会带pathtarget不一定是SQL中最后需要的target列表一般都是层层继承上来的。make_one_rel生成的最终path中会忽略volatile函数列交给外层grouping_planner函数处理所以生成的path中的pathtarget都是看不到volatile函数列的。这里一个关键逻辑就path中的pathtarget和make_sort_input_target计算出来列表的是不是一样的 如果是一样的就不加投影节点等后面加sort时create_ordered_paths先加sort在加投影计算顺序就是先排序在拿排序阶段投影计算random函数如果不一样就直接加投影节点后面sort会加到投影上面计算顺序就是先投影计算random函数再排序。 path中的pathtarget会忽略volatile函数。make_sort_input_target中的volatile函数正常也会被忽略掉实例3除非volatile函数就是排序列实例4。
最终效果是投影列有volatile函数的SQL函数非排序列sort节点会忽略这类函数的执行sort结束后在投影节点使用sort的结果集来计算这类函数。
实例3
1 实例简单join
drop table student;
create table student(sno int primary key, sname varchar(10), ssex int);
insert into student values(1, stu1, 0);
insert into student values(2, stu2, 1);
insert into student values(3, stu3, 1);
insert into student values(4, stu4, 0);drop table course;
create table course(cno int primary key, cname varchar(10), tno int);
insert into course values(20, meth, 10);
insert into course values(21, english, 11);drop table teacher;
create table teacher(tno int primary key, tname varchar(10), tsex int);
insert into teacher values(10, te1, 1);
insert into teacher values(11, te2, 0);drop table score;
create table score (sno int, cno int, degree int);
create index idx_score_sno on score(sno);
insert into score values (1, 20, 100);
insert into score values (1, 21, 89);
insert into score values (2, 20, 99);
insert into score values (2, 21, 90);
insert into score values (3, 20, 87);
insert into score values (3, 21, 20);
insert into score values (4, 20, 60);
insert into score values (4, 21, 70);explain
SELECT STUDENT.sname, COURSE.cname, SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno SCORE.sno
LEFT JOIN COURSE ON SCORE.cno COURSE.cno;QUERY PLAN
------------------------------------------------------------------------------Hash Left Join (cost69.50..110.65 rows2040 width80)Hash Cond: (score.cno course.cno)- Hash Right Join (cost34.75..70.53 rows2040 width46)Hash Cond: (score.sno student.sno)- Seq Scan on score (cost0.00..30.40 rows2040 width12)- Hash (cost21.00..21.00 rows1100 width42)- Seq Scan on student (cost0.00..21.00 rows1100 width42)- Hash (cost21.00..21.00 rows1100 width42)- Seq Scan on course (cost0.00..21.00 rows1100 width42)1.1 subquery_planner→grouping_planner
grouping_plannercurrent_rel query_planner(root, standard_qp_callback, qp_extra);current_rel final_target create_pathtarget(root, root-processed_tlist);得到final_target final_target-exprs-elements[0] : {varno 1, varattno 2, vartype 1043} STUDENT.snamefinal_target-exprs-elements[1] : {varno 4, varattno 2, vartype 1043} COURSE.cnamefinal_target-exprs-elements[2] : {varno 2, varattno 3, vartype 23} SCORE.degree if (parse-sortClause)make_sort_input_targetif (activeWindows)...if (have_grouping)...if (parse-hasTargetSRFs)...apply_scanjoin_target_to_paths创建投影节点 /* Apply scan/join target. */scanjoin_target_same_exprs list_length(scanjoin_targets) 1 equal(scanjoin_target-exprs, current_rel-reltarget-exprs);apply_scanjoin_target_to_paths(root, current_rel, scanjoin_targets,scanjoin_targets_contain_srfs,scanjoin_target_parallel_safe,scanjoin_target_same_exprs);继续 if (have_grouping)...if (activeWindows)...if (parse-distinctClause)...if (parse-sortClause)create_ordered_paths创建空的最顶层节点 final_rel fetch_upper_rel(root, UPPERREL_FINAL, NULL);遍历current_rel中所有的path用add_path加入到最顶层节点中。其中limit、rowclock的场景需要特殊处理下。 foreach(lc, current_rel-pathlist)if (parse-rowMarks)create_lockrows_pathif (limit_needed(parse))create_limit_pathadd_path(final_rel, path);grouping_planner函数执行结束最后拼接的final_rel在upper_rels里面记录 pathlist最上层是投影节点
1.2 standard_planner→subquery_planner
subquery_planner中后续处理流程
计划生成步骤作用root subquery_planner优化器入口返回PlannerInfo里面记录了一个最终的RelOptInfo相当于一张逻辑表每个ROI都记录了多个path表示不同的计算路径final_rel fetch_upper_rel拿到最终的RelOptInfobest_path get_cheapest_fractional_path在RelOptInfo中选择一个最优的pathtop_plan create_plan→create_plan_recurse根据最优path生成计划
2 实例【简单join】【排序非投影列】【投影列无函数】
drop table student;
create table student(sno int primary key, sname varchar(10), ssex int);
insert into student values(1, stu1, 0);
insert into student values(2, stu2, 1);
insert into student values(3, stu3, 1);
insert into student values(4, stu4, 0);drop table course;
create table course(cno int primary key, cname varchar(10), tno int);
insert into course values(20, meth, 10);
insert into course values(21, english, 11);drop table teacher;
create table teacher(tno int primary key, tname varchar(10), tsex int);
insert into teacher values(10, te1, 1);
insert into teacher values(11, te2, 0);drop table score;
create table score (sno int, cno int, degree int);
create index idx_score_sno on score(sno);
insert into score values (1, 20, 100);
insert into score values (1, 21, 89);
insert into score values (2, 20, 99);
insert into score values (2, 21, 90);
insert into score values (3, 20, 87);
insert into score values (3, 21, 20);
insert into score values (4, 20, 60);
insert into score values (4, 21, 70);explain verbose
SELECT STUDENT.sname, COURSE.cname, SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno SCORE.sno
LEFT JOIN COURSE ON SCORE.cno COURSE.cno
ORDER BY COURSE.cno;QUERY PLAN
--------------------------------------------------------------------------------------Sort (cost3.44..3.46 rows8 width19)Output: student.sname, course.cname, score.degree, course.cnoSort Key: course.cno- Hash Left Join (cost2.14..3.32 rows8 width19)Output: student.sname, course.cname, score.degree, course.cnoInner Unique: trueHash Cond: (score.cno course.cno)- Hash Right Join (cost1.09..2.21 rows8 width13)Output: student.sname, score.degree, score.cnoInner Unique: trueHash Cond: (score.sno student.sno)- Seq Scan on public.score (cost0.00..1.08 rows8 width12)Output: score.sno, score.cno, score.degree- Hash (cost1.04..1.04 rows4 width9)Output: student.sname, student.sno- Seq Scan on public.student (cost0.00..1.04 rows4 width9)Output: student.sname, student.sno- Hash (cost1.02..1.02 rows2 width10)Output: course.cname, course.cno- Seq Scan on public.course (cost0.00..1.02 rows2 width10)Output: course.cname, course.cno2.1 grouping_planner
grouping_plannercurrent_rel query_planner(root, standard_qp_callback, qp_extra);final_target create_pathtarget(root, root-processed_tlist);if (parse-sortClause)sort_input_target make_sort_input_target(root, final_target, have_postponed_srfs);make_sort_input_target函数的作用
排序列可能不在最终的投影列里面需要特殊处理下。易变函数和成本很高的函数需要再投影列中识别出来先排序在计算。 因为1sort limit场景可以少算一些。因为2易变函数每次算都可能不一样先排序好了再算有利于结果集稳定例如current_timestamp这种期望是排序后给出的每一样的时间都是递增的如果先排序在计算就能得到这种效果。
生成的final_target和sort_input_target相同因为没看到srf函数、易变函数。
final_target同sort_input_targetVar指向列sortgrouprefsfinal_target-exprs-elements[0]varno 1, varattno 2, vartype 1043STUDENT.sname0final_target-exprs-elements[1]varno 4, varattno 2, vartype 1043COURSE.cname0final_target-exprs-elements[2]varno 2, varattno 3, vartype 23SCORE.degree0final_target-exprs-elements[3]varno 4, varattno 1, vartype 23COURSE.cno1
grouping_planner继续执行开始生成排序path ...if (parse-sortClause)current_rel create_ordered_paths(root,current_rel,final_target,final_target_parallel_safe,have_postponed_srfs ? -1.0 :limit_tuples);grouping_planner→create_ordered_paths
create_ordered_paths// 创建一个排序节点ordered_rel fetch_upper_rel(root, UPPERREL_ORDERED, NULL);// 拿到path入口目前顶层是T_ProjectionPath就一个节点foreach(lc, input_rel-pathlist)// 判断input_path-pathkeys是不是有序的// 因为现在计划树是hashjoin每一列都是无序的所以input_path-pathkeys是空的需要排序is_sorted pathkeys_count_contained_in(root-sort_pathkeys, input_path-pathkeys, presorted_keys);if (is_sorted)sorted_path input_path;elsesorted_path (Path *) create_sort_path(root,ordered_rel,input_path,root-sort_pathkeys,limit_tuples);
输入的path顶层节点是project本来没有带pathkeys信息这里创建一个sort节点放在上面加入pathkey信息。但生成的sortpath没看到排序列的信息排序信息在基类path的pathkeys中。
sorted_path
{ path { type T_SortPath, pathtype T_Sort, parent 0x2334030, pathtarget 0x2333ef0, param_info 0x0, parallel_aware false, parallel_safe true, parallel_workers 0, rows 8, startup_cost 3.4437500000000005, total_cost 3.4637500000000006, pathkeys 0x232e018}, subpath 0x2333a00}T_PathKey每个pathkey排序列都对应了一个T_EquivalenceClassT_EquivalenceClass中记录了排序的具体信息。
{ type T_PathKey, pk_eclass 0x232bf88, pk_opfamily 1976, pk_strategy 1, pk_nulls_first false}T_EquivalenceClass中的ec_members记录了排序列信息Var{varno 4, varattno 1}
{ type T_EquivalenceClass, ec_opfamilies 0x232ddf8, // List{ 1976 }ec_collation 0, ec_members 0x232df48, // List { EquivalenceMember }// EquivalenceMember{// type T_EquivalenceMember, // em_expr 0x232de68, Var{varno 4, varattno 1}// em_relids 0x232de48, // em_is_const false, // em_is_child false, // em_datatype 23, // em_jdomain 0x2329158, em_parent 0x0}ec_sources 0x0, ec_derives 0x0, ec_relids 0x232df28,ec_has_const false, ec_has_volatile false, ec_broken false, ec_sortref 1, ec_min_security 4294967295, ec_max_security 0, ec_merged 0x0}生成排序节点后的计划
sort节点的target是四列虽然sql只写了三列但有一列是排序需要的也会加到pathtarget中。
3 实例【简单join】【排序非投影列】【投影列中有volatile函数】
drop table student;
create table student(sno int primary key, sname varchar(10), ssex int);
insert into student values(1, stu1, 0);
insert into student values(2, stu2, 1);
insert into student values(3, stu3, 1);
insert into student values(4, stu4, 0);drop table course;
create table course(cno int primary key, cname varchar(10), tno int);
insert into course values(20, meth, 10);
insert into course values(21, english, 11);drop table teacher;
create table teacher(tno int primary key, tname varchar(10), tsex int);
insert into teacher values(10, te1, 1);
insert into teacher values(11, te2, 0);drop table score;
create table score (sno int, cno int, degree int);
create index idx_score_sno on score(sno);
insert into score values (1, 20, 100);
insert into score values (1, 21, 89);
insert into score values (2, 20, 99);
insert into score values (2, 21, 90);
insert into score values (3, 20, 87);
insert into score values (3, 21, 20);
insert into score values (4, 20, 60);
insert into score values (4, 21, 70);explain verbose
SELECT STUDENT.sname, random(), SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno SCORE.sno
LEFT JOIN COURSE ON SCORE.cno COURSE.cno
ORDER BY COURSE.cno;QUERY PLAN
--------------------------------------------------------------------------------------------Result (cost3.44..3.56 rows8 width21)Output: student.sname, random(), score.degree, course.cno- Sort (cost3.44..3.46 rows8 width13)Output: student.sname, score.degree, course.cnoSort Key: course.cno- Hash Left Join (cost2.14..3.32 rows8 width13)Output: student.sname, score.degree, course.cnoInner Unique: trueHash Cond: (score.cno course.cno)- Hash Right Join (cost1.09..2.21 rows8 width13)Output: student.sname, score.degree, score.cnoInner Unique: trueHash Cond: (score.sno student.sno)- Seq Scan on public.score (cost0.00..1.08 rows8 width12)Output: score.sno, score.cno, score.degree- Hash (cost1.04..1.04 rows4 width9)Output: student.sname, student.sno- Seq Scan on public.student (cost0.00..1.04 rows4 width9)Output: student.sname, student.sno- Hash (cost1.02..1.02 rows2 width4)Output: course.cno- Seq Scan on public.course (cost0.00..1.02 rows2 width4)Output: course.cno3.1 grouping_planner→make_one_rel生成的RelOptInfo→reltarget
make_one_rel前
准备连接的RelOptInfo在simple_rel_array数组中这里关注下三个RelOptInfo的reltarget
(gdb) plist root-simple_rel_array[1]-reltarget-exprs
$67 2
$68 {ptr_value 0x3083218, int_value 50868760, oid_value 50868760, xid_value 50868760}
$69 {ptr_value 0x30ab8b8, int_value 51034296, oid_value 51034296, xid_value 51034296}
(gdb) p root-simple_rte_array[1]-relid
$70 16564root→simple_rel_array[i]simple_rel_array[i]→reltarget-exprsrelid1varno 1, varattno 2, vartype 104316564 student.sname1varno 1, varattno 1, vartype 2316564 student.sno2varno 2, varattno 3, vartype 2316579 score.degree2varno 2, varattno 1, vartype 2316579 score.cno2varno 2, varattno 2, vartype 2316579 score.sno4varno 4, varattno 1, vartype 2316569 course.cno
SELECT STUDENT.sname, random(), SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno SCORE.sno
LEFT JOIN COURSE ON SCORE.cno COURSE.cno
ORDER BY COURSE.cno;make_one_rel生成后
final_rel-reltarget-exprs列1varno 1, varattno 2, vartype 1043投影第1列STUDENT.sname2varno 2, varattno 3, vartype 23投影第3列SCORE.degree3varno 4, varattno 1, vartype 23排序列COURSE.cno
3.2 grouping_planner→make_sort_input_target规律v函数生成排序target
final_target create_pathtarget(root, root-processed_tlist);拿到的final_target
final_targetVar / FuncExpr指向列sortgrouprefsfinal_target-exprs-elements[0]varno 1, varattno 2, vartype 1043STUDENT.sname0final_target-exprs-elements[1]funcid 1598, funcresulttype 701random()0final_target-exprs-elements[2]varno 2, varattno 3, vartype 23SCORE.degree0final_target-exprs-elements[3]varno 4, varattno 1, vartype 23COURSE.cno1
make_sort_input_target拿到的sort_input_target过滤掉了random列
sort_input_targetVar / FuncExpr指向列sortgrouprefssort_input_target-exprs-elements[0]varno 1, varattno 2, vartype 1043STUDENT.sname0sort_input_target-exprs-elements[1]varno 2, varattno 3, vartype 23SCORE.degree0sort_input_target-exprs-elements[2]varno 4, varattno 1, vartype 23COURSE.cno1 实例2中apply_scanjoin_target_to_paths会先挂投影节点后面的create_ordered_paths在创建顶层的排序节点为什么这里的投影节点在最上层因为有volatile函数在需要先排序在到投影节点上计算random函数 3.3 grouping_planner→apply_scanjoin_target_to_paths final_target create_pathtarget(root, root-processed_tlist);...sort_input_target make_sort_input_target(...);...grouping_target sort_input_target;...scanjoin_target grouping_target;...scanjoin_targets list_make1(scanjoin_target);...scanjoin_target_same_exprs list_length(scanjoin_targets) 1 equal(scanjoin_target-exprs, current_rel-reltarget-exprs);...// 1 确定没有SRF list_length(scanjoin_targets) 1// 2 这里make_one_rel出来的current_rel和上面make_sort_input_target计算出来的投影列一样都过滤掉了v函数剩下三列// scanjoin_target_same_exprs truescanjoin_target_same_exprs list_length(scanjoin_targets) 1 equal(scanjoin_target-exprs, current_rel-reltarget-exprs);apply_scanjoin_target_to_paths(root, current_rel, scanjoin_targets,scanjoin_targets_contain_srfs,scanjoin_target_parallel_safe,注意
scanjoin_target-exprs表示最终结果需要的targetlist。current_rel-reltarget-exprs表示当前生成path中带的targetlist。生成path的路径需要和scanjoin_target一致所以进入下面函数判断是否生成投影节点。如果相同scanjoin_target_same_exprstrue则不生成投影节点。如果不同scanjoin_target_same_exprsfalse则调用create_projection_path传入scanjoin_target生成投影节点。
在apply_scanjoin_target_to_paths中:
apply_scanjoin_target_to_paths......foreach(lc, rel-pathlist){Path *subpath (Path *) lfirst(lc);if (tlist_same_exprs)// scanjoin_target-sortgrouprefs [0, 0, 1] 表示第三列是排序列// 因为现在的scanjoin_target同sort_input_target中只有三列投影列1、3和排序列参考上面sort_input_target表格。subpath-pathtarget-sortgrouprefs scanjoin_target-sortgrouprefs;else{Path *newpath;newpath (Path *) create_projection_path(root, rel, subpath,scanjoin_target);lfirst(lc) newpath;}}3.4 grouping_planner→create_ordered_paths
继续成成排序node
grouping_planner...if (parse-sortClause)current_rel create_ordered_paths(root,current_rel,final_target,final_target_parallel_safe,have_postponed_srfs ? -1.0 :limit_tuples);create_ordered_paths最重要的入参就是final_target保存了全部的列信息和排序列的位置sortgrouprefs。注意前面生成path中的reltarget已经过滤了random列但这里没有过滤需要全量的信息。
final_targetVar / FuncExpr指向列sortgrouprefsfinal_target-exprs-elements[0]varno 1, varattno 2, vartype 1043STUDENT.sname0final_target-exprs-elements[1]funcid 1598, funcresulttype 701random()0final_target-exprs-elements[2]varno 2, varattno 3, vartype 23SCORE.degree0final_target-exprs-elements[3]varno 4, varattno 1, vartype 23COURSE.cno1
注意这里create_sort_path为hashjoin节点上面加了一层sort节点sort节点的pathtarget继承了hash节点的pathtarget也就是三列没有random函数列。注意这里的target是上面表格中的final_target也就是四列带random函数。加了sort节点后发现这里不相同所以开始增加投影列apply_projection_to_path。
create_ordered_pathsordered_rel fetch_upper_rel(root, UPPERREL_ORDERED, NULL);foreach(lc, input_rel-pathlist)is_sorted pathkeys_count_contained_inif (is_sorted)sorted_path input_path;elsesorted_path (Path *) create_sort_path(...)// 生成sorted_path// {type T_SortPath, pathtype T_Sort, pathtarget 三列 }if (sorted_path-pathtarget ! target)sorted_path apply_projection_to_path(root, ordered_rel, sorted_path, target);// 生成投影列// {type T_ProjectionPath, pathtype T_Result, pathtarget 四列 }最终生成PATH
SELECT STUDENT.sname, random(), SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno SCORE.sno
LEFT JOIN COURSE ON SCORE.cno COURSE.cno
ORDER BY COURSE.cno;最终效果
4 实例【简单join】【排序volatile函数】【投影列中有volatile函数】
drop table student;
create table student(sno int primary key, sname varchar(10), ssex int);
insert into student values(1, stu1, 0);
insert into student values(2, stu2, 1);
insert into student values(3, stu3, 1);
insert into student values(4, stu4, 0);drop table course;
create table course(cno int primary key, cname varchar(10), tno int);
insert into course values(20, meth, 10);
insert into course values(21, english, 11);drop table teacher;
create table teacher(tno int primary key, tname varchar(10), tsex int);
insert into teacher values(10, te1, 1);
insert into teacher values(11, te2, 0);drop table score;
create table score (sno int, cno int, degree int);
create index idx_score_sno on score(sno);
insert into score values (1, 20, 100);
insert into score values (1, 21, 89);
insert into score values (2, 20, 99);
insert into score values (2, 21, 90);
insert into score values (3, 20, 87);
insert into score values (3, 21, 20);
insert into score values (4, 20, 60);
insert into score values (4, 21, 70);explain verbose
SELECT STUDENT.sname, random(), SCORE.degree
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.sno SCORE.sno
LEFT JOIN COURSE ON SCORE.cno COURSE.cno
ORDER BY random();QUERY PLAN
--------------------------------------------------------------------------------Sort (cost2.35..2.37 rows8 width17)Output: student.sname, (random()), score.degreeSort Key: (random())- Hash Right Join (cost1.09..2.23 rows8 width17)Output: student.sname, random(), score.degreeInner Unique: trueHash Cond: (score.sno student.sno)- Seq Scan on public.score (cost0.00..1.08 rows8 width12)Output: score.sno, score.cno, score.degree- Hash (cost1.04..1.04 rows4 width9)Output: student.sname, student.sno- Seq Scan on public.student (cost0.00..1.04 rows4 width9)Output: student.sname, student.sno4.1 make_one_rel结果
第一步拿到RelOptInfo current_rel query_planner(root, standard_qp_callback, qp_extra);
current_rel-reltarget中忽略了random函数
{ type T_PathTarget, exprs {Var{varno 1, varattno 2, vartype 1043}, // STUDENT.snameVar{varno 2, varattno 3, vartype 23} // SCORE.degree}, sortgrouprefs 0x0 }4.2 拿到final_target
final_target create_pathtarget(root, root-processed_tlist);
{type T_PathTarget, exprs {Var{varno 1, varattno 2, vartype 1043}, // STUDENT.snameFuncExpr {xpr {type T_FuncExpr}, funcid 1598}, // random()Var{varno 2, varattno 3, vartype 23} // SCORE.degree}, sortgrouprefs [0, 1, 0]
}4.3 构造排序targetmake_sort_input_target
sort_input_target make_sort_input_target(root, final_target, have_postponed_srfs);
{type T_PathTarget,exprs {Var{varno 1, varattno 2, vartype 1043}, // STUDENT.snameFuncExpr {xpr {type T_FuncExpr}, funcid 1598}, // random()Var{varno 2, varattno 3, vartype 23} // SCORE.degree}, sortgrouprefs [0, 1, 0]
}4.4 apply_scanjoin_target_to_paths增加投影
apply_scanjoin_target_to_paths执行后增加投影节点
{ path {type T_ProjectionPath, pathtype T_Result }4.5 create_ordered_paths后增加排序节点在最顶层
{ path {type T_SortPath, pathtype T_Sort }
