PostgreSQL中grouping_planner函数有什么作用

这篇文章主要介绍“postgresql中grouping_planner函数有什么作用”，在日常操作中，相信很多人在Postgresql中grouping_planner函数有什么作用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中grouping_planner函数有什么作用”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

源码解读

分组/聚集等操作是在一个Relation上叠加分组/聚集运算,grouping_planner函数首先通过query_planner函数生成一个新的关系,然后在此关系上attached分组/聚集等操作。

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;

    
    //如果存在LIMIT/OFFSET子句,调整tuple_fraction
    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;//
    }

    
    //使tuple_fraction可被低级别的处理过程访问(在优化器信息中设置)
    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;  

        
        //为了安全起见，复制processed_tlist，而不是就地修改
        tlist = postprocess_setop_tlist(copyObject(tlist), parse->targetList);

        
        //
        root->processed_tlist = tlist;

        
        //从集合操作结果投影列中获取PathTarget格式的结果列
        final_target = current_rel->cheapest_total_path->pathtarget;

        
        //检查是否并行安全
        final_target_parallel_safe =
            is_parallel_safe(root, (node *) final_target->exprs);

        
        //集合操作结果投影列不能包含任何的SRFs
        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);//检查

        
        //预处理grouping sets语句和GROUP BY 子句
        if (parse->groupingSets)//
        {
            gset_data = preprocess_grouping_sets(root);//预处理grouping sets语句
        }
        else
        {
            
            //如处理常规的GROUP BY 子句
            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;//否则,正常赋值

        
        //配置standard_qp_callback函数需要的相关数据
        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
        {
            
            //在sort_input_target中不需要重复计算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));
        }

        
        //添加到final_rel中
        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);

    
    //注意:目前的做法是让调用放来执行set_cheap()函数
}

到此，关于“PostgreSQL中grouping_planner函数有什么作用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注编程网网站，小编会继续努力为大家带来更多实用的文章！