PostgreSQL在何处处理 sql查询之四十八
接着，分析：
/*-------------------- * subquery_planner * Invokes the planner on a subquery. We recurse to here for each * sub-SELECT found in the query tree. * * glob is the global state for the current planner run. * parse is the querytree produced by the parser & rewriter. * parent_root is the immediate parent Query's info (NULL at the top level). * hasRecursion is true if this is a recursive WITH query. * tuple_fraction is the fraction of tuples we expect will be retrieved. * tuple_fraction is interpreted as explained for grouping_planner, below. * * If subroot isn't NULL, we pass back the query's final PlannerInfo struct; * among other things this tells the output sort ordering of the plan. * * Basically, this routine does the stuff that should only be done once * per Query object. It then calls grouping_planner. At one time, * grouping_planner could be invoked recursively on the same Query object; * that's not currently true, but we keep the separation between the two * routines anyway, in case we need it again someday. * * subquery_planner will be called recursively to handle sub-Query nodes * found within the query's expressions and rangetable. * * Returns a query plan. *-------------------- */ Plan * subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root, bool hasRecursion, double tuple_fraction, PlannerInfo **subroot) { int num_old_subplans = list_length(glob->subplans); PlannerInfo *root; Plan *plan; List *newHaving; bool hasOuterJoins; ListCell *l; /* Create a PlannerInfo data structure for this subquery */ root = makeNode(PlannerInfo); root->parse = parse; root->glob = glob; root->query_level = parent_root ? parent_root->query_level + 1 : 1; root->parent_root = parent_root; root->plan_params = NIL; root->planner_cxt = CurrentMemoryContext; root->init_plans = NIL; root->cte_plan_ids = NIL; root->eq_classes = NIL; root->append_rel_list = NIL; root->rowMarks = NIL; root->hasInheritedTarget = false; root->hasRecursion = hasRecursion; if (hasRecursion) root->wt_param_id = SS_assign_special_param(root); else root->wt_param_id = -1; root->non_recursive_plan = NULL; /* * If there is a WITH list, process each WITH query and build an initplan * SubPlan structure for it. */ if (parse->cteList) SS_process_ctes(root); /* * Look for ANY and EXISTS SubLinks in WHERE and JOIN/ON clauses, and try * to transform them into joins. Note that this step does not descend * into subqueries; if we pull up any subqueries below, their SubLinks are * processed just before pulling them up. */ if (parse->hasSubLinks) pull_up_sublinks(root); /* * Scan the rangetable for set-returning functions, and inline them if * possible (producing subqueries that might get pulled up next). * Recursion issues here are handled in the same way as for SubLinks. */ inline_set_returning_functions(root); /* * Check to see if any subqueries in the jointree can be merged into this * query. */ parse->jointree = (FromExpr *) pull_up_subqueries(root, (Node *) parse->jointree, NULL, NULL); /* * If this is a simple UNION ALL query, flatten it into an appendrel. We * do this now because it requires applying pull_up_subqueries to the leaf * queries of the UNION ALL, which weren't touched above because they * weren't referenced by the jointree (they will be after we do this). */ if (parse->setOperations) flatten_simple_union_all(root); /* * Detect whether any rangetable entries are RTE_JOIN kind; if not, we can * avoid the expense of doing flatten_join_alias_vars(). Also check for * outer joins --- if none, we can skip reduce_outer_joins(). This must be * done after we have done pull_up_subqueries, of course. */ root->hasJoinRTEs = false; hasOuterJoins = false; foreach(l, parse->rtable) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); if (rte->rtekind == RTE_JOIN) { root->hasJoinRTEs = true; if (IS_OUTER_JOIN(rte->jointype)) { hasOuterJoins = true; /* Can quit scanning once we find an outer join */ break; } } } /* * Preprocess RowMark information. We need to do this after subquery * pullup (so that all non-inherited RTEs are present) and before * inheritance expansion (so that the info is available for * expand_inherited_tables to examine and modify). */ preprocess_rowmarks(root); /* * Expand any rangetable entries that are inheritance sets into "append * relations". This can add entries to the rangetable, but they must be * plain base relations not joins, so it's OK (and marginally more * efficient) to do it after checking for join RTEs. We must do it after * pulling up subqueries, else we'd fail to handle inherited tables in * subqueries. */ expand_inherited_tables(root); /* * Set hasHavingQual to remember if HAVING clause is present. Needed * because preprocess_expression will reduce a constant-true condition to * an empty qual list ... but "HAVING TRUE" is not a semantic no-op. */ root->hasHavingQual = (parse->havingQual != NULL); /* Clear this flag; might get set in distribute_qual_to_rels */ root->hasPseudoConstantQuals = false; /* * Do expression preprocessing on targetlist and quals, as well as other * random expressions in the querytree. Note that we do not need to * handle sort/group expressions explicitly, because they are actually * part of the targetlist. */ parse->targetList = (List *) preprocess_expression(root, (Node *) parse->targetList, EXPRKIND_TARGET); parse->returningList = (List *) preprocess_expression(root, (Node *) parse->returningList, EXPRKIND_TARGET); preprocess_qual_conditions(root, (Node *) parse->jointree); parse->havingQual = preprocess_expression(root, parse->havingQual, EXPRKIND_QUAL); foreach(l, parse->windowClause) { WindowClause *wc = (WindowClause *) lfirst(l); /* partitionClause/orderClause are sort/group expressions */ wc->startOffset = preprocess_expression(root, wc->startOffset, EXPRKIND_LIMIT); wc->endOffset = preprocess_expression(root, wc->endOffset, EXPRKIND_LIMIT); } parse->limitOffset = preprocess_expression(root, parse->limitOffset, EXPRKIND_LIMIT); parse->limitCount = preprocess_expression(root, parse->limitCount, EXPRKIND_LIMIT); root->append_rel_list = (List *) preprocess_expression(root, (Node *) root->append_rel_list, EXPRKIND_APPINFO); /* Also need to preprocess expressions for function and values RTEs */ foreach(l, parse->rtable) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); if (rte->rtekind == RTE_FUNCTION) rte->funcexpr = preprocess_expression(root, rte->funcexpr, EXPRKIND_RTFUNC); else if (rte->rtekind == RTE_VALUES) rte->values_lists = (List *) preprocess_expression(root, (Node *) rte->values_lists, EXPRKIND_VALUES); } /* * In some cases we may want to transfer a HAVING clause into WHERE. We * cannot do so if the HAVING clause contains aggregates (obviously) or * volatile functions (since a HAVING clause is supposed to be executed * only once per group). Also, it may be that the clause is so expensive * to execute that we're better off doing it only once per group, despite * the loss of selectivity. This is hard to estimate short of doing the * entire planning process twice, so we use a heuristic: clauses * containing subplans are left in HAVING. Otherwise, we move or copy the * HAVING clause into WHERE, in hopes of eliminating tuples before * aggregation instead of after. * * If the query has explicit grouping then we can simply move such a * clause into WHERE; any group that fails the clause will not be in the * output because none of its tuples will reach the grouping or * aggregation stage. Otherwise we must have a degenerate (variable-free) * HAVING clause, which we put in WHERE so that query_planner() can use it * in a gating Result node, but also keep in HAVING to ensure that we * don't emit a bogus aggregated row. (This could be done better, but it * seems not worth optimizing.) * * Note that both havingQual and parse->jointree->quals are in * implicitly-ANDed-list form at this point, even though they are declared * as Node *. */ newHaving = NIL; foreach(l, (List *) parse->havingQual) { Node *havingclause = (Node *) lfirst(l); if (contain_agg_clause(havingclause) || contain_volatile_functions(havingclause) || contain_subplans(havingclause)) { /* keep it in HAVING */ newHaving = lappend(newHaving, havingclause); } else if (parse->groupClause) { /* move it to WHERE */ parse->jointree->quals = (Node *) lappend((List *) parse->jointree->quals, havingclause); } else { /* put a copy in WHERE, keep it in HAVING */ parse->jointree->quals = (Node *) lappend((List *) parse->jointree->quals, copyObject(havingclause)); newHaving = lappend(newHaving, havingclause); } } parse->havingQual = (Node *) newHaving; /* * If we have any outer joins, try to reduce them to plain inner joins. * This step is most easily done after we've done expression * preprocessing. */ if (hasOuterJoins) reduce_outer_joins(root); /* * Do the main planning. If we have an inherited target relation, that * needs special processing, else go straight to grouping_planner. */ if (parse->resultRelation && rt_fetch(parse->resultRelation, parse->rtable)->inh) plan = inheritance_planner(root); else { plan = grouping_planner(root, tuple_fraction); /* If it's not SELECT, we need a ModifyTable node */ if (parse->commandType != CMD_SELECT) { List *returningLists; List *rowMarks; /* * Set up the RETURNING list-of-lists, if needed. */ if (parse->returningList) returningLists = list_make1(parse->returningList); else returningLists = NIL; /* * If there was a FOR UPDATE/SHARE clause, the LockRows node will * have dealt with fetching non-locked marked rows, else we need * to have ModifyTable do that. */ if (parse->rowMarks) rowMarks = NIL; else rowMarks = root->rowMarks; plan = (Plan *) make_modifytable(parse->commandType, parse->canSetTag, list_make1_int(parse->resultRelation), list_make1(plan), returningLists, rowMarks, SS_assign_special_param(root)); } } /* * If any subplans were generated, or if there are any parameters to worry * about, build initPlan list and extParam/allParam sets for plan nodes, * and attach the initPlans to the top plan node. */ if (list_length(glob->subplans) != num_old_subplans || root->glob->nParamExec > 0) SS_finalize_plan(root, plan, true); /* Return internal info if caller wants it */ if (subroot) *subroot = root; return plan; }
进行分析，看这一句的状况：
int num_old_subplans = list_length(glob->subplans);
对于这个 num_old_subplans ，在我的简单查询sql 文执行的时候，都是 0:
Plan * subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root, bool hasRecursion, double tuple_fraction, PlannerInfo **subroot) { int num_old_subplans = list_length(glob->subplans); ... if (list_length(glob->subplans) != num_old_subplans || root->glob->nParamExec > 0) SS_finalize_plan(root, plan, true); ... }
而且，subquery_planner 只被调用了一次，root->query_level 值为1(入口参数为 NULL)。
/* primary planning entry point (may recurse for subqueries) */ top_plan = subquery_planner(glob, parse, NULL, false, tuple_fraction, &root);
下面：
因为入口参数的原因，这个也是false。
root->hasRecursion = hasRecursion; if (hasRecursion) root->wt_param_id = SS_assign_special_param(root); else root->wt_param_id = -1;
下面这段：也是 false。因为我没有使用 WITH
/* * If there is a WITH list, process each WITH query and build an initplan * SubPlan structure for it. */ if (parse->cteList) SS_process_ctes(root);
对于我的简单查询，这一段也是false。
/* * Look for ANY and EXISTS SubLinks in WHERE and JOIN/ON clauses, and try * to transform them into joins. Note that this step does not descend * into subqueries; if we pull up any subqueries below, their SubLinks are * processed just before pulling them up. */ if (parse->hasSubLinks) pull_up_sublinks(root);
再看这句话的功能：
/* * Scan the rangetable for set-returning functions, and inline them if * possible (producing subqueries that might get pulled up next). * Recursion issues here are handled in the same way as for SubLinks. */ inline_set_returning_functions(root);
展开后：
/* * inline_set_returning_functions * Attempt to "inline" set-returning functions in the FROM clause. * * If an RTE_FUNCTION rtable entry invokes a set-returning function that * contains just a simple SELECT, we can convert the rtable entry to an * RTE_SUBQUERY entry exposing the SELECT directly. This is especially * useful if the subquery can then be "pulled up" for further optimization, * but we do it even if not, to reduce executor overhead. * * This has to be done before we have started to do any optimization of * subqueries, else any such steps wouldn't get applied to subqueries * obtained via inlining. However, we do it after pull_up_sublinks * so that we can inline any functions used in SubLink subselects. * * Like most of the planner, this feels free to scribble on its input data * structure. */ void inline_set_returning_functions(PlannerInfo *root) { ListCell *rt; foreach(rt, root->parse->rtable) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt); if (rte->rtekind == RTE_FUNCTION) { Query *funcquery; /* Check safety of expansion, and expand if possible */ funcquery = inline_set_returning_function(root, rte); if (funcquery) { /* Successful expansion, replace the rtable entry */ rte->rtekind = RTE_SUBQUERY; rte->subquery = funcquery; rte->funcexpr = NULL; rte->funccoltypes = NIL; rte->funccoltypmods = NIL; rte->funccolcollations = NIL; } } } }
因为我的简单查询中没有集合运算，所以这里面 inline_set_returning_functions 相当于什么都没作。
下面这一段，对我的简单查询，也可以无视：
/* * Check to see if any subqueries in the jointree can be merged into this * query. */ parse->jointree = (FromExpr *) pull_up_subqueries(root, (Node *) parse->jointree, NULL, NULL);
下面这一段，其实也可以忽略：
/* * If this is a simple UNION ALL query, flatten it into an appendrel. We * do this now because it requires applying pull_up_subqueries to the leaf * queries of the UNION ALL, which weren't touched above because they * weren't referenced by the jointree (they will be after we do this). */ if (parse->setOperations) flatten_simple_union_all(root);
然后，可以看下面的：
对于我们的简单查询，这个 (rte->rtekind == RTE_JOIN) 也是不能成立的。
/* * Detect whether any rangetable entries are RTE_JOIN kind; if not, we can * avoid the expense of doing flatten_join_alias_vars(). Also check for * outer joins --- if none, we can skip reduce_outer_joins(). This must be * done after we have done pull_up_subqueries, of course. */ root->hasJoinRTEs = false; hasOuterJoins = false; foreach(l, parse->rtable) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); if (rte->rtekind == RTE_JOIN) { root->hasJoinRTEs = true; if (IS_OUTER_JOIN(rte->jointype)) { hasOuterJoins = true; /* Can quit scanning once we find an outer join */ break; } } }
接着分析：
/* * Preprocess RowMark information. We need to do this after subquery * pullup (so that all non-inherited RTEs are present) and before * inheritance expansion (so that the info is available for * expand_inherited_tables to examine and modify). */ preprocess_rowmarks(root);
接着看 preprocess_rowmarks，对于我的简单查询SQL文，其实是直接return 了。
/* * preprocess_rowmarks - set up PlanRowMarks if needed */ static void preprocess_rowmarks(PlannerInfo *root) { Query *parse = root->parse; Bitmapset *rels; List *prowmarks; ListCell *l; int i; if (parse->rowMarks) { fprintf(stderr,"parse->rowMarks is true.\n"); /* * We've got trouble if FOR UPDATE/SHARE appears inside grouping, * since grouping renders a reference to individual tuple CTIDs * invalid. This is also checked at parse time, but that's * insufficient because of rule substitution, query pullup, etc. */ CheckSelectLocking(parse); } else { /**
fprintf(stderr,"parse->rowMarks is false.\n"); if (parse->commandType != CMD_UPDATE && parse->commandType != CMD_DELETE) fprintf(stderr,"+_+_+_+_+_+_+_+_+_+_+directly return!\n"); */
/* * We only need rowmarks for UPDATE, DELETE, or FOR UPDATE/SHARE. */ if (parse->commandType != CMD_UPDATE && parse->commandType != CMD_DELETE) return; } ... }
再接着分析：
/* * Expand any rangetable entries that are inheritance sets into "append * relations". This can add entries to the rangetable, but they must be * plain base relations not joins, so it's OK (and marginally more * efficient) to do it after checking for join RTEs. We must do it after * pulling up subqueries, else we'd fail to handle inherited tables in * subqueries. */ expand_inherited_tables(root);
/* * expand_inherited_tables * Expand each rangetable entry that represents an inheritance set * into an "append relation". At the conclusion of this process, * the "inh" flag is set in all and only those RTEs that are append * relation parents. */ void expand_inherited_tables(PlannerInfo *root) { Index nrtes; Index rti; ListCell *rl; /* * expand_inherited_rtentry may add RTEs to parse->rtable; there is no * need to scan them since they can't have inh=true. So just scan as far * as the original end of the rtable list. */ nrtes = list_length(root->parse->rtable); rl = list_head(root->parse->rtable); for (rti = 1; rti <= nrtes; rti++) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(rl); expand_inherited_rtentry(root, rte, rti); rl = lnext(rl); } }
这个 expand_inherited_rtentry 也是直接return了：
static void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) { Query *parse = root->parse; Oid parentOID; PlanRowMark *oldrc; Relation oldrelation; LOCKMODE lockmode; List *inhOIDs; List *appinfos; ListCell *l; /* Does RT entry allow inheritance? */ if (!rte->inh) return; /* Ignore any already-expanded UNION ALL nodes */ if (rte->rtekind != RTE_RELATION) { Assert(rte->rtekind == RTE_SUBQUERY); return; } /* Fast path for common case of childless table */ parentOID = rte->relid; if (!has_subclass(parentOID)) { /* Clear flag before returning */ rte->inh = false; return; }
...
}
