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

Commit7ebb03c

Browse files
tglsfdcpull[bot]
authored andcommitted
Estimate cost of elided SubqueryScan, Append, MergeAppend nodes better.
setrefs.c contains logic to discard no-op SubqueryScan nodes, that is,ones that have no qual to check and copy the input targetlist unchanged.(Formally it's not very nice to be applying such optimizations so latein the planner, but there are practical reasons for it; mostly that wecan't unify relids between the subquery and the parent query until weflatten the rangetable during setrefs.c.) This behavior falsifies ourprevious cost estimates, since we would've charged cpu_tuple_cost perrow just to pass data through the node. Most of the time that's littleenough to not matter, but there are cases where this effect visiblychanges the plan compared to what you would've gotten with nosub-select.To improve the situation, make the callers of cost_subqueryscan tellit whether they think the targetlist is trivial. cost_subqueryscanalready has the qual list, so it can check the other half of thecondition easily. It could make its own determination of tlisttriviality too, but doing so would be repetitive (for callers thatmay call it several times) or unnecessarily expensive (for callersthat can determine this more cheaply than a general test would do).This isn't a 100% solution, because createplan.c also does thingsthat can falsify any earlier estimate of whether the tlist istrivial. However, it fixes nearly all cases in practice, if resultsfor the regression tests are anything to go by.setrefs.c also contains logic to discard no-op Append and MergeAppendnodes. We did have knowledge of that behavior at costing time, butsomebody failed to update it when a check on parallel-awareness wasadded to the setrefs.c logic. Fix that while we're here.These changes result in two minor changes in query plans shown inour regression tests. Neither is relevant to the purposes of itstest case AFAICT.Patch by me; thanks to Richard Guo for review.Discussion:https://postgr.es/m/2581077.1651703520@sss.pgh.pa.us
1 parentbca2d0e commit7ebb03c

File tree

9 files changed

+147
-39
lines changed

9 files changed

+147
-39
lines changed

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

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2451,6 +2451,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
24512451
{
24522452
Query*parse=root->parse;
24532453
Query*subquery=rte->subquery;
2454+
booltrivial_pathtarget;
24542455
Relidsrequired_outer;
24552456
pushdown_safety_infosafetyInfo;
24562457
doubletuple_fraction;
@@ -2613,6 +2614,36 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
26132614
*/
26142615
set_subquery_size_estimates(root,rel);
26152616

2617+
/*
2618+
* Also detect whether the reltarget is trivial, so that we can pass that
2619+
* info to cost_subqueryscan (rather than re-deriving it multiple times).
2620+
* It's trivial if it fetches all the subplan output columns in order.
2621+
*/
2622+
if (list_length(rel->reltarget->exprs)!=list_length(subquery->targetList))
2623+
trivial_pathtarget= false;
2624+
else
2625+
{
2626+
trivial_pathtarget= true;
2627+
foreach(lc,rel->reltarget->exprs)
2628+
{
2629+
Node*node= (Node*)lfirst(lc);
2630+
Var*var;
2631+
2632+
if (!IsA(node,Var))
2633+
{
2634+
trivial_pathtarget= false;
2635+
break;
2636+
}
2637+
var= (Var*)node;
2638+
if (var->varno!=rti||
2639+
var->varattno!=foreach_current_index(lc)+1)
2640+
{
2641+
trivial_pathtarget= false;
2642+
break;
2643+
}
2644+
}
2645+
}
2646+
26162647
/*
26172648
* For each Path that subquery_planner produced, make a SubqueryScanPath
26182649
* in the outer query.
@@ -2631,6 +2662,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
26312662
/* Generate outer path using this subpath */
26322663
add_path(rel, (Path*)
26332664
create_subqueryscan_path(root,rel,subpath,
2665+
trivial_pathtarget,
26342666
pathkeys,required_outer));
26352667
}
26362668

@@ -2656,6 +2688,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
26562688
/* Generate outer path using this subpath */
26572689
add_partial_path(rel, (Path*)
26582690
create_subqueryscan_path(root,rel,subpath,
2691+
trivial_pathtarget,
26592692
pathkeys,
26602693
required_outer));
26612694
}

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

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1415,10 +1415,12 @@ cost_tidrangescan(Path *path, PlannerInfo *root,
14151415
*
14161416
* 'baserel' is the relation to be scanned
14171417
* 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
1418+
* 'trivial_pathtarget' is true if the pathtarget is believed to be trivial.
14181419
*/
14191420
void
14201421
cost_subqueryscan(SubqueryScanPath*path,PlannerInfo*root,
1421-
RelOptInfo*baserel,ParamPathInfo*param_info)
1422+
RelOptInfo*baserel,ParamPathInfo*param_info,
1423+
booltrivial_pathtarget)
14221424
{
14231425
Coststartup_cost;
14241426
Costrun_cost;
@@ -1458,6 +1460,22 @@ cost_subqueryscan(SubqueryScanPath *path, PlannerInfo *root,
14581460
path->path.startup_cost=path->subpath->startup_cost;
14591461
path->path.total_cost=path->subpath->total_cost;
14601462

1463+
/*
1464+
* However, if there are no relevant restriction clauses and the
1465+
* pathtarget is trivial, then we expect that setrefs.c will optimize away
1466+
* the SubqueryScan plan node altogether, so we should just make its cost
1467+
* and rowcount equal to the input path's.
1468+
*
1469+
* Note: there are some edge cases where createplan.c will apply a
1470+
* different targetlist to the SubqueryScan node, thus falsifying our
1471+
* current estimate of whether the target is trivial, and making the cost
1472+
* estimate (though not the rowcount) wrong. It does not seem worth the
1473+
* extra complication to try to account for that exactly, especially since
1474+
* that behavior falsifies other cost estimates as well.
1475+
*/
1476+
if (qpquals==NIL&&trivial_pathtarget)
1477+
return;
1478+
14611479
get_restriction_qual_cost(root,baserel,param_info,&qpqual_cost);
14621480

14631481
startup_cost=qpqual_cost.startup;

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

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1636,9 +1636,10 @@ set_append_references(PlannerInfo *root,
16361636
/*
16371637
* See if it's safe to get rid of the Append entirely. For this to be
16381638
* safe, there must be only one child plan and that child plan's parallel
1639-
* awareness must match that of the Append's. The reason for the latter
1640-
* is that if the Append is parallel aware and the child is not, then the
1641-
* calling plan may execute the non-parallel aware child multiple times.
1639+
* awareness must match the Append's. The reason for the latter is that
1640+
* if the Append is parallel aware and the child is not, then the calling
1641+
* plan may execute the non-parallel aware child multiple times. (If you
1642+
* change these rules, update create_append_path to match.)
16421643
*/
16431644
if (list_length(aplan->appendplans)==1)
16441645
{
@@ -1710,10 +1711,11 @@ set_mergeappend_references(PlannerInfo *root,
17101711
/*
17111712
* See if it's safe to get rid of the MergeAppend entirely. For this to
17121713
* be safe, there must be only one child plan and that child plan's
1713-
* parallel awareness must match that of the MergeAppend's. The reason
1714-
* for the latter is that if the MergeAppend is parallel aware and the
1715-
* child is not then the calling plan may execute the non-parallel aware
1716-
* child multiple times.
1714+
* parallel awareness must match the MergeAppend's. The reason for the
1715+
* latter is that if the MergeAppend is parallel aware and the child is
1716+
* not, then the calling plan may execute the non-parallel aware child
1717+
* multiple times. (If you change these rules, update
1718+
* create_merge_append_path to match.)
17171719
*/
17181720
if (list_length(mplan->mergeplans)==1)
17191721
{

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

Lines changed: 23 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -78,7 +78,8 @@ static List *generate_setop_tlist(List *colTypes, List *colCollations,
7878
Indexvarno,
7979
boolhack_constants,
8080
List*input_tlist,
81-
List*refnames_tlist);
81+
List*refnames_tlist,
82+
bool*trivial_tlist);
8283
staticList*generate_append_tlist(List*colTypes,List*colCollations,
8384
boolflag,
8485
List*input_tlists,
@@ -226,6 +227,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
226227
Path*subpath;
227228
Path*path;
228229
List*tlist;
230+
booltrivial_tlist;
229231

230232
Assert(subquery!=NULL);
231233

@@ -254,7 +256,8 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
254256
rtr->rtindex,
255257
true,
256258
subroot->processed_tlist,
257-
refnames_tlist);
259+
refnames_tlist,
260+
&trivial_tlist);
258261
rel->reltarget=create_pathtarget(root,tlist);
259262

260263
/* Return the fully-fledged tlist to caller, too */
@@ -291,6 +294,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
291294
* soon too, likely.)
292295
*/
293296
path= (Path*)create_subqueryscan_path(root,rel,subpath,
297+
trivial_tlist,
294298
NIL,NULL);
295299

296300
add_path(rel,path);
@@ -309,6 +313,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
309313
partial_subpath=linitial(final_rel->partial_pathlist);
310314
partial_path= (Path*)
311315
create_subqueryscan_path(root,rel,partial_subpath,
316+
trivial_tlist,
312317
NIL,NULL);
313318
add_partial_path(rel,partial_path);
314319
}
@@ -376,14 +381,16 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
376381
!tlist_same_collations(*pTargetList,colCollations,junkOK))
377382
{
378383
PathTarget*target;
384+
booltrivial_tlist;
379385
ListCell*lc;
380386

381387
*pTargetList=generate_setop_tlist(colTypes,colCollations,
382388
flag,
383389
0,
384390
false,
385391
*pTargetList,
386-
refnames_tlist);
392+
refnames_tlist,
393+
&trivial_tlist);
387394
target=create_pathtarget(root,*pTargetList);
388395

389396
/* Apply projection to each path */
@@ -1117,14 +1124,16 @@ choose_hashed_setop(PlannerInfo *root, List *groupClauses,
11171124
* hack_constants: true to copy up constants (see comments in code)
11181125
* input_tlist: targetlist of this node's input node
11191126
* refnames_tlist: targetlist to take column names from
1127+
* trivial_tlist: output parameter, set to true if targetlist is trivial
11201128
*/
11211129
staticList*
11221130
generate_setop_tlist(List*colTypes,List*colCollations,
11231131
intflag,
11241132
Indexvarno,
11251133
boolhack_constants,
11261134
List*input_tlist,
1127-
List*refnames_tlist)
1135+
List*refnames_tlist,
1136+
bool*trivial_tlist)
11281137
{
11291138
List*tlist=NIL;
11301139
intresno=1;
@@ -1135,6 +1144,8 @@ generate_setop_tlist(List *colTypes, List *colCollations,
11351144
TargetEntry*tle;
11361145
Node*expr;
11371146

1147+
*trivial_tlist= true;/* until proven differently */
1148+
11381149
forfour(ctlc,colTypes,cclc,colCollations,
11391150
itlc,input_tlist,rtlc,refnames_tlist)
11401151
{
@@ -1160,6 +1171,9 @@ generate_setop_tlist(List *colTypes, List *colCollations,
11601171
* this only at the first level of subquery-scan plans; we don't want
11611172
* phony constants appearing in the output tlists of upper-level
11621173
* nodes!
1174+
*
1175+
* Note that copying a constant doesn't in itself require us to mark
1176+
* the tlist nontrivial; see trivial_subqueryscan() in setrefs.c.
11631177
*/
11641178
if (hack_constants&&inputtle->expr&&IsA(inputtle->expr,Const))
11651179
expr= (Node*)inputtle->expr;
@@ -1185,6 +1199,7 @@ generate_setop_tlist(List *colTypes, List *colCollations,
11851199
expr,
11861200
colType,
11871201
"UNION/INTERSECT/EXCEPT");
1202+
*trivial_tlist= false;/* the coercion makes it not trivial */
11881203
}
11891204

11901205
/*
@@ -1199,9 +1214,12 @@ generate_setop_tlist(List *colTypes, List *colCollations,
11991214
* will reach the executor without any further processing.
12001215
*/
12011216
if (exprCollation(expr)!=colColl)
1217+
{
12021218
expr=applyRelabelType(expr,
12031219
exprType(expr),exprTypmod(expr),colColl,
12041220
COERCE_IMPLICIT_CAST,-1, false);
1221+
*trivial_tlist= false;/* the relabel makes it not trivial */
1222+
}
12051223

12061224
tle=makeTargetEntry((Expr*)expr,
12071225
(AttrNumber)resno++,
@@ -1234,6 +1252,7 @@ generate_setop_tlist(List *colTypes, List *colCollations,
12341252
pstrdup("flag"),
12351253
true);
12361254
tlist=lappend(tlist,tle);
1255+
*trivial_tlist= false;/* the extra entry makes it not trivial */
12371256
}
12381257

12391258
returntlist;

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

Lines changed: 44 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1326,19 +1326,28 @@ create_append_path(PlannerInfo *root,
13261326
Assert(!parallel_aware||pathnode->path.parallel_safe);
13271327

13281328
/*
1329-
* If there's exactly one child path, the Append is a no-op and will be
1330-
* discarded later (in setrefs.c); therefore, we can inherit the child's
1331-
* size and cost, as well as its pathkeys if any (overriding whatever the
1332-
* caller might've said). Otherwise, we must do the normal costsize
1329+
* If there's exactly one child path then the output of the Append is
1330+
* necessarily ordered the same as the child's, so we can inherit the
1331+
* child's pathkeys if any, overriding whatever the caller might've said.
1332+
* Furthermore, if the child's parallel awareness matches the Append's,
1333+
* then the Append is a no-op and will be discarded later (in setrefs.c).
1334+
* Then we can inherit the child's size and cost too, effectively charging
1335+
* zero for the Append. Otherwise, we must do the normal costsize
13331336
* calculation.
13341337
*/
13351338
if (list_length(pathnode->subpaths)==1)
13361339
{
13371340
Path*child= (Path*)linitial(pathnode->subpaths);
13381341

1339-
pathnode->path.rows=child->rows;
1340-
pathnode->path.startup_cost=child->startup_cost;
1341-
pathnode->path.total_cost=child->total_cost;
1342+
if (child->parallel_aware==parallel_aware)
1343+
{
1344+
pathnode->path.rows=child->rows;
1345+
pathnode->path.startup_cost=child->startup_cost;
1346+
pathnode->path.total_cost=child->total_cost;
1347+
}
1348+
else
1349+
cost_append(pathnode,root);
1350+
/* Must do this last, else cost_append complains */
13421351
pathnode->path.pathkeys=child->pathkeys;
13431352
}
13441353
else
@@ -1476,10 +1485,13 @@ create_merge_append_path(PlannerInfo *root,
14761485

14771486
/*
14781487
* Now we can compute total costs of the MergeAppend. If there's exactly
1479-
* one child path, the MergeAppend is a no-op and will be discarded later
1480-
* (in setrefs.c); otherwise we do the normal cost calculation.
1488+
* one child path and its parallel awareness matches that of the
1489+
* MergeAppend, then the MergeAppend is a no-op and will be discarded
1490+
* later (in setrefs.c); otherwise we do the normal cost calculation.
14811491
*/
1482-
if (list_length(subpaths)==1)
1492+
if (list_length(subpaths)==1&&
1493+
((Path*)linitial(subpaths))->parallel_aware==
1494+
pathnode->path.parallel_aware)
14831495
{
14841496
pathnode->path.startup_cost=input_startup_cost;
14851497
pathnode->path.total_cost=input_total_cost;
@@ -1986,9 +1998,15 @@ create_gather_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
19861998
* create_subqueryscan_path
19871999
* Creates a path corresponding to a scan of a subquery,
19882000
* returning the pathnode.
2001+
*
2002+
* Caller must pass trivial_pathtarget = true if it believes rel->reltarget to
2003+
* be trivial, ie just a fetch of all the subquery output columns in order.
2004+
* While we could determine that here, the caller can usually do it more
2005+
* efficiently (or at least amortize it over multiple calls).
19892006
*/
19902007
SubqueryScanPath*
19912008
create_subqueryscan_path(PlannerInfo*root,RelOptInfo*rel,Path*subpath,
2009+
booltrivial_pathtarget,
19922010
List*pathkeys,Relidsrequired_outer)
19932011
{
19942012
SubqueryScanPath*pathnode=makeNode(SubqueryScanPath);
@@ -2005,7 +2023,8 @@ create_subqueryscan_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
20052023
pathnode->path.pathkeys=pathkeys;
20062024
pathnode->subpath=subpath;
20072025

2008-
cost_subqueryscan(pathnode,root,rel,pathnode->path.param_info);
2026+
cost_subqueryscan(pathnode,root,rel,pathnode->path.param_info,
2027+
trivial_pathtarget);
20092028

20102029
returnpathnode;
20112030
}
@@ -3901,10 +3920,23 @@ reparameterize_path(PlannerInfo *root, Path *path,
39013920
caseT_SubqueryScan:
39023921
{
39033922
SubqueryScanPath*spath= (SubqueryScanPath*)path;
3923+
Path*subpath=spath->subpath;
3924+
booltrivial_pathtarget;
3925+
3926+
/*
3927+
* If existing node has zero extra cost, we must have decided
3928+
* its target is trivial. (The converse is not true, because
3929+
* it might have a trivial target but quals to enforce; but in
3930+
* that case the new node will too, so it doesn't matter
3931+
* whether we get the right answer here.)
3932+
*/
3933+
trivial_pathtarget=
3934+
(subpath->total_cost==spath->path.total_cost);
39043935

39053936
return (Path*)create_subqueryscan_path(root,
39063937
rel,
3907-
spath->subpath,
3938+
subpath,
3939+
trivial_pathtarget,
39083940
spath->path.pathkeys,
39093941
required_outer);
39103942
}

‎src/include/optimizer/cost.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -91,7 +91,8 @@ extern void cost_tidrangescan(Path *path, PlannerInfo *root,
9191
RelOptInfo*baserel,List*tidrangequals,
9292
ParamPathInfo*param_info);
9393
externvoidcost_subqueryscan(SubqueryScanPath*path,PlannerInfo*root,
94-
RelOptInfo*baserel,ParamPathInfo*param_info);
94+
RelOptInfo*baserel,ParamPathInfo*param_info,
95+
booltrivial_pathtarget);
9596
externvoidcost_functionscan(Path*path,PlannerInfo*root,
9697
RelOptInfo*baserel,ParamPathInfo*param_info);
9798
externvoidcost_valuesscan(Path*path,PlannerInfo*root,

‎src/include/optimizer/pathnode.h

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -103,8 +103,11 @@ extern GatherMergePath *create_gather_merge_path(PlannerInfo *root,
103103
Relidsrequired_outer,
104104
double*rows);
105105
externSubqueryScanPath*create_subqueryscan_path(PlannerInfo*root,
106-
RelOptInfo*rel,Path*subpath,
107-
List*pathkeys,Relidsrequired_outer);
106+
RelOptInfo*rel,
107+
Path*subpath,
108+
booltrivial_pathtarget,
109+
List*pathkeys,
110+
Relidsrequired_outer);
108111
externPath*create_functionscan_path(PlannerInfo*root,RelOptInfo*rel,
109112
List*pathkeys,Relidsrequired_outer);
110113
externPath*create_valuesscan_path(PlannerInfo*root,RelOptInfo*rel,

0 commit comments

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