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

Commit3b0f776

Browse files
committed
Fix some questionable edge-case behaviors in add_path() and friends.
add_path_precheck was doing exact comparisons of path costs, but it reallyneeds to do them fuzzily to be sure it won't reject paths that couldsurvive add_path's comparisons. (This can only matter if the initial costestimate is very close to the final one, but that turns out to often betrue.)Also, it should ignore startup cost for this purpose if and only ifcompare_path_costs_fuzzily would do so. The previous coding always ignoredstartup cost for parameterized paths, which is wrong as of commit3f59be8; it could result in improper early rejection of paths thatwe care about for SEMI/ANTI joins. It also always considered startup costfor unparameterized paths, which is just as wrong though the only effect isto waste planner cycles on paths that can't survive. Instead, it shouldconsider startup cost only when directed to by the consider_startup/consider_param_startup relation flags.Likewise, compare_path_costs_fuzzily should have symmetrical behaviorfor parameterized and unparameterized paths. In this case, the bestanswer seems to be that after establishing that total costs are fuzzilyequal, we should compare startup costs whether or not the consider_xxxflags are on. That is what it's always done for unparameterized paths,so let's make the behavior for parameterized paths match.These issues were noted while developing the SEMI/ANTI join costing fixof commit3f59be8, but we chose not to back-patch these fixes,because they can cause changes in the planner's choices amongnearly-same-cost plans. (There is in fact one minor change in plan choicewithin the core regression tests.) Destabilizing plan choices in backbranches without very clear improvements is frowned on, so we'll just fixthis in HEAD.
1 parent3f59be8 commit3b0f776

File tree

2 files changed

+53
-49
lines changed

2 files changed

+53
-49
lines changed

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

Lines changed: 27 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,13 @@ typedef enum
3838
COSTS_DIFFERENT/* neither path dominates the other on cost */
3939
}PathCostComparison;
4040

41+
/*
42+
* STD_FUZZ_FACTOR is the normal fuzz factor for compare_path_costs_fuzzily.
43+
* XXX is it worth making this user-controllable? It provides a tradeoff
44+
* between planner runtime and the accuracy of path cost comparisons.
45+
*/
46+
#defineSTD_FUZZ_FACTOR 1.01
47+
4148
staticList*translate_sub_tlist(List*tlist,intrelid);
4249

4350

@@ -140,8 +147,10 @@ compare_fractional_path_costs(Path *path1, Path *path2,
140147
*
141148
* This function also enforces a policy rule that paths for which the relevant
142149
* one of parent->consider_startup and parent->consider_param_startup is false
143-
* cannot win comparisons on the grounds of good startup cost, so we never
144-
* return COSTS_DIFFERENT when that is true for the total-cost loser.
150+
* cannot survive comparisons solely on the grounds of good startup cost, so
151+
* we never return COSTS_DIFFERENT when that is true for the total-cost loser.
152+
* (But if total costs are fuzzily equal, we compare startup costs anyway,
153+
* in hopes of eliminating one path or the other.)
145154
*/
146155
staticPathCostComparison
147156
compare_path_costs_fuzzily(Path*path1,Path*path2,doublefuzz_factor)
@@ -177,21 +186,13 @@ compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor)
177186
/* else path1 dominates */
178187
returnCOSTS_BETTER1;
179188
}
180-
181-
/*
182-
* Fuzzily the same on total cost (so we might as well compare startup
183-
* cost, even when that would otherwise be uninteresting; but
184-
* parameterized paths aren't allowed to win this way, we'd rather move on
185-
* to other comparison heuristics)
186-
*/
187-
if (path1->startup_cost>path2->startup_cost*fuzz_factor&&
188-
path2->param_info==NULL)
189+
/* fuzzily the same on total cost ... */
190+
if (path1->startup_cost>path2->startup_cost*fuzz_factor)
189191
{
190192
/* ... but path1 fuzzily worse on startup, so path2 wins */
191193
returnCOSTS_BETTER2;
192194
}
193-
if (path2->startup_cost>path1->startup_cost*fuzz_factor&&
194-
path1->param_info==NULL)
195+
if (path2->startup_cost>path1->startup_cost*fuzz_factor)
195196
{
196197
/* ... but path2 fuzzily worse on startup, so path1 wins */
197198
returnCOSTS_BETTER1;
@@ -434,10 +435,10 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
434435
p1_next=lnext(p1);
435436

436437
/*
437-
* Do a fuzzy cost comparison with 1% fuzziness limit. (XXX does this
438-
* percentage need to be user-configurable?)
438+
* Do a fuzzy cost comparison with standard fuzziness limit.
439439
*/
440-
costcmp=compare_path_costs_fuzzily(new_path,old_path,1.01);
440+
costcmp=compare_path_costs_fuzzily(new_path,old_path,
441+
STD_FUZZ_FACTOR);
441442

442443
/*
443444
* If the two paths compare differently for startup and total cost,
@@ -624,11 +625,15 @@ add_path_precheck(RelOptInfo *parent_rel,
624625
List*pathkeys,Relidsrequired_outer)
625626
{
626627
List*new_path_pathkeys;
628+
boolconsider_startup;
627629
ListCell*p1;
628630

629631
/* Pretend parameterized paths have no pathkeys, per add_path policy */
630632
new_path_pathkeys=required_outer ?NIL :pathkeys;
631633

634+
/* Decide whether new path's startup cost is interesting */
635+
consider_startup=required_outer ?parent_rel->consider_param_startup :parent_rel->consider_startup;
636+
632637
foreach(p1,parent_rel->pathlist)
633638
{
634639
Path*old_path= (Path*)lfirst(p1);
@@ -640,16 +645,15 @@ add_path_precheck(RelOptInfo *parent_rel,
640645
* pathkeys as well as both cost metrics. If we find one, we can
641646
* reject the new path.
642647
*
643-
* For speed, we make exact rather than fuzzy cost comparisons. If an
644-
* old path dominates the new path exactly on both costs, it will
645-
* surely do so fuzzily.
648+
* Cost comparisons here should match compare_path_costs_fuzzily.
646649
*/
647-
if (total_cost >=old_path->total_cost)
650+
if (total_cost>old_path->total_cost*STD_FUZZ_FACTOR)
648651
{
649-
/* can win on startup cost only if unparameterized */
650-
if (startup_cost >=old_path->startup_cost||required_outer)
652+
/* new path can win on startup cost only if consider_startup */
653+
if (startup_cost>old_path->startup_cost*STD_FUZZ_FACTOR||
654+
!consider_startup)
651655
{
652-
/* new pathdoes not win on cost, so check pathkeys... */
656+
/* new pathloses on cost, so check pathkeys... */
653657
List*old_path_pathkeys;
654658

655659
old_path_pathkeys=old_path->param_info ?NIL :old_path->pathkeys;

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

Lines changed: 26 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -3771,74 +3771,74 @@ explain (costs off)
37713771
------------------------------------------
37723772
Nested Loop
37733773
-> Seq Scan on int8_tbl a
3774-
-> HashLeft Join
3775-
Hash Cond: (x.q2 =(a.q1))
3776-
-> Seq Scan onint8_tbl x
3774+
-> HashRight Join
3775+
Hash Cond: ((a.q1) = x.q2)
3776+
-> Seq Scan onint4_tbl y
37773777
-> Hash
3778-
-> Seq Scan onint4_tbl y
3778+
-> Seq Scan onint8_tbl x
37793779
(7 rows)
37803780

37813781
select * from int8_tbl a,
37823782
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
37833783
on x.q2 = ss.z;
37843784
q1 | q2 | q1 | q2 | z
37853785
------------------+-------------------+------------------+-------------------+------------------
3786-
123 | 456 | 123 | 456 |
3787-
123 | 456 | 123 | 4567890123456789 |
37883786
123 | 456 | 4567890123456789 | 123 | 123
37893787
123 | 456 | 4567890123456789 | 123 | 123
37903788
123 | 456 | 4567890123456789 | 123 | 123
37913789
123 | 456 | 4567890123456789 | 123 | 123
37923790
123 | 456 | 4567890123456789 | 123 | 123
37933791
123 | 456 | 4567890123456789 | 4567890123456789 |
3792+
123 | 456 | 123 | 4567890123456789 |
3793+
123 | 456 | 123 | 456 |
37943794
123 | 456 | 4567890123456789 | -4567890123456789 |
3795-
123 | 4567890123456789 | 123 | 456 |
3796-
123 | 4567890123456789 | 123 | 4567890123456789 |
37973795
123 | 4567890123456789 | 4567890123456789 | 123 | 123
37983796
123 | 4567890123456789 | 4567890123456789 | 123 | 123
37993797
123 | 4567890123456789 | 4567890123456789 | 123 | 123
38003798
123 | 4567890123456789 | 4567890123456789 | 123 | 123
38013799
123 | 4567890123456789 | 4567890123456789 | 123 | 123
38023800
123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
3801+
123 | 4567890123456789 | 123 | 4567890123456789 |
3802+
123 | 4567890123456789 | 123 | 456 |
38033803
123 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
3804-
4567890123456789 | 123 | 123| 456 |
3804+
4567890123456789 | 123 |4567890123456789|4567890123456789 | 4567890123456789
38053805
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
3806+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
38063807
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
3808+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
38073809
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
3810+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
38083811
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
3812+
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
38093813
4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
38103814
4567890123456789 | 123 | 4567890123456789 | 123 |
3811-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
3812-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
3813-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
3814-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
3815-
4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
3815+
4567890123456789 | 123 | 123 | 456 |
38163816
4567890123456789 | 123 | 4567890123456789 | -4567890123456789 |
3817-
4567890123456789 | 4567890123456789 | 123| 456 |
3817+
4567890123456789 | 4567890123456789 |4567890123456789|4567890123456789 | 4567890123456789
38183818
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3819+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38193820
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3821+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38203822
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3823+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38213824
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3825+
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38223826
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
38233827
4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
3824-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3825-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3826-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3827-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3828-
4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3828+
4567890123456789 | 4567890123456789 | 123 | 456 |
38293829
4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
3830-
4567890123456789 | -4567890123456789 | 123| 456 |
3830+
4567890123456789 | -4567890123456789 |4567890123456789|4567890123456789 | 4567890123456789
38313831
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3832+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38323833
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3834+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38333835
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3836+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38343837
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
3838+
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
38353839
4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
38363840
4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
3837-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3838-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3839-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3840-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3841-
4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
3841+
4567890123456789 | -4567890123456789 | 123 | 456 |
38423842
4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
38433843
(57 rows)
38443844

0 commit comments

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