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

Commit2388658

Browse files
committed
Fix old corner-case logic error in final_cost_nestloop().
When costing a nestloop with stop-at-first-inner-match semantics, and anon-indexscan inner path, final_cost_nestloop() wants to charge the fullscan cost of the inner rel at least once, with additional scans chargedat inner_rescan_run_cost which might be less. However the logic fordoing this effectively assumed that outer_matched_rows is at least 1.If it's zero, which is not unlikely for a small outer rel, we ended upcharging inner_run_cost plus N times inner_rescan_run_cost, as much asdouble the correct charge for an outer rel with only one row thatwe're betting won't be matched. (Unless the inner rel is materialized,in which case it has very small inner_rescan_run_cost and the costis not so far off what it should have been.)The upshot of this was that the planner had a tendency to select plansthat failed to make effective use of the stop-at-first-inner-matchsemantics, and that might have Materialize nodes in them even when thepredicted number of executions of the Materialize subplan was only 1.This was not so obvious before commit9c7f522, because the case onlyarose in connection with semi/anti joins where there's not freedom toreverse the join order. But with the addition of unique-inner joins,it could result in some fairly bad planning choices, as reported byTeodor Sigaev. Indeed, some of the test cases added by that commithave plans that look dubious on closer inspection, and are changedby this patch.Fix the logic to ensure that we don't charge for too many inner scans.I chose to adjust it so that the full-freight scan cost is associatedwith an unmatched outer row if possible, not a matched one, since thatseems like a better model of what would happen at runtime.This is a longstanding bug, but given the lesser impact in back branches,and the lack of field complaints, I won't risk a back-patch.Discussion:https://postgr.es/m/CAKJS1f-LzkUsFxdJ_-Luy38orQ+AdEXM5o+vANR+-pHAWPSecg@mail.gmail.com
1 parent66b84fa commit2388658

File tree

2 files changed

+42
-38
lines changed

2 files changed

+42
-38
lines changed

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

Lines changed: 20 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2214,6 +2214,7 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path,
22142214
Costinner_run_cost=workspace->inner_run_cost;
22152215
Costinner_rescan_run_cost=workspace->inner_rescan_run_cost;
22162216
doubleouter_matched_rows;
2217+
doubleouter_unmatched_rows;
22172218
Selectivityinner_scan_frac;
22182219

22192220
/*
@@ -2226,6 +2227,7 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path,
22262227
* least 1, no such clamp is needed now.)
22272228
*/
22282229
outer_matched_rows=rint(outer_path_rows*extra->semifactors.outer_match_frac);
2230+
outer_unmatched_rows=outer_path_rows-outer_matched_rows;
22292231
inner_scan_frac=2.0 / (extra->semifactors.match_count+1.0);
22302232

22312233
/*
@@ -2269,7 +2271,7 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path,
22692271
* of a nonempty scan. We consider that these are all rescans,
22702272
* since we used inner_run_cost once already.
22712273
*/
2272-
run_cost+=(outer_path_rows-outer_matched_rows)*
2274+
run_cost+=outer_unmatched_rows*
22732275
inner_rescan_run_cost /inner_path_rows;
22742276

22752277
/*
@@ -2287,20 +2289,28 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path,
22872289
* difficult to estimate whether that will happen (and it could
22882290
* not happen if there are any unmatched outer rows!), so be
22892291
* conservative and always charge the whole first-scan cost once.
2292+
* We consider this charge to correspond to the first unmatched
2293+
* outer row, unless there isn't one in our estimate, in which
2294+
* case blame it on the first matched row.
22902295
*/
2296+
2297+
/* First, count all unmatched join tuples as being processed */
2298+
ntuples+=outer_unmatched_rows*inner_path_rows;
2299+
2300+
/* Now add the forced full scan, and decrement appropriate count */
22912301
run_cost+=inner_run_cost;
2302+
if (outer_unmatched_rows >=1)
2303+
outer_unmatched_rows-=1;
2304+
else
2305+
outer_matched_rows-=1;
22922306

22932307
/* Add inner run cost for additional outer tuples having matches */
2294-
if (outer_matched_rows>1)
2295-
run_cost+= (outer_matched_rows-1)*inner_rescan_run_cost*inner_scan_frac;
2296-
2297-
/* Add inner run cost for unmatched outer tuples */
2298-
run_cost+= (outer_path_rows-outer_matched_rows)*
2299-
inner_rescan_run_cost;
2308+
if (outer_matched_rows>0)
2309+
run_cost+=outer_matched_rows*inner_rescan_run_cost*inner_scan_frac;
23002310

2301-
/*And count the unmatched join tuples as being processed */
2302-
ntuples+= (outer_path_rows-outer_matched_rows)*
2303-
inner_path_rows;
2311+
/*Add inner run cost for additional unmatched outer tuples */
2312+
if (outer_unmatched_rows>0)
2313+
run_cost+=outer_unmatched_rows*inner_rescan_run_cost;
23042314
}
23052315
}
23062316
else

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

Lines changed: 22 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -5476,48 +5476,44 @@ select * from j1 natural join j2;
54765476
explain (verbose, costs off)
54775477
select * from j1
54785478
inner join (select distinct id from j3) j3 on j1.id = j3.id;
5479-
QUERY PLAN
5480-
-----------------------------------------------
5479+
QUERY PLAN
5480+
-----------------------------------------
54815481
Nested Loop
54825482
Output: j1.id, j3.id
54835483
Inner Unique: true
54845484
Join Filter: (j1.id = j3.id)
5485-
-> Seq Scan on public.j1
5486-
Output: j1.id
5487-
-> Materialize
5485+
-> Unique
54885486
Output: j3.id
5489-
->Unique
5487+
->Sort
54905488
Output: j3.id
5491-
-> Sort
5489+
Sort Key: j3.id
5490+
-> Seq Scan on public.j3
54925491
Output: j3.id
5493-
Sort Key: j3.id
5494-
-> Seq Scan on public.j3
5495-
Output: j3.id
5496-
(15 rows)
5492+
-> Seq Scan on public.j1
5493+
Output: j1.id
5494+
(13 rows)
54975495

54985496
-- ensure group by clause allows the inner to become unique
54995497
explain (verbose, costs off)
55005498
select * from j1
55015499
inner join (select id from j3 group by id) j3 on j1.id = j3.id;
5502-
QUERY PLAN
5503-
-----------------------------------------------
5500+
QUERY PLAN
5501+
-----------------------------------------
55045502
Nested Loop
55055503
Output: j1.id, j3.id
55065504
Inner Unique: true
55075505
Join Filter: (j1.id = j3.id)
5508-
-> Seq Scan on public.j1
5509-
Output: j1.id
5510-
-> Materialize
5506+
-> Group
55115507
Output: j3.id
5512-
-> Group
5508+
Group Key: j3.id
5509+
-> Sort
55135510
Output: j3.id
5514-
Group Key: j3.id
5515-
->Sort
5511+
Sort Key: j3.id
5512+
->Seq Scan on public.j3
55165513
Output: j3.id
5517-
Sort Key: j3.id
5518-
-> Seq Scan on public.j3
5519-
Output: j3.id
5520-
(16 rows)
5514+
-> Seq Scan on public.j1
5515+
Output: j1.id
5516+
(14 rows)
55215517

55225518
drop table j1;
55235519
drop table j2;
@@ -5558,13 +5554,11 @@ inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2;
55585554
Output: j1.id1, j1.id2, j2.id1, j2.id2
55595555
Inner Unique: true
55605556
Join Filter: ((j1.id1 = j2.id1) AND (j1.id2 = j2.id2))
5557+
-> Seq Scan on public.j2
5558+
Output: j2.id1, j2.id2
55615559
-> Seq Scan on public.j1
55625560
Output: j1.id1, j1.id2
5563-
-> Materialize
5564-
Output: j2.id1, j2.id2
5565-
-> Seq Scan on public.j2
5566-
Output: j2.id1, j2.id2
5567-
(10 rows)
5561+
(8 rows)
55685562

55695563
-- ensure we don't detect the join to be unique when quals are not part of the
55705564
-- join condition

0 commit comments

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