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

Commit1007b0a

Browse files
committed
Fix hashjoin costing mistake introduced with inner_unique optimization.
In final_cost_hashjoin(), commit9c7f522 allowed inner_unique casesto follow a code path previously used only for SEMI/ANTI joins; but itneglected to fix an if-test within that path that assumed SEMI and ANTIwere the only possible cases. This resulted in a wrong value forhashjointuples, and an ensuing bad cost estimate, for inner_unique normaljoins. Fortunately, for inner_unique normal joins we can assume the numberof joined tuples is the same as for a SEMI join; so there's no need formore code, we just have to invert the test to check for ANTI not SEMI.It turns out that in two contrib tests in which commit9c7f522changed the plan expected for a query, the change was actually wrongand induced by this estimation error, not by any real improvement.Hence this patch also reverts those changes.Per report from RK Korlapati. Backpatch to v10 where the error wasintroduced.David RowleyDiscussion:https://postgr.es/m/CA+SNy03bhq0fodsfOkeWDCreNjJVjsdHwUsb7AG=jpe0PtZc_g@mail.gmail.com
1 parent28a1ae5 commit1007b0a

File tree

4 files changed

+16
-13
lines changed

4 files changed

+16
-13
lines changed

‎contrib/citext/expected/citext.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2336,8 +2336,8 @@ SELECT *
23362336
WHERE t.id IS NULL OR m.id IS NULL;
23372337
id | name | id | name
23382338
----+------+----+------
2339-
2 | two | |
23402339
| | 2 | Two
2340+
2 | two | |
23412341
(2 rows)
23422342

23432343
REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;

‎contrib/citext/expected/citext_1.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2336,8 +2336,8 @@ SELECT *
23362336
WHERE t.id IS NULL OR m.id IS NULL;
23372337
id | name | id | name
23382338
----+------+----+------
2339-
2 | two | |
23402339
| | 2 | Two
2340+
2 | two | |
23412341
(2 rows)
23422342

23432343
REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -4233,18 +4233,21 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
42334233

42344234
explain (verbose, costs off) select * from ft3 f, loct3 l
42354235
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
4236-
QUERY PLAN
4237-
---------------------------------------------------------
4238-
Nested Loop
4236+
QUERY PLAN
4237+
-------------------------------------------------------------
4238+
Hash Join
42394239
Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
4240-
Join Filter: ((f.f3)::text = (l.f3)::text)
4241-
-> Index Scan using loct3_f1_key on public.loct3 l
4242-
Output: l.f1, l.f2, l.f3
4243-
Index Cond: (l.f1 = 'foo'::text)
4240+
Inner Unique: true
4241+
Hash Cond: ((f.f3)::text = (l.f3)::text)
42444242
-> Foreign Scan on public.ft3 f
42454243
Output: f.f1, f.f2, f.f3
42464244
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
4247-
(9 rows)
4245+
-> Hash
4246+
Output: l.f1, l.f2, l.f3
4247+
-> Index Scan using loct3_f1_key on public.loct3 l
4248+
Output: l.f1, l.f2, l.f3
4249+
Index Cond: (l.f1 = 'foo'::text)
4250+
(12 rows)
42484251

42494252
-- ===================================================================
42504253
-- test writable foreign table stuff

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3454,10 +3454,10 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
34543454
clamp_row_est(inner_path_rows /virtualbuckets)*0.05;
34553455

34563456
/* Get # of tuples that will pass the basic join */
3457-
if (path->jpath.jointype==JOIN_SEMI)
3458-
hashjointuples=outer_matched_rows;
3459-
else
3457+
if (path->jpath.jointype==JOIN_ANTI)
34603458
hashjointuples=outer_path_rows-outer_matched_rows;
3459+
else
3460+
hashjointuples=outer_matched_rows;
34613461
}
34623462
else
34633463
{

0 commit comments

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