summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorTomas Vondra2017-07-30 21:52:54 +0000
committerTomas Vondra2017-07-31 01:22:02 +0000
commit295f3b607239153b22c1d2912e5140e54a8a4838 (patch)
tree0a7c1f26c8d49ab1f7452866e93cb37e9c09d9a1 /src
parentfc3148e7e327b8c7c5da4c9a5816e8c61061e970 (diff)
Partially accept plan changes in updatable_views
Upstream commit 215b43cdc8d6b4a1700886a39df1ee735cb0274d significantly reworked planning of leaky functions. In practice that change means we no longer have to push leaky functions into a subquery. Which greatly simplifies some plans, including the two in this patch. This commit accepts the plans only partially, though. It uses the plans from upstream, and adds a Remote Subquery Scan node at the top, so we accept the general plan shape change. But there are a few additional differences that need futher evaluation, particularly in target lists (Postgres-XL generating more entries than upstream) and SubPlans (Postgres-XL only generating one subplan, while upstream generates two alternative ones).
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/updatable_views.out234
1 files changed, 77 insertions, 157 deletions
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1a05e62f3d..830c01a7ec 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2076,86 +2076,46 @@ SELECT * FROM v1 WHERE a=8;
(4 rows)
EXPLAIN (VERBOSE, COSTS OFF)
-UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
- Remote Subquery Scan on all (datanode_1)
- -> Update on public.t1 t1_4
- Update on public.t1 t1_4
- Update on public.t11 t1
- Update on public.t12 t1
- Update on public.t111 t1
- -> Subquery Scan on t1
- Output: 100, t1.b, t1.c, t1.a, t1.a, t1.a, t1.a, t1.a, t1.a, t1.ctid
- Filter: snoop(t1.a)
- -> LockRows
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c, t1_5.ctid, t12.ctid, t12.tableoid
- -> Nested Loop Semi Join
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c, t1_5.ctid, t12.ctid, t12.tableoid
- -> Seq Scan on public.t1 t1_5
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
- Filter: ((t1_5.a > 5) AND (t1_5.a = 3) AND leakproof(t1_5.a))
- -> Append
- -> Seq Scan on public.t12
- Output: t12.ctid, t12.tableoid, t12.a
- Filter: (t12.a = 3)
- -> Seq Scan on public.t111
- Output: t111.ctid, t111.tableoid, t111.a
- Filter: (t111.a = 3)
- -> Subquery Scan on t1_1
- Output: 100, t1_1.b, t1_1.c, t1_1.d, t1_1.a, t1_1.a, t1_1.a, t1_1.a, t1_1.a, t1_1.a, t1_1.ctid
- Filter: snoop(t1_1.a)
- -> LockRows
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d, t11.ctid, t12_1.ctid, t12_1.tableoid
- -> Nested Loop Semi Join
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d, t11.ctid, t12_1.ctid, t12_1.tableoid
- -> Seq Scan on public.t11
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
- Filter: ((t11.a > 5) AND (t11.a = 3) AND leakproof(t11.a))
- -> Append
- -> Seq Scan on public.t12 t12_1
- Output: t12_1.ctid, t12_1.tableoid, t12_1.a
- Filter: (t12_1.a = 3)
- -> Seq Scan on public.t111 t111_1
- Output: t111_1.ctid, t111_1.tableoid, t111_1.a
- Filter: (t111_1.a = 3)
- -> Subquery Scan on t1_2
- Output: 100, t1_2.b, t1_2.c, t1_2.e, t1_2.a, t1_2.a, t1_2.a, t1_2.a, t1_2.a, t1_2.a, t1_2.ctid
- Filter: snoop(t1_2.a)
- -> LockRows
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e, t12_2.ctid, t12_3.ctid, t12_3.tableoid
- -> Nested Loop Semi Join
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e, t12_2.ctid, t12_3.ctid, t12_3.tableoid
- -> Seq Scan on public.t12 t12_2
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
- Filter: ((t12_2.a > 5) AND (t12_2.a = 3) AND leakproof(t12_2.a))
- -> Append
- -> Seq Scan on public.t12 t12_3
- Output: t12_3.ctid, t12_3.tableoid, t12_3.a
- Filter: (t12_3.a = 3)
- -> Seq Scan on public.t111 t111_2
- Output: t111_2.ctid, t111_2.tableoid, t111_2.a
- Filter: (t111_2.a = 3)
- -> Subquery Scan on t1_3
- Output: 100, t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.a, t1_3.a, t1_3.a, t1_3.a, t1_3.a, t1_3.a, t1_3.ctid
- Filter: snoop(t1_3.a)
- -> LockRows
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e, t111_3.ctid, t12_4.ctid, t12_4.tableoid
- -> Nested Loop Semi Join
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e, t111_3.ctid, t12_4.ctid, t12_4.tableoid
- -> Seq Scan on public.t111 t111_3
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- Filter: ((t111_3.a > 5) AND (t111_3.a = 3) AND leakproof(t111_3.a))
- -> Append
- -> Seq Scan on public.t12 t12_4
- Output: t12_4.ctid, t12_4.tableoid, t12_4.a
- Filter: (t12_4.a = 3)
- -> Seq Scan on public.t111 t111_4
- Output: t111_4.ctid, t111_4.tableoid, t111_4.a
- Filter: (t111_4.a = 3)
-(74 rows)
-
-UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
+UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
+ Remote Subquery Scan on any (datanode_1,datanode_2)
+ -> Update on public.t1
+ Update on public.t1
+ Update on public.t11
+ Update on public.t12
+ Update on public.t111
+ -> Index Scan using t1_a_idx on public.t1
+ Output: 100, t1.b, t1.c, t1.ctid
+ Index Cond: ((t1.a > 5) AND (t1.a < 7))
+ Filter: ((t1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
+ SubPlan 1
+ -> Append
+ -> Seq Scan on public.t12 t12_1
+ Filter: (t12_1.a = t1.a)
+ -> Seq Scan on public.t111 t111_1
+ Filter: (t111_1.a = t1.a)
+ SubPlan 2
+ -> Append
+ -> Seq Scan on public.t12 t12_2
+ Output: t12_2.a
+ -> Seq Scan on public.t111 t111_2
+ Output: t111_2.a
+ -> Index Scan using t11_a_idx on public.t11
+ Output: 100, t11.b, t11.c, t11.d, t11.ctid
+ Index Cond: ((t11.a > 5) AND (t11.a < 7))
+ Filter: ((t11.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND leakproof(t11.a))
+ -> Index Scan using t12_a_idx on public.t12
+ Output: 100, t12.b, t12.c, t12.e, t12.ctid
+ Index Cond: ((t12.a > 5) AND (t12.a < 7))
+ Filter: ((t12.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND leakproof(t12.a))
+ -> Index Scan using t111_a_idx on public.t111
+ Output: 100, t111.b, t111.c, t111.d, t111.e, t111.ctid
+ Index Cond: ((t111.a > 5) AND (t111.a < 7))
+ Filter: ((t111.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND leakproof(t111.a))
+(33 rows)
+
+UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
a | b | c | d
---+---+---+---
@@ -2168,83 +2128,43 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
- Remote Subquery Scan on all (datanode_1)
- -> Update on public.t1 t1_4
- Update on public.t1 t1_4
- Update on public.t11 t1
- Update on public.t12 t1
- Update on public.t111 t1
- -> Subquery Scan on t1
- Output: (t1.a + 1), t1.b, t1.c, t1.a, t1.a, t1.a, t1.a, t1.a, t1.a, t1.ctid
- Filter: snoop(t1.a)
- -> LockRows
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c, t1_5.ctid, t12.ctid, t12.tableoid
- -> Nested Loop Semi Join
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c, t1_5.ctid, t12.ctid, t12.tableoid
- -> Seq Scan on public.t1 t1_5
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
- Filter: ((t1_5.a > 5) AND (t1_5.a = 8) AND leakproof(t1_5.a))
- -> Append
- -> Seq Scan on public.t12
- Output: t12.ctid, t12.tableoid, t12.a
- Filter: (t12.a = 8)
- -> Seq Scan on public.t111
- Output: t111.ctid, t111.tableoid, t111.a
- Filter: (t111.a = 8)
- -> Subquery Scan on t1_1
- Output: (t1_1.a + 1), t1_1.b, t1_1.c, t1_1.d, t1_1.a, t1_1.a, t1_1.a, t1_1.a, t1_1.a, t1_1.a, t1_1.ctid
- Filter: snoop(t1_1.a)
- -> LockRows
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d, t11.ctid, t12_1.ctid, t12_1.tableoid
- -> Nested Loop Semi Join
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d, t11.ctid, t12_1.ctid, t12_1.tableoid
- -> Seq Scan on public.t11
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
- Filter: ((t11.a > 5) AND (t11.a = 8) AND leakproof(t11.a))
- -> Append
- -> Seq Scan on public.t12 t12_1
- Output: t12_1.ctid, t12_1.tableoid, t12_1.a
- Filter: (t12_1.a = 8)
- -> Seq Scan on public.t111 t111_1
- Output: t111_1.ctid, t111_1.tableoid, t111_1.a
- Filter: (t111_1.a = 8)
- -> Subquery Scan on t1_2
- Output: (t1_2.a + 1), t1_2.b, t1_2.c, t1_2.e, t1_2.a, t1_2.a, t1_2.a, t1_2.a, t1_2.a, t1_2.a, t1_2.ctid
- Filter: snoop(t1_2.a)
- -> LockRows
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e, t12_2.ctid, t12_3.ctid, t12_3.tableoid
- -> Nested Loop Semi Join
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e, t12_2.ctid, t12_3.ctid, t12_3.tableoid
- -> Seq Scan on public.t12 t12_2
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
- Filter: ((t12_2.a > 5) AND (t12_2.a = 8) AND leakproof(t12_2.a))
- -> Append
- -> Seq Scan on public.t12 t12_3
- Output: t12_3.ctid, t12_3.tableoid, t12_3.a
- Filter: (t12_3.a = 8)
- -> Seq Scan on public.t111 t111_2
- Output: t111_2.ctid, t111_2.tableoid, t111_2.a
- Filter: (t111_2.a = 8)
- -> Subquery Scan on t1_3
- Output: (t1_3.a + 1), t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.a, t1_3.a, t1_3.a, t1_3.a, t1_3.a, t1_3.a, t1_3.ctid
- Filter: snoop(t1_3.a)
- -> LockRows
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e, t111_3.ctid, t12_4.ctid, t12_4.tableoid
- -> Nested Loop Semi Join
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e, t111_3.ctid, t12_4.ctid, t12_4.tableoid
- -> Seq Scan on public.t111 t111_3
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- Filter: ((t111_3.a > 5) AND (t111_3.a = 8) AND leakproof(t111_3.a))
- -> Append
- -> Seq Scan on public.t12 t12_4
- Output: t12_4.ctid, t12_4.tableoid, t12_4.a
- Filter: (t12_4.a = 8)
- -> Seq Scan on public.t111 t111_4
- Output: t111_4.ctid, t111_4.tableoid, t111_4.a
- Filter: (t111_4.a = 8)
-(74 rows)
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------
+ Remote Subquery Scan on any (datanode_1,datanode_2)
+ -> Update on public.t1
+ Update on public.t1
+ Update on public.t11
+ Update on public.t12
+ Update on public.t111
+ -> Index Scan using t1_a_idx on public.t1
+ Output: (t1.a + 1), t1.b, t1.c, t1.ctid
+ Index Cond: ((t1.a > 5) AND (t1.a = 8))
+ Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
+ SubPlan 1
+ -> Append
+ -> Seq Scan on public.t12 t12_1
+ Filter: (t12_1.a = t1.a)
+ -> Seq Scan on public.t111 t111_1
+ Filter: (t111_1.a = t1.a)
+ SubPlan 2
+ -> Append
+ -> Seq Scan on public.t12 t12_2
+ Output: t12_2.a
+ -> Seq Scan on public.t111 t111_2
+ Output: t111_2.a
+ -> Index Scan using t11_a_idx on public.t11
+ Output: (t11.a + 1), t11.b, t11.c, t11.d, t11.ctid
+ Index Cond: ((t11.a > 5) AND (t11.a = 8))
+ Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND leakproof(t11.a))
+ -> Index Scan using t12_a_idx on public.t12
+ Output: (t12.a + 1), t12.b, t12.c, t12.e, t12.ctid
+ Index Cond: ((t12.a > 5) AND (t12.a = 8))
+ Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND leakproof(t12.a))
+ -> Index Scan using t111_a_idx on public.t111
+ Output: (t111.a + 1), t111.b, t111.c, t111.d, t111.e, t111.ctid
+ Index Cond: ((t111.a > 5) AND (t111.a = 8))
+ Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND leakproof(t111.a))
+(33 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
SELECT * FROM v1 WHERE b=8;