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

Commit6d2c8c0

Browse files
committed
Drop cheap-startup-cost paths during add_path() if we don't need them.
We can detect whether the planner top level is going to care at all aboutcheap startup cost (it will only do so if query_planner's tuple_fractionargument is greater than zero). If it isn't, we might as well discardpaths immediately whose only advantage over others is cheap startup cost.This turns out to get rid of quite a lot of paths in complex queries ---I saw planner runtime reduction of more than a third on one large query.Since add_path isn't currently passed the PlannerInfo "root", the easiestway to tell it whether to do this was to add a bool flag to RelOptInfo.That's a bit redundant, since all relations in a given query level willhave the same setting. But in the future it's possible that we'd refinethe control decision to work on a per-relation basis, so this seems likea good arrangement anyway.Per my suggestion of a few months ago.
1 parent4da6439 commit6d2c8c0

File tree

6 files changed

+36
-11
lines changed

6 files changed

+36
-11
lines changed

‎src/backend/nodes/outfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1733,6 +1733,7 @@ _outRelOptInfo(StringInfo str, const RelOptInfo *node)
17331733
WRITE_BITMAPSET_FIELD(relids);
17341734
WRITE_FLOAT_FIELD(rows,"%.0f");
17351735
WRITE_INT_FIELD(width);
1736+
WRITE_BOOL_FIELD(consider_startup);
17361737
WRITE_NODE_FIELD(reltargetlist);
17371738
WRITE_NODE_FIELD(pathlist);
17381739
WRITE_NODE_FIELD(ppilist);

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

Lines changed: 19 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -139,14 +139,18 @@ compare_fractional_path_costs(Path *path1, Path *path2,
139139
* total cost, we just say that their costs are "different", since neither
140140
* dominates the other across the whole performance spectrum.
141141
*
142+
* If consider_startup is false, then we don't care about keeping paths with
143+
* good startup cost, so we'll never return COSTS_DIFFERENT.
144+
*
142145
* This function also includes special hacks to support a policy enforced
143146
* by its sole caller, add_path(): paths that have any parameterization
144147
* cannot win comparisons on the grounds of having cheaper startup cost,
145148
* since we deem only total cost to be of interest for a parameterized path.
146149
* (Unparameterized paths are more common, so we check for this case last.)
147150
*/
148151
staticPathCostComparison
149-
compare_path_costs_fuzzily(Path*path1,Path*path2,doublefuzz_factor)
152+
compare_path_costs_fuzzily(Path*path1,Path*path2,doublefuzz_factor,
153+
boolconsider_startup)
150154
{
151155
/*
152156
* Check total cost first since it's more likely to be different; many
@@ -155,7 +159,8 @@ compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor)
155159
if (path1->total_cost>path2->total_cost*fuzz_factor)
156160
{
157161
/* path1 fuzzily worse on total cost */
158-
if (path2->startup_cost>path1->startup_cost*fuzz_factor&&
162+
if (consider_startup&&
163+
path2->startup_cost>path1->startup_cost*fuzz_factor&&
159164
path1->param_info==NULL)
160165
{
161166
/* ... but path2 fuzzily worse on startup, so DIFFERENT */
@@ -167,7 +172,8 @@ compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor)
167172
if (path2->total_cost>path1->total_cost*fuzz_factor)
168173
{
169174
/* path2 fuzzily worse on total cost */
170-
if (path1->startup_cost>path2->startup_cost*fuzz_factor&&
175+
if (consider_startup&&
176+
path1->startup_cost>path2->startup_cost*fuzz_factor&&
171177
path2->param_info==NULL)
172178
{
173179
/* ... but path1 fuzzily worse on startup, so DIFFERENT */
@@ -177,6 +183,7 @@ compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor)
177183
returnCOSTS_BETTER1;
178184
}
179185
/* fuzzily the same on total cost */
186+
/* (so we may as well compare startup cost, even if !consider_startup) */
180187
if (path1->startup_cost>path2->startup_cost*fuzz_factor&&
181188
path2->param_info==NULL)
182189
{
@@ -360,6 +367,9 @@ set_cheapest(RelOptInfo *parent_rel)
360367
* reduce the number of parameterized paths that are kept. See discussion
361368
* in src/backend/optimizer/README.
362369
*
370+
* Another policy that is enforced here is that we only consider cheap
371+
* startup cost to be interesting if parent_rel->consider_startup is true.
372+
*
363373
* The pathlist is kept sorted by total_cost, with cheaper paths
364374
* at the front. Within this routine, that's simply a speed hack:
365375
* doing it that way makes it more likely that we will reject an inferior
@@ -423,7 +433,8 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
423433
* Do a fuzzy cost comparison with 1% fuzziness limit.(XXX does this
424434
* percentage need to be user-configurable?)
425435
*/
426-
costcmp=compare_path_costs_fuzzily(new_path,old_path,1.01);
436+
costcmp=compare_path_costs_fuzzily(new_path,old_path,1.01,
437+
parent_rel->consider_startup);
427438

428439
/*
429440
* If the two paths compare differently for startup and total cost,
@@ -488,8 +499,10 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
488499
remove_old= true;/* new dominates old */
489500
elseif (new_path->rows>old_path->rows)
490501
accept_new= false;/* old dominates new */
491-
elseif (compare_path_costs_fuzzily(new_path,old_path,
492-
1.0000000001)==COSTS_BETTER1)
502+
elseif (compare_path_costs_fuzzily(new_path,
503+
old_path,
504+
1.0000000001,
505+
parent_rel->consider_startup)==COSTS_BETTER1)
493506
remove_old= true;/* new dominates old */
494507
else
495508
accept_new= false;/* old equals or

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -99,6 +99,8 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
9999
rel->relids=bms_make_singleton(relid);
100100
rel->rows=0;
101101
rel->width=0;
102+
/* cheap startup cost is interesting iff not all tuples to be retrieved */
103+
rel->consider_startup= (root->tuple_fraction>0);
102104
rel->reltargetlist=NIL;
103105
rel->pathlist=NIL;
104106
rel->ppilist=NIL;
@@ -354,6 +356,8 @@ build_join_rel(PlannerInfo *root,
354356
joinrel->relids=bms_copy(joinrelids);
355357
joinrel->rows=0;
356358
joinrel->width=0;
359+
/* cheap startup cost is interesting iff not all tuples to be retrieved */
360+
joinrel->consider_startup= (root->tuple_fraction>0);
357361
joinrel->reltargetlist=NIL;
358362
joinrel->pathlist=NIL;
359363
joinrel->ppilist=NIL;

‎src/include/nodes/relation.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -299,6 +299,8 @@ typedef struct PlannerInfo
299299
* clauses have been applied (ie, output rows of a plan for it)
300300
*width - avg. number of bytes per tuple in the relation after the
301301
*appropriate projections have been done (ie, output width)
302+
*consider_startup - true if there is any value in keeping paths for
303+
* this rel on the basis of having cheap startup cost
302304
*reltargetlist - List of Var and PlaceHolderVar nodes for the values
303305
*we need to output from this relation.
304306
*List is in no particular order, but all rels of an
@@ -405,6 +407,9 @@ typedef struct RelOptInfo
405407
doublerows;/* estimated number of result tuples */
406408
intwidth;/* estimated avg width of result tuples */
407409

410+
/* per-relation planner control flags */
411+
boolconsider_startup;/* keep cheap-startup-cost paths? */
412+
408413
/* materialization information */
409414
List*reltargetlist;/* Vars to be output by scan of relation */
410415
List*pathlist;/* Path structures */

‎src/test/regress/expected/join.out

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3437,6 +3437,7 @@ select v.* from
34373437

34383438
create temp table dual();
34393439
insert into dual default values;
3440+
analyze dual;
34403441
select v.* from
34413442
(int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
34423443
left join int4_tbl z on z.f1 = x.q2,
@@ -3446,21 +3447,21 @@ select v.* from
34463447
123 |
34473448
456 |
34483449
123 | 4567890123456789
3449-
4567890123456789 | 123
3450+
4567890123456789 |-4567890123456789
34503451
123 | 4567890123456789
34513452
4567890123456789 | 4567890123456789
34523453
123 | 4567890123456789
3453-
4567890123456789 | -4567890123456789
34543454
4567890123456789 | 123
3455-
123 | 456
34563455
4567890123456789 | 123
34573456
123 | 4567890123456789
3458-
4567890123456789 | 4567890123456789
34593457
4567890123456789 | 123
3458+
123 | 456
34603459
4567890123456789 | 4567890123456789
3460+
4567890123456789 | -4567890123456789
34613461
4567890123456789 | 4567890123456789
34623462
4567890123456789 | 4567890123456789
3463-
4567890123456789 | -4567890123456789
3463+
4567890123456789 | 4567890123456789
3464+
4567890123456789 | 123
34643465
4567890123456789 |
34653466
-4567890123456789 |
34663467
(20 rows)

‎src/test/regress/sql/join.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -937,6 +937,7 @@ select v.* from
937937
lateral (selectx.q1,y.q1union allselectx.q2,y.q2) v(vx,vy);
938938
create temp table dual();
939939
insert into dual defaultvalues;
940+
analyze dual;
940941
select v.*from
941942
(int8_tbl xleft join (select q1,(select coalesce(q2,0)) q2from int8_tbl) yonx.q2=y.q1)
942943
left join int4_tbl zonz.f1=x.q2,

0 commit comments

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