Hi! I found another example where the transformation worked incorrectly
and reconsidered the idea.
As for conversion of exists_sublink_to_ANY, we need to get the flattened
implicit-AND list of clauses and pull out the chunks of the WHERE clause
that belong to the parent query,
since we are called halfway through the parent's preprocess_expression()
and earlier steps of preprocess_expression() wouldn't get applied to the
pulled-up stuff unless we do them here.
We also do some processing for vars depending on which side the var is
on - if it's in a subquery, we only need to lower its level (varlevel)
because subquery will be flatted, while
for other vars that belong to the parent query, we need to do
preparation to pull up the sub-select into top range table.
For those expressions that we couldn't assign to either list, we define
newWhere and apply both cases.
--
Regards,
Alena Rybakina
Postgres Professional
From 9f42dc0bbe1899751892a67355fe447d917096a4 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybak...@postgrespro.ru>
Date: Mon, 27 Jan 2025 22:31:47 +0300
Subject: [PATCH] Add appling the pull-up transformation for expressions. We
examine all expressions and need to increase the varno of vars which belong
to subquery, because they will be belonged to another part of the query. But
we need to decrease the sublevel number for vars which belong the upper level
of the query because the subquery will be plat, so they will be the same
sublevel number. That's why we separate such vars on leftargs and rightargs
lists. leftargs list contain vars belonged to upper level part of query,
rightargs contain vars belonged to subquery. newWhere list contain
expressions for which we can't determine what part of query they belonged to,
probably constants from subquery.
---
src/backend/optimizer/plan/subselect.c | 259 ++++++++++++++----
src/test/regress/expected/partition_join.out | 12 +-
src/test/regress/expected/subselect.out | 232 ++++++++++++++++
src/test/regress/expected/updatable_views.out | 10 +-
src/test/regress/sql/subselect.sql | 99 +++++++
5 files changed, 550 insertions(+), 62 deletions(-)
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 8230cbea3c3..afa97cf3c5c 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1378,6 +1378,16 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
int varno;
Relids clause_varnos;
Relids upper_varnos;
+ List *testlist;
+ ListCell *lc;
+ List *newWhere = NIL;
+ List *opcollations;
+ List *opids;
+ List *rightargs;
+ List *leftargs;
+ List *all_clauses = NIL;
+ ListCell *rc, *oc, *cc;
+
Assert(sublink->subLinkType == EXISTS_SUBLINK);
@@ -1390,7 +1400,9 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
* get flattened into the parent query.
*/
if (subselect->cteList)
- return NULL;
+ {
+ return NULL;
+ }
/*
* Copy the subquery so we can modify it safely (see comments in
@@ -1405,35 +1417,171 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
* with noplace to evaluate the targetlist.
*/
if (!simplify_EXISTS_query(root, subselect))
- return NULL;
+ {
+ return NULL;
+ }
+
+ if (subselect->jointree->quals)
+ all_clauses = lappend(all_clauses, subselect->jointree->quals);
- /*
- * Separate out the WHERE clause. (We could theoretically also remove
- * top-level plain JOIN/ON clauses, but it's probably not worth the
- * trouble.)
- */
- whereClause = subselect->jointree->quals;
subselect->jointree->quals = NULL;
+ /* Gather all clauses in main list for the further consideration */
+ all_clauses = list_concat(all_clauses, subselect->jointree->fromlist);
+
+ leftargs = rightargs = opids = opcollations = newWhere = NIL;
+
/*
- * The rest of the sub-select must not refer to any Vars of the parent
- * query. (Vars of higher levels should be okay, though.)
+ * We will able to remove top-level plain JOIN/ON clauses if they are not outer join.
*/
- if (contain_vars_of_level((Node *) subselect, 1))
+ foreach (lc, all_clauses)
+ {
+ Node *je = ((Node *) lfirst(lc));
+ ListCell *lc1;
+
+ whereClause = copyObject(je);
+
+ if (IsA(whereClause, RangeTblRef))
+ continue;
+
+ if ((IsA(whereClause, JoinExpr) && ((JoinExpr *)whereClause)->jointype != JOIN_INNER))
+ continue;
+
+ if (IsA(whereClause, JoinExpr) && ((JoinExpr *)whereClause)->quals != NULL)
+ whereClause = ((JoinExpr *)whereClause)->quals;
+
+ /*
+ * We don't risk optimizing if the WHERE clause is volatile, either.
+ */
+ if (contain_volatile_functions(whereClause))
+ {
+ return NULL;
+ }
+
+ /*
+ * Clean up the WHERE clause by doing const-simplification etc on it.
+ * Aside from simplifying the processing we're about to do, this is
+ * important for being able to pull chunks of the WHERE clause up into the
+ * parent query. Since we are invoked partway through the parent's
+ * preprocess_expression() work, earlier steps of preprocess_expression()
+ * wouldn't get applied to the pulled-up stuff unless we do them here. For
+ * the parts of the WHERE clause that get put back into the child query,
+ * this work is partially duplicative, but it shouldn't hurt.
+ *
+ * Note: we do not run flatten_join_alias_vars. This is OK because any
+ * parent aliases were flattened already, and we're not going to pull any
+ * child Vars (of any description) into the parent.
+ *
+ * Note: passing the parent's root to eval_const_expressions is
+ * technically wrong, but we can get away with it since only the
+ * boundParams (if any) are used, and those would be the same in a
+ * subroot.
+ */
+ whereClause = eval_const_expressions(root, whereClause);
+ whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
+ whereClause = (Node *) make_ands_implicit((Expr *) whereClause);
+
+ foreach(lc1, (List *) whereClause)
+ {
+ OpExpr *expr = (OpExpr *) lfirst(lc1);
+
+ if (IsA(expr, OpExpr))
+ {
+
+ Node *leftarg = (Node *) linitial(expr->args);
+ Node *rightarg = (Node *) lsecond(expr->args);
+
+ leftarg =(Node *) eval_const_expressions(root, leftarg);
+ rightarg =(Node *) eval_const_expressions(root, rightarg);
+
+ /*
+ * We need to separate outer and inner vars.
+ * Since outer var will need to be level up only its
+ * its sublevel number yet inner var will need to be
+ * updated.
+ * Their varno will be increased due to elimination of
+ * subquery and consequently rebuilding query tree.
+ * Later expressions will be formed again and for this
+ * reason their operate id and collacations need to be
+ * saved too.
+ * newWhere list contains inner constant expressions.
+ */
+ if (contain_vars_of_level(leftarg, 1))
+ {
+ leftargs = lappend(leftargs, leftarg);
+ rightargs = lappend(rightargs, rightarg);
+ opids = lappend_oid(opids, expr->opno);
+ opcollations = lappend_oid(opcollations, expr->inputcollid);
+ continue;
+ }
+ else if (contain_vars_of_level(rightarg, 1))
+ {
+ expr->opno = get_commutator(expr->opno);
+ if (OidIsValid(expr->opno))
+ {
+ leftargs = lappend(leftargs, rightarg);
+ rightargs = lappend(rightargs, leftarg);
+ opids = lappend_oid(opids, expr->opno);
+ opcollations = lappend_oid(opcollations, expr->inputcollid);
+ continue;
+ }
+ /* If no commutator, no chance to optimize the WHERE clause */
+ Assert(0);
+ }
+ newWhere = lappend(newWhere, expr);
+ }
+ /* We only need to be sure that it contains some var which
+ * is acceptable for pull-up application.
+ */
+ else if (contain_vars_of_level((Node *) expr, 1))
+ newWhere = lappend(newWhere, expr);
+ else
+ return NULL;
+ }
+
+ /*
+ * In case of a successful attempt, replaces it with the correct condition.
+ * We were sure that inner relations are independent, so we confidently
+ * can replace their join condition on true.
+ * ToDo: operate connected inner join expressions.
+ */
+ if ((List *) whereClause != NIL && IsA(je, JoinExpr))
+ ((JoinExpr *)je)->quals = (Node *) makeConst(BOOLOID,
+ -1,
+ InvalidOid,
+ sizeof(bool),
+ (Datum) 1,
+ false,
+ true);
+ }
+
+ list_free(all_clauses);
+
+ /* We don't have any clauses for pull-up creation */
+ if (newWhere == NIL && leftargs== NIL)
+ {
return NULL;
+ }
/*
- * On the other hand, the WHERE clause must contain some Vars of the
- * parent query, else it's not gonna be a join.
+ * And there can't be any child Vars in the stuff we intend to pull up.
+ * (Note: we'd need to check for child Aggs too, except we know the child
+ * has no aggs at all because of simplify_EXISTS_query's check. The same
+ * goes for window functions.)
*/
- if (!contain_vars_of_level(whereClause, 1))
+ if (contain_vars_of_level((Node *) subselect, 1))
return NULL;
/*
- * We don't risk optimizing if the WHERE clause is volatile, either.
+ * Prepare to pull up the sub-select into top range table.
+ *
+ * We need to adjust all level-zero varnos in the subquery
+ * to account for the rtable merger.
*/
- if (contain_volatile_functions(whereClause))
- return NULL;
+ rtoffset = list_length(parse->rtable);
+ OffsetVarNodes((Node *) subselect, rtoffset, 0);
+ OffsetVarNodes((Node *)newWhere, rtoffset, 0);
+ OffsetVarNodes((Node *)rightargs, rtoffset, 0);
/*
* The subquery must have a nonempty jointree, but we can make it so.
@@ -1441,23 +1589,11 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
replace_empty_jointree(subselect);
/*
- * Prepare to pull up the sub-select into top range table.
- *
- * We rely here on the assumption that the outer query has no references
- * to the inner (necessarily true). Therefore this is a lot easier than
- * what pull_up_subqueries has to go through.
- *
- * In fact, it's even easier than what convert_ANY_sublink_to_join has to
- * do. The machinations of simplify_EXISTS_query ensured that there is
- * nothing interesting in the subquery except an rtable and jointree, and
- * even the jointree FromExpr no longer has quals. So we can just append
- * the rtable to our own and use the FromExpr in our jointree. But first,
- * adjust all level-zero varnos in the subquery to account for the rtable
- * merger.
+ * Also reject sublinks in the stuff we intend to pull up. (It might be
+ * possible to support this, but doesn't seem worth the complication.)
*/
- rtoffset = list_length(parse->rtable);
- OffsetVarNodes((Node *) subselect, rtoffset, 0);
- OffsetVarNodes(whereClause, rtoffset, 0);
+ if (contain_subplans((Node *) leftargs) || contain_subplans((Node *) rightargs))
+ return NULL;
/*
* Upper-level vars in subquery will now be one level closer to their
@@ -1465,31 +1601,38 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
* becomes level zero.
*/
IncrementVarSublevelsUp((Node *) subselect, -1, 1);
- IncrementVarSublevelsUp(whereClause, -1, 1);
+ IncrementVarSublevelsUp((Node *) leftargs, -1, 1);
+ IncrementVarSublevelsUp((Node *) newWhere, -1, 1);
/*
* Now that the WHERE clause is adjusted to match the parent query
* environment, we can easily identify all the level-zero rels it uses.
* The ones <= rtoffset belong to the upper query; the ones > rtoffset do
* not.
+ * XXX: Should we check newWhere?
*/
- clause_varnos = pull_varnos(root, whereClause);
- upper_varnos = NULL;
- varno = -1;
- while ((varno = bms_next_member(clause_varnos, varno)) >= 0)
+ if(leftargs != NIL)
{
- if (varno <= rtoffset)
- upper_varnos = bms_add_member(upper_varnos, varno);
- }
- bms_free(clause_varnos);
- Assert(!bms_is_empty(upper_varnos));
+ clause_varnos = pull_varnos(root, (Node *) leftargs);
+ upper_varnos = NULL;
+ varno = -1;
+ while ((varno = bms_next_member(clause_varnos, varno)) >= 0)
+ {
+ if (varno <= rtoffset)
+ upper_varnos = bms_add_member(upper_varnos, varno);
+ }
+ bms_free(clause_varnos);
- /*
- * Now that we've got the set of upper-level varnos, we can make the last
- * check: only available_rels can be referenced.
- */
- if (!bms_is_subset(upper_varnos, available_rels))
- return NULL;
+ Assert(!bms_is_empty(upper_varnos) || bms_is_empty(clause_varnos));
+
+
+ /*
+ * Now that we've got the set of upper-level varnos, we can make the last
+ * check: only available_rels can be referenced.
+ */
+ if (!bms_is_subset(upper_varnos, available_rels) && !bms_is_empty(clause_varnos))
+ return NULL;
+ }
/*
* Now we can attach the modified subquery rtable to the parent. This also
@@ -1498,6 +1641,22 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
CombineRangeTables(&parse->rtable, &parse->rteperminfos,
subselect->rtable, subselect->rteperminfos);
+ testlist = NIL;
+ forfour(lc, leftargs, rc, rightargs, oc, opids, cc, opcollations)
+ {
+ Node *leftarg = (Node *) lfirst(lc);
+ Node *rightarg = (Node *) lfirst(rc);
+ Oid opid = lfirst_oid(oc);
+ Oid opcollation = lfirst_oid(cc);
+
+ testlist = lappend(testlist,
+ (Node *) make_opclause(opid, BOOLOID, false,
+ (Expr *) leftarg, (Expr *) rightarg,
+ InvalidOid, opcollation));
+ }
+
+ testlist = list_concat(testlist, newWhere);
+
/*
* And finally, build the JoinExpr node.
*/
@@ -1512,7 +1671,7 @@ convert_EXISTS_sublink_to_join(PlannerInfo *root, SubLink *sublink,
result->rarg = (Node *) subselect->jointree;
result->usingClause = NIL;
result->join_using_alias = NULL;
- result->quals = whereClause;
+ result->quals = (Node *) make_ands_explicit(testlist);
result->alias = NULL;
result->rtindex = 0; /* we don't need an RTE for it */
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index af468682a2d..8cb4cda388e 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2492,8 +2492,8 @@ where not exists (select 1 from prtx2
-> Seq Scan on prtx1_1
Filter: ((a < 20) AND (c = 120))
-> Bitmap Heap Scan on prtx2_1
- Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
- Filter: (a = prtx1_1.a)
+ Recheck Cond: ((prtx1_1.b = b) AND (c = 123))
+ Filter: (prtx1_1.a = a)
-> BitmapAnd
-> Bitmap Index Scan on prtx2_1_b_idx
Index Cond: (b = prtx1_1.b)
@@ -2503,8 +2503,8 @@ where not exists (select 1 from prtx2
-> Seq Scan on prtx1_2
Filter: ((a < 20) AND (c = 120))
-> Bitmap Heap Scan on prtx2_2
- Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
- Filter: (a = prtx1_2.a)
+ Recheck Cond: ((prtx1_2.b = b) AND (c = 123))
+ Filter: (prtx1_2.a = a)
-> BitmapAnd
-> Bitmap Index Scan on prtx2_2_b_idx
Index Cond: (b = prtx1_2.b)
@@ -2534,7 +2534,7 @@ where not exists (select 1 from prtx2
Filter: ((a < 20) AND (c = 91))
-> Bitmap Heap Scan on prtx2_1
Recheck Cond: ((c = 99) OR (b = (prtx1_1.b + 1)))
- Filter: (a = prtx1_1.a)
+ Filter: (prtx1_1.a = a)
-> BitmapOr
-> Bitmap Index Scan on prtx2_1_c_idx
Index Cond: (c = 99)
@@ -2545,7 +2545,7 @@ where not exists (select 1 from prtx2
Filter: ((a < 20) AND (c = 91))
-> Bitmap Heap Scan on prtx2_2
Recheck Cond: ((c = 99) OR (b = (prtx1_2.b + 1)))
- Filter: (a = prtx1_2.a)
+ Filter: (prtx1_2.a = a)
-> BitmapOr
-> Bitmap Index Scan on prtx2_2_c_idx
Index Cond: (c = 99)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index ebc545e2461..cff5026887f 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -812,6 +812,238 @@ where exists (
from text_tbl ) ss
where road.name = ss.f1 );
rollback;
+-- Test case for exist sublink where we can consider some undependent expression
+-- with outer link
+--
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tb.id);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Semi Join (actual rows=2 loops=1)
+ -> Seq Scan on ta (actual rows=2 loops=1)
+ -> Nested Loop (actual rows=1 loops=2)
+ -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2)
+ Index Cond: (id = ta.id)
+ Heap Fetches: 2
+ -> Seq Scan on tc (actual rows=1 loops=2)
+(7 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tb.id);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Anti Join (actual rows=0 loops=1)
+ -> Seq Scan on ta (actual rows=2 loops=1)
+ -> Nested Loop (actual rows=1 loops=2)
+ -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2)
+ Index Cond: (id = ta.id)
+ Heap Fetches: 2
+ -> Seq Scan on tc (actual rows=1 loops=2)
+(7 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Semi Join (actual rows=2 loops=1)
+ -> Seq Scan on ta (actual rows=2 loops=1)
+ -> Nested Loop (actual rows=1 loops=2)
+ -> Index Only Scan using tc_pkey on tc (actual rows=1 loops=2)
+ Index Cond: (id = ta.id)
+ Heap Fetches: 2
+ -> Seq Scan on tb (actual rows=1 loops=2)
+(7 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tb.id);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Anti Join (actual rows=0 loops=1)
+ -> Seq Scan on ta (actual rows=2 loops=1)
+ -> Nested Loop (actual rows=1 loops=2)
+ -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2)
+ Index Cond: (id = ta.id)
+ Heap Fetches: 2
+ -> Seq Scan on tc (actual rows=1 loops=2)
+(7 rows)
+
+-- Join compound expression
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = tb.id);
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Right Semi Join (actual rows=2 loops=1)
+ Hash Cond: (tc.id = ta.id)
+ -> Hash Join (actual rows=2 loops=1)
+ Hash Cond: (tb.id = tc.id)
+ -> Seq Scan on tb (actual rows=4 loops=1)
+ -> Hash (actual rows=2 loops=1)
+ Buckets: 4096 Batches: 1 Memory Usage: 33kB
+ -> Seq Scan on tc (actual rows=2 loops=1)
+ -> Hash (actual rows=2 loops=1)
+ Buckets: 4096 Batches: 1 Memory Usage: 33kB
+ -> Seq Scan on ta (actual rows=2 loops=1)
+(11 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = tb.id);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Anti Join (actual rows=0 loops=1)
+ -> Seq Scan on ta (actual rows=2 loops=1)
+ -> Nested Loop (actual rows=1 loops=2)
+ -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2)
+ Index Cond: (id = ta.id)
+ Heap Fetches: 2
+ -> Index Only Scan using tc_pkey on tc (actual rows=1 loops=2)
+ Index Cond: (id = ta.id)
+ Heap Fetches: 2
+(9 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = tb.id);
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Right Semi Join (actual rows=2 loops=1)
+ Hash Cond: (tc.id = ta.id)
+ -> Hash Join (actual rows=2 loops=1)
+ Hash Cond: (tb.id = tc.id)
+ -> Seq Scan on tb (actual rows=4 loops=1)
+ -> Hash (actual rows=2 loops=1)
+ Buckets: 4096 Batches: 1 Memory Usage: 33kB
+ -> Seq Scan on tc (actual rows=2 loops=1)
+ -> Hash (actual rows=2 loops=1)
+ Buckets: 4096 Batches: 1 Memory Usage: 33kB
+ -> Seq Scan on ta (actual rows=2 loops=1)
+(11 rows)
+
+-- Compound expression with const type
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = 1);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Semi Join (actual rows=1 loops=1)
+ -> Index Only Scan using ta_pkey on ta (actual rows=1 loops=1)
+ Index Cond: (id = 1)
+ Heap Fetches: 1
+ -> Nested Loop (actual rows=1 loops=1)
+ -> Index Only Scan using tc_pkey on tc (actual rows=1 loops=1)
+ Index Cond: (id = 1)
+ Heap Fetches: 1
+ -> Seq Scan on tb (actual rows=1 loops=1)
+(9 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = 1);
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Right Anti Join (actual rows=1 loops=1)
+ Hash Cond: (tc.id = ta.id)
+ Join Filter: (ta.id = 1)
+ Rows Removed by Join Filter: 4
+ -> Nested Loop (actual rows=8 loops=1)
+ -> Seq Scan on tb (actual rows=4 loops=1)
+ -> Materialize (actual rows=2 loops=4)
+ Storage: Memory Maximum Storage: 17kB
+ -> Seq Scan on tc (actual rows=2 loops=1)
+ -> Hash (actual rows=2 loops=1)
+ Buckets: 4096 Batches: 1 Memory Usage: 33kB
+ -> Seq Scan on ta (actual rows=2 loops=1)
+(12 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON tb.id = 1 and
+ ta.id = 1);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Semi Join (actual rows=1 loops=1)
+ -> Index Only Scan using ta_pkey on ta (actual rows=1 loops=1)
+ Index Cond: (id = 1)
+ Heap Fetches: 1
+ -> Nested Loop (actual rows=1 loops=1)
+ -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=1)
+ Index Cond: (id = 1)
+ Heap Fetches: 1
+ -> Seq Scan on tc (actual rows=1 loops=1)
+(9 rows)
+
+-- Disabled pull up because it is applcapable for INNER JOIN connection
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ RIGHT JOIN tc
+ ON ta.id = tc.id);
+ QUERY PLAN
+------------------------------------------------------------
+ Seq Scan on ta (actual rows=2 loops=1)
+ Filter: EXISTS(SubPlan 1)
+ SubPlan 1
+ -> Nested Loop Left Join (actual rows=1 loops=2)
+ Join Filter: (ta.id = tc.id)
+ Rows Removed by Join Filter: 2
+ -> Seq Scan on tc (actual rows=1 loops=2)
+ -> Materialize (actual rows=2 loops=2)
+ Storage: Memory Maximum Storage: 17kB
+ -> Seq Scan on tb (actual rows=4 loops=1)
+(10 rows)
+
--
-- Test case for sublinks pushed down into subselects via join alias expansion
--
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 095df0a670c..d0762c1299e 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3177,14 +3177,12 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE)
Update on base_tbl
- InitPlan 1
- -> Index Only Scan using base_tbl_pkey on base_tbl t
- Index Cond: (id = 2)
- -> Result
- One-Time Filter: (InitPlan 1).col1
+ -> Nested Loop Semi Join
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (id = 2)
-(15 rows)
+ -> Index Scan using base_tbl_pkey on base_tbl t
+ Index Cond: (id = 2)
+(13 rows)
INSERT INTO rw_view1 VALUES (2, 'New row 2');
SELECT * FROM base_tbl;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 6ed3636a9e4..13d7066a823 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -439,6 +439,105 @@ where exists (
rollback;
+-- Test case for exist sublink where we can consider some undependent expression
+-- with outer link
+--
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tb.id);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tb.id);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tb.id);
+
+-- Join compound expression
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = tb.id);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = tb.id);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = tb.id);
+
+-- Compound expression with const type
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = 1);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE NOT EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON ta.id = tc.id and
+ ta.id = 1);
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ JOIN tc
+ ON tb.id = 1 and
+ ta.id = 1);
+-- Disabled pull up because it is applcapable for INNER JOIN connection
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
+ SELECT 1
+ FROM ta
+ WHERE EXISTS (SELECT 1
+ FROM tb
+ RIGHT JOIN tc
+ ON ta.id = tc.id);
+
--
-- Test case for sublinks pushed down into subselects via join alias expansion
--
--
2.34.1
