Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / DML_over_joins_optimizer.out
blob: c0d7286affd89712d5e53c007611bca4d1e12a44 [file] [log] [blame]
-- ----------------------------------------------------------------------
-- Test: setup_schema.sql
-- ----------------------------------------------------------------------
create schema DML_over_joins;
set search_path to DML_over_joins;
set optimizer_trace_fallback=on;
-- ----------------------------------------------------------------------
-- Test: heap_motion1.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
-- r,s colocated on join attributes
-- delete: using clause, subquery, initplan
-- update: join and subsubquery
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
-- end_ignore
create table r (a int4, b int4) distributed by (a);
create table s (a int4, b int4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
analyze r;
analyze s;
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
-- Redistribute s
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
explain update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Update on s (cost=0.00..865.66 rows=34 width=1)
-> Hash Semi Join (cost=0.00..862.80 rows=100 width=18)
Hash Cond: (s_1.a = r.b)
-> Seq Scan on s (cost=0.00..431.00 rows=100 width=18)
-> Hash (cost=431.15..431.15 rows=10000 width=4)
-> Result (cost=0.00..431.15 rows=10000 width=4)
-> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..431.14 rows=10000 width=4)
Hash Key: r.b
-> Seq Scan on r (cost=0.00..431.07 rows=3334 width=4)
Optimizer: Pivotal Optimizer (GPORCA)
(10 rows)
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
explain delete from s where exists (select 1 from r where s.a = r.b);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Delete on s (cost=0.00..865.66 rows=34 width=1)
-> Hash Semi Join (cost=0.00..862.80 rows=34 width=18)
Hash Cond: (s_1.a = r.b)
-> Seq Scan on s (cost=0.00..431.00 rows=34 width=18)
-> Hash (cost=431.15..431.15 rows=3334 width=4)
-> Result (cost=0.00..431.15 rows=3334 width=4)
-> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..431.14 rows=3334 width=4)
Hash Key: r.b
-> Seq Scan on r (cost=0.00..431.07 rows=3334 width=4)
Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0
(10 rows)
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion2.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
-- r,s colocated on join attributes
-- delete: using clause, subquery, initplan
-- update: join and subsubquery
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
-- end_ignore
create table r (a int8, b int8) distributed by (a);
create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
-- Redistribute s
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion3.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
-- r,s colocated on join attributes
-- delete: using clause, subquery, initplan
-- update: join and subsubquery
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
-- end_ignore
create table r (a float4, b float4) distributed by (a);
create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
-- Redistribute s
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion4.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
-- r,s colocated on join attributes
-- delete: using clause, subquery, initplan
-- update: join and subsubquery
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
-- end_ignore
create table r (a float(24), b float(24)) distributed by (a);
create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
-- Redistribute s
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion5.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
-- r,s colocated on join attributes
-- delete: using clause, subquery, initplan
-- update: join and subsubquery
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
-- end_ignore
create table r (a float(53), b float(53)) distributed by (a);
create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
-- Redistribute s
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
------------------------------------------------------------
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
------------------------------------------------------------
-- Update with Motion:
-- Updating the distribution key
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a int4, b int4) distributed by (a);
create table s (a int4, b int4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
create table p (a int4, b int4, c int4)
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
------------------------------------------------------------
update p set c = c + 1 where c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
update p set c = c + 1 where b in (select b from s) and c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select tableoid::regclass, c, count(*) from p group by 1, 2;
tableoid | c | count
---------------+---+-------
p_1_prt_3 | 2 | 1667
p_1_prt_4 | 3 | 1667
p_1_prt_extra | 5 | 1666
p_1_prt_5 | 4 | 1667
p_1_prt_2 | 1 | 3333
(5 rows)
------------------------------------------------------------
-- Update with Motion:
-- Updating the distribution key
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a int8, b int8) distributed by (a);
create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
create table p (a int8, b int8, c int8)
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
------------------------------------------------------------
update p set c = c + 1 where c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
update p set c = c + 1 where b in (select b from s where b = 36);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select tableoid::regclass, c, count(*) from p group by 1, 2;
tableoid | c | count
---------------+---+-------
p_1_prt_3 | 2 | 1668
p_1_prt_4 | 3 | 1667
p_1_prt_extra | 5 | 1666
p_1_prt_5 | 4 | 1667
p_1_prt_2 | 1 | 3332
(5 rows)
------------------------------------------------------------
-- Update with Motion:
-- Updating the distribution key
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a float4, b float4) distributed by (a);
create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
create table p (a float4, b float4, c float4)
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
------------------------------------------------------------
update p set c = c + 1 where c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
update p set c = c + 1 where b in (select b from s) and c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select tableoid::regclass, c, count(*) from p group by 1, 2;
tableoid | c | count
---------------+---+-------
p_1_prt_5 | 4 | 1667
p_1_prt_4 | 3 | 1667
p_1_prt_2 | 1 | 3333
p_1_prt_3 | 2 | 1667
p_1_prt_extra | 5 | 1666
(5 rows)
------------------------------------------------------------
-- Update with Motion:
-- Updating the distribution key
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a float(24), b float(24)) distributed by (a);
create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
create table p (a float(24), b float(24), c float(24))
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
------------------------------------------------------------
update p set c = c + 1 where c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
update p set c = c + 1 where b in (select b from s) and c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select tableoid::regclass, c, count(*) from p group by 1, 2;
tableoid | c | count
---------------+---+-------
p_1_prt_3 | 2 | 1667
p_1_prt_extra | 5 | 1666
p_1_prt_5 | 4 | 1667
p_1_prt_4 | 3 | 1667
p_1_prt_2 | 1 | 3333
(5 rows)
------------------------------------------------------------
-- Update with Motion:
-- Updating the distribution key
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a float(53), b float(53)) distributed by (a);
create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
create table p (a float(53), b float(53), c float(53))
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
delete from s where b = (select min(a) from r where b = s.b);
------------------------------------------------------------
-- Update partition key (requires moving tuples from one partition to another)
------------------------------------------------------------
update p set c = c + 1 where c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
update p set c = c + 1 where b in (select b from s) and c = 0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select tableoid::regclass, c, count(*) from p group by 1, 2;
tableoid | c | count
---------------+---+-------
p_1_prt_extra | 5 | 1666
p_1_prt_4 | 3 | 1667
p_1_prt_3 | 2 | 1667
p_1_prt_2 | 1 | 3333
p_1_prt_5 | 4 | 1667
(5 rows)
-- ----------------------------------------------------------------------
-- Test: partition_motion1.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a int4, b int4) distributed by (a);
create table s (a int4, b int4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
create table p (a int4, b int4, c int4)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
------------------------------------------------------------
update p set b = b + 1 where a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
delete from p where p.a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.b and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- Updates with motion:
-- No motion, colocated distribution key
------------------------------------------------------------
delete from p where a in (select a from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.a and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- No motion of s
------------------------------------------------------------
delete from s where a in (select a from p where p.b = s.b);
select count(*) from s;
count
-------
6
(1 row)
select * from s;
a | b
---+----
1 | 3
2 | 6
3 | 9
4 | 12
5 | 15
9 | 27
(6 rows)
delete from s where b in (select a from p where p.c = s.b);
-- ----------------------------------------------------------------------
-- Test: partition_motion2.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a int8, b int8) distributed by (a);
create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
create table p (a int8, b int8, c int8)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
------------------------------------------------------------
update p set b = b + 1 where a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
delete from p where p.a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.b and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- Updates with motion:
-- No motion, colocated distribution key
------------------------------------------------------------
delete from p where a in (select a from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.a and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- No motion of s
------------------------------------------------------------
delete from s where a in (select a from p where p.b = s.b);
select count(*) from s;
count
-------
6
(1 row)
select * from s;
a | b
---+----
1 | 3
2 | 6
3 | 9
4 | 12
5 | 15
9 | 27
(6 rows)
delete from s where b in (select a from p where p.c = s.b);
-- ----------------------------------------------------------------------
-- Test: partition_motion3.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a float4, b float4) distributed by (a);
create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
create table p (a float4, b float4, c float4)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
------------------------------------------------------------
update p set b = b + 1 where a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
delete from p where p.a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.b and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- Updates with motion:
-- No motion, colocated distribution key
------------------------------------------------------------
delete from p where a in (select a from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.a and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- No motion of s
------------------------------------------------------------
delete from s where a in (select a from p where p.b = s.b);
select count(*) from s;
count
-------
6
(1 row)
select * from s;
a | b
---+----
9 | 27
1 | 3
5 | 15
2 | 6
3 | 9
4 | 12
(6 rows)
delete from s where b in (select a from p where p.c = s.b);
-- ----------------------------------------------------------------------
-- Test: partition_motion4.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a float(24), b float(24)) distributed by (a);
create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
create table p (a float(24), b float(24), c float(24))
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
------------------------------------------------------------
update p set b = b + 1 where a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
delete from p where p.a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.b and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- Updates with motion:
-- No motion, colocated distribution key
------------------------------------------------------------
delete from p where a in (select a from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.a and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- No motion of s
------------------------------------------------------------
delete from s where a in (select a from p where p.b = s.b);
select count(*) from s;
count
-------
6
(1 row)
select * from s;
a | b
---+----
9 | 27
2 | 6
3 | 9
4 | 12
1 | 3
5 | 15
(6 rows)
delete from s where b in (select a from p where p.c = s.b);
-- ----------------------------------------------------------------------
-- Test: partition_motion5.sql
-- ----------------------------------------------------------------------
------------------------------------------------------------
-- Update with Motion:
------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
-- end_ignore
create table r (a float(53), b float(53)) distributed by (a);
create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
create table p (a float(53), b float(53), c float(53))
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
------------------------------------------------------------
update p set b = b + 1 where a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
delete from p where p.a in (select b from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.b and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- Updates with motion:
-- No motion, colocated distribution key
------------------------------------------------------------
delete from p where a in (select a from r where a = p.c);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
delete from p using r where p.a = r.a and r.a = p.c;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
------------------------------------------------------------
-- No motion of s
------------------------------------------------------------
delete from s where a in (select a from p where p.b = s.b);
select count(*) from s;
count
-------
6
(1 row)
select * from s;
a | b
---+----
3 | 9
4 | 12
9 | 27
1 | 3
2 | 6
5 | 15
(6 rows)
delete from s where b in (select a from p where p.c = s.b);
-- ----------------------------------------------------------------------
-- Test: mpp1070.sql
-- ----------------------------------------------------------------------
--
-- MPP-1070
--
-- start_ignore
DROP TABLE IF EXISTS update_test;
NOTICE: table "update_test" does not exist, skipping
DROP TABLE IF EXISTS t1;
NOTICE: table "t1" does not exist, skipping
DROP TABLE IF EXISTS t2;
NOTICE: table "t2" does not exist, skipping
-- end_ignore
CREATE TABLE update_test (
e INT DEFAULT 1,
a INT DEFAULT 10,
b INT,
c TEXT
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'e' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO update_test(a,b,c) VALUES (5, 10, 'foo');
INSERT INTO update_test(b,a) VALUES (15, 10);
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
5 | 10 | foo
10 | 15 |
(2 rows)
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+---+-----
10 | | foo
10 | |
(2 rows)
-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
10 | 10 | foo
10 | 10 |
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
10 | 20 | foo
10 | 20 |
(2 rows)
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
-----+----+-----
100 | 20 | foo
100 | 20 |
(2 rows)
-- ----------------------------------------------
-- Create 2 tables with the same columns, but distributed differently.
CREATE TABLE t1 (id INTEGER, data1 INTEGER, data2 INTEGER) DISTRIBUTED BY (id);
CREATE TABLE t2 (id INTEGER, data1 INTEGER, data2 INTEGER) DISTRIBUTED BY (data1);
INSERT INTO t1 (id, data1, data2) VALUES (1, 1, 1);
INSERT INTO t1 (id, data1, data2) VALUES (2, 2, 2);
INSERT INTO t1 (id, data1, data2) VALUES (3, 3, 3);
INSERT INTO t1 (id, data1, data2) VALUES (4, 4, 4);
INSERT INTO t2 (id, data1, data2) VALUES (1, 2, 101);
INSERT INTO t2 (id, data1, data2) VALUES (2, 1, 102);
INSERT INTO t2 (id, data1, data2) VALUES (3, 4, 103);
INSERT INTO t2 (id, data1, data2) VALUES (4, 3, 104);
-- Now let's try an update that would require us to move info across segments
-- (depending upon exactly where the data is stored, which will vary depending
-- upon the number of segments; in my case, I used only 2 segments).
UPDATE t1 SET data2 = t2.data2 FROM t2 WHERE t1.data1 = t2.data1;
SELECT * from t1;
id | data1 | data2
----+-------+-------
1 | 1 | 102
2 | 2 | 101
3 | 3 | 104
4 | 4 | 103
(4 rows)
-- ----------------------------------------------------------------------
-- Test: query00.sql
-- ----------------------------------------------------------------------
-- start_ignore
drop table if exists r;
drop table if exists s;
drop table if exists p;
drop table if exists t cascade;
NOTICE: table "t" does not exist, skipping
drop table if exists m;
NOTICE: table "m" does not exist, skipping
drop table if exists sales cascade;
NOTICE: table "sales" does not exist, skipping
drop table if exists sales_par cascade;
NOTICE: table "sales_par" does not exist, skipping
drop table if exists sales_par2 cascade;
NOTICE: table "sales_par2" does not exist, skipping
drop table if exists sales_par_CO cascade;
NOTICE: table "sales_par_co" does not exist, skipping
DROP FUNCTION InsertIntoSales(VARCHAR, INTEGER, VARCHAR);
ERROR: function insertintosales(character varying, integer, character varying) does not exist
DROP FUNCTION InsertManyIntoSales(INTEGER, VARCHAR);
ERROR: function insertmanyintosales(integer, character varying) does not exist
-- end_ignore
create table r (a int, b int) distributed by (a);
create table s (a int, b int) distributed by (a);
create table m ();
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry.
alter table m add column a int;
alter table m add column b int;
create table t (region text, id int) distributed by (region);
create table p (a int, b int, c int)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
CREATE TABLE sales (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id);
-- Create one or more indexes before we insert data.
CREATE INDEX sales_index_on_id ON sales (id);
CREATE INDEX sales_index_on_year ON sales (year);
CREATE TABLE sales_par (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY LIST (region)
( PARTITION usa VALUES ('usa'),
PARTITION europe VALUES ('europe'),
PARTITION asia VALUES ('asia'),
DEFAULT PARTITION other_regions)
;
CREATE TABLE sales_par2 (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions)
(PARTITION year2002 START (2002) INCLUSIVE,
PARTITION year2003 START (2003) INCLUSIVE,
PARTITION year2004 START (2004) INCLUSIVE,
PARTITION year2005 START (2005) INCLUSIVE,
PARTITION year2006 START (2006) INCLUSIVE
END (2007) EXCLUSIVE)
;
-- Add default partition
ALTER TABLE sales_par2 ADD DEFAULT PARTITION yearXXXX;
CREATE TABLE sales_par_CO (id int, year int, month int, day int, region text)
WITH (APPENDONLY=True, ORIENTATION=column, COMPRESSTYPE='zlib', COMPRESSLEVEL=1)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions)
(PARTITION year2002 START (2002) INCLUSIVE,
PARTITION year2003 START (2003) INCLUSIVE,
PARTITION year2004 START (2004) INCLUSIVE,
PARTITION year2005 START (2005) INCLUSIVE,
PARTITION year2006 START (2006) INCLUSIVE
END (2007) EXCLUSIVE)
;
-- Add default partition
ALTER TABLE sales_par_CO ADD DEFAULT PARTITION yearXXXX
WITH (APPENDONLY=True, ORIENTATION=column, COMPRESSTYPE='zlib', COMPRESSLEVEL=1);
-- Create a function to insert data.
CREATE FUNCTION insertIntoSales(VARCHAR, INTEGER, VARCHAR)
RETURNS VOID AS
$$
DECLARE
tablename VARCHAR;
BEGIN
tablename = $1;
if (tablename = 'sales')
then INSERT INTO sales (id, year, month, day, region)
VALUES ($2, 2002 + ($2 % 7), ($2 % 12) + 1, ($2 % 28) + 1, $3);
elsif (tablename = 'sales_par')
then INSERT INTO sales_par (id, year, month, day, region)
VALUES ($2, 2002 + ($2 % 7), ($2 % 12) + 1, ($2 % 28) + 1, $3);
elsif (tablename = 'sales_par2')
then INSERT INTO sales_par2 (id, year, month, day, region)
VALUES ($2, 2002 + ($2 % 7), ($2 % 12) + 1, ($2 % 28) + 1, $3);
elsif (tablename = 'sales_par_CO')
then INSERT INTO sales_par_CO (id, year, month, day, region)
VALUES ($2, 2002 + ($2 % 7), ($2 % 12) + 1, ($2 % 28) + 1, $3);
end if;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION InsertManyIntoSales(INTEGER, VARCHAR)
RETURNS VOID AS
$$
DECLARE
rowCount INTEGER;
region VARCHAR;
tablename VARCHAR;
BEGIN
-- Set trace fallback to off to stabilize the test. Issue is that ORCA can
-- fallback due to Query Parameter not supported in DXL. That is
-- non-deterministic based on plancache. This can be removed this after
-- ORCA implements Query Parameters.
set optimizer_trace_fallback=off;
rowCount = $1;
tablename = $2;
FOR i IN 1 .. rowCount LOOP
region = 'antarctica'; -- Never actually used.
IF (i % 4) = 0
THEN region := 'asia';
ELSEIF (i % 4) = 1
THEN region := 'europe';
ELSEIF (i % 4) = 2
THEN region := 'usa';
ELSEIF (i % 4) = 3
THEN region := 'australia';
END IF;
PERFORM insertIntoSales(tablename, i, region );
END LOOP;
set optimizer_trace_fallback=on;
END;
$$ LANGUAGE plpgsql;
--
SELECT InsertManyIntoSales(20,'sales_par_CO');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
-- created tables, functions are cleaned up at test99.sql
-- ----------------------------------------------------------------------
-- Test: query01.sql
-- ----------------------------------------------------------------------
-- update/delete requires motion by joining 3 tables
delete from r;
delete from s;
delete from sales;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
SELECT InsertManyIntoSales(40,'sales');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
ANALYZE r;
ANALYZE s;
ANALYZE sales;
-- index on distribution key
select sales.* from sales,s,r where sales.id = s.b and sales.month = r.b+1;
id | year | month | day | region
----+------+-------+-----+-----------
9 | 2004 | 10 | 10 | europe
21 | 2002 | 10 | 22 | europe
33 | 2007 | 10 | 6 | europe
27 | 2008 | 4 | 28 | australia
39 | 2006 | 4 | 12 | australia
3 | 2005 | 4 | 4 | australia
15 | 2003 | 4 | 16 | australia
18 | 2006 | 7 | 19 | usa
6 | 2008 | 7 | 7 | usa
30 | 2004 | 7 | 3 | usa
(10 rows)
update sales set month = month+1 from r,s where sales.id = s.b and sales.month = r.b+1;
select sales.* from sales,s,r where sales.id = s.b and sales.month = r.b+2;
id | year | month | day | region
----+------+-------+-----+-----------
39 | 2006 | 5 | 12 | australia
3 | 2005 | 5 | 4 | australia
27 | 2008 | 5 | 28 | australia
15 | 2003 | 5 | 16 | australia
18 | 2006 | 8 | 19 | usa
6 | 2008 | 8 | 7 | usa
30 | 2004 | 8 | 3 | usa
21 | 2002 | 11 | 22 | europe
9 | 2004 | 11 | 10 | europe
33 | 2007 | 11 | 6 | europe
(10 rows)
select sales.* from sales where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
id | year | month | day | region
----+------+-------+-----+-----------
27 | 2008 | 5 | 28 | australia
18 | 2006 | 8 | 19 | usa
36 | 2003 | 1 | 9 | asia
9 | 2004 | 11 | 10 | europe
(4 rows)
update sales set region = 'new_region' where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
select sales.* from sales where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
id | year | month | day | region
----+------+-------+-----+------------
27 | 2008 | 5 | 28 | new_region
18 | 2006 | 8 | 19 | new_region
36 | 2003 | 1 | 9 | new_region
9 | 2004 | 11 | 10 | new_region
(4 rows)
select sales.* from sales where id in (select s.b-1 from s,r where s.a = r.b);
id | year | month | day | region
----+------+-------+-----+-----------
17 | 2005 | 6 | 18 | europe
35 | 2002 | 12 | 8 | australia
8 | 2003 | 9 | 9 | asia
26 | 2007 | 3 | 27 | usa
(4 rows)
delete from sales where id in (select s.b-1 from s,r where s.a = r.b);
select sales.* from sales where id in (select s.b-1 from s,r where s.a = r.b);
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
-- no index on distribution key
select s.* from s, r,sales where s.a = r.b and s.b = sales.id;
a | b
----+----
3 | 9
6 | 18
9 | 27
12 | 36
(4 rows)
delete from s using r,sales where s.a = r.b and s.b = sales.id;
select s.* from s, r,sales where s.a = r.b and s.b = sales.id;
a | b
---+---
(0 rows)
select r.* from r,s,sales where s.a = sales.day and sales.month = r.b;
a | b
---+----
1 | 3
1 | 3
2 | 6
3 | 9
3 | 9
4 | 12
(6 rows)
update r set b = r.b + 1 from s,sales where s.a = sales.day and sales.month = r.b;
select r.* from r,s,sales where s.a = sales.day and sales.month = r.b-1;
a | b
---+----
1 | 4
1 | 4
2 | 7
3 | 10
3 | 10
4 | 13
(6 rows)
-- ----------------------------------------------------------------------
-- Test: query02.sql
-- ----------------------------------------------------------------------
-- 3 tables: heap and partition table
delete from r;
delete from s;
delete from sales_par;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
SELECT InsertManyIntoSales(20,'sales_par');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
-- update partition key
select sales_par.* from sales_par where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
id | year | month | day | region
----+------+-------+-----+--------
18 | 2006 | 7 | 19 | usa
9 | 2004 | 10 | 10 | europe
(2 rows)
update sales_par set region = 'new_region' where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select sales_par.* from sales_par where id in (select s.b from s, r where s.a = r.b) and day in (select a from r);
id | year | month | day | region
----+------+-------+-----+------------
18 | 2006 | 7 | 19 | new_region
9 | 2004 | 10 | 10 | new_region
(2 rows)
select sales_par.* from sales_par,s,r where sales_par.id = s.b and sales_par.month = r.b+1;
id | year | month | day | region
----+------+-------+-----+------------
3 | 2005 | 4 | 4 | australia
15 | 2003 | 4 | 16 | australia
18 | 2006 | 7 | 19 | new_region
6 | 2008 | 7 | 7 | usa
9 | 2004 | 10 | 10 | new_region
(5 rows)
update sales_par set month = month+1 from r,s where sales_par.id = s.b and sales_par.month = r.b+1;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select sales_par.* from sales_par,s,r where sales_par.id = s.b and sales_par.month = r.b+2;
id | year | month | day | region
----+------+-------+-----+------------
3 | 2005 | 5 | 4 | australia
15 | 2003 | 5 | 16 | australia
18 | 2006 | 8 | 19 | new_region
6 | 2008 | 8 | 7 | usa
9 | 2004 | 11 | 10 | new_region
(5 rows)
select sales_par.* from sales_par where id in (select s.b-1 from s,r where s.a = r.b);
id | year | month | day | region
----+------+-------+-----+--------
17 | 2005 | 6 | 18 | europe
8 | 2003 | 9 | 9 | asia
(2 rows)
delete from sales_par where id in (select s.b-1 from s,r where s.a = r.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
select sales_par.* from sales_par where id in (select s.b-1 from s,r where s.a = r.b);
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
-- heap table
select s.* from s, r,sales_par where s.a = r.b and s.b = sales_par.id;
a | b
---+----
3 | 9
6 | 18
(2 rows)
delete from s using r,sales_par where s.a = r.b and s.b = sales_par.id;
select s.* from s, r,sales_par where s.a = r.b and s.b = sales_par.id;
a | b
---+---
(0 rows)
-- ----------------------------------------------------------------------
-- Test: query03.sql
-- ----------------------------------------------------------------------
-- 3 tables: heap and 2-level partition CO table + prepare
-- direct dispatch
delete from r;
delete from s;
delete from sales_par2;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
SELECT InsertManyIntoSales(20,'sales_par2');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
-- partition key
select sales_par2.* from sales_par2,s,r where sales_par2.id = s.b and sales_par2.month = r.b+1;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
id | year | month | day | region
----+------+-------+-----+-----------
3 | 2005 | 4 | 4 | australia
15 | 2003 | 4 | 16 | australia
18 | 2006 | 7 | 19 | usa
6 | 2008 | 7 | 7 | usa
9 | 2004 | 10 | 10 | europe
(5 rows)
update sales_par2 set month = month+1 from r,s where sales_par2.id = s.b and sales_par2.month = r.b+1;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select sales_par2.* from sales_par2,s,r where sales_par2.id = s.b and sales_par2.month = r.b+2;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
id | year | month | day | region
----+------+-------+-----+-----------
9 | 2004 | 11 | 10 | europe
15 | 2003 | 5 | 16 | australia
3 | 2005 | 5 | 4 | australia
18 | 2006 | 8 | 19 | usa
6 | 2008 | 8 | 7 | usa
(5 rows)
PREPARE plan0 as update sales_par2 set month = month+1 from r,s where sales_par2.id = s.b and sales_par2.month = r.b+2;
EXECUTE plan0;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select sales_par2.* from sales_par2,s,r where sales_par2.id = s.b and sales_par2.month = r.b+3;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
id | year | month | day | region
----+------+-------+-----+-----------
9 | 2004 | 12 | 10 | europe
3 | 2005 | 6 | 4 | australia
15 | 2003 | 6 | 16 | australia
18 | 2006 | 9 | 19 | usa
6 | 2008 | 9 | 7 | usa
(5 rows)
select sales_par2.* from sales_par2 where id in (select s.b-1 from s,r where s.a = r.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
id | year | month | day | region
----+------+-------+-----+--------
17 | 2005 | 6 | 18 | europe
8 | 2003 | 9 | 9 | asia
(2 rows)
delete from sales_par2 where id in (select s.b-1 from s,r where s.a = r.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select sales_par2.* from sales_par2 where id in (select s.b-1 from s,r where s.a = r.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
-- heap table
select s.* from s, r,sales_par2 where s.a = r.b and s.b = sales_par2.id;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
---+----
3 | 9
6 | 18
(2 rows)
delete from s using r,sales_par2 where s.a = r.b and s.b = sales_par2.id;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select s.* from s, r,sales_par2 where s.a = r.b and s.b = sales_par2.id;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
---+---
(0 rows)
-- ----------------------------------------------------------------------
-- Test: query05.sql
-- ----------------------------------------------------------------------
-- 4 tables: heap, AO, CO tables + duplicate distribution key
delete from r;
delete from s;
drop table if exists s_ao;
NOTICE: table "s_ao" does not exist, skipping
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
create table s_ao (a int, b int) with (appendonly = true) distributed by (a);
insert into s_ao select generate_series(1, 100), generate_series(1, 100) * 3;
-- heap table: delete --
select * from r where b in (select month-1 from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
---+---
3 | 9
1 | 3
2 | 6
(3 rows)
delete from r where b in (select month-1 from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select * from r where b in (select month-1 from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
---+---
(0 rows)
-- hdeap table: update: duplicate distribution key --
SELECT InsertManyIntoSales(20,'sales_par_CO');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
select * from r where a in (select sales_par_CO.id from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
----+----
6 | 18
18 | 54
15 | 45
9 | 27
12 | 36
(5 rows)
update r set b = r.b + 1 where a in (select sales_par_CO.id from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select * from r where a in (select sales_par_CO.id from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
----+----
15 | 46
9 | 28
12 | 37
6 | 19
18 | 55
(5 rows)
-- heap table: delete:
select * from r where a in (select month from sales_par_CO, s_ao, s where sales_par_CO.id = s_ao.b and s_ao.a = s.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
----+----
7 | 21
10 | 30
(2 rows)
delete from r where a in (select month from sales_par_CO, s_ao, s where sales_par_CO.id = s_ao.b and s_ao.a = s.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
select * from r where a in (select month from sales_par_CO, s_ao, s where sales_par_CO.id = s_ao.b and s_ao.a = s.b);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Multi-level partitioned tables
a | b
---+---
(0 rows)
-- ----------------------------------------------------------------------
-- Test: query06.sql
-- ----------------------------------------------------------------------
-- 3 tables: direct dispatch + duplicate distribution key
delete from s;
delete from m;
delete from sales_par;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
insert into s select generate_series(1, 10), generate_series(1, 10) * 4;
insert into m select generate_series(1, 1000), generate_series(1, 1000) * 4;
SELECT InsertManyIntoSales(20,'sales_par');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
-- heap table: duplicate distribution key: delete --
select * from s where a in (select day from sales_par,m where sales_par.id = m.b);
a | b
----+----
13 | 39
17 | 51
9 | 27
9 | 36
5 | 15
21 | 63
5 | 20
(7 rows)
delete from s where a in (select day from sales_par,m where sales_par.id = m.b);
select * from s where a in (select day from sales_par,m where sales_par.id = m.b);
a | b
---+---
(0 rows)
-- direct dispatch: heap table: update --
select * from s where a = 4 and a in (select b from m);
a | b
---+----
4 | 12
4 | 16
(2 rows)
update s set b = b + 1 where a = 4 and a in (select b from m);
select * from s where a = 4 and a in (select b from m);
a | b
---+----
4 | 13
4 | 17
(2 rows)
-- direct dispatch: partitioned table: update --
select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) and region='europe';
id | year | month | day | region
----+------+-------+-----+--------
13 | 2008 | 2 | 14 | europe
5 | 2007 | 6 | 6 | europe
17 | 2005 | 6 | 18 | europe
9 | 2004 | 10 | 10 | europe
(4 rows)
update sales_par set month = month+1 from s where sales_par.id in (s.b, s.b+1) and region = 'europe';
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) and region='europe';
id | year | month | day | region
----+------+-------+-----+--------
5 | 2007 | 7 | 6 | europe
9 | 2004 | 11 | 10 | europe
13 | 2008 | 3 | 14 | europe
17 | 2005 | 7 | 18 | europe
(4 rows)
-- direct dispatch: partitioned table: delete --
select * from sales_par where region='asia' and id in (select b from s where a = 1);
id | year | month | day | region
----+------+-------+-----+--------
4 | 2006 | 5 | 5 | asia
(1 row)
delete from sales_par where region='asia' and id in (select b from s where a = 1);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
select * from sales_par where region='asia' and id in (select b from s where a = 1);
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
select * from sales_par where region='asia' and id in (select b from m where a = 2);
id | year | month | day | region
----+------+-------+-----+--------
8 | 2003 | 9 | 9 | asia
(1 row)
delete from sales_par where region='asia' and id in (select b from m where a = 2);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
select * from sales_par where region='asia' and id in (select b from m where a = 2);
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
-- ----------------------------------------------------------------------
-- Test: query08.sql
-- ----------------------------------------------------------------------
-- prepared statement: 3 tables: direct dispatch + duplicate distribution key
delete from s;
delete from m;
delete from sales_par;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
insert into s select generate_series(1, 10), generate_series(1, 10) * 4;
insert into m select generate_series(1, 1000), generate_series(1, 1000) * 4;
SELECT InsertManyIntoSales(20,'sales_par');
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: SIRV functions
insertmanyintosales
---------------------
(1 row)
-- heap table: duplicate distribution key: delete --
select * from s where a in (select day from sales_par,m where sales_par.id = m.b);
a | b
----+----
13 | 39
17 | 51
5 | 15
21 | 63
5 | 20
9 | 27
9 | 36
(7 rows)
PREPARE plan1 AS delete from s where a in (select day from sales_par,m where sales_par.id = m.b);
EXECUTE plan1;
select * from s where a in (select day from sales_par,m where sales_par.id = m.b);
a | b
---+---
(0 rows)
-- direct dispatch: heap table: update --
select * from s where a = 4 and a in (select b from m);
a | b
---+----
4 | 12
4 | 16
(2 rows)
PREPARE plan2 AS update s set b = b + 1 where a = 4 and a in (select b from m);
EXECUTE plan2;
select * from s where a = 4 and a in (select b from m);
a | b
---+----
4 | 13
4 | 17
(2 rows)
-- direct dispatch: partitioned table: update --
select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) and region='europe';
id | year | month | day | region
----+------+-------+-----+--------
5 | 2007 | 6 | 6 | europe
17 | 2005 | 6 | 18 | europe
13 | 2008 | 2 | 14 | europe
9 | 2004 | 10 | 10 | europe
(4 rows)
PREPARE plan3 AS update sales_par set month = month+1 from s where sales_par.id in (s.b, s.b+1) and region = 'europe';
EXECUTE plan3;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables
select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) and region='europe';
id | year | month | day | region
----+------+-------+-----+--------
5 | 2007 | 7 | 6 | europe
9 | 2004 | 11 | 10 | europe
13 | 2008 | 3 | 14 | europe
17 | 2005 | 7 | 18 | europe
(4 rows)
-- direct dispatch: partitioned table: delete --
select * from sales_par where region='asia' and id in (select b from s where a = 1);
id | year | month | day | region
----+------+-------+-----+--------
4 | 2006 | 5 | 5 | asia
(1 row)
PREPARE plan4 AS delete from sales_par where region='asia' and id in (select b from s where a = 1);
EXECUTE plan4;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
select * from sales_par where region='asia' and id in (select b from s where a = 1);
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
select * from sales_par where region='asia' and id in (select b from m where a = 2);
id | year | month | day | region
----+------+-------+-----+--------
8 | 2003 | 9 | 9 | asia
(1 row)
PREPARE plan5 AS delete from sales_par where region='asia' and id in (select b from m where a = 2);
EXECUTE plan5;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
select * from sales_par where region='asia' and id in (select b from m where a = 2);
id | year | month | day | region
----+------+-------+-----+--------
(0 rows)
-- ----------------------------------------------------------------------
-- Test: Explicit redistributed motion may be removed.
-- ----------------------------------------------------------------------
create table tab1(a int, b int) distributed by (a);
create table tab2(a int, b int) distributed by (a);
analyze tab1;
analyze tab2;
-- colocate, no motion, thus no explicit redistributed motion
explain (costs off) delete from tab1 using tab2 where tab1.a = tab2.a;
QUERY PLAN
------------------------------------------------------
Delete on tab1
-> Hash Join
Hash Cond: (tab1.a = tab2.a)
-> Seq Scan on tab1
-> Hash
-> Seq Scan on tab2
Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(7 rows)
-- redistribtued tab2, no motion above result relation, thus no explicit
-- redistributed motion
explain (costs off) delete from tab1 using tab2 where tab1.a = tab2.b;
QUERY PLAN
------------------------------------------------------------------
Delete on tab1
-> Hash Join
Hash Cond: (tab1.a = tab2.b)
-> Seq Scan on tab1
-> Hash
-> Redistribute Motion 3:3 (slice1; segments: 3)
Hash Key: tab2.b
-> Seq Scan on tab2
Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(9 rows)
-- redistributed motion table, has to add explicit redistributed motion
explain (costs off) delete from tab1 using tab2 where tab1.b = tab2.b;
QUERY PLAN
---------------------------------------------------------------
Delete on tab1
-> Hash Join
Hash Cond: (tab1.b = tab2.b)
-> Seq Scan on tab1
-> Hash
-> Broadcast Motion 3:3 (slice1; segments: 3)
-> Seq Scan on tab2
Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(8 rows)
alter table tab1 set distributed by (b);
create table tab3 (a int, b int) distributed by (b);
insert into tab1 values (1, 1);
insert into tab2 values (1, 1);
insert into tab3 values (1, 1);
analyze tab3;
-- we must not write the WHERE condition as `relname='tab2'`, it matches tables
-- in all the schemas, which will cause problems in other tests; we should use
-- the `::regclass` way as it only matches the table in current search_path.
set allow_system_table_mods=true;
update pg_class set relpages = 10000 where oid='tab2'::regclass;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Queries on master-only tables
update pg_class set reltuples = 100000000 where oid='tab2'::regclass;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Queries on master-only tables
update pg_class set relpages = 100000000 where oid='tab3'::regclass;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Queries on master-only tables
update pg_class set reltuples = 100000 where oid='tab3'::regclass;
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: Queries on master-only tables
-- Planner: there is redistribute motion above tab1, however, we can also
-- remove the explicit redistribute motion here because the final join
-- co-locate with the result relation tab1.
explain (costs off) delete from tab1 using tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.b;
NOTICE: One or more columns in the following table(s) do not have statistics: tab2
HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics.
QUERY PLAN
------------------------------------------------------------------------------
Delete on tab1
-> Result
-> Redistribute Motion 3:3 (slice1; segments: 3)
Hash Key: tab1.b
-> Hash Join
Hash Cond: (tab2.a = tab1.a)
-> Seq Scan on tab2
-> Hash
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: tab1.a
-> Hash Join
Hash Cond: (tab3.b = tab1.b)
-> Seq Scan on tab3
-> Hash
-> Seq Scan on tab1
Optimizer: GPORCA
(16 rows)
-- ----------------------------------------------------------------------
-- Test delete on partition table from join on another partition table
-- ----------------------------------------------------------------------
drop table if exists part_eq_dis_1;
NOTICE: table "part_eq_dis_1" does not exist, skipping
drop table if exists part_eq_dis_2;
NOTICE: table "part_eq_dis_2" does not exist, skipping
create table part_eq_dis_1 (a int4, b int4) partition by range (a) (start(1) end(20) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table part_eq_dis_2 (c int4, d int4) partition by range (c) (start(1) end(20) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into part_eq_dis_1 select generate_series(1,40), generate_series(1,40);
insert into part_eq_dis_2 select generate_series(1,40), generate_series(1,40);
drop table if exists part_neq_dis_1;
NOTICE: table "part_neq_dis_1" does not exist, skipping
drop table if exists part_neq_dis_2;
NOTICE: table "part_neq_dis_2" does not exist, skipping
create table part_neq_dis_1 (a int4, b int4) partition by range (b) (start(1) end(20) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table part_neq_dis_2 (c int4, d int4) partition by range (d) (start(1) end(20) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into part_neq_dis_1 select generate_series(1,40), generate_series(1,40);
insert into part_neq_dis_2 select generate_series(1,40), generate_series(1,40);
-- T1 - distribution partitioned column, T2 - distributed partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where a = (select c from part_eq_dis_2 where c=1);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where a = (select c from part_eq_dis_2 where c=21);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution partitioned column, T2 - non-distributed non-partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where a = (select d from part_eq_dis_2 where c=2);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where a = (select d from part_eq_dis_2 where c=22);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution partitioned column, T2 - distributed non-partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where a = (select c from part_neq_dis_2 where c=3);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where a = (select c from part_neq_dis_2 where c=23);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution partitioned column, T2 - non-distributed partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where a = (select d from part_neq_dis_2 where c=4);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where a = (select d from part_neq_dis_2 where c=24);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution non-partitioned column, T2 - distributed partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where b = (select c from part_eq_dis_2 where c=5);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where b = (select c from part_eq_dis_2 where c=25);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution non-partitioned column, T2 - non-distributed non-partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where b = (select d from part_eq_dis_2 where c=6);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where b = (select d from part_eq_dis_2 where c=26);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution non-partitioned column, T2 - distributed non-partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where b = (select c from part_neq_dis_2 where c=7);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where b = (select c from part_neq_dis_2 where c=27);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution non-partitioned column, T2 - non-distributed partitioned column
-- a) non-default partition
delete from part_eq_dis_1 where b = (select d from part_neq_dis_2 where c=8);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_eq_dis_1 where b = (select d from part_neq_dis_2 where c=28);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution non-partitioned column, T2 - distributed partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where a = (select c from part_eq_dis_2 where c=9);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where a = (select c from part_eq_dis_2 where c=29);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution non-partitioned column, T2 - non-distributed non-partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where a = (select d from part_eq_dis_2 where c=10);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where a = (select d from part_eq_dis_2 where c=30);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution non-partitioned column, T2 - non-distributed partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where a = (select d from part_neq_dis_2 where c=11);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where a = (select d from part_neq_dis_2 where c=31);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - distribution non-partitioned column, T2 - distributed non-partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where a = (select c from part_neq_dis_2 where c=12);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where a = (select c from part_neq_dis_2 where c=32);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution partitioned column, T2 - distributed partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where b = (select c from part_eq_dis_2 where c=13);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where b = (select c from part_eq_dis_2 where c=33);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution partitioned column, T2 - non-distributed non-partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where b = (select d from part_eq_dis_2 where c=14);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where b = (select d from part_eq_dis_2 where c=34);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution partitioned column, T2 - distributed non-partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where b = (select d from part_neq_dis_2 where c=15);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where b = (select d from part_neq_dis_2 where c=35);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- T1 - non-distribution partitioned column, T2 - non-distributed partitioned column
-- a) non-default partition
delete from part_neq_dis_1 where b = (select c from part_neq_dis_2 where c=16);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
-- b) default partition
delete from part_neq_dis_1 where b = (select c from part_neq_dis_2 where c=36);
INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL: Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(delete) on partitioned tables
select * from part_eq_dis_1;
a | b
----+----
12 | 12
15 | 15
20 | 20
30 | 30
31 | 31
35 | 35
36 | 36
38 | 38
40 | 40
16 | 16
18 | 18
19 | 19
29 | 29
34 | 34
37 | 37
39 | 39
9 | 9
10 | 10
11 | 11
13 | 13
14 | 14
17 | 17
32 | 32
33 | 33
(24 rows)
select * from part_neq_dis_1;
a | b
----+----
1 | 1
20 | 20
23 | 23
26 | 26
38 | 38
40 | 40
2 | 2
3 | 3
4 | 4
7 | 7
8 | 8
18 | 18
19 | 19
22 | 22
24 | 24
27 | 27
37 | 37
39 | 39
5 | 5
6 | 6
17 | 17
21 | 21
25 | 25
28 | 28
(24 rows)
reset optimizer_trace_fallback;
-- ----------------------------------------------------------------------
-- Test: teardown.sql
-- ----------------------------------------------------------------------
-- start_ignore
drop schema DML_over_joins cascade;
NOTICE: drop cascades to 22 other objects
DETAIL: drop cascades to table update_test
drop cascades to table t1
drop cascades to table t2
drop cascades to table r
drop cascades to table s
drop cascades to table m
drop cascades to table t
drop cascades to table p
drop cascades to table sales
drop cascades to table sales_par
drop cascades to table sales_par2
drop cascades to table sales_par_co
drop cascades to function insertintosales(character varying,integer,character varying)
drop cascades to function insertmanyintosales(integer,character varying)
drop cascades to table s_ao
drop cascades to table tab1
drop cascades to table tab2
drop cascades to table tab3
drop cascades to table part_eq_dis_1
drop cascades to table part_eq_dis_2
drop cascades to table part_neq_dis_1
drop cascades to table part_neq_dis_2
-- end_ignore