小工具知识网

请输入关键词
当前位置： 首页 > 行业知识 > Postgresql源码（134）优化器针对volatile函数的排序优化分析

行业知识

Postgresql源码（134）优化器针对volatile函数的排序优化分析

2026-10-28 10:44:00 来源：互联网转载

相关

《Postgresql源码（133）优化器动态规划生成连接路径的实例分析》

上一篇对路径的生成进行了分析，通过make_one_rel最终拿到了一个带着路径的RelOptInfo。本篇针对带volatile函数的排序场景继续分析subquery_planner的后续流程。

subquery_plannergrouping_plannerquery_plannermake_one_rel     Seq Scan on student  (cost=0.00..21.00 rows=1100 width=42)   ->  Hash  (cost=21.00..21.00 rows=1100 width=42)         ->  Seq Scan on course  (cost=0.00..21.00 rows=1100 width=42)

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.sname
      • final_target->exprs->elements[1] : {varno = 4, varattno = 2, vartype = 1043} COURSE.cname
      • final_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拿到最终的RelOptInfo
                best_path = get_cheapest_fractional_path在RelOptInfo中选择一个最优的path
                top_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 verboseSELECT STUDENT.sname, COURSE.cname, SCORE.degreeFROM STUDENTLEFT JOIN SCORE ON STUDENT.sno = SCORE.snoLEFT JOIN COURSE ON SCORE.cno = COURSE.cnoORDER BY COURSE.cno;                                      QUERY PLAN-------------------------------------------------------------------------------------- Sort  (cost=3.44..3.46 rows=8 width=19)   Output: student.sname, course.cname, score.degree, course.cno   Sort Key: course.cno   ->  Hash Left Join  (cost=2.14..3.32 rows=8 width=19)         Output: student.sname, course.cname, score.degree, course.cno         Inner Unique: true         Hash Cond: (score.cno = course.cno)         ->  Hash Right Join  (cost=1.09..2.21 rows=8 width=13)               Output: student.sname, score.degree, score.cno               Inner Unique: true               Hash Cond: (score.sno = student.sno)               ->  Seq Scan on public.score  (cost=0.00..1.08 rows=8 width=12)                     Output: score.sno, score.cno, score.degree               ->  Hash  (cost=1.04..1.04 rows=4 width=9)                     Output: student.sname, student.sno                     ->  Seq Scan on public.student  (cost=0.00..1.04 rows=4 width=9)                           Output: student.sname, student.sno         ->  Hash  (cost=1.02..1.02 rows=2 width=10)               Output: course.cname, course.cno               ->  Seq Scan on public.course  (cost=0.00..1.02 rows=2 width=10)                     Output: course.cname, course.cno

                2.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函数的作用：

                • 排序列可能不在最终的投影列里面，需要特殊处理下。
                • 易变函数和成本很高的函数需要再投影列中识别出来，先排序，在计算。
                  • 因为1：sort limit场景可以少算一些。
                  • 因为2：易变函数每次算都可能不一样，先排序好了再算有利于结果集稳定，例如current_timestamp这种，期望是排序后给出的每一样的时间都是递增的，如果先排序在计算就能得到这种效果。

                    生成的final_target和sort_input_target相同，因为没看到srf函数、易变函数。

                    final_target同sort_input_targetVar指向列sortgrouprefs
                    final_target->exprs->elements[0]varno = 1, varattno = 2, vartype = 1043STUDENT.sname0
                    final_target->exprs->elements[1]varno = 4, varattno = 2, vartype = 1043COURSE.cname0
                    final_target->exprs->elements[2]varno = 2, varattno = 3, vartype = 23SCORE.degree0
                    final_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_EquivalenceClass，T_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 verboseSELECT STUDENT.sname, random(), SCORE.degreeFROM STUDENTLEFT JOIN SCORE ON STUDENT.sno = SCORE.snoLEFT JOIN COURSE ON SCORE.cno = COURSE.cnoORDER BY COURSE.cno;                                         QUERY PLAN-------------------------------------------------------------------------------------------- Result  (cost=3.44..3.56 rows=8 width=21)   Output: student.sname, random(), score.degree, course.cno   ->  Sort  (cost=3.44..3.46 rows=8 width=13)         Output: student.sname, score.degree, course.cno         Sort Key: course.cno         ->  Hash Left Join  (cost=2.14..3.32 rows=8 width=13)               Output: student.sname, score.degree, course.cno               Inner Unique: true               Hash Cond: (score.cno = course.cno)               ->  Hash Right Join  (cost=1.09..2.21 rows=8 width=13)                     Output: student.sname, score.degree, score.cno                     Inner Unique: true                     Hash Cond: (score.sno = student.sno)                     ->  Seq Scan on public.score  (cost=0.00..1.08 rows=8 width=12)                           Output: score.sno, score.cno, score.degree                     ->  Hash  (cost=1.04..1.04 rows=4 width=9)                           Output: student.sname, student.sno                           ->  Seq Scan on public.student  (cost=0.00..1.04 rows=4 width=9)                                 Output: student.sname, student.sno               ->  Hash  (cost=1.02..1.02 rows=2 width=4)                     Output: course.cno                     ->  Seq Scan on public.course  (cost=0.00..1.02 rows=2 width=4)                           Output: course.cno

                        3.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 = 2 = {ptr_value = 0x3083218, int_value = 50868760, oid_value = 50868760, xid_value = 50868760} = {ptr_value = 0x30ab8b8, int_value = 51034296, oid_value = 51034296, xid_value = 51034296}(gdb) p root->simple_rte_array[1]->relid = 16564
                        root→simple_rel_array[i]simple_rel_array[i]→reltarget->exprsrelid
                        1varno = 1, varattno = 2, vartype = 104316564 student.sname
                        1varno = 1, varattno = 1, vartype = 2316564 student.sno
                        2varno = 2, varattno = 3, vartype = 2316579 score.degree
                        2varno = 2, varattno = 1, vartype = 2316579 score.cno
                        2varno = 2, varattno = 2, vartype = 2316579 score.sno
                        4varno = 4, varattno = 1, vartype = 2316569 course.cno
                        SELECT STUDENT.sname, random(), SCORE.degreeFROM STUDENTLEFT JOIN SCORE ON STUDENT.sno = SCORE.snoLEFT JOIN COURSE ON SCORE.cno = COURSE.cnoORDER BY COURSE.cno;

                        make_one_rel生成后：

                        final_rel->reltarget->exprs列
                        1varno = 1, varattno = 2, vartype = 1043投影第1列：STUDENT.sname
                        2varno = 2, varattno = 3, vartype = 23投影第3列：SCORE.degree
                        3varno = 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指向列sortgrouprefs
                        final_target->exprs->elements[0]varno = 1, varattno = 2, vartype = 1043STUDENT.sname0
                        final_target->exprs->elements[1]funcid = 1598, funcresulttype = 701random()0
                        final_target->exprs->elements[2]varno = 2, varattno = 3, vartype = 23SCORE.degree0
                        final_target->exprs->elements[3]varno = 4, varattno = 1, vartype = 23COURSE.cno1

                        make_sort_input_target拿到的sort_input_target，过滤掉了random列：

                        sort_input_targetVar / FuncExpr指向列sortgrouprefs
                        sort_input_target->exprs->elements[0]varno = 1, varattno = 2, vartype = 1043STUDENT.sname0
                        sort_input_target->exprs->elements[1]varno = 2, varattno = 3, vartype = 23SCORE.degree0
                        sort_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,

                        注意：

                        1. scanjoin_target->exprs：表示最终结果需要的targetlist。
                        2. current_rel->reltarget->exprs：表示当前生成path中带的targetlist。
                        3. 生成path的路径需要和scanjoin_target一致，所以进入下面函数判断是否生成投影节点。
                        4. 如果相同，scanjoin_target_same_exprs==true，则不生成投影节点。
                        5. 如果不同，scanjoin_target_same_exprs==false，则调用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指向列sortgrouprefs
                          final_target->exprs->elements[0]varno = 1, varattno = 2, vartype = 1043STUDENT.sname0
                          final_target->exprs->elements[1]funcid = 1598, funcresulttype = 701random()0
                          final_target->exprs->elements[2]varno = 2, varattno = 3, vartype = 23SCORE.degree0
                          final_target->exprs->elements[3]varno = 4, varattno = 1, vartype = 23COURSE.cno1
                          1. 注意：这里create_sort_path为hashjoin节点上面加了一层sort节点，sort节点的pathtarget继承了hash节点的pathtarget，也就是三列（没有random函数列）。
                          2. 注意：这里的target是上面表格中的final_target，也就是四列（带random函数）。
                          3. 加了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.degreeFROM STUDENTLEFT JOIN SCORE ON STUDENT.sno = SCORE.snoLEFT JOIN COURSE ON SCORE.cno = COURSE.cnoORDER 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 verboseSELECT STUDENT.sname, random(), SCORE.degreeFROM STUDENTLEFT JOIN SCORE ON STUDENT.sno = SCORE.snoLEFT JOIN COURSE ON SCORE.cno = COURSE.cnoORDER BY random();                                   QUERY PLAN-------------------------------------------------------------------------------- Sort  (cost=2.35..2.37 rows=8 width=17)   Output: student.sname, (random()), score.degree   Sort Key: (random())   ->  Hash Right Join  (cost=1.09..2.23 rows=8 width=17)         Output: student.sname, random(), score.degree         Inner Unique: true         Hash Cond: (score.sno = student.sno)         ->  Seq Scan on public.score  (cost=0.00..1.08 rows=8 width=12)               Output: score.sno, score.cno, score.degree         ->  Hash  (cost=1.04..1.04 rows=4 width=9)               Output: student.sname, student.sno               ->  Seq Scan on public.student  (cost=0.00..1.04 rows=4 width=9)                     Output: student.sname, student.sno

                          4.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.sname    Var{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.sname FuncExpr {xpr = {type = T_FuncExpr}, funcid = 1598},  // random() Var{varno = 2, varattno = 3, vartype = 23}            // SCORE.degree },  sortgrouprefs = [0, 1, 0]}

                          4.3 构造排序target：make_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.sname FuncExpr {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 }

上一篇：洛克王国服务器怎么打不开?

下一篇：复韵母发音宝典，轻松掌握标准读法！

最新文章

图文

热门标签

友情链接