From 641054ad7882a701cf3ac4c5666f6ee59103d3a7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 21 Jul 2012 17:45:15 -0400 Subject: [PATCH] Account for SRFs in targetlists in planner rowcount estimates. We made use of the ROWS estimate for set-returning functions used in FROM, but not for those used in SELECT targetlists; which is a bit of an oversight considering there are common usages that require the latter approach. Improve that. (I had initially thought it might be worth folding this into cost_qual_eval, but after investigation concluded that that wouldn't be very helpful, so just do it separately.) Per complaint from David Johnston. Back-patch to 9.2, but not further, for fear of destabilizing plan choices in existing releases. --- src/backend/optimizer/path/costsize.c | 9 ++- src/backend/optimizer/plan/createplan.c | 29 ++++----- src/backend/optimizer/plan/planagg.c | 6 +- src/backend/optimizer/plan/planner.c | 78 ++++++++++++++++++------- src/backend/optimizer/util/clauses.c | 42 ++++++++++++- src/include/optimizer/clauses.h | 1 + src/include/optimizer/planner.h | 3 + 7 files changed, 120 insertions(+), 48 deletions(-) diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 480c1b7425..875c611ab5 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -2958,6 +2958,13 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context) * to expect that the current ordering of the clauses is the one that's * going to end up being used. The above per-RestrictInfo caching would * not mix well with trying to re-order clauses anyway. + * + * Another issue that is entirely ignored here is that if a set-returning + * function is below top level in the tree, the functions/operators above + * it will need to be evaluated multiple times. In practical use, such + * cases arise so seldom as to not be worth the added complexity needed; + * moreover, since our rowcount estimates for functions tend to be pretty + * phony, the results would also be pretty phony. */ if (IsA(node, FuncExpr)) { @@ -3742,7 +3749,7 @@ set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel) Assert(rte->rtekind == RTE_FUNCTION); /* Estimate number of rows the function itself will return */ - rel->tuples = clamp_row_est(expression_returns_set_rows(rte->funcexpr)); + rel->tuples = expression_returns_set_rows(rte->funcexpr); /* Now estimate number of output rows, etc */ set_baserel_size_estimates(root, rel); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 65ad1694b0..414406bb8a 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -30,6 +30,7 @@ #include "optimizer/placeholder.h" #include "optimizer/plancat.h" #include "optimizer/planmain.h" +#include "optimizer/planner.h" #include "optimizer/predtest.h" #include "optimizer/restrictinfo.h" #include "optimizer/subselect.h" @@ -4126,8 +4127,8 @@ make_agg(PlannerInfo *root, List *tlist, List *qual, * anything for Aggref nodes; this is okay since they are really * comparable to Vars. * - * See notes in grouping_planner about why only make_agg, make_windowagg - * and make_group worry about tlist eval cost. + * See notes in add_tlist_costs_to_plan about why only make_agg, + * make_windowagg and make_group worry about tlist eval cost. */ if (qual) { @@ -4136,10 +4137,7 @@ make_agg(PlannerInfo *root, List *tlist, List *qual, plan->total_cost += qual_cost.startup; plan->total_cost += qual_cost.per_tuple * plan->plan_rows; } - cost_qual_eval(&qual_cost, tlist, root); - plan->startup_cost += qual_cost.startup; - plan->total_cost += qual_cost.startup; - plan->total_cost += qual_cost.per_tuple * plan->plan_rows; + add_tlist_costs_to_plan(root, plan, tlist); plan->qual = qual; plan->targetlist = tlist; @@ -4160,7 +4158,6 @@ make_windowagg(PlannerInfo *root, List *tlist, WindowAgg *node = makeNode(WindowAgg); Plan *plan = &node->plan; Path windowagg_path; /* dummy for result of cost_windowagg */ - QualCost qual_cost; node->winref = winref; node->partNumCols = partNumCols; @@ -4185,13 +4182,10 @@ make_windowagg(PlannerInfo *root, List *tlist, /* * We also need to account for the cost of evaluation of the tlist. * - * See notes in grouping_planner about why only make_agg, make_windowagg - * and make_group worry about tlist eval cost. + * See notes in add_tlist_costs_to_plan about why only make_agg, + * make_windowagg and make_group worry about tlist eval cost. */ - cost_qual_eval(&qual_cost, tlist, root); - plan->startup_cost += qual_cost.startup; - plan->total_cost += qual_cost.startup; - plan->total_cost += qual_cost.per_tuple * plan->plan_rows; + add_tlist_costs_to_plan(root, plan, tlist); plan->targetlist = tlist; plan->lefttree = lefttree; @@ -4242,8 +4236,8 @@ make_group(PlannerInfo *root, * lower plan level and will only be copied by the Group node. Worth * fixing? * - * See notes in grouping_planner about why only make_agg, make_windowagg - * and make_group worry about tlist eval cost. + * See notes in add_tlist_costs_to_plan about why only make_agg, + * make_windowagg and make_group worry about tlist eval cost. */ if (qual) { @@ -4252,10 +4246,7 @@ make_group(PlannerInfo *root, plan->total_cost += qual_cost.startup; plan->total_cost += qual_cost.per_tuple * plan->plan_rows; } - cost_qual_eval(&qual_cost, tlist, root); - plan->startup_cost += qual_cost.startup; - plan->total_cost += qual_cost.startup; - plan->total_cost += qual_cost.per_tuple * plan->plan_rows; + add_tlist_costs_to_plan(root, plan, tlist); plan->qual = qual; plan->targetlist = tlist; diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index be52d16ff0..0bbca071fb 100644 --- a/src/backend/optimizer/plan/planagg.c +++ b/src/backend/optimizer/plan/planagg.c @@ -36,6 +36,7 @@ #include "optimizer/cost.h" #include "optimizer/paths.h" #include "optimizer/planmain.h" +#include "optimizer/planner.h" #include "optimizer/subselect.h" #include "parser/parsetree.h" #include "parser/parse_clause.h" @@ -205,7 +206,6 @@ optimize_minmax_aggregates(PlannerInfo *root, List *tlist, Path agg_p; Plan *plan; Node *hqual; - QualCost tlist_cost; ListCell *lc; /* Nothing to do if preprocess_minmax_aggs rejected the query */ @@ -272,9 +272,7 @@ optimize_minmax_aggregates(PlannerInfo *root, List *tlist, plan = (Plan *) make_result(root, tlist, hqual, NULL); /* Account for evaluation cost of the tlist (make_result did the rest) */ - cost_qual_eval(&tlist_cost, tlist, root); - plan->startup_cost += tlist_cost.startup; - plan->total_cost += tlist_cost.startup + tlist_cost.per_tuple; + add_tlist_costs_to_plan(root, plan, tlist); return plan; } diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index df76341c0a..31fe557072 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1045,7 +1045,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) double sub_limit_tuples; AttrNumber *groupColIdx = NULL; bool need_tlist_eval = true; - QualCost tlist_cost; Path *cheapest_path; Path *sorted_path; Path *best_path; @@ -1355,27 +1354,9 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) /* * Also, account for the cost of evaluation of the sub_tlist. - * - * Up to now, we have only been dealing with "flat" tlists, - * containing just Vars. So their evaluation cost is zero - * according to the model used by cost_qual_eval() (or if you - * prefer, the cost is factored into cpu_tuple_cost). Thus we - * can avoid accounting for tlist cost throughout - * query_planner() and subroutines. But now we've inserted a - * tlist that might contain actual operators, sub-selects, etc - * --- so we'd better account for its cost. - * - * Below this point, any tlist eval cost for added-on nodes - * should be accounted for as we create those nodes. - * Presently, of the node types we can add on, only Agg, - * WindowAgg, and Group project new tlists (the rest just copy - * their input tuples) --- so make_agg(), make_windowagg() and - * make_group() are responsible for computing the added cost. + * See comments for add_tlist_costs_to_plan() for more info. */ - cost_qual_eval(&tlist_cost, sub_tlist, root); - result_plan->startup_cost += tlist_cost.startup; - result_plan->total_cost += tlist_cost.startup + - tlist_cost.per_tuple * result_plan->plan_rows; + add_tlist_costs_to_plan(root, result_plan, sub_tlist); } else { @@ -1815,6 +1796,61 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) return result_plan; } +/* + * add_tlist_costs_to_plan + * + * Estimate the execution costs associated with evaluating the targetlist + * expressions, and add them to the cost estimates for the Plan node. + * + * If the tlist contains set-returning functions, also inflate the Plan's cost + * and plan_rows estimates accordingly. (Hence, this must be called *after* + * any logic that uses plan_rows to, eg, estimate qual evaluation costs.) + * + * Note: during initial stages of planning, we mostly consider plan nodes with + * "flat" tlists, containing just Vars. So their evaluation cost is zero + * according to the model used by cost_qual_eval() (or if you prefer, the cost + * is factored into cpu_tuple_cost). Thus we can avoid accounting for tlist + * cost throughout query_planner() and subroutines. But once we apply a + * tlist that might contain actual operators, sub-selects, etc, we'd better + * account for its cost. Any set-returning functions in the tlist must also + * affect the estimated rowcount. + * + * Once grouping_planner() has applied a general tlist to the topmost + * scan/join plan node, any tlist eval cost for added-on nodes should be + * accounted for as we create those nodes. Presently, of the node types we + * can add on later, only Agg, WindowAgg, and Group project new tlists (the + * rest just copy their input tuples) --- so make_agg(), make_windowagg() and + * make_group() are responsible for calling this function to account for their + * tlist costs. + */ +void +add_tlist_costs_to_plan(PlannerInfo *root, Plan *plan, List *tlist) +{ + QualCost tlist_cost; + double tlist_rows; + + cost_qual_eval(&tlist_cost, tlist, root); + plan->startup_cost += tlist_cost.startup; + plan->total_cost += tlist_cost.startup + + tlist_cost.per_tuple * plan->plan_rows; + + tlist_rows = tlist_returns_set_rows(tlist); + if (tlist_rows > 1) + { + /* + * We assume that execution costs of the tlist proper were all + * accounted for by cost_qual_eval. However, it still seems + * appropriate to charge something more for the executor's general + * costs of processing the added tuples. The cost is probably less + * than cpu_tuple_cost, though, so we arbitrarily use half of that. + */ + plan->total_cost += plan->plan_rows * (tlist_rows - 1) * + cpu_tuple_cost / 2; + + plan->plan_rows *= tlist_rows; + } +} + /* * Detect whether a plan node is a "dummy" plan created when a relation * is deemed not to need scanning due to constraint exclusion. diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 73f5e11abe..c2d36ca38d 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -661,10 +661,12 @@ find_window_functions_walker(Node *node, WindowFuncLists *lists) /* * expression_returns_set_rows - * Estimate the number of rows in a set result. + * Estimate the number of rows returned by a set-returning expression. + * The result is 1 if there are no set-returning functions. * * We use the product of the rowcount estimates of all the functions in - * the given tree. The result is 1 if there are no set-returning functions. + * the given tree (this corresponds to the behavior of ExecMakeFunctionResult + * for nested set-returning functions). * * Note: keep this in sync with expression_returns_set() in nodes/nodeFuncs.c. */ @@ -674,7 +676,7 @@ expression_returns_set_rows(Node *clause) double result = 1; (void) expression_returns_set_rows_walker(clause, &result); - return result; + return clamp_row_est(result); } static bool @@ -736,6 +738,40 @@ expression_returns_set_rows_walker(Node *node, double *count) (void *) count); } +/* + * tlist_returns_set_rows + * Estimate the number of rows returned by a set-returning targetlist. + * The result is 1 if there are no set-returning functions. + * + * Here, the result is the largest rowcount estimate of any of the tlist's + * expressions, not the product as you would get from naively applying + * expression_returns_set_rows() to the whole tlist. The behavior actually + * implemented by ExecTargetList produces a number of rows equal to the least + * common multiple of the expression rowcounts, so that the product would be + * a worst-case estimate that is typically not realistic. Taking the max as + * we do here is a best-case estimate that might not be realistic either, + * but it's probably closer for typical usages. We don't try to compute the + * actual LCM because we're working with very approximate estimates, so their + * LCM would be unduly noisy. + */ +double +tlist_returns_set_rows(List *tlist) +{ + double result = 1; + ListCell *lc; + + foreach(lc, tlist) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + double colresult; + + colresult = expression_returns_set_rows((Node *) tle->expr); + if (result < colresult) + result = colresult; + } + return result; +} + /***************************************************************************** * Subplan clause manipulation diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index 1713b70400..ac75bd4d6e 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -55,6 +55,7 @@ extern bool contain_window_function(Node *clause); extern WindowFuncLists *find_window_functions(Node *clause, Index maxWinRef); extern double expression_returns_set_rows(Node *clause); +extern double tlist_returns_set_rows(List *tlist); extern bool contain_subplans(Node *clause); diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h index 1f0993b519..af0c817a43 100644 --- a/src/include/optimizer/planner.h +++ b/src/include/optimizer/planner.h @@ -35,6 +35,9 @@ extern Plan *subquery_planner(PlannerGlobal *glob, Query *parse, bool hasRecursion, double tuple_fraction, PlannerInfo **subroot); +extern void add_tlist_costs_to_plan(PlannerInfo *root, Plan *plan, + List *tlist); + extern bool is_dummy_plan(Plan *plan); extern Expr *expression_planner(Expr *expr); -- 2.40.0