PostgreSQL 源码解读（37）- 查询语句#22（查询优化-grouping_plan...

在主函数subquery_planner完成外连接消除后,接下来调用grouping_planner函数,本节简单介绍了此函数的主体逻辑。

一、源码解读

grouping_planner函数源码：

 static void
 grouping_planner(PlannerInfo *root, bool inheritance_update,
                  double tuple_fraction)
 {
     Query      *parse = root->parse;
     List       *tlist;
     int64       offset_est = 0;
     int64       count_est = 0;
     double      limit_tuples = -1.0;
     bool        have_postponed_srfs = false;
     PathTarget *final_target;
     List       *final_targets;
     List       *final_targets_contain_srfs;
     bool        final_target_parallel_safe;
     RelOptInfo *current_rel;
     RelOptInfo *final_rel;
     ListCell   *lc;
 
     
     if (parse->limitCount || parse->limitOffset)//存在LIMIT/OFFSET语句
     {
         tuple_fraction = preprocess_limit(root, tuple_fraction,
                                           &offset_est, &count_est);//获取元组数量
 
         
         if (count_est > 0 && offset_est >= 0)
             limit_tuples = (double) count_est + (double) offset_est;//
     }
 
     
     root->tuple_fraction = tuple_fraction;//设置值
 
     if (parse->setOperations)//集合操作,如UNION等
     {
         
         if (parse->sortClause)
             root->tuple_fraction = 0.0;//存在排序操作,需扫描所有的元组
 
         
         current_rel = plan_set_operations(root);//调用集合操作的"规划"函数
 
         
         Assert(parse->commandType == CMD_SELECT);
 
         tlist = root->processed_tlist;  
 
         
         tlist = postprocess_setop_tlist(copyObject(tlist), parse->targetList);
 
         
         root->processed_tlist = tlist;
 
         
         final_target = current_rel->cheapest_total_path->pathtarget;
 
         
         final_target_parallel_safe =
             is_parallel_safe(root, (node *) final_target->exprs);
 
         
         Assert(!parse->hasTargetSRFs);
         final_targets = final_targets_contain_srfs = NIL;
 
         
         if (parse->rowMarks)
             ereport(ERROR,
                     (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
             
                      errmsg("%s is not allowed with UNION/INTERSECT/EXCEPT",
                             LCS_asString(linitial_node(RowMarkClause,
                                                        parse->rowMarks)->strength))));
 
         
         Assert(parse->distinctClause == NIL);
         root->sort_pathkeys = make_pathkeys_for_sortclauses(root,
                                                             parse->sortClause,
                                                             tlist);
     }
     else//非集合操作
     {
         
         PathTarget *sort_input_target;
         List       *sort_input_targets;
         List       *sort_input_targets_contain_srfs;
         bool        sort_input_target_parallel_safe;
         PathTarget *grouping_target;
         List       *grouping_targets;
         List       *grouping_targets_contain_srfs;
         bool        grouping_target_parallel_safe;
         PathTarget *scanjoin_target;
         List       *scanjoin_targets;
         List       *scanjoin_targets_contain_srfs;
         bool        scanjoin_target_parallel_safe;
         bool        scanjoin_target_same_exprs;
         bool        have_grouping;
         AgGClauseCosts agg_costs;
         WindowFuncLists *wflists = NULL;
         List       *activewindows = NIL;
         grouping_sets_data *gset_data = NULL;
         standard_qp_extra qp_extra;
 
         
         Assert(!root->hasRecursion);//检查
 
         
         if (parse->groupingSets)//
         {
             gset_data = preprocess_grouping_sets(root);//预处理grouping sets语句
         }
         else
         {
             
             if (parse->groupClause)
                 parse->groupClause = preprocess_groupclause(root, NIL);//处理普通的Group By语句
         }
 
         
         tlist = preprocess_targetlist(root);//处理投影列
 
         
         root->processed_tlist = tlist;//赋值
 
         
         MemSet(&agg_costs, 0, sizeof(AggClauseCosts));
         if (parse->hasAggs)//存在聚合函数
         {
             get_agg_clause_costs(root, (Node *) tlist, AGGSPLIT_SIMPLE,
                                  &agg_costs);//收集用于估算成本的统计信息
             get_agg_clause_costs(root, parse->havingQual, AGGSPLIT_SIMPLE,
                                  &agg_costs);//收集用于估算成本的统计信息
         }
 
         
         if (parse->hasWindowFuncs)//窗口函数
         {
             wflists = find_window_functions((Node *) tlist,
                                             list_length(parse->windowClause));
             if (wflists->numWindowFuncs > 0)
                 activeWindows = select_active_windows(root, wflists);
             else
                 parse->hasWindowFuncs = false;
         }
 
         
         if (parse->hasAggs)//预处理最大最小聚合
             preprocess_minmax_aggregates(root, tlist);
 
         
         if (parse->groupClause ||
             parse->groupingSets ||
             parse->distinctClause ||
             parse->hasAggs ||
             parse->hasWindowFuncs ||
             parse->hasTargetSRFs ||
             root->hasHavingQual)//存在Group By/Grouping Set等语句,则limit_tuples设置为-1
             root->limit_tuples = -1.0;
         else
             root->limit_tuples = limit_tuples;//否则,正常赋值
 
         
         qp_extra.tlist = tlist;//赋值
         qp_extra.activeWindows = activeWindows;
         qp_extra.groupClause = (gset_data
                                 ? (gset_data->rollups ? linitial_node(RollupData, gset_data->rollups)->groupClause : NIL)
                                 : parse->groupClause);
 
         
     //为查询中的扫描/连接部分生成最优的未排序/预排序路径(如FROM/WHERE语句表示的处理过程)
         current_rel = query_planner(root, tlist,
                                     standard_qp_callback, &qp_extra);
 
         
         final_target = create_pathtarget(root, tlist);
         final_target_parallel_safe =
             is_parallel_safe(root, (Node *) final_target->exprs);
 
         
         if (parse->sortClause)//存在sort语句?
         {
             sort_input_target = make_sort_input_target(root,
                                                        final_target,
                                                        &have_postponed_srfs);
             sort_input_target_parallel_safe =
                 is_parallel_safe(root, (Node *) sort_input_target->exprs);
         }
         else
         {
             sort_input_target = final_target;//不存在,则直接赋值
             sort_input_target_parallel_safe = final_target_parallel_safe;
         }
 
         
         if (activeWindows)//存在窗口函数?
         {
             grouping_target = make_window_input_target(root,
                                                        final_target,
                                                        activeWindows);
             grouping_target_parallel_safe =
                 is_parallel_safe(root, (Node *) grouping_target->exprs);
         }
         else
         {
             grouping_target = sort_input_target;
             grouping_target_parallel_safe = sort_input_target_parallel_safe;
         }
 
         
         have_grouping = (parse->groupClause || parse->groupingSets ||
                          parse->hasAggs || root->hasHavingQual);
         if (have_grouping)
         {//存在group等分组语句
             scanjoin_target = make_group_input_target(root, final_target);
             scanjoin_target_parallel_safe =
                 is_parallel_safe(root, (Node *) grouping_target->exprs);
         }
         else
         {
             scanjoin_target = grouping_target;
             scanjoin_target_parallel_safe = grouping_target_parallel_safe;
         }
 
         
         if (parse->hasTargetSRFs)//存在SRFs
         {
             
             split_pathtarget_at_srfs(root, final_target, sort_input_target,
                                      &final_targets,
                                      &final_targets_contain_srfs);
             final_target = linitial_node(PathTarget, final_targets);
             Assert(!linitial_int(final_targets_contain_srfs));
             
             split_pathtarget_at_srfs(root, sort_input_target, grouping_target,
                                      &sort_input_targets,
                                      &sort_input_targets_contain_srfs);
             sort_input_target = linitial_node(PathTarget, sort_input_targets);
             Assert(!linitial_int(sort_input_targets_contain_srfs));
             
             split_pathtarget_at_srfs(root, grouping_target, scanjoin_target,
                                      &grouping_targets,
                                      &grouping_targets_contain_srfs);
             grouping_target = linitial_node(PathTarget, grouping_targets);
             Assert(!linitial_int(grouping_targets_contain_srfs));
             
             split_pathtarget_at_srfs(root, scanjoin_target, NULL,
                                      &scanjoin_targets,
                                      &scanjoin_targets_contain_srfs);
             scanjoin_target = linitial_node(PathTarget, scanjoin_targets);
             Assert(!linitial_int(scanjoin_targets_contain_srfs));
         }
         else
         {
             
             final_targets = final_targets_contain_srfs = NIL;
             sort_input_targets = sort_input_targets_contain_srfs = NIL;
             grouping_targets = grouping_targets_contain_srfs = NIL;
             scanjoin_targets = list_make1(scanjoin_target);
             scanjoin_targets_contain_srfs = NIL;
         }
 
         
     //应用扫描/连接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);
 
         
     //赋值
         root->upper_targets[UPPERREL_FINAL] = final_target;
         root->upper_targets[UPPERREL_WINDOW] = sort_input_target;
         root->upper_targets[UPPERREL_GROUP_AGG] = grouping_target;
 
         
         if (have_grouping)//存在分组操作
         {
             current_rel = create_grouping_paths(root,
                                                 current_rel,
                                                 grouping_target,
                                                 grouping_target_parallel_safe,
                                                 &agg_costs,
                                                 gset_data);//创建分组访问路径
             
             if (parse->hasTargetSRFs)
                 adjust_paths_for_srfs(root, current_rel,
                                       grouping_targets,
                                       grouping_targets_contain_srfs);
         }
 
         
         if (activeWindows)//存在窗口函数
         {
             current_rel = create_window_paths(root,
                                               current_rel,
                                               grouping_target,
                                               sort_input_target,
                                               sort_input_target_parallel_safe,
                                               tlist,
                                               wflists,
                                               activeWindows);
             
             if (parse->hasTargetSRFs)
                 adjust_paths_for_srfs(root, current_rel,
                                       sort_input_targets,
                                       sort_input_targets_contain_srfs);
         }
 
         
         if (parse->distinctClause)//存在distinct?
         {
             current_rel = create_distinct_paths(root,
                                                 current_rel);
         }
     }                           
 
     
     if (parse->sortClause)//存在sort语句?
     {
         current_rel = create_ordered_paths(root,
                                            current_rel,
                                            final_target,
                                            final_target_parallel_safe,
                                            have_postponed_srfs ? -1.0 :
                                            limit_tuples);
         
         if (parse->hasTargetSRFs)
             adjust_paths_for_srfs(root, current_rel,
                                   final_targets,
                                   final_targets_contain_srfs);
     }
 
     
     final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);//获取最终的RelOptInfo(用于替换RTE)
 
     
     if (current_rel->consider_parallel &&
         is_parallel_safe(root, parse->limitOffset) &&
         is_parallel_safe(root, parse->limitCount))
         final_rel->consider_parallel = true;//并行
 
     
     final_rel->serverid = current_rel->serverid;
     final_rel->userid = current_rel->userid;
     final_rel->useridiscurrent = current_rel->useridiscurrent;
     final_rel->fdwroutine = current_rel->fdwroutine;
 
     
     foreach(lc, current_rel->pathlist)//逐一遍历访问路径
     {
         Path       *path = (Path *) lfirst(lc);
 
         
         if (parse->rowMarks)
         {
             path = (Path *) create_lockrows_path(root, final_rel, path,
                                                  root->rowMarks,
                                                  SS_assign_special_param(root));
         }
 
         
         if (limit_needed(parse))
         {
             path = (Path *) create_limit_path(root, final_rel, path,
                                               parse->limitOffset,
                                               parse->limitCount,
                                               offset_est, count_est);
         }
 
         
         if (parse->commandType != CMD_SELECT && !inheritance_update)//非查询语句
         {
             List       *withCheckOptionLists;
             List       *returningLists;
             List       *rowMarks;
 
             
             if (parse->withCheckOptions)
                 withCheckOptionLists = list_make1(parse->withCheckOptions);
             else
                 withCheckOptionLists = NIL;
 
             if (parse->returningList)
                 returningLists = list_make1(parse->returningList);
             else
                 returningLists = NIL;
 
             
             if (parse->rowMarks)
                 rowMarks = NIL;
             else
                 rowMarks = root->rowMarks;
 
             path = (Path *)
                 create_modifytable_path(root, final_rel,
                                         parse->commandType,
                                         parse->canSetTag,
                                         parse->resultRelation,
                                         NIL,
                                         false,
                                         list_make1_int(parse->resultRelation),
                                         list_make1(path),
                                         list_make1(root),
                                         withCheckOptionLists,
                                         returningLists,
                                         rowMarks,
                                         parse->onConflict,
                                         SS_assign_special_param(root));
         }
 
         
         add_path(final_rel, path);
     }
 
     
     if (final_rel->consider_parallel && root->query_level > 1 &&
         !limit_needed(parse))
     {
         Assert(!parse->rowMarks && parse->commandType == CMD_SELECT);
         foreach(lc, current_rel->partial_pathlist)
         {
             Path       *partial_path = (Path *) lfirst(lc);
 
             add_partial_path(final_rel, partial_path);
         }
     }
 
     
     if (final_rel->fdwroutine &&
         final_rel->fdwroutine->GetForeignUpperPaths)
         final_rel->fdwroutine->GetForeignUpperPaths(root, UPPERREL_FINAL,
                                                     current_rel, final_rel,
                                                     NULL);
 
     
     if (create_upper_paths_hook)
         (*create_upper_paths_hook) (root, UPPERREL_FINAL,
                                     current_rel, final_rel, NULL);
 
     
 }

二、参考资料

planner.c