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

Commit0bb28ca

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 parent1f47eb0 commit0bb28ca

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
@@ -4226,18 +4226,21 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
42264226

42274227
explain (verbose, costs off) select * from ft3 f, loct3 l
42284228
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
4229-
QUERY PLAN
4230-
---------------------------------------------------------
4231-
Nested Loop
4229+
QUERY PLAN
4230+
-------------------------------------------------------------
4231+
Hash Join
42324232
Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
4233-
Join Filter: ((f.f3)::text = (l.f3)::text)
4234-
-> Index Scan using loct3_f1_key on public.loct3 l
4235-
Output: l.f1, l.f2, l.f3
4236-
Index Cond: (l.f1 = 'foo'::text)
4233+
Inner Unique: true
4234+
Hash Cond: ((f.f3)::text = (l.f3)::text)
42374235
-> Foreign Scan on public.ft3 f
42384236
Output: f.f1, f.f2, f.f3
42394237
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
4240-
(9 rows)
4238+
-> Hash
4239+
Output: l.f1, l.f2, l.f3
4240+
-> Index Scan using loct3_f1_key on public.loct3 l
4241+
Output: l.f1, l.f2, l.f3
4242+
Index Cond: (l.f1 = 'foo'::text)
4243+
(12 rows)
42414244

42424245
-- ===================================================================
42434246
-- 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
@@ -3180,10 +3180,10 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
31803180
clamp_row_est(inner_path_rows /virtualbuckets)*0.05;
31813181

31823182
/* Get # of tuples that will pass the basic join */
3183-
if (path->jpath.jointype==JOIN_SEMI)
3184-
hashjointuples=outer_matched_rows;
3185-
else
3183+
if (path->jpath.jointype==JOIN_ANTI)
31863184
hashjointuples=outer_path_rows-outer_matched_rows;
3185+
else
3186+
hashjointuples=outer_matched_rows;
31873187
}
31883188
else
31893189
{

0 commit comments

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