From 3188ff2ee9db2f67290594fd7cdad0d3b1e5359d Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" Date: Tue, 9 Jul 2024 12:25:23 +0700 Subject: [PATCH 2/2] Apply SJE to DML queries with RETURNING clause --- src/backend/optimizer/plan/analyzejoins.c | 32 +- src/test/regress/expected/join.out | 687 +++++++++++++++++- src/test/regress/expected/updatable_views.out | 17 +- src/test/regress/sql/join.sql | 270 +++++++ 4 files changed, 963 insertions(+), 43 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index bb14597762..7fc59d80cf 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1860,10 +1860,6 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark, /* restore the rangetblref in a proper order. */ restore_rangetblref((Node *) root->parse, toKeep->relid, toRemove->relid, 0, 0); - /* See remove_self_joins_one_group() */ - Assert(root->parse->resultRelation != toRemove->relid); - Assert(root->parse->resultRelation != toKeep->relid); - /* Replace links in the planner info */ remove_rel_from_query(root, toRemove, toKeep->relid, NULL, NULL); @@ -2046,14 +2042,6 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) { RelOptInfo *inner = root->simple_rel_array[r]; - /* - * We don't accept result relation as either source or target relation - * of SJE, because result relation has different behavior in - * EvalPlanQual() and RETURNING clause. - */ - if (root->parse->resultRelation == r) - continue; - k = r; while ((k = bms_next_member(relids, k)) > 0) @@ -2069,9 +2057,6 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) PlanRowMark *imark = NULL; List *uclauses = NIL; - if (root->parse->resultRelation == k) - continue; - /* A sanity check: the relations have the same Oid. */ Assert(root->simple_rte_array[k]->relid == root->simple_rte_array[r]->relid); @@ -2300,6 +2285,23 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist, Relids toRemove) relids = bms_del_members(relids, group); + /* + * if one of relations is in the RETURNING statement + * we can only apply this optimisation if returning list + * contains only resultRelation relid (we don't apply it to + * partitioned relations so far) or doesn't contain result + * relation at all. + */ + if (root->parse && root->parse->returningList != NIL) + { + Relids rets = pull_varnos_of_level( + root, (Node *) root->parse->returningList, 0); + + if (bms_is_member(root->parse->resultRelation, rets) && + bms_num_members(rets) != 1) + continue; + } + /* * Try to remove self-joins from a group of identical entries. * Make the next attempt iteratively - if something is deleted diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 4e4cec633a..b889b2889c 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6440,6 +6440,663 @@ on true; -> Seq Scan on int8_tbl y (7 rows) +--truncate sj, refill data. +truncate sj; +insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g; +analyze sj; +------------------- UPDATE SJE (self join elimination) applicable cases. +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2); + QUERY PLAN +------------------------------------------------------------ + Update on sj sz + -> Seq Scan on sj sz + Filter: ((a IS NOT NULL) AND ((b = 2) OR (a = 2))) +(3 rows) + +UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2); +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3; + QUERY PLAN +----------------------------------------------- + Update on sj + -> Seq Scan on sj + Filter: ((b IS NOT NULL) AND (a = 3)) +(3 rows) + +WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3; +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4; + QUERY PLAN +------------------------- + Update on sj + -> Seq Scan on sj + Filter: (a = 4) +(3 rows) + +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4; +select * from sj order by a; + a | b | c +----+---+---- + 1 | 1 | 11 + 2 | 4 | 12 + 5 | 5 | 15 + 6 | 6 | 16 + 7 | 7 | 17 + 8 | 8 | 18 + 9 | 9 | 19 + 10 | 0 | 20 + 12 | 4 | 14 + 19 | 3 | 13 +(10 rows) + +------------------- UPDATE SJE not applicable cases. +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sz.a = sq.a or (sq.b = 2 or sq.a = 2); + QUERY PLAN +------------------------------------------------------------------ + Update on sj sq + -> Nested Loop + Join Filter: ((sz.a = sq.a) OR (sq.b = 2) OR (sq.a = 2)) + -> Seq Scan on sj sq + -> Materialize + -> Seq Scan on sj sz +(6 rows) + +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a; + QUERY PLAN +------------------------------------ + Update on sj sq + -> Nested Loop + Join Filter: (sq.a = sz.b) + -> Seq Scan on sj sq + Filter: (a = b) + -> Seq Scan on sj sz +(6 rows) + +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3; + QUERY PLAN +------------------------------------ + Update on sj sq + -> Nested Loop + Join Filter: (sq.b = sz.b) + -> Seq Scan on sj sq + Filter: (a = 3) + -> Seq Scan on sj sz + Filter: (a = 2) +(7 rows) + +EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + 1 FROM t1 WHERE t1.b = sj.b and t1.a = 2 and sj.a = 3; + QUERY PLAN +-------------------------------------- + Update on sj + -> Nested Loop + Join Filter: (sj.b = sj_1.b) + -> Seq Scan on sj + Filter: (a = 3) + -> Seq Scan on sj sj_1 + Filter: (a = 2) +(7 rows) + +------------------- DELETE SJE applicable cases. +EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5; + QUERY PLAN +------------------------- + Delete on sj sz + -> Seq Scan on sj sz + Filter: (a = 5) +(3 rows) + +DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5; +EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113); + QUERY PLAN +-------------------------------------------------------------- + Delete on sj sz + -> Seq Scan on sj sz + Filter: ((a IS NOT NULL) AND ((a = 6) OR (b = 113))) +(3 rows) + +DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113); +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7; + QUERY PLAN +----------------------------------------------- + Delete on sj t1 + -> Seq Scan on sj t1 + Filter: ((b IS NOT NULL) AND (a = 7)) +(3 rows) + +WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7; +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8; + QUERY PLAN +------------------------- + Delete on sj + -> Seq Scan on sj + Filter: (a = 8) +(3 rows) + +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8; +select * from sj order by a; + a | b | c +----+---+---- + 1 | 1 | 11 + 2 | 4 | 12 + 9 | 9 | 19 + 10 | 0 | 20 + 12 | 4 | 14 + 19 | 3 | 13 +(6 rows) + +------------------- DELETE SJE not applicable cases. +EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a; + QUERY PLAN +------------------------------------ + Delete on sj sq + -> Nested Loop + Join Filter: (sq.a = sz.b) + -> Seq Scan on sj sq + Filter: (a = b) + -> Seq Scan on sj sz +(6 rows) + +EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3; + QUERY PLAN +------------------------------------ + Delete on sj sq + -> Nested Loop + Join Filter: (sq.b = sz.b) + -> Seq Scan on sj sq + Filter: (a = 3) + -> Seq Scan on sj sz + Filter: (a = 2) +(7 rows) + +EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 3; + QUERY PLAN +------------------------------------ + Delete on sj sq + -> Nested Loop + Join Filter: (sq.b = t1.b) + -> Seq Scan on sj t1 + Filter: (a = 3) + -> Seq Scan on sj sq +(6 rows) + +EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.a = sq.c; + QUERY PLAN +------------------------------------- + Delete on sj sq + -> Nested Loop + Join Filter: (sq.c = t1.a) + -> Seq Scan on sj sq + -> Materialize + -> Seq Scan on sj t1 +(6 rows) + +---UPDATE/DELETE RETURNING SJE applicable cases. +truncate sj; +insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g; +analyze sj; +----test query that can use SJE +explain(costs off) +UPDATE sj sq +SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2 +returning sq.a, sq.b, (select sq.c); + QUERY PLAN +------------------------- + Update on sj sz + -> Seq Scan on sj sz + Filter: (a = 2) + SubPlan 1 + -> Result +(5 rows) + +explain(costs off) +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.a and sz.a = 3 +returning sq.*; + QUERY PLAN +------------------------- + Update on sj s2 + -> Seq Scan on sj s2 + Filter: (a = 3) +(3 rows) + +explain(costs off) +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sz.a = 4 and sq.a = 4 +returning sq.*, (select sq.a); + QUERY PLAN +------------------------------------ + Update on sj s1 + -> Nested Loop + Join Filter: (s1.b = s2.b) + -> Seq Scan on sj s1 + Filter: (a = 4) + -> Seq Scan on sj s2 + SubPlan 1 + -> Result +(8 rows) + +explain(costs off) +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5 +returning sq.a, sq.a * sq.b, (select sq.a + sq.b); + QUERY PLAN +------------------------- + Delete on sj + -> Seq Scan on sj + Filter: (a = 5) + SubPlan 1 + -> Result +(5 rows) + +explain(costs off) +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sq.a = sz.a and sq.a = 6 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + QUERY PLAN +------------------------------------ + Delete on sj s1 + -> Nested Loop + Join Filter: (s1.b = s2.b) + -> Seq Scan on sj s1 + Filter: (a = 6) + -> Seq Scan on sj s2 +(6 rows) + +explain(costs off) +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.b and sz.a = 7 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + QUERY PLAN +------------------------------------ + Delete on sj sq + -> Nested Loop + Join Filter: (sq.a = s2.b) + -> Seq Scan on sj s2 + Filter: (a = 7) + -> Seq Scan on sj sq +(6 rows) + +----actually execute the query to validate the result +UPDATE sj sq +SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2 +returning sq.a, sq.b, (select sq.c); + a | b | c +----+----+---- + 17 | 17 | 32 +(1 row) + +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.a and sz.a = 3 +returning sq.*; + a | b | c +----+----+---- + 18 | 18 | 38 +(1 row) + +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sz.a = 4 and sq.a = 4 +returning sq.*, (select sq.a); + a | b | c | a +----+----+----+---- + 19 | 19 | 44 | 19 +(1 row) + +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5 +returning sq.a, sq.a * sq.b, (select sq.a + sq.b); + a | ?column? | ?column? +---+----------+---------- + 5 | 25 | 10 +(1 row) + +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sq.a = sz.a and sq.a = 6 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + a | b | c | tbl +---+---+----+----- + 6 | 6 | 16 | sj +(1 row) + +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.b and sz.a = 7 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + a | b | c | tbl +---+---+----+----- + 7 | 7 | 17 | sj +(1 row) + +select * from sj order by a; + a | b | c +----+----+---- + 1 | 1 | 11 + 8 | 8 | 18 + 9 | 9 | 19 + 10 | 0 | 20 + 17 | 17 | 32 + 18 | 18 | 38 + 19 | 19 | 44 +(7 rows) + +------------------- MERGE SJE table setup +CREATE TABLE sj_target (tid integer primary key, balance integer) WITH (autovacuum_enabled=off); +INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60); +create view rw_sj_target as select * from sj_target where tid >= 2; +create or replace view no_rw_sj_target as select * from sj_target where balance = 60 limit 1; +--cannot use SJE for RETURNING +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN update set balance = t.balance + 11 RETURNING *; + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop + -> Seq Scan on sj_target t + -> Index Scan using sj_target_pkey on sj_target s + Index Cond: (tid = t.tid) +(5 rows) + +--cannot use SJE for merge insert +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN NOT MATCHED AND s.balance = 10 + THEN INSERT VALUES (s.tid, s.balance); + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop Left Join + -> Seq Scan on sj_target s + -> Index Scan using sj_target_pkey on sj_target t + Index Cond: (tid = s.tid) +(5 rows) + +--cannot use SJE for merge with non-updatable view +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 60 + THEN update set balance = t.balance + 6; + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop + -> Subquery Scan on s + -> Limit + -> Seq Scan on sj_target + Filter: (balance = 60) + -> Index Scan using sj_target_pkey on sj_target t + Index Cond: (tid = s.tid) +(8 rows) + +---- the following cases can apply SJE with merge. +EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 20 + THEN update set balance = t.balance + 2; + QUERY PLAN +------------------------------------------------- + Merge on sj_target + -> Bitmap Heap Scan on sj_target + Recheck Cond: (tid >= 2) + -> Bitmap Index Scan on sj_target_pkey + Index Cond: (tid >= 2) +(5 rows) + +EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN update set balance = t.balance + 2; + QUERY PLAN +------------------------------------------------- + Merge on sj_target + -> Bitmap Heap Scan on sj_target + Recheck Cond: (tid >= 2) + -> Bitmap Index Scan on sj_target_pkey + Index Cond: (tid >= 2) +(5 rows) + +EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 + THEN update set balance = t.balance + 3; + QUERY PLAN +------------------------------------------------- + Merge on sj_target + -> Bitmap Heap Scan on sj_target + Recheck Cond: (tid >= 2) + -> Bitmap Index Scan on sj_target_pkey + Index Cond: (tid >= 2) +(5 rows) + +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 + THEN update set balance = t.balance + 4; + QUERY PLAN +------------------------------- + Merge on sj_target s + -> Seq Scan on sj_target s +(2 rows) + +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 50 + THEN DELETE; + QUERY PLAN +------------------------------- + Merge on sj_target s + -> Seq Scan on sj_target s +(2 rows) + +--- and run the actual query. +MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 60 THEN update set balance = t.balance + 6; +MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 20 THEN update set balance = t.balance + 2; +MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 THEN update set balance = t.balance + 3; +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 THEN update set balance = t.balance + 4; +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 50 THEN DELETE; +MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 THEN update set balance = t.balance + 1; +select * from sj_target order by tid; + tid | balance +-----+--------- + 1 | 10 + 2 | 22 + 3 | 33 + 4 | 44 + 6 | 66 +(5 rows) + +-------------------MERGRE RETURNING SJE TEST-------------------------------- +TRUNCATE sj_target; +INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60); +--"when matched" and "when not matched" both specified +--because of "when not matched" then can only use left join +--therefore cannot apply SJE +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN UPDATE SET balance = t.balance + s.balance, tid = t.tid + s.tid + WHEN NOT MATCHED BY SOURCE AND t.balance = 20 THEN DELETE + RETURNING t.*, (select t.balance), merge_action(); + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop Left Join + -> Seq Scan on sj_target t + -> Index Scan using sj_target_pkey on sj_target s + Index Cond: (tid = t.tid) + SubPlan 1 + -> Result +(7 rows) + +---"when not matched" using left join, SJE cannot be applied +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid +WHEN NOT MATCHED BY SOURCE AND t.balance = 10 THEN + DELETE +returning t.*; + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop Left Join + -> Seq Scan on sj_target t + -> Index Scan using sj_target_pkey on sj_target s + Index Cond: (tid = t.tid) +(5 rows) + +---"when not matched by target" using left join, SJE cannot be applied +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target s ON t.tid = s.tid + WHEN NOT MATCHED THEN + INSERT VALUES (s.tid, 11) + RETURNING t.*, merge_action(); + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop Left Join + -> Seq Scan on sj_target s + -> Index Scan using sj_target_pkey on sj_target t + Index Cond: (tid = s.tid) +(5 rows) + +---returning with multiple rel, merge returning cannot be applied. +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 50 + THEN UPDATE SET balance = t.balance + s.balance + 50 + RETURNING s.*, t.balance, merge_action(); + QUERY PLAN +------------------------------------------------------------ + Merge on sj_target t + -> Nested Loop + -> Seq Scan on sj_target t + -> Index Scan using sj_target_pkey on sj_target s + Index Cond: (tid = t.tid) +(5 rows) + +--------the following (explain) query can use SJE. +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (select t.balance), merge_action(); + QUERY PLAN +------------------------------- + Merge on sj_target s + -> Seq Scan on sj_target s + SubPlan 1 + -> Result +(4 rows) + +EXPLAIN (COSTS OFF) +MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND s.balance = 20 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + QUERY PLAN +------------------------------------------------------------- + Merge on sj_target s0 + -> Nested Loop + Join Filter: (s0.balance = s1.balance) + -> Seq Scan on sj_target s1 + -> Materialize + -> Bitmap Heap Scan on sj_target s0 + Recheck Cond: (tid >= 2) + -> Bitmap Index Scan on sj_target_pkey + Index Cond: (tid >= 2) +(9 rows) + +EXPLAIN (COSTS OFF) +MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + QUERY PLAN +------------------------------------------------------------- + Merge on sj_target + -> Nested Loop + Join Filter: (s0.balance = sj_target.balance) + -> Seq Scan on sj_target s0 + -> Materialize + -> Bitmap Heap Scan on sj_target + Recheck Cond: (tid >= 2) + -> Bitmap Index Scan on sj_target_pkey + Index Cond: (tid >= 2) +(9 rows) + +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 AND s.balance = 40 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (SELECT t.tableoid::regclass), merge_action(); + QUERY PLAN +------------------------------------------------ + Merge on sj_target s0 + -> Nested Loop + Join Filter: (s0.balance = s1.balance) + -> Seq Scan on sj_target s0 + -> Materialize + -> Seq Scan on sj_target s1 + SubPlan 1 + -> Result +(8 rows) + +--------and actually running query validate the result. +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (select t.balance), merge_action(); + tid | balance | balance | merge_action +-----+---------+---------+-------------- + 12 | 120 | 120 | UPDATE +(1 row) + +MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND s.balance = 20 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + tid | balance | merge_action +-----+---------+-------------- + 14 | 140 | UPDATE +(1 row) + +MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + tid | balance | merge_action +-----+---------+-------------- + 16 | 160 | UPDATE +(1 row) + +MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 AND s.balance = 40 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (SELECT t.tableoid::regclass), merge_action(); + tid | balance | tableoid | merge_action +-----+---------+-----------+-------------- + 18 | 180 | sj_target | UPDATE +(1 row) + +select * from sj_target; + tid | balance +-----+--------- + 5 | 50 + 6 | 60 + 12 | 120 + 14 | 140 + 16 | 160 + 18 | 180 +(6 rows) + +DROP VIEW no_rw_sj_target; +DROP VIEW rw_sj_target; +DROP TABLE sj_target; +truncate sj; +insert into sj values (1, null, 2), (null, 2, null), (2, 1, 1); +analyze sj; -- Test that references to the removed rel in lateral subqueries are replaced -- correctly after join removal explain (verbose, costs off) @@ -7058,29 +7715,23 @@ WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code; TRUNCATE emp1; EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.a; - QUERY PLAN -------------------------------------- - Update on sj sq - -> Nested Loop - Join Filter: (sq.a = sz.a) - -> Seq Scan on sj sq - -> Materialize - -> Seq Scan on sj sz -(6 rows) + QUERY PLAN +--------------------------------- + Update on sj sz + -> Seq Scan on sj sz + Filter: (a IS NOT NULL) +(3 rows) CREATE RULE sj_del_rule AS ON DELETE TO sj DO INSTEAD UPDATE sj SET a = 1 WHERE a = old.a; EXPLAIN (COSTS OFF) DELETE FROM sj; - QUERY PLAN --------------------------------------- - Update on sj sj_1 - -> Nested Loop - Join Filter: (sj.a = sj_1.a) - -> Seq Scan on sj sj_1 - -> Materialize - -> Seq Scan on sj -(6 rows) + QUERY PLAN +--------------------------------- + Update on sj + -> Seq Scan on sj + Filter: (a IS NOT NULL) +(3 rows) DROP RULE sj_del_rule ON sj CASCADE; -- Check that SJE does not mistakenly omit qual clauses (bug #18187) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 9c21b76800..2c6487818a 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -3111,16 +3111,13 @@ SELECT * FROM rw_view1; (1 row) EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); - QUERY PLAN -------------------------------------------------------------------- - Update on base_tbl base_tbl_1 - -> Nested Loop - -> Index Scan using base_tbl_pkey on base_tbl base_tbl_1 - Index Cond: (id = 1) - -> Index Scan using base_tbl_pkey on base_tbl - Index Cond: (id = 1) - Filter: ((NOT deleted) AND snoop(data)) -(7 rows) + QUERY PLAN +-------------------------------------------------- + Update on base_tbl + -> Index Scan using base_tbl_pkey on base_tbl + Index Cond: (id = 1) + Filter: ((NOT deleted) AND snoop(data)) +(4 rows) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); NOTICE: snooped value: Row 1 diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 3e94e0af53..981f9a8c47 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2450,6 +2450,276 @@ left join (select coalesce(y.q1, 1) from int8_tbl y on true) z on true; +--truncate sj, refill data. +truncate sj; +insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g; +analyze sj; +------------------- UPDATE SJE (self join elimination) applicable cases. +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2); + +UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2); + +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3; + +WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3; + +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4; + +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4; + +select * from sj order by a; +------------------- UPDATE SJE not applicable cases. +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sz.a = sq.a or (sq.b = 2 or sq.a = 2); + +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a; + +EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3; + +EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + 1 FROM t1 WHERE t1.b = sj.b and t1.a = 2 and sj.a = 3; + +------------------- DELETE SJE applicable cases. +EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5; + +DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5; + +EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113); + +DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113); + +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7; + +WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7; + +EXPLAIN (COSTS OFF) +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8; + +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8; + +select * from sj order by a; +------------------- DELETE SJE not applicable cases. +EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a; + +EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3; + +EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 3; + +EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.a = sq.c; + +---UPDATE/DELETE RETURNING SJE applicable cases. +truncate sj; +insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g; +analyze sj; + +----test query that can use SJE +explain(costs off) +UPDATE sj sq +SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2 +returning sq.a, sq.b, (select sq.c); + +explain(costs off) +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.a and sz.a = 3 +returning sq.*; + +explain(costs off) +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sz.a = 4 and sq.a = 4 +returning sq.*, (select sq.a); + +explain(costs off) +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5 +returning sq.a, sq.a * sq.b, (select sq.a + sq.b); + +explain(costs off) +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sq.a = sz.a and sq.a = 6 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + +explain(costs off) +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.b and sz.a = 7 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + +----actually execute the query to validate the result +UPDATE sj sq +SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2 +returning sq.a, sq.b, (select sq.c); + +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.a and sz.a = 3 +returning sq.*; + +UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15 +FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sz.a = 4 and sq.a = 4 +returning sq.*, (select sq.a); + +WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5 +returning sq.a, sq.a * sq.b, (select sq.a + sq.b); + +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz +WHERE sq.a = sz.a and sq.a = 6 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + +delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz +WHERE sq.a = sz.b and sz.a = 7 +returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl; + +select * from sj order by a; +------------------- MERGE SJE table setup +CREATE TABLE sj_target (tid integer primary key, balance integer) WITH (autovacuum_enabled=off); +INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60); +create view rw_sj_target as select * from sj_target where tid >= 2; +create or replace view no_rw_sj_target as select * from sj_target where balance = 60 limit 1; + +--cannot use SJE for RETURNING +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN update set balance = t.balance + 11 RETURNING *; +--cannot use SJE for merge insert +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN NOT MATCHED AND s.balance = 10 + THEN INSERT VALUES (s.tid, s.balance); + +--cannot use SJE for merge with non-updatable view +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 60 + THEN update set balance = t.balance + 6; + +---- the following cases can apply SJE with merge. +EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 20 + THEN update set balance = t.balance + 2; +EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN update set balance = t.balance + 2; +EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 + THEN update set balance = t.balance + 3; +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 + THEN update set balance = t.balance + 4; +EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 50 + THEN DELETE; + +--- and run the actual query. +MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 60 THEN update set balance = t.balance + 6; + +MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 20 THEN update set balance = t.balance + 2; + +MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 THEN update set balance = t.balance + 3; + +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 THEN update set balance = t.balance + 4; + +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 50 THEN DELETE; + +MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 THEN update set balance = t.balance + 1; + +select * from sj_target order by tid; +-------------------MERGRE RETURNING SJE TEST-------------------------------- +TRUNCATE sj_target; +INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60); + +--"when matched" and "when not matched" both specified +--because of "when not matched" then can only use left join +--therefore cannot apply SJE +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN UPDATE SET balance = t.balance + s.balance, tid = t.tid + s.tid + WHEN NOT MATCHED BY SOURCE AND t.balance = 20 THEN DELETE + RETURNING t.*, (select t.balance), merge_action(); + +---"when not matched" using left join, SJE cannot be applied +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid +WHEN NOT MATCHED BY SOURCE AND t.balance = 10 THEN + DELETE +returning t.*; + +---"when not matched by target" using left join, SJE cannot be applied +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target s ON t.tid = s.tid + WHEN NOT MATCHED THEN + INSERT VALUES (s.tid, 11) + RETURNING t.*, merge_action(); + +---returning with multiple rel, merge returning cannot be applied. +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 50 + THEN UPDATE SET balance = t.balance + s.balance + 50 + RETURNING s.*, t.balance, merge_action(); + +--------the following (explain) query can use SJE. +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (select t.balance), merge_action(); + +EXPLAIN (COSTS OFF) +MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND s.balance = 20 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + +EXPLAIN (COSTS OFF) +MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + +EXPLAIN (COSTS OFF) +MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 AND s.balance = 40 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (SELECT t.tableoid::regclass), merge_action(); +--------and actually running query validate the result. +MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 10 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (select t.balance), merge_action(); + +MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND s.balance = 20 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + +MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 30 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, merge_action(); + +MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid + WHEN MATCHED AND t.balance = 40 AND s.balance = 40 + THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10 + RETURNING t.*, (SELECT t.tableoid::regclass), merge_action(); + +select * from sj_target; + +DROP VIEW no_rw_sj_target; +DROP VIEW rw_sj_target; +DROP TABLE sj_target; + +truncate sj; +insert into sj values (1, null, 2), (null, 2, null), (2, 1, 1); +analyze sj; -- Test that references to the removed rel in lateral subqueries are replaced -- correctly after join removal explain (verbose, costs off) -- 2.39.2