Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit641054a

Browse files
committed
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 anoversight considering there are common usages that require the latterapproach. Improve that. (I had initially thought it might be worthfolding this into cost_qual_eval, but after investigation concluded thatthat wouldn't be very helpful, so just do it separately.) Per complaintfrom David Johnston.Back-patch to 9.2, but not further, for fear of destabilizing plan choicesin existing releases.
1 parentc6c6f21 commit641054a

File tree

7 files changed

+120
-48
lines changed

7 files changed

+120
-48
lines changed

‎src/backend/optimizer/path/costsize.c‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2958,6 +2958,13 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
29582958
* to expect that the current ordering of the clauses is the one that's
29592959
* going to end up being used.The above per-RestrictInfo caching would
29602960
* not mix well with trying to re-order clauses anyway.
2961+
*
2962+
* Another issue that is entirely ignored here is that if a set-returning
2963+
* function is below top level in the tree, the functions/operators above
2964+
* it will need to be evaluated multiple times. In practical use, such
2965+
* cases arise so seldom as to not be worth the added complexity needed;
2966+
* moreover, since our rowcount estimates for functions tend to be pretty
2967+
* phony, the results would also be pretty phony.
29612968
*/
29622969
if (IsA(node,FuncExpr))
29632970
{
@@ -3742,7 +3749,7 @@ set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel)
37423749
Assert(rte->rtekind==RTE_FUNCTION);
37433750

37443751
/* Estimate number of rows the function itself will return */
3745-
rel->tuples=clamp_row_est(expression_returns_set_rows(rte->funcexpr));
3752+
rel->tuples=expression_returns_set_rows(rte->funcexpr);
37463753

37473754
/* Now estimate number of output rows, etc */
37483755
set_baserel_size_estimates(root,rel);

‎src/backend/optimizer/plan/createplan.c‎

Lines changed: 10 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include"optimizer/placeholder.h"
3131
#include"optimizer/plancat.h"
3232
#include"optimizer/planmain.h"
33+
#include"optimizer/planner.h"
3334
#include"optimizer/predtest.h"
3435
#include"optimizer/restrictinfo.h"
3536
#include"optimizer/subselect.h"
@@ -4126,8 +4127,8 @@ make_agg(PlannerInfo *root, List *tlist, List *qual,
41264127
* anything for Aggref nodes; this is okay since they are really
41274128
* comparable to Vars.
41284129
*
4129-
* See notes ingrouping_planner about why only make_agg, make_windowagg
4130-
* and make_group worry about tlist eval cost.
4130+
* See notes inadd_tlist_costs_to_plan about why only make_agg,
4131+
*make_windowaggand make_group worry about tlist eval cost.
41314132
*/
41324133
if (qual)
41334134
{
@@ -4136,10 +4137,7 @@ make_agg(PlannerInfo *root, List *tlist, List *qual,
41364137
plan->total_cost+=qual_cost.startup;
41374138
plan->total_cost+=qual_cost.per_tuple*plan->plan_rows;
41384139
}
4139-
cost_qual_eval(&qual_cost,tlist,root);
4140-
plan->startup_cost+=qual_cost.startup;
4141-
plan->total_cost+=qual_cost.startup;
4142-
plan->total_cost+=qual_cost.per_tuple*plan->plan_rows;
4140+
add_tlist_costs_to_plan(root,plan,tlist);
41434141

41444142
plan->qual=qual;
41454143
plan->targetlist=tlist;
@@ -4160,7 +4158,6 @@ make_windowagg(PlannerInfo *root, List *tlist,
41604158
WindowAgg*node=makeNode(WindowAgg);
41614159
Plan*plan=&node->plan;
41624160
Pathwindowagg_path;/* dummy for result of cost_windowagg */
4163-
QualCostqual_cost;
41644161

41654162
node->winref=winref;
41664163
node->partNumCols=partNumCols;
@@ -4185,13 +4182,10 @@ make_windowagg(PlannerInfo *root, List *tlist,
41854182
/*
41864183
* We also need to account for the cost of evaluation of the tlist.
41874184
*
4188-
* See notes ingrouping_planner about why only make_agg, make_windowagg
4189-
* and make_group worry about tlist eval cost.
4185+
* See notes inadd_tlist_costs_to_plan about why only make_agg,
4186+
*make_windowaggand make_group worry about tlist eval cost.
41904187
*/
4191-
cost_qual_eval(&qual_cost,tlist,root);
4192-
plan->startup_cost+=qual_cost.startup;
4193-
plan->total_cost+=qual_cost.startup;
4194-
plan->total_cost+=qual_cost.per_tuple*plan->plan_rows;
4188+
add_tlist_costs_to_plan(root,plan,tlist);
41954189

41964190
plan->targetlist=tlist;
41974191
plan->lefttree=lefttree;
@@ -4242,8 +4236,8 @@ make_group(PlannerInfo *root,
42424236
* lower plan level and will only be copied by the Group node. Worth
42434237
* fixing?
42444238
*
4245-
* See notes ingrouping_planner about why only make_agg, make_windowagg
4246-
* and make_group worry about tlist eval cost.
4239+
* See notes inadd_tlist_costs_to_plan about why only make_agg,
4240+
*make_windowaggand make_group worry about tlist eval cost.
42474241
*/
42484242
if (qual)
42494243
{
@@ -4252,10 +4246,7 @@ make_group(PlannerInfo *root,
42524246
plan->total_cost+=qual_cost.startup;
42534247
plan->total_cost+=qual_cost.per_tuple*plan->plan_rows;
42544248
}
4255-
cost_qual_eval(&qual_cost,tlist,root);
4256-
plan->startup_cost+=qual_cost.startup;
4257-
plan->total_cost+=qual_cost.startup;
4258-
plan->total_cost+=qual_cost.per_tuple*plan->plan_rows;
4249+
add_tlist_costs_to_plan(root,plan,tlist);
42594250

42604251
plan->qual=qual;
42614252
plan->targetlist=tlist;

‎src/backend/optimizer/plan/planagg.c‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@
3636
#include"optimizer/cost.h"
3737
#include"optimizer/paths.h"
3838
#include"optimizer/planmain.h"
39+
#include"optimizer/planner.h"
3940
#include"optimizer/subselect.h"
4041
#include"parser/parsetree.h"
4142
#include"parser/parse_clause.h"
@@ -205,7 +206,6 @@ optimize_minmax_aggregates(PlannerInfo *root, List *tlist,
205206
Pathagg_p;
206207
Plan*plan;
207208
Node*hqual;
208-
QualCosttlist_cost;
209209
ListCell*lc;
210210

211211
/* Nothing to do if preprocess_minmax_aggs rejected the query */
@@ -272,9 +272,7 @@ optimize_minmax_aggregates(PlannerInfo *root, List *tlist,
272272
plan= (Plan*)make_result(root,tlist,hqual,NULL);
273273

274274
/* Account for evaluation cost of the tlist (make_result did the rest) */
275-
cost_qual_eval(&tlist_cost,tlist,root);
276-
plan->startup_cost+=tlist_cost.startup;
277-
plan->total_cost+=tlist_cost.startup+tlist_cost.per_tuple;
275+
add_tlist_costs_to_plan(root,plan,tlist);
278276

279277
returnplan;
280278
}

‎src/backend/optimizer/plan/planner.c‎

Lines changed: 57 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1045,7 +1045,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
10451045
doublesub_limit_tuples;
10461046
AttrNumber*groupColIdx=NULL;
10471047
boolneed_tlist_eval= true;
1048-
QualCosttlist_cost;
10491048
Path*cheapest_path;
10501049
Path*sorted_path;
10511050
Path*best_path;
@@ -1355,27 +1354,9 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
13551354

13561355
/*
13571356
* Also, account for the cost of evaluation of the sub_tlist.
1358-
*
1359-
* Up to now, we have only been dealing with "flat" tlists,
1360-
* containing just Vars. So their evaluation cost is zero
1361-
* according to the model used by cost_qual_eval() (or if you
1362-
* prefer, the cost is factored into cpu_tuple_cost). Thus we
1363-
* can avoid accounting for tlist cost throughout
1364-
* query_planner() and subroutines. But now we've inserted a
1365-
* tlist that might contain actual operators, sub-selects, etc
1366-
* --- so we'd better account for its cost.
1367-
*
1368-
* Below this point, any tlist eval cost for added-on nodes
1369-
* should be accounted for as we create those nodes.
1370-
* Presently, of the node types we can add on, only Agg,
1371-
* WindowAgg, and Group project new tlists (the rest just copy
1372-
* their input tuples) --- so make_agg(), make_windowagg() and
1373-
* make_group() are responsible for computing the added cost.
1357+
* See comments for add_tlist_costs_to_plan() for more info.
13741358
*/
1375-
cost_qual_eval(&tlist_cost,sub_tlist,root);
1376-
result_plan->startup_cost+=tlist_cost.startup;
1377-
result_plan->total_cost+=tlist_cost.startup+
1378-
tlist_cost.per_tuple*result_plan->plan_rows;
1359+
add_tlist_costs_to_plan(root,result_plan,sub_tlist);
13791360
}
13801361
else
13811362
{
@@ -1815,6 +1796,61 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
18151796
returnresult_plan;
18161797
}
18171798

1799+
/*
1800+
* add_tlist_costs_to_plan
1801+
*
1802+
* Estimate the execution costs associated with evaluating the targetlist
1803+
* expressions, and add them to the cost estimates for the Plan node.
1804+
*
1805+
* If the tlist contains set-returning functions, also inflate the Plan's cost
1806+
* and plan_rows estimates accordingly. (Hence, this must be called *after*
1807+
* any logic that uses plan_rows to, eg, estimate qual evaluation costs.)
1808+
*
1809+
* Note: during initial stages of planning, we mostly consider plan nodes with
1810+
* "flat" tlists, containing just Vars. So their evaluation cost is zero
1811+
* according to the model used by cost_qual_eval() (or if you prefer, the cost
1812+
* is factored into cpu_tuple_cost). Thus we can avoid accounting for tlist
1813+
* cost throughout query_planner() and subroutines. But once we apply a
1814+
* tlist that might contain actual operators, sub-selects, etc, we'd better
1815+
* account for its cost. Any set-returning functions in the tlist must also
1816+
* affect the estimated rowcount.
1817+
*
1818+
* Once grouping_planner() has applied a general tlist to the topmost
1819+
* scan/join plan node, any tlist eval cost for added-on nodes should be
1820+
* accounted for as we create those nodes. Presently, of the node types we
1821+
* can add on later, only Agg, WindowAgg, and Group project new tlists (the
1822+
* rest just copy their input tuples) --- so make_agg(), make_windowagg() and
1823+
* make_group() are responsible for calling this function to account for their
1824+
* tlist costs.
1825+
*/
1826+
void
1827+
add_tlist_costs_to_plan(PlannerInfo*root,Plan*plan,List*tlist)
1828+
{
1829+
QualCosttlist_cost;
1830+
doubletlist_rows;
1831+
1832+
cost_qual_eval(&tlist_cost,tlist,root);
1833+
plan->startup_cost+=tlist_cost.startup;
1834+
plan->total_cost+=tlist_cost.startup+
1835+
tlist_cost.per_tuple*plan->plan_rows;
1836+
1837+
tlist_rows=tlist_returns_set_rows(tlist);
1838+
if (tlist_rows>1)
1839+
{
1840+
/*
1841+
* We assume that execution costs of the tlist proper were all
1842+
* accounted for by cost_qual_eval. However, it still seems
1843+
* appropriate to charge something more for the executor's general
1844+
* costs of processing the added tuples. The cost is probably less
1845+
* than cpu_tuple_cost, though, so we arbitrarily use half of that.
1846+
*/
1847+
plan->total_cost+=plan->plan_rows* (tlist_rows-1)*
1848+
cpu_tuple_cost /2;
1849+
1850+
plan->plan_rows *=tlist_rows;
1851+
}
1852+
}
1853+
18181854
/*
18191855
* Detect whether a plan node is a "dummy" plan created when a relation
18201856
* is deemed not to need scanning due to constraint exclusion.

‎src/backend/optimizer/util/clauses.c‎

Lines changed: 39 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -661,10 +661,12 @@ find_window_functions_walker(Node *node, WindowFuncLists *lists)
661661

662662
/*
663663
* expression_returns_set_rows
664-
* Estimate the number of rows in a set result.
664+
* Estimate the number of rows returned by a set-returning expression.
665+
* The result is 1 if there are no set-returning functions.
665666
*
666667
* We use the product of the rowcount estimates of all the functions in
667-
* the given tree.The result is 1 if there are no set-returning functions.
668+
* the given tree (this corresponds to the behavior of ExecMakeFunctionResult
669+
* for nested set-returning functions).
668670
*
669671
* Note: keep this in sync with expression_returns_set() in nodes/nodeFuncs.c.
670672
*/
@@ -674,7 +676,7 @@ expression_returns_set_rows(Node *clause)
674676
doubleresult=1;
675677

676678
(void)expression_returns_set_rows_walker(clause,&result);
677-
returnresult;
679+
returnclamp_row_est(result);
678680
}
679681

680682
staticbool
@@ -736,6 +738,40 @@ expression_returns_set_rows_walker(Node *node, double *count)
736738
(void*)count);
737739
}
738740

741+
/*
742+
* tlist_returns_set_rows
743+
* Estimate the number of rows returned by a set-returning targetlist.
744+
* The result is 1 if there are no set-returning functions.
745+
*
746+
* Here, the result is the largest rowcount estimate of any of the tlist's
747+
* expressions, not the product as you would get from naively applying
748+
* expression_returns_set_rows() to the whole tlist. The behavior actually
749+
* implemented by ExecTargetList produces a number of rows equal to the least
750+
* common multiple of the expression rowcounts, so that the product would be
751+
* a worst-case estimate that is typically not realistic. Taking the max as
752+
* we do here is a best-case estimate that might not be realistic either,
753+
* but it's probably closer for typical usages. We don't try to compute the
754+
* actual LCM because we're working with very approximate estimates, so their
755+
* LCM would be unduly noisy.
756+
*/
757+
double
758+
tlist_returns_set_rows(List*tlist)
759+
{
760+
doubleresult=1;
761+
ListCell*lc;
762+
763+
foreach(lc,tlist)
764+
{
765+
TargetEntry*tle= (TargetEntry*)lfirst(lc);
766+
doublecolresult;
767+
768+
colresult=expression_returns_set_rows((Node*)tle->expr);
769+
if (result<colresult)
770+
result=colresult;
771+
}
772+
returnresult;
773+
}
774+
739775

740776
/*****************************************************************************
741777
*Subplan clause manipulation

‎src/include/optimizer/clauses.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ extern bool contain_window_function(Node *clause);
5555
externWindowFuncLists*find_window_functions(Node*clause,IndexmaxWinRef);
5656

5757
externdoubleexpression_returns_set_rows(Node*clause);
58+
externdoubletlist_returns_set_rows(List*tlist);
5859

5960
externboolcontain_subplans(Node*clause);
6061

‎src/include/optimizer/planner.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,9 @@ extern Plan *subquery_planner(PlannerGlobal *glob, Query *parse,
3535
boolhasRecursion,doubletuple_fraction,
3636
PlannerInfo**subroot);
3737

38+
externvoidadd_tlist_costs_to_plan(PlannerInfo*root,Plan*plan,
39+
List*tlist);
40+
3841
externboolis_dummy_plan(Plan*plan);
3942

4043
externExpr*expression_planner(Expr*expr);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp