aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/optimizer/plan/analyzejoins.c147
-rw-r--r--src/test/regress/expected/join.out278
-rw-r--r--src/test/regress/sql/join.sql218
3 files changed, 337 insertions, 306 deletions
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 19d6d4f3aba..4978758f8e1 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -35,15 +35,6 @@
#include "utils/lsyscache.h"
/*
- * The context for replace_varno_walker() containing source and target relids.
- */
-typedef struct
-{
- int from;
- int to;
-} ReplaceVarnoContext;
-
-/*
* The struct containing self-join candidate. Used to find duplicate reloids.
*/
typedef struct
@@ -75,13 +66,11 @@ static bool is_innerrel_unique_for(PlannerInfo *root,
JoinType jointype,
List *restrictlist,
List **extra_clauses);
-static Bitmapset *replace_relid(Relids relids, int oldId, int newId);
static void replace_varno(Node *node, int from, int to);
-static bool replace_varno_walker(Node *node, ReplaceVarnoContext *ctx);
+static Bitmapset *replace_relid(Relids relids, int oldId, int newId);
static int self_join_candidates_cmp(const void *a, const void *b);
-
/*
* remove_useless_joins
* Check for relations that don't actually need to be joined at all,
@@ -367,7 +356,8 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
ListCell *l;
/*
- * Remove references to the rel from other baserels' attr_needed arrays.
+ * Remove references to the rel from other baserels' attr_needed arrays
+ * and lateral_vars lists.
*/
for (rti = 1; rti < root->simple_rel_array_size; rti++)
{
@@ -394,35 +384,8 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
replace_relid(otherrel->attr_needed[attroff], ojrelid, subst);
}
- /* Update lateral references. */
- if (root->hasLateralRTEs)
- {
- RangeTblEntry *rte = root->simple_rte_array[rti];
- ReplaceVarnoContext ctx = {.from = relid,.to = subst};
-
- if (rte->lateral)
- {
- replace_varno((Node *) otherrel->lateral_vars, relid, subst);
-
- /*
- * Although we pass root->parse through cleanup procedure, but
- * parse->rtable and rte contains refs to different copies of
- * the subquery.
- */
- if (otherrel->rtekind == RTE_SUBQUERY)
- query_tree_walker(rte->subquery, replace_varno_walker, &ctx,
- QTW_EXAMINE_SORTGROUP);
-#ifdef USE_ASSERT_CHECKING
- /* Just check possibly hidden non-replaced relids */
- Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->tablesample)));
- Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->functions)));
- Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->tablefunc)));
- Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->values_lists)));
-#endif
- }
- }
-
-
+ /* Update lateral_vars list. */
+ replace_varno((Node *) otherrel->lateral_vars, relid, subst);
}
/*
@@ -1462,35 +1425,32 @@ is_innerrel_unique_for(PlannerInfo *root,
}
/*
- * Replace each occurrence of removing relid with the keeping one
+ * replace_varno - find in the given tree any Vars, PlaceHolderVar, and Relids
+ * that reference the removing relid, and change them to the reference to
+ * the replacement relid.
+ *
+ * NOTE: although this has the form of a walker, we cheat and modify the
+ * nodes in-place.
*/
-static void
-replace_varno(Node *node, int from, int to)
-{
- ReplaceVarnoContext ctx;
-
- if (to <= 0)
- return;
- ctx.from = from;
- ctx.to = to;
- replace_varno_walker(node, &ctx);
-}
+typedef struct
+{
+ int from;
+ int to;
+ int sublevels_up;
+} ReplaceVarnoContext;
-/*
- * Walker function for replace_varno()
- */
static bool
replace_varno_walker(Node *node, ReplaceVarnoContext *ctx)
{
if (node == NULL)
return false;
-
if (IsA(node, Var))
{
Var *var = (Var *) node;
- if (var->varno == ctx->from)
+ if (var->varno == ctx->from &&
+ var->varlevelsup == ctx->sublevels_up)
{
var->varno = ctx->to;
var->varnosyn = ctx->to;
@@ -1501,11 +1461,29 @@ replace_varno_walker(Node *node, ReplaceVarnoContext *ctx)
{
PlaceHolderVar *phv = (PlaceHolderVar *) node;
- phv->phrels = replace_relid(phv->phrels, ctx->from, ctx->to);
- phv->phnullingrels = replace_relid(phv->phnullingrels, ctx->from, ctx->to);
+ if (phv->phlevelsup == ctx->sublevels_up)
+ {
+ phv->phrels =
+ replace_relid(phv->phrels, ctx->from, ctx->to);
+ phv->phnullingrels =
+ replace_relid(phv->phnullingrels, ctx->from, ctx->to);
+ }
/* fall through to recurse into the placeholder's expression */
}
+ else if (IsA(node, Query))
+ {
+ /* Recurse into subselects */
+ bool result;
+
+ ctx->sublevels_up++;
+ result = query_tree_walker((Query *) node,
+ replace_varno_walker,
+ (void *) ctx,
+ QTW_EXAMINE_SORTGROUP);
+ ctx->sublevels_up--;
+ return result;
+ }
else if (IsA(node, RestrictInfo))
{
RestrictInfo *rinfo = (RestrictInfo *) node;
@@ -1517,18 +1495,24 @@ replace_varno_walker(Node *node, ReplaceVarnoContext *ctx)
{
replace_varno((Node *) rinfo->clause, ctx->from, ctx->to);
replace_varno((Node *) rinfo->orclause, ctx->from, ctx->to);
- rinfo->clause_relids = replace_relid(rinfo->clause_relids, ctx->from, ctx->to);
- rinfo->left_relids = replace_relid(rinfo->left_relids, ctx->from, ctx->to);
- rinfo->right_relids = replace_relid(rinfo->right_relids, ctx->from, ctx->to);
+ rinfo->clause_relids =
+ replace_relid(rinfo->clause_relids, ctx->from, ctx->to);
+ rinfo->left_relids =
+ replace_relid(rinfo->left_relids, ctx->from, ctx->to);
+ rinfo->right_relids =
+ replace_relid(rinfo->right_relids, ctx->from, ctx->to);
}
if (is_req_equal)
rinfo->required_relids = rinfo->clause_relids;
else
- rinfo->required_relids = replace_relid(rinfo->required_relids, ctx->from, ctx->to);
+ rinfo->required_relids =
+ replace_relid(rinfo->required_relids, ctx->from, ctx->to);
- rinfo->outer_relids = replace_relid(rinfo->outer_relids, ctx->from, ctx->to);
- rinfo->incompatible_relids = replace_relid(rinfo->incompatible_relids, ctx->from, ctx->to);
+ rinfo->outer_relids =
+ replace_relid(rinfo->outer_relids, ctx->from, ctx->to);
+ rinfo->incompatible_relids =
+ replace_relid(rinfo->incompatible_relids, ctx->from, ctx->to);
if (rinfo->mergeopfamilies &&
bms_get_singleton_member(rinfo->clause_relids, &relid) &&
@@ -1556,7 +1540,30 @@ replace_varno_walker(Node *node, ReplaceVarnoContext *ctx)
return false;
}
- return expression_tree_walker(node, replace_varno_walker, (void *) ctx);
+
+ return expression_tree_walker(node, replace_varno_walker,
+ (void *) ctx);
+}
+
+static void
+replace_varno(Node *node, int from, int to)
+{
+ ReplaceVarnoContext ctx;
+
+ if (to <= 0)
+ return;
+
+ ctx.from = from;
+ ctx.to = to;
+ ctx.sublevels_up = 0;
+
+ /*
+ * Must be prepared to start with a Query or a bare expression tree.
+ */
+ query_or_expression_tree_walker(node,
+ replace_varno_walker,
+ (void *) &ctx,
+ QTW_EXAMINE_SORTGROUP);
}
/*
@@ -1748,7 +1755,6 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
int i;
List *jinfo_candidates = NIL;
List *binfo_candidates = NIL;
- ReplaceVarnoContext ctx = {.from = toRemove->relid,.to = toKeep->relid};
Assert(toKeep->relid != -1);
@@ -1925,8 +1931,7 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
}
/* Replace varno in all the query structures */
- query_tree_walker(root->parse, replace_varno_walker, &ctx,
- QTW_EXAMINE_SORTGROUP);
+ replace_varno((Node *) root->parse, toRemove->relid, toKeep->relid);
/* See remove_self_joins_one_group() */
Assert(root->parse->resultRelation != toRemove->relid);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index d560a4a6b9e..96054000218 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6187,14 +6187,16 @@ select p.* from sj p, sj q where q.a = p.a and q.b = q.a - 1;
explain (costs off)
select * from sj p
where exists (select * from sj q
- where q.a = p.a and q.b < 10);
+ where q.a = p.a and q.b < 10);
QUERY PLAN
------------------------------------------
Seq Scan on sj q
Filter: ((a IS NOT NULL) AND (b < 10))
(2 rows)
-select * from sj p where exists (select * from sj q where q.a = p.a and q.b < 10);
+select * from sj p
+where exists (select * from sj q
+ where q.a = p.a and q.b < 10);
a | b | c
---+---+---
2 | 1 | 1
@@ -6216,8 +6218,8 @@ select * from sj t1, sj t2 where t1.a = t2.c and t1.b is not null;
-- Degenerated case.
explain (costs off)
select * from
- (select a as x from sj where false) as q1,
- (select a as y from sj where false) as q2
+ (select a as x from sj where false) as q1,
+ (select a as y from sj where false) as q2
where q1.x = q2.y;
QUERY PLAN
--------------------------
@@ -6241,8 +6243,8 @@ select * from sj t1, sj t2 where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a;
explain (costs off)
select * from sj t1, sj t2, sj t3
-where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a
- and t1.b = t3.b and t3.b = t3.a;
+where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a and
+ t1.b = t3.b and t3.b = t3.a;
QUERY PLAN
------------------------------------
Nested Loop
@@ -6262,8 +6264,10 @@ where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a
-- the equivalence class is different from the first one, and we can
-- test the non-ec code path.
explain (costs off)
-select * from sj t1 join sj t2 on t1.a = t2.a and t1.b = t2.b
- join sj t3 on t2.a = t3.a and t2.b + 1 = t3.b + 1;
+select *
+from sj t1
+ join sj t2 on t1.a = t2.a and t1.b = t2.b
+ join sj t3 on t2.a = t3.a and t2.b + 1 = t3.b + 1;
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on sj t3
@@ -6314,13 +6318,13 @@ left join int8_tbl z on y.a = z.q1;
(6 rows)
explain (costs off)
-SELECT * FROM (
- SELECT t1.*, t2.a AS ax FROM sj t1 JOIN sj t2
- ON (t1.a = t2.a AND t1.c*t1.c = t2.c+2 AND t2.b IS NULL)
-) AS q1
-LEFT JOIN
- (SELECT t3.* FROM sj t3, sj t4 WHERE t3.c = t4.c) AS q2
-ON q1.ax = q2.a;
+select * from (
+ select t1.*, t2.a as ax from sj t1 join sj t2
+ on (t1.a = t2.a and t1.c * t1.c = t2.c + 2 and t2.b is null)
+) as q1
+left join
+ (select t3.* from sj t3, sj t4 where t3.c = t4.c) as q2
+on q1.ax = q2.a;
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop Left Join
@@ -6349,49 +6353,52 @@ on true;
-> Seq Scan on int8_tbl y
(7 rows)
--- Test processing target lists in lateral subqueries
+-- Test that references to the removed rel in lateral subqueries are replaced
+-- correctly after join removal
explain (verbose, costs off)
-SELECT t3.a FROM sj t1, sj t2,
-LATERAL (SELECT t1.a WHERE t1.a <> 1
- GROUP BY (t1.a) HAVING t1.a > 0 ORDER BY t1.a LIMIT 1) t3,
-LATERAL (SELECT t1.a,t3.a WHERE t1.a <> t3.a+t2.a
- GROUP BY (t3.a) HAVING t1.a > t3.a*t3.a+t2.a/t1.a LIMIT 2) t4,
-LATERAL (SELECT * FROM sj TABLESAMPLE bernoulli(t1.a/t2.a)
- REPEATABLE (t1.a+t2.a)) t5,
-LATERAL generate_series(1, t1.a + t2.a) AS t6
-WHERE t1.a = t2.a;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
+select t3.a from sj t1
+ join sj t2 on t1.a = t2.a
+ join lateral (select t1.a offset 0) t3 on true;
+ QUERY PLAN
+------------------------------------
Nested Loop
Output: (t2.a)
- -> Nested Loop
- Output: t2.a, (t2.a)
- -> Nested Loop
- Output: t2.a, (t2.a)
- -> Nested Loop
- Output: t2.a, (t2.a)
- -> Seq Scan on public.sj t2
- Output: t2.a, t2.b, t2.c
- Filter: (t2.a IS NOT NULL)
- -> Limit
- Output: (t2.a)
- -> Group
- Output: t2.a
- -> Result
- One-Time Filter: ((t2.a <> 1) AND (t2.a > 0))
- -> Limit
- Output: NULL::integer, ((t2.a))
- -> Group
- Output: NULL::integer, (t2.a)
- -> Result
- One-Time Filter: ((t2.a <> ((t2.a) + t2.a)) AND (t2.a > (((t2.a) * (t2.a)) + (t2.a / t2.a))))
- -> Sample Scan on public.sj
- Output: sj.a, sj.b, sj.c
- Sampling: bernoulli ((t2.a / t2.a)) REPEATABLE ((t2.a + t2.a))
- -> Function Scan on pg_catalog.generate_series t6
- Output: t6.t6
- Function Call: generate_series(1, (t2.a + t2.a))
-(29 rows)
+ -> Seq Scan on public.sj t2
+ Output: t2.a, t2.b, t2.c
+ Filter: (t2.a IS NOT NULL)
+ -> Result
+ Output: t2.a
+(7 rows)
+
+explain (verbose, costs off)
+select t3.a from sj t1
+ join sj t2 on t1.a = t2.a
+ join lateral (select * from (select t1.a offset 0) offset 0) t3 on true;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ Output: (t2.a)
+ -> Seq Scan on public.sj t2
+ Output: t2.a, t2.b, t2.c
+ Filter: (t2.a IS NOT NULL)
+ -> Result
+ Output: t2.a
+(7 rows)
+
+explain (verbose, costs off)
+select t4.a from sj t1
+ join sj t2 on t1.a = t2.a
+ join lateral (select t3.a from sj t3, (select t1.a) offset 0) t4 on true;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ Output: t3.a
+ -> Seq Scan on public.sj t2
+ Output: t2.a, t2.b, t2.c
+ Filter: (t2.a IS NOT NULL)
+ -> Seq Scan on public.sj t3
+ Output: t3.a
+(7 rows)
-- Check updating of Lateral links from top-level query to the removing relation
explain (COSTS OFF)
@@ -6412,13 +6419,14 @@ SELECT * FROM pg_am am WHERE am.amname IN (
(6 rows)
--
--- SJR corner case: uniqueness of an inner is [partially] derived from
+-- SJE corner case: uniqueness of an inner is [partially] derived from
-- baserestrictinfo clauses.
--- XXX: We really should allow SJR for these corner cases?
+-- XXX: We really should allow SJE for these corner cases?
--
INSERT INTO sj VALUES (3, 1, 3);
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
QUERY PLAN
------------------------------
Nested Loop
@@ -6429,92 +6437,87 @@ explain (costs off) -- Don't remove SJ
Filter: (a = 3)
(6 rows)
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; -- Return one row
+-- Return one row
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
a | b | c | a | b | c
---+---+---+---+---+---
2 | 1 | 1 | 3 | 1 | 3
(1 row)
-explain (costs off) -- Remove SJ, define uniqueness by a constant
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
+-- Remove SJ, define uniqueness by a constant
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
QUERY PLAN
-----------------------------------------
Seq Scan on sj j2
Filter: ((b IS NOT NULL) AND (a = 2))
(2 rows)
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; -- Return one row
+-- Return one row
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
a | b | c | a | b | c
---+---+---+---+---+---
2 | 1 | 1 | 2 | 1 | 1
(1 row)
+-- Remove SJ, define uniqueness by a constant expression
EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND j1.a = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a
-; -- Remove SJ, define uniqueness by a constant expression
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on sj j2
Filter: ((b IS NOT NULL) AND (a = (((EXTRACT(dow FROM CURRENT_TIMESTAMP(0)) / '15'::numeric) + '3'::numeric))::integer))
(2 rows)
+-- Return one row
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND j1.a = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a
-; -- Return one row
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a;
a | b | c | a | b | c
---+---+---+---+---+---
3 | 1 | 3 | 3 | 1 | 3
(1 row)
-explain (costs off) -- Remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
+-- Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
QUERY PLAN
-----------------------------------------
Seq Scan on sj j2
Filter: ((b IS NOT NULL) AND (a = 1))
(2 rows)
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; -- Return no rows
+-- Return no rows
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
a | b | c | a | b | c
---+---+---+---+---+---
(0 rows)
-explain (costs off) -- Shuffle a clause. Remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
+-- Shuffle a clause. Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
QUERY PLAN
-----------------------------------------
Seq Scan on sj j2
Filter: ((b IS NOT NULL) AND (a = 1))
(2 rows)
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; -- Return no rows
+-- Return no rows
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
a | b | c | a | b | c
---+---+---+---+---+---
(0 rows)
-- SJE Corner case: a 'a.x=a.x' clause, have replaced with 'a.x IS NOT NULL'
-- after SJ elimination it shouldn't be a mergejoinable clause.
-SELECT t4.*
-FROM (SELECT t1.*, t2.a AS a1 FROM sj t1, sj t2 WHERE t1.b = t2.b) AS t3
-JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42;
- a | b | c
----+---+---
-(0 rows)
-
EXPLAIN (COSTS OFF)
SELECT t4.*
FROM (SELECT t1.*, t2.a AS a1 FROM sj t1, sj t2 WHERE t1.b = t2.b) AS t3
-JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42
-; -- SJs must be removed.
+JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42;
QUERY PLAN
---------------------------------
Nested Loop
@@ -6525,10 +6528,18 @@ JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42
Filter: (a IS NOT NULL)
(6 rows)
+SELECT t4.*
+FROM (SELECT t1.*, t2.a AS a1 FROM sj t1, sj t2 WHERE t1.b = t2.b) AS t3
+JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42;
+ a | b | c
+---+---+---
+(0 rows)
+
-- Functional index
CREATE UNIQUE INDEX sj_fn_idx ON sj((a * a));
-explain (costs off) -- Remove SJ
- SELECT * FROM sj j1, sj j2
+-- Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 1;
QUERY PLAN
-----------------------------------------------
@@ -6536,8 +6547,9 @@ explain (costs off) -- Remove SJ
Filter: ((b IS NOT NULL) AND ((a * a) = 1))
(2 rows)
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 2;
QUERY PLAN
-------------------------------
@@ -6549,48 +6561,48 @@ explain (costs off) -- Don't remove SJ
Filter: ((a * a) = 2)
(6 rows)
+-- Restriction contains expressions in both sides, Remove SJ.
EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND (j1.a*j1.a) = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a)
-; -- Restriction contains expressions in both sides, Remove SJ.
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on sj j2
Filter: ((b IS NOT NULL) AND ((a * a) = (((EXTRACT(dow FROM CURRENT_TIMESTAMP(0)) / '15'::numeric) + '3'::numeric))::integer))
(2 rows)
+-- Empty set of rows should be returned
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND (j1.a*j1.a) = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a)
-; -- Empty set of rows should be returned
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a);
a | b | c | a | b | c
---+---+---+---+---+---
(0 rows)
+-- Restriction contains volatile function - disable SJE feature.
EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
- AND (j1.a*j1.a) = (random()/3 + 3)::int
- AND (random()/3 + 3)::int = (j2.a*j2.a)
-; -- Restriction contains volatile function - disable SJR feature.
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ AND (j1.a*j1.c/3) = (random()/3 + 3)::int
+ AND (random()/3 + 3)::int = (j2.a*j2.c/3);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Nested Loop
Join Filter: (j1.b = j2.b)
-> Seq Scan on sj j1
- Filter: ((a * a) = (((random() / '3'::double precision) + '3'::double precision))::integer)
+ Filter: (((a * c) / 3) = (((random() / '3'::double precision) + '3'::double precision))::integer)
-> Seq Scan on sj j2
- Filter: ((((random() / '3'::double precision) + '3'::double precision))::integer = (a * a))
+ Filter: ((((random() / '3'::double precision) + '3'::double precision))::integer = ((a * c) / 3))
(6 rows)
+-- Return one row
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND (j1.a*j1.c/3) = (random()/3 + 3)::int
- AND (random()/3 + 3)::int = (j2.a*j2.c/3)
-; -- Return one row
+ AND (random()/3 + 3)::int = (j2.a*j2.c/3);
a | b | c | a | b | c
---+---+---+---+---+---
3 | 1 | 3 | 3 | 1 | 3
@@ -6598,8 +6610,9 @@ WHERE j1.b = j2.b
-- Multiple filters
CREATE UNIQUE INDEX sj_temp_idx1 ON sj(a,b,c);
-explain (costs off) -- Remove SJ
- SELECT * FROM sj j1, sj j2
+-- Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND j1.a = 2 AND j1.c = 3 AND j2.a = 2 AND 3 = j2.c;
QUERY PLAN
-----------------------------------------------------
@@ -6607,7 +6620,8 @@ explain (costs off) -- Remove SJ
Filter: ((b IS NOT NULL) AND (a = 2) AND (c = 3))
(2 rows)
-explain (costs off) -- Don't remove SJ
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND 2 = j1.a AND j1.c = 3 AND j2.a = 1 AND 3 = j2.c;
QUERY PLAN
@@ -6621,8 +6635,9 @@ explain (costs off) -- Don't remove SJ
(6 rows)
CREATE UNIQUE INDEX sj_temp_idx ON sj(a,b);
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2;
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2;
QUERY PLAN
------------------------------
Nested Loop
@@ -6632,8 +6647,9 @@ explain (costs off) -- Don't remove SJ
-> Seq Scan on sj j2
(5 rows)
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a;
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a;
QUERY PLAN
------------------------------
Nested Loop
@@ -6643,8 +6659,9 @@ explain (costs off) -- Don't remove SJ
-> Seq Scan on sj j1
(5 rows)
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1);
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1);
QUERY PLAN
---------------------------------------------------------------
Nested Loop
@@ -6658,7 +6675,7 @@ DROP INDEX sj_fn_idx, sj_temp_idx1, sj_temp_idx;
-- Test that OR predicated are updated correctly after join removal
CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT);
CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag);
-explain (costs off)
+EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM tab_with_flag
WHERE
(is_flag IS NULL OR is_flag = 0)
@@ -6787,7 +6804,7 @@ reset join_collapse_limit;
reset enable_seqscan;
-- Check that clauses from the join filter list is not lost on the self-join removal
CREATE TABLE emp1 (id SERIAL PRIMARY KEY NOT NULL, code int);
-explain (verbose, costs off)
+EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
QUERY PLAN
------------------------------------------
@@ -6799,7 +6816,7 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
-- Shuffle self-joined relations. Only in the case of iterative deletion
-- attempts explains of these queries will be identical.
CREATE UNIQUE INDEX ON emp1((id*id));
-explain (costs off)
+EXPLAIN (COSTS OFF)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id;
QUERY PLAN
@@ -6809,7 +6826,7 @@ WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id;
Filter: ((id * id) IS NOT NULL)
(3 rows)
-explain (costs off)
+EXPLAIN (COSTS OFF)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id;
QUERY PLAN
@@ -6819,7 +6836,7 @@ WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id;
Filter: ((id * id) IS NOT NULL)
(3 rows)
-explain (costs off)
+EXPLAIN (COSTS OFF)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id;
QUERY PLAN
@@ -6830,7 +6847,7 @@ WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id;
(3 rows)
-- Check the usage of a parse tree by the set operations (bug #18170)
-explain (costs off)
+EXPLAIN (COSTS OFF)
SELECT c1.code FROM emp1 c1 LEFT JOIN emp1 c2 ON c1.id = c2.id
WHERE c2.id IS NOT NULL
EXCEPT ALL
@@ -6928,7 +6945,7 @@ select * from emp1 t1
(5 rows)
-- Check that SJE doesn't replace the target relation
-explain (costs off)
+EXPLAIN (COSTS OFF)
WITH t1 AS (SELECT * FROM emp1)
UPDATE emp1 SET code = t1.code + 1 FROM t1
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
@@ -7077,7 +7094,7 @@ where t1.b = t2.b and t2.a = 3 and t1.a = 3
(3 rows)
-- Join qual isn't mergejoinable, but inner is unique.
-explain (COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a AND n2.a = 1;
QUERY PLAN
-------------------------------
@@ -7088,9 +7105,9 @@ SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a AND n2.a = 1;
-> Seq Scan on sj n1
(5 rows)
-explain (COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT * FROM
- (SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
+(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
QUERY PLAN
-------------------------------
@@ -7103,20 +7120,6 @@ WHERE q0.a = 1;
-> Seq Scan on sj n1
(7 rows)
---
----- Only one side is unique
---select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1;
---select * from sl t1, sl t2 where t1.a = t2.a and t2.b = 1;
---
----- Several uniques indexes match, and we select a different one
----- for each side, so the join is not removed
---create table sm(a int unique, b int unique, c int unique);
---explain (costs off)
---select * from sm m, sm n where m.a = n.b and m.c = n.c;
---explain (costs off)
---select * from sm m, sm n where m.a = n.c and m.b = n.b;
---explain (costs off)
---select * from sm m, sm n where m.c = n.b and m.a = n.a;
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
@@ -7175,7 +7178,8 @@ ON sj_t1.id = _t2t3t4.id;
--
-- Test RowMarks-related code
--
-EXPLAIN (COSTS OFF) -- Both sides have explicit LockRows marks
+-- Both sides have explicit LockRows marks
+EXPLAIN (COSTS OFF)
SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
QUERY PLAN
---------------------------------
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 9341f80bbc4..c4c6c7b8ba2 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2343,8 +2343,10 @@ select p.* from sj p, sj q where q.a = p.a and q.b = q.a - 1;
explain (costs off)
select * from sj p
where exists (select * from sj q
- where q.a = p.a and q.b < 10);
-select * from sj p where exists (select * from sj q where q.a = p.a and q.b < 10);
+ where q.a = p.a and q.b < 10);
+select * from sj p
+where exists (select * from sj q
+ where q.a = p.a and q.b < 10);
-- Don't remove self-join for the case of equality of two different unique columns.
explain (costs off)
@@ -2353,8 +2355,8 @@ select * from sj t1, sj t2 where t1.a = t2.c and t1.b is not null;
-- Degenerated case.
explain (costs off)
select * from
- (select a as x from sj where false) as q1,
- (select a as y from sj where false) as q2
+ (select a as x from sj where false) as q1,
+ (select a as y from sj where false) as q2
where q1.x = q2.y;
-- We can't use a cross-EC generated self join qual because of current logic of
@@ -2363,16 +2365,18 @@ explain (costs off)
select * from sj t1, sj t2 where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a;
explain (costs off)
select * from sj t1, sj t2, sj t3
-where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a
- and t1.b = t3.b and t3.b = t3.a;
+where t1.a = t1.b and t1.b = t2.b and t2.b = t2.a and
+ t1.b = t3.b and t3.b = t3.a;
-- Double self-join removal.
-- Use a condition on "b + 1", not on "b", for the second join, so that
-- the equivalence class is different from the first one, and we can
-- test the non-ec code path.
explain (costs off)
-select * from sj t1 join sj t2 on t1.a = t2.a and t1.b = t2.b
- join sj t3 on t2.a = t3.a and t2.b + 1 = t3.b + 1;
+select *
+from sj t1
+ join sj t2 on t1.a = t2.a and t1.b = t2.b
+ join sj t3 on t2.a = t3.a and t2.b + 1 = t3.b + 1;
-- subselect that references the removed relation
explain (costs off)
@@ -2390,13 +2394,13 @@ select * from sj x join sj y on x.a = y.a
left join int8_tbl z on y.a = z.q1;
explain (costs off)
-SELECT * FROM (
- SELECT t1.*, t2.a AS ax FROM sj t1 JOIN sj t2
- ON (t1.a = t2.a AND t1.c*t1.c = t2.c+2 AND t2.b IS NULL)
-) AS q1
-LEFT JOIN
- (SELECT t3.* FROM sj t3, sj t4 WHERE t3.c = t4.c) AS q2
-ON q1.ax = q2.a;
+select * from (
+ select t1.*, t2.a as ax from sj t1 join sj t2
+ on (t1.a = t2.a and t1.c * t1.c = t2.c + 2 and t2.b is null)
+) as q1
+left join
+ (select t3.* from sj t3, sj t4 where t3.c = t4.c) as q2
+on q1.ax = q2.a;
-- Test that placeholders are updated correctly after join removal
explain (costs off)
@@ -2406,17 +2410,22 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
on true) z
on true;
--- Test processing target lists in lateral subqueries
+-- Test that references to the removed rel in lateral subqueries are replaced
+-- correctly after join removal
explain (verbose, costs off)
-SELECT t3.a FROM sj t1, sj t2,
-LATERAL (SELECT t1.a WHERE t1.a <> 1
- GROUP BY (t1.a) HAVING t1.a > 0 ORDER BY t1.a LIMIT 1) t3,
-LATERAL (SELECT t1.a,t3.a WHERE t1.a <> t3.a+t2.a
- GROUP BY (t3.a) HAVING t1.a > t3.a*t3.a+t2.a/t1.a LIMIT 2) t4,
-LATERAL (SELECT * FROM sj TABLESAMPLE bernoulli(t1.a/t2.a)
- REPEATABLE (t1.a+t2.a)) t5,
-LATERAL generate_series(1, t1.a + t2.a) AS t6
-WHERE t1.a = t2.a;
+select t3.a from sj t1
+ join sj t2 on t1.a = t2.a
+ join lateral (select t1.a offset 0) t3 on true;
+
+explain (verbose, costs off)
+select t3.a from sj t1
+ join sj t2 on t1.a = t2.a
+ join lateral (select * from (select t1.a offset 0) offset 0) t3 on true;
+
+explain (verbose, costs off)
+select t4.a from sj t1
+ join sj t2 on t1.a = t2.a
+ join lateral (select t3.a from sj t3, (select t1.a) offset 0) t4 on true;
-- Check updating of Lateral links from top-level query to the removing relation
explain (COSTS OFF)
@@ -2428,109 +2437,129 @@ SELECT * FROM pg_am am WHERE am.amname IN (
);
--
--- SJR corner case: uniqueness of an inner is [partially] derived from
+-- SJE corner case: uniqueness of an inner is [partially] derived from
-- baserestrictinfo clauses.
--- XXX: We really should allow SJR for these corner cases?
+-- XXX: We really should allow SJE for these corner cases?
--
INSERT INTO sj VALUES (3, 1, 3);
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; -- Return one row
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
+-- Return one row
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
-explain (costs off) -- Remove SJ, define uniqueness by a constant
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; -- Return one row
+-- Remove SJ, define uniqueness by a constant
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
+-- Return one row
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
+-- Remove SJ, define uniqueness by a constant expression
EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND j1.a = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a
-; -- Remove SJ, define uniqueness by a constant expression
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a;
+-- Return one row
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND j1.a = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a
-; -- Return one row
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = j2.a;
-explain (costs off) -- Remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; -- Return no rows
+-- Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
+-- Return no rows
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
-explain (costs off) -- Shuffle a clause. Remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
-SELECT * FROM sj j1, sj j2
-WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; -- Return no rows
+-- Shuffle a clause. Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
+-- Return no rows
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
-- SJE Corner case: a 'a.x=a.x' clause, have replaced with 'a.x IS NOT NULL'
-- after SJ elimination it shouldn't be a mergejoinable clause.
+EXPLAIN (COSTS OFF)
SELECT t4.*
FROM (SELECT t1.*, t2.a AS a1 FROM sj t1, sj t2 WHERE t1.b = t2.b) AS t3
JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42;
-EXPLAIN (COSTS OFF)
SELECT t4.*
FROM (SELECT t1.*, t2.a AS a1 FROM sj t1, sj t2 WHERE t1.b = t2.b) AS t3
-JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42
-; -- SJs must be removed.
+JOIN sj t4 ON (t4.a = t3.a) WHERE t3.a1 = 42;
-- Functional index
CREATE UNIQUE INDEX sj_fn_idx ON sj((a * a));
-explain (costs off) -- Remove SJ
- SELECT * FROM sj j1, sj j2
+
+-- Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 1;
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 2;
+
+-- Restriction contains expressions in both sides, Remove SJ.
EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND (j1.a*j1.a) = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a)
-; -- Restriction contains expressions in both sides, Remove SJ.
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a);
+-- Empty set of rows should be returned
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND (j1.a*j1.a) = (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int
- AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a)
-; -- Empty set of rows should be returned
+ AND (EXTRACT(DOW FROM current_timestamp(0))/15 + 3)::int = (j2.a*j2.a);
+
+-- Restriction contains volatile function - disable SJE feature.
EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
- AND (j1.a*j1.a) = (random()/3 + 3)::int
- AND (random()/3 + 3)::int = (j2.a*j2.a)
-; -- Restriction contains volatile function - disable SJR feature.
+ AND (j1.a*j1.c/3) = (random()/3 + 3)::int
+ AND (random()/3 + 3)::int = (j2.a*j2.c/3);
+-- Return one row
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b
AND (j1.a*j1.c/3) = (random()/3 + 3)::int
- AND (random()/3 + 3)::int = (j2.a*j2.c/3)
-; -- Return one row
+ AND (random()/3 + 3)::int = (j2.a*j2.c/3);
-- Multiple filters
CREATE UNIQUE INDEX sj_temp_idx1 ON sj(a,b,c);
-explain (costs off) -- Remove SJ
- SELECT * FROM sj j1, sj j2
+
+-- Remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND j1.a = 2 AND j1.c = 3 AND j2.a = 2 AND 3 = j2.c;
-explain (costs off) -- Don't remove SJ
+
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
SELECT * FROM sj j1, sj j2
WHERE j1.b = j2.b AND 2 = j1.a AND j1.c = 3 AND j2.a = 1 AND 3 = j2.c;
CREATE UNIQUE INDEX sj_temp_idx ON sj(a,b);
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2;
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a;
-explain (costs off) -- Don't remove SJ
- SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1);
+
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2;
+
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a;
+
+-- Don't remove SJ
+EXPLAIN (COSTS OFF)
+SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1);
+
DROP INDEX sj_fn_idx, sj_temp_idx1, sj_temp_idx;
-- Test that OR predicated are updated correctly after join removal
CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT);
CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag);
-explain (costs off)
+
+EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM tab_with_flag
WHERE
(is_flag IS NULL OR is_flag = 0)
@@ -2581,24 +2610,27 @@ reset enable_seqscan;
-- Check that clauses from the join filter list is not lost on the self-join removal
CREATE TABLE emp1 (id SERIAL PRIMARY KEY NOT NULL, code int);
-explain (verbose, costs off)
+EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
-- Shuffle self-joined relations. Only in the case of iterative deletion
-- attempts explains of these queries will be identical.
CREATE UNIQUE INDEX ON emp1((id*id));
-explain (costs off)
+
+EXPLAIN (COSTS OFF)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id;
-explain (costs off)
+
+EXPLAIN (COSTS OFF)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id;
-explain (costs off)
+
+EXPLAIN (COSTS OFF)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id;
-- Check the usage of a parse tree by the set operations (bug #18170)
-explain (costs off)
+EXPLAIN (COSTS OFF)
SELECT c1.code FROM emp1 c1 LEFT JOIN emp1 c2 ON c1.id = c2.id
WHERE c2.id IS NOT NULL
EXCEPT ALL
@@ -2637,14 +2669,17 @@ select * from emp1 t1
left join emp1 t3 on t1.id > 1 and t1.id < 2;
-- Check that SJE doesn't replace the target relation
-explain (costs off)
+EXPLAIN (COSTS OFF)
WITH t1 AS (SELECT * FROM emp1)
UPDATE emp1 SET code = t1.code + 1 FROM t1
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
+
INSERT INTO emp1 VALUES (1, 1), (2, 1);
+
WITH t1 AS (SELECT * FROM emp1)
UPDATE emp1 SET code = t1.code + 1 FROM t1
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
+
TRUNCATE emp1;
EXPLAIN (COSTS OFF)
@@ -2707,28 +2742,14 @@ where t1.b = t2.b and t2.a = 3 and t1.a = 3
and t1.a IS NOT NULL and t2.a IS NOT NULL;
-- Join qual isn't mergejoinable, but inner is unique.
-explain (COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a AND n2.a = 1;
-explain (COSTS OFF)
+
+EXPLAIN (COSTS OFF)
SELECT * FROM
- (SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
+(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
---
----- Only one side is unique
---select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1;
---select * from sl t1, sl t2 where t1.a = t2.a and t2.b = 1;
---
----- Several uniques indexes match, and we select a different one
----- for each side, so the join is not removed
---create table sm(a int unique, b int unique, c int unique);
---explain (costs off)
---select * from sm m, sm n where m.a = n.b and m.c = n.c;
---explain (costs off)
---select * from sm m, sm n where m.a = n.c and m.b = n.b;
---explain (costs off)
---select * from sm m, sm n where m.c = n.b and m.a = n.a;
-
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
@@ -2763,7 +2784,8 @@ ON sj_t1.id = _t2t3t4.id;
-- Test RowMarks-related code
--
-EXPLAIN (COSTS OFF) -- Both sides have explicit LockRows marks
+-- Both sides have explicit LockRows marks
+EXPLAIN (COSTS OFF)
SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
reset enable_hashjoin;
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-02 21:58:36 +0000