summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2023-01-30 13:44:36 -0500
committerTom Lane <tgl@sss.pgh.pa.us>2023-01-30 13:44:36 -0500
commitb448f1c8d83f8b65e2f0080c556ee21a7076da25 (patch)
treebc76c0506f01f224521b14304224598b8ba6699a /src/test
parent2489d76c4906f4461a364ca8ad7e0751ead8aa0d (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.out34
-rw-r--r--src/test/regress/sql/join.sql4
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