From 1007b0a126c608b530fd2914825f1e6d133cb649 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 14 Jul 2018 11:59:12 -0400 Subject: [PATCH] Fix hashjoin costing mistake introduced with inner_unique optimization. In final_cost_hashjoin(), commit 9c7f5229a allowed inner_unique cases to follow a code path previously used only for SEMI/ANTI joins; but it neglected to fix an if-test within that path that assumed SEMI and ANTI were the only possible cases. This resulted in a wrong value for hashjointuples, and an ensuing bad cost estimate, for inner_unique normal joins. Fortunately, for inner_unique normal joins we can assume the number of joined tuples is the same as for a SEMI join; so there's no need for more code, we just have to invert the test to check for ANTI not SEMI. It turns out that in two contrib tests in which commit 9c7f5229a changed the plan expected for a query, the change was actually wrong and 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 was introduced. David Rowley Discussion: https://postgr.es/m/CA+SNy03bhq0fodsfOkeWDCreNjJVjsdHwUsb7AG=jpe0PtZc_g@mail.gmail.com --- contrib/citext/expected/citext.out | 2 +- contrib/citext/expected/citext_1.out | 2 +- .../postgres_fdw/expected/postgres_fdw.out | 19 +++++++++++-------- src/backend/optimizer/path/costsize.c | 6 +++--- 4 files changed, 16 insertions(+), 13 deletions(-) diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out index 95373182af..99365c57b0 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -2336,8 +2336,8 @@ SELECT * WHERE t.id IS NULL OR m.id IS NULL; id | name | id | name ----+------+----+------ - 2 | two | | | | 2 | Two + 2 | two | | (2 rows) REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview; diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out index 855ec3f10b..8aac72e226 100644 --- a/contrib/citext/expected/citext_1.out +++ b/contrib/citext/expected/citext_1.out @@ -2336,8 +2336,8 @@ SELECT * WHERE t.id IS NULL OR m.id IS NULL; id | name | id | name ----+------+----+------ - 2 | two | | | | 2 | Two + 2 | two | | (2 rows) REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview; diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 8c58720df6..fc2c51c9d0 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -4233,18 +4233,21 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C"; explain (verbose, costs off) select * from ft3 f, loct3 l where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo'; - QUERY PLAN ---------------------------------------------------------- - Nested Loop + QUERY PLAN +------------------------------------------------------------- + Hash Join Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3 - Join Filter: ((f.f3)::text = (l.f3)::text) - -> Index Scan using loct3_f1_key on public.loct3 l - Output: l.f1, l.f2, l.f3 - Index Cond: (l.f1 = 'foo'::text) + Inner Unique: true + Hash Cond: ((f.f3)::text = (l.f3)::text) -> Foreign Scan on public.ft3 f Output: f.f1, f.f2, f.f3 Remote SQL: SELECT f1, f2, f3 FROM public.loct3 -(9 rows) + -> Hash + Output: l.f1, l.f2, l.f3 + -> Index Scan using loct3_f1_key on public.loct3 l + Output: l.f1, l.f2, l.f3 + Index Cond: (l.f1 = 'foo'::text) +(12 rows) -- =================================================================== -- test writable foreign table stuff diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index a6811e0338..7bf67a0529 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -3454,10 +3454,10 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, clamp_row_est(inner_path_rows / virtualbuckets) * 0.05; /* Get # of tuples that will pass the basic join */ - if (path->jpath.jointype == JOIN_SEMI) - hashjointuples = outer_matched_rows; - else + if (path->jpath.jointype == JOIN_ANTI) hashjointuples = outer_path_rows - outer_matched_rows; + else + hashjointuples = outer_matched_rows; } else { -- 2.40.0