diff options
author | Tomas Vondra | 2017-07-30 21:52:54 +0000 |
---|---|---|
committer | Tomas Vondra | 2017-07-31 01:22:02 +0000 |
commit | 295f3b607239153b22c1d2912e5140e54a8a4838 (patch) | |
tree | 0a7c1f26c8d49ab1f7452866e93cb37e9c09d9a1 /src | |
parent | fc3148e7e327b8c7c5da4c9a5816e8c61061e970 (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.out | 234 |
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; |