这篇文章主要介绍“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函数有什么作用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
