diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2023-01-30 13:44:36 -0500 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2023-01-30 13:44:36 -0500 |
commit | b448f1c8d83f8b65e2f0080c556ee21a7076da25 (patch) | |
tree | bc76c0506f01f224521b14304224598b8ba6699a /src/test | |
parent | 2489d76c4906f4461a364ca8ad7e0751ead8aa0d (diff) |
Do assorted mop-up in the planner.
Remove RestrictInfo.nullable_relids, along with a good deal of
infrastructure that calculated it. One use-case for it was in
join_clause_is_movable_to, but we can now replace that usage with
a check to see if the clause's relids include any outer join
that can null the target relation. The other use-case was in
join_clause_is_movable_into, but that test can just be dropped
entirely now that the clause's relids include outer joins.
Furthermore, join_clause_is_movable_into should now be
accurate enough that it will accept anything returned by
generate_join_implied_equalities, so we can restore the Assert
that was diked out in commit 95f4e59c3.
Remove the outerjoin_delayed mechanism. We needed this before to
prevent quals from getting evaluated below outer joins that should
null some of their vars. Now that we consider varnullingrels while
placing quals, that's taken care of automatically, so throw the
whole thing away.
Teach remove_useless_result_rtes to also remove useless FromExprs.
Having done that, the delay_upper_joins flag serves no purpose any
more and we can remove it, largely reverting 11086f2f2.
Use constant TRUE for "dummy" clauses when throwing back outer joins.
This improves on a hack I introduced in commit 6a6522529. If we
have a left-join clause l.x = r.y, and a WHERE clause l.x = constant,
we generate r.y = constant and then don't really have a need for the
join clause. But we must throw the join clause back anyway after
marking it redundant, so that the join search heuristics won't think
this is a clauseless join and avoid it. That was a kluge introduced
under time pressure, and after looking at it I thought of a better
way: let's just introduce constant-TRUE "join clauses" instead,
and get rid of them at the end. This improves the generated plans for
such cases by not having to test a redundant join clause. We can also
get rid of the ugly hack used to mark such clauses as redundant for
selectivity estimation.
Patch by me; thanks to Richard Guo for review.
Discussion: https://postgr.es/m/830269.1656693747@sss.pgh.pa.us
Diffstat (limited to 'src/test')
-rw-r--r-- | src/test/regress/expected/join.out | 34 | ||||
-rw-r--r-- | src/test/regress/sql/join.sql | 4 |
2 files changed, 17 insertions, 21 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 51c9df3d582..87e12768042 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2308,8 +2308,8 @@ order by 1, 2; (5 rows) -- --- regression test: check a case where join_clause_is_movable_into() gives --- an imprecise result, causing an assertion failure +-- regression test: check a case where join_clause_is_movable_into() +-- used to give an imprecise result, causing an assertion failure -- select count(*) from @@ -4020,10 +4020,10 @@ explain (costs off) select q1, unique2, thousand, hundred from int8_tbl a left join tenk1 b on q1 = unique2 where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123); - QUERY PLAN --------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Nested Loop Left Join - Filter: ((COALESCE(b.thousand, 123) = a.q1) AND (a.q1 = COALESCE(b.hundred, 123))) + Filter: ((COALESCE(b.thousand, 123) = COALESCE(b.hundred, 123)) AND (a.q1 = COALESCE(b.hundred, 123))) -> Seq Scan on int8_tbl a -> Index Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 = a.q1) @@ -4064,8 +4064,8 @@ explain (costs off) select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44; - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Nested Loop Left Join -> Nested Loop Left Join Filter: (COALESCE(b.twothousand, a.twothousand) = 44) @@ -4076,7 +4076,7 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) -> Bitmap Index Scan on tenk1_thous_tenthous Index Cond: (thousand = a.unique1) -> Index Scan using tenk1_unique2 on tenk1 c - Index Cond: ((unique2 = COALESCE(b.twothousand, a.twothousand)) AND (unique2 = 44)) + Index Cond: (unique2 = 44) (11 rows) select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) @@ -4561,7 +4561,6 @@ where tt1.f1 = ss1.c0; Output: tt4.f1 -> Nested Loop Left Join Output: tt4.f1 - Join Filter: (tt3.f1 = tt4.f1) -> Seq Scan on public.text_tbl tt3 Output: tt3.f1 Filter: (tt3.f1 = 'foo'::text) @@ -4579,7 +4578,7 @@ where tt1.f1 = ss1.c0; Output: (tt4.f1) -> Seq Scan on public.text_tbl tt5 Output: tt4.f1 -(33 rows) +(32 rows) select 1 from text_tbl as tt1 @@ -4686,24 +4685,22 @@ explain (costs off) QUERY PLAN ------------------------------------------------- Nested Loop Left Join - Join Filter: (a.f1 = b.unique2) -> Seq Scan on int4_tbl a Filter: (f1 = 0) -> Index Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 = 0) -(6 rows) +(5 rows) explain (costs off) select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42; QUERY PLAN ------------------------------------------------- Merge Full Join - Merge Cond: (a.unique2 = b.unique2) -> Index Scan using tenk1_unique2 on tenk1 a Index Cond: (unique2 = 42) -> Index Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 = 42) -(6 rows) +(5 rows) -- -- test that quals attached to an outer join have correct semantics, @@ -4791,12 +4788,11 @@ select a.unique1, b.unique2 QUERY PLAN ---------------------------------------------------- Nested Loop Left Join - Join Filter: (a.unique1 = b.unique2) -> Index Only Scan using onek_unique1 on onek a Index Cond: (unique1 = 42) -> Index Only Scan using onek_unique2 on onek b Index Cond: (unique2 = 42) -(6 rows) +(5 rows) select a.unique1, b.unique2 from onek a full join onek b on a.unique1 = b.unique2 @@ -4813,12 +4809,11 @@ select a.unique1, b.unique2 QUERY PLAN ---------------------------------------------------- Nested Loop Left Join - Join Filter: (a.unique1 = b.unique2) -> Index Only Scan using onek_unique2 on onek b Index Cond: (unique2 = 43) -> Index Only Scan using onek_unique1 on onek a Index Cond: (unique1 = 43) -(6 rows) +(5 rows) select a.unique1, b.unique2 from onek a full join onek b on a.unique1 = b.unique2 @@ -6202,12 +6197,13 @@ select * from int8_tbl i8 left join lateral -------------------------------------- Nested Loop Left Join Output: i8.q1, i8.q2, f1, (i8.q2) + Join Filter: false -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Result Output: f1, i8.q2 One-Time Filter: false -(7 rows) +(8 rows) explain (verbose, costs off) select * from int8_tbl i8 left join lateral diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 7035e4a4c42..2bb24dbfcdc 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -411,8 +411,8 @@ select * from int8_tbl i1 left join (int8_tbl i2 join order by 1, 2; -- --- regression test: check a case where join_clause_is_movable_into() gives --- an imprecise result, causing an assertion failure +-- regression test: check a case where join_clause_is_movable_into() +-- used to give an imprecise result, causing an assertion failure -- select count(*) from |