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

Commit7b6c075

Browse files
committed
Teach planner to account for HAVING quals in aggregation plan nodes.
For some reason, we have never accounted for either the evaluation costor the selectivity of filter conditions attached to Agg and Group nodes(which, in practice, are always conditions from a HAVING clause).Applying our regular selectivity logic to post-grouping conditions is abit bogus, but it's surely better than taking the selectivity as 1.0.Perhaps someday the extended-statistics mechanism can be taught to providestatistics that would help us in getting non-default estimates here.Per a gripe from Benjamin Coutu. This is surely a bug fix, but I'mhesitant to back-patch because of the prospect of destabilizing existingplan choices. Given that it took us this long to notice the bug, it'sprobably not hurting too many people in the field.Discussion:https://postgr.es/m/20968.1509486337@sss.pgh.pa.us
1 parentc6764eb commit7b6c075

File tree

4 files changed

+74
-1
lines changed

4 files changed

+74
-1
lines changed

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

Lines changed: 45 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1874,6 +1874,7 @@ void
18741874
cost_agg(Path*path,PlannerInfo*root,
18751875
AggStrategyaggstrategy,constAggClauseCosts*aggcosts,
18761876
intnumGroupCols,doublenumGroups,
1877+
List*quals,
18771878
Costinput_startup_cost,Costinput_total_cost,
18781879
doubleinput_tuples)
18791880
{
@@ -1955,6 +1956,26 @@ cost_agg(Path *path, PlannerInfo *root,
19551956
output_tuples=numGroups;
19561957
}
19571958

1959+
/*
1960+
* If there are quals (HAVING quals), account for their cost and
1961+
* selectivity.
1962+
*/
1963+
if (quals)
1964+
{
1965+
QualCostqual_cost;
1966+
1967+
cost_qual_eval(&qual_cost,quals,root);
1968+
startup_cost+=qual_cost.startup;
1969+
total_cost+=qual_cost.startup+output_tuples*qual_cost.per_tuple;
1970+
1971+
output_tuples=clamp_row_est(output_tuples*
1972+
clauselist_selectivity(root,
1973+
quals,
1974+
0,
1975+
JOIN_INNER,
1976+
NULL));
1977+
}
1978+
19581979
path->rows=output_tuples;
19591980
path->startup_cost=startup_cost;
19601981
path->total_cost=total_cost;
@@ -2040,12 +2061,15 @@ cost_windowagg(Path *path, PlannerInfo *root,
20402061
void
20412062
cost_group(Path*path,PlannerInfo*root,
20422063
intnumGroupCols,doublenumGroups,
2064+
List*quals,
20432065
Costinput_startup_cost,Costinput_total_cost,
20442066
doubleinput_tuples)
20452067
{
2068+
doubleoutput_tuples;
20462069
Coststartup_cost;
20472070
Costtotal_cost;
20482071

2072+
output_tuples=numGroups;
20492073
startup_cost=input_startup_cost;
20502074
total_cost=input_total_cost;
20512075

@@ -2055,7 +2079,27 @@ cost_group(Path *path, PlannerInfo *root,
20552079
*/
20562080
total_cost+=cpu_operator_cost*input_tuples*numGroupCols;
20572081

2058-
path->rows=numGroups;
2082+
/*
2083+
* If there are quals (HAVING quals), account for their cost and
2084+
* selectivity.
2085+
*/
2086+
if (quals)
2087+
{
2088+
QualCostqual_cost;
2089+
2090+
cost_qual_eval(&qual_cost,quals,root);
2091+
startup_cost+=qual_cost.startup;
2092+
total_cost+=qual_cost.startup+output_tuples*qual_cost.per_tuple;
2093+
2094+
output_tuples=clamp_row_est(output_tuples*
2095+
clauselist_selectivity(root,
2096+
quals,
2097+
0,
2098+
JOIN_INNER,
2099+
NULL));
2100+
}
2101+
2102+
path->rows=output_tuples;
20592103
path->startup_cost=startup_cost;
20602104
path->total_cost=total_cost;
20612105
}

‎src/backend/optimizer/prep/prepunion.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -977,6 +977,7 @@ choose_hashed_setop(PlannerInfo *root, List *groupClauses,
977977
*/
978978
cost_agg(&hashed_p,root,AGG_HASHED,NULL,
979979
numGroupCols,dNumGroups,
980+
NIL,
980981
input_path->startup_cost,input_path->total_cost,
981982
input_path->rows);
982983

@@ -991,6 +992,7 @@ choose_hashed_setop(PlannerInfo *root, List *groupClauses,
991992
input_path->rows,input_path->pathtarget->width,
992993
0.0,work_mem,-1.0);
993994
cost_group(&sorted_p,root,numGroupCols,dNumGroups,
995+
NIL,
994996
sorted_p.startup_cost,sorted_p.total_cost,
995997
input_path->rows);
996998

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1374,6 +1374,11 @@ create_result_path(PlannerInfo *root, RelOptInfo *rel,
13741374
pathnode->path.startup_cost=target->cost.startup;
13751375
pathnode->path.total_cost=target->cost.startup+
13761376
cpu_tuple_cost+target->cost.per_tuple;
1377+
1378+
/*
1379+
* Add cost of qual, if any --- but we ignore its selectivity, since our
1380+
* rowcount estimate should be 1 no matter what the qual is.
1381+
*/
13771382
if (resconstantqual)
13781383
{
13791384
QualCostqual_cost;
@@ -1596,6 +1601,7 @@ create_unique_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
15961601
cost_agg(&agg_path,root,
15971602
AGG_HASHED,NULL,
15981603
numCols,pathnode->path.rows,
1604+
NIL,
15991605
subpath->startup_cost,
16001606
subpath->total_cost,
16011607
rel->rows);
@@ -2592,6 +2598,7 @@ create_group_path(PlannerInfo *root,
25922598
cost_group(&pathnode->path,root,
25932599
list_length(groupClause),
25942600
numGroups,
2601+
qual,
25952602
subpath->startup_cost,subpath->total_cost,
25962603
subpath->rows);
25972604

@@ -2709,6 +2716,7 @@ create_agg_path(PlannerInfo *root,
27092716
cost_agg(&pathnode->path,root,
27102717
aggstrategy,aggcosts,
27112718
list_length(groupClause),numGroups,
2719+
qual,
27122720
subpath->startup_cost,subpath->total_cost,
27132721
subpath->rows);
27142722

@@ -2817,6 +2825,7 @@ create_groupingsets_path(PlannerInfo *root,
28172825
agg_costs,
28182826
numGroupCols,
28192827
rollup->numGroups,
2828+
having_qual,
28202829
subpath->startup_cost,
28212830
subpath->total_cost,
28222831
subpath->rows);
@@ -2840,6 +2849,7 @@ create_groupingsets_path(PlannerInfo *root,
28402849
agg_costs,
28412850
numGroupCols,
28422851
rollup->numGroups,
2852+
having_qual,
28432853
0.0,0.0,
28442854
subpath->rows);
28452855
if (!rollup->is_hashed)
@@ -2863,6 +2873,7 @@ create_groupingsets_path(PlannerInfo *root,
28632873
agg_costs,
28642874
numGroupCols,
28652875
rollup->numGroups,
2876+
having_qual,
28662877
sort_path.startup_cost,
28672878
sort_path.total_cost,
28682879
sort_path.rows);
@@ -2932,6 +2943,19 @@ create_minmaxagg_path(PlannerInfo *root,
29322943
pathnode->path.total_cost=initplan_cost+target->cost.startup+
29332944
target->cost.per_tuple+cpu_tuple_cost;
29342945

2946+
/*
2947+
* Add cost of qual, if any --- but we ignore its selectivity, since our
2948+
* rowcount estimate should be 1 no matter what the qual is.
2949+
*/
2950+
if (quals)
2951+
{
2952+
QualCostqual_cost;
2953+
2954+
cost_qual_eval(&qual_cost,quals,root);
2955+
pathnode->path.startup_cost+=qual_cost.startup;
2956+
pathnode->path.total_cost+=qual_cost.startup+qual_cost.per_tuple;
2957+
}
2958+
29352959
returnpathnode;
29362960
}
29372961

@@ -3781,6 +3805,7 @@ reparameterize_pathlist_by_child(PlannerInfo *root,
37813805
{
37823806
Path*path=reparameterize_path_by_child(root,lfirst(lc),
37833807
child_rel);
3808+
37843809
if (path==NULL)
37853810
{
37863811
list_free(result);

‎src/include/optimizer/cost.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -116,6 +116,7 @@ extern void cost_material(Path *path,
116116
externvoidcost_agg(Path*path,PlannerInfo*root,
117117
AggStrategyaggstrategy,constAggClauseCosts*aggcosts,
118118
intnumGroupCols,doublenumGroups,
119+
List*quals,
119120
Costinput_startup_cost,Costinput_total_cost,
120121
doubleinput_tuples);
121122
externvoidcost_windowagg(Path*path,PlannerInfo*root,
@@ -124,6 +125,7 @@ extern void cost_windowagg(Path *path, PlannerInfo *root,
124125
doubleinput_tuples);
125126
externvoidcost_group(Path*path,PlannerInfo*root,
126127
intnumGroupCols,doublenumGroups,
128+
List*quals,
127129
Costinput_startup_cost,Costinput_total_cost,
128130
doubleinput_tuples);
129131
externvoidinitial_cost_nestloop(PlannerInfo*root,

0 commit comments

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