| -- turn off autostats so we don't have to worry about the logging of the autostat queries |
| set gp_autostats_mode = None; |
| -- create needed tables (in a transaction, for speed) |
| begin; |
| create table direct_test |
| ( |
| key int NULL, |
| value varchar(50) NULL |
| ) |
| distributed by (key); |
| create table direct_test_two_column |
| ( |
| key1 int NULL, |
| key2 int NULL, |
| value varchar(50) NULL |
| ) |
| distributed by (key1, key2); |
| create table direct_test_bitmap as select '2008-02-01'::DATE AS DT, |
| case when j <= 996 |
| then 0 |
| when j<= 998 then 2 |
| when j<=999 then 3 |
| when i%10000 < 9000 then 4 |
| when i%10000 < 9800 then 5 |
| when i % 10000 <= 9998 then 5 else 6 |
| end as ind, |
| (i*1017-j)::bigint as s from generate_series(1,10) i, generate_series(1,10) j distributed by (dt); |
| create index direct_test_bitmap_idx on direct_test_bitmap using bitmap (ind, dt); |
| CREATE TABLE direct_test_partition (trans_id int, date date, amount decimal(9,2), region text) DISTRIBUTED BY (trans_id) PARTITION BY RANGE (date) (START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1month') ); |
| create unique index direct_test_uk on direct_test_partition(trans_id,date); |
| create table direct_test_range_partition (a int, b int, c int, d int) distributed by (a) partition by range(d) (start(1) end(10) every(1)); |
| insert into direct_test_range_partition select i, i+1, i+2, i+3 from generate_series(1, 2) i; |
| commit; |
| -- enable printing of printing info |
| set test_print_direct_dispatch_info=on; |
| -- Constant single-row insert, one column in distribution |
| -- DO direct dispatch |
| insert into direct_test values (100, 'cow'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- verify |
| select * from direct_test order by key, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| 100 | cow |
| (1 row) |
| |
| -- Constant single-row update, one column in distribution |
| -- DO direct dispatch |
| -- Known_opt_diff: MPP-21346 |
| update direct_test set value = 'horse' where key = 100; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- verify |
| select * from direct_test order by key, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| 100 | horse |
| (1 row) |
| |
| -- Constant single-row delete, one column in distribution |
| -- DO direct dispatch |
| -- Known_opt_diff: MPP-21346 |
| delete from direct_test where key = 100; |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- verify |
| select * from direct_test order by key, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| (0 rows) |
| |
| -- Constant single-row insert, one column in distribution |
| -- DO direct dispatch |
| insert into direct_test values (NULL, 'cow'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- verify |
| select * from direct_test order by key, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| | cow |
| (1 row) |
| |
| -- DELETE with an IS NULL predicate |
| -- DO direct dispatch |
| delete from direct_test where key is null; |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- Same single-row insert as above, but with DEFAULT instead of an explicit values. |
| -- DO direct dispatch |
| insert into direct_test values (default, 'cow'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- verify |
| select * from direct_test order by key, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| | cow |
| (1 row) |
| |
| -- Constant single-row insert, two columns in distribution |
| -- DO direct dispatch |
| -- Known_opt_diff: MPP-21346 |
| insert into direct_test_two_column values (100, 101, 'cow'); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- verify |
| select * from direct_test_two_column order by key1, key2, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key1 | key2 | value |
| ------+------+------- |
| 100 | 101 | cow |
| (1 row) |
| |
| -- Constant single-row update, two columns in distribution |
| -- DO direct dispatch |
| -- Known_opt_diff: MPP-21346 |
| update direct_test_two_column set value = 'horse' where key1 = 100 and key2 = 101; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- verify |
| select * from direct_test_two_column order by key1, key2, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key1 | key2 | value |
| ------+------+------- |
| 100 | 101 | horse |
| (1 row) |
| |
| -- Constant single-row delete, two columns in distribution |
| -- DO direct dispatch |
| delete from direct_test_two_column where key1 = 100 and key2 = 101; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- verify |
| select * from direct_test_two_column order by key1, key2, value; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key1 | key2 | value |
| ------+------+------- |
| (0 rows) |
| |
| -- expression single-row insert |
| -- DO direct dispatch |
| insert into direct_test (key, value) values ('123',123123); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into direct_test (key, value) values (sqrt(100*10*10),123123); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- |
| -- should get 100 and 123 as the values |
| -- |
| select * from direct_test where value = '123123' order by key; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+-------- |
| 100 | 123123 |
| 123 | 123123 |
| (2 rows) |
| |
| delete from direct_test where value = '123123'; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -------------------------------------------------------------------------------- |
| -- Multiple row update, where clause lists multiple values which hash differently so no direct dispatch |
| -- |
| -- note that if the hash function for values changes then certain segment configurations may actually |
| -- hash all these values to the same content! (and so test would change) |
| -- |
| update direct_test set value = 'pig' where key in (1,2,3,4,5); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| update direct_test_two_column set value = 'pig' where key1 = 100 and key2 in (1,2,3,4); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| update direct_test_two_column set value = 'pig' where key1 in (100,101,102,103,104) and key2 in (1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| update direct_test_two_column set value = 'pig' where key1 in (100,101) and key2 in (1,2); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- Multiple row update, where clause lists values which all hash to same segment |
| -- DO direct dispatch |
| -- CAN'T IMPLEMENT THIS TEST BECAUSE THE # of segments changes again (unless we use a # of segments function, and exploit the simple nature of int4 hashing -- can we do that?) |
| ------------------------------ |
| -- Transaction cases |
| -- |
| -- note that single-row insert can happen BUT DTM will always go to all contents |
| -- |
| begin; |
| insert into direct_test values (1,100); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| rollback; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| begin; |
| insert into direct_test values (1,100); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| insert into direct_test values (2,100); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| insert into direct_test values (3,100); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| rollback; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to PARTIAL contents: 1 0 |
| ------------------- |
| -- MPP-7634: bitmap index scan |
| -- |
| -- Known_opt_diff: MPP-21346 |
| select count(*) from direct_test_bitmap where dt='2008-02-05'; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| -- Known_opt_diff: MPP-21346 |
| select count(*) from direct_test_bitmap where dt='2008-02-01'; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| ---------------------------------------------------------------------------------- |
| -- MPP-7637: partitioned table |
| -- |
| insert into direct_test_partition values (1,'2008-01-02',1,'usa'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| select * from direct_test_partition where trans_id =1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| trans_id | date | amount | region |
| ----------+------------+--------+-------- |
| 1 | 01-02-2008 | 1.00 | usa |
| (1 row) |
| |
| ---------------------------------------------------------------------------------- |
| -- MPP-7638: range table partition |
| -- |
| -- Known_opt_diff: MPP-21346 |
| select count(*) from direct_test_range_partition where a =1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| ---------------------------------------------------------------------------------- |
| -- Prepared statements |
| -- do same as above ones but using prepared statements, verify data goes to the right spot |
| prepare test_insert (int) as insert into direct_test values ($1,100); |
| execute test_insert(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| execute test_insert(2); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| select * from direct_test; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| 2 | 100 |
| | cow |
| 1 | 100 |
| (3 rows) |
| |
| prepare test_update (int) as update direct_test set value = 'boo' where key = $1; |
| -- Known_opt_diff: MPP-21346 |
| execute test_update(2); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| select * from direct_test; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| key | value |
| -----+------- |
| | cow |
| 1 | 100 |
| 2 | boo |
| (3 rows) |
| |
| ------------------------ |
| -- A subquery |
| -- |
| set test_print_direct_dispatch_info=off; |
| CREATE TEMP TABLE direct_dispatch_foo (id integer) DISTRIBUTED BY (id); |
| CREATE TEMP TABLE direct_dispatch_bar (id1 integer, id2 integer) DISTRIBUTED by (id1); |
| INSERT INTO direct_dispatch_foo VALUES (1); |
| INSERT INTO direct_dispatch_bar VALUES (1, 1); |
| INSERT INTO direct_dispatch_bar VALUES (2, 2); |
| INSERT INTO direct_dispatch_bar VALUES (3, 1); |
| set test_print_direct_dispatch_info=on; |
| -- Known_opt_diff: MPP-21346 |
| SELECT * FROM direct_dispatch_foo WHERE id IN |
| (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM direct_dispatch_bar WHERE direct_dispatch_bar.id1 = 1) AS s) ORDER BY 1; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| id |
| ---- |
| 1 |
| (1 row) |
| |
| -- |
| -- this one will NOT do direct dispatch because it is a many slice query and those are disabled right now |
| SELECT * FROM direct_dispatch_foo WHERE id IN |
| (SELECT id2 FROM (SELECT id1, id2 FROM direct_dispatch_bar WHERE direct_dispatch_bar.id1 = 1 UNION |
| SELECT id1, id2 FROM direct_dispatch_bar WHERE direct_dispatch_bar.id1 = 2) AS s) ORDER BY 1; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| id |
| ---- |
| 1 |
| (1 row) |
| |
| -- simple one using an expression on the variable |
| SELECT * from direct_dispatch_foo WHERE id * id = 1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| id |
| ---- |
| 1 |
| (1 row) |
| |
| SELECT * from direct_dispatch_foo WHERE id * id = 1 OR id = 1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| id |
| ---- |
| 1 |
| (1 row) |
| |
| SELECT * from direct_dispatch_foo where id * id = 1 AND id = 1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| id |
| ---- |
| 1 |
| (1 row) |
| |
| -- main plan is direct dispatch and also has init plans |
| update direct_dispatch_bar set id2 = 1 where id1 = 1 and exists (select * from direct_dispatch_foo where id = 2); |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- init plan to see how transaction escalation happens |
| -- Known_opt_diff: MPP-21346 |
| delete from direct_dispatch_foo where id = (select max(id2) from direct_dispatch_bar where id1 = 5); |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 3) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- Known_opt_diff: MPP-21346 |
| delete from direct_dispatch_foo where id * id = (select max(id2) from direct_dispatch_bar where id1 = 5) AND id = 3; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 3) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- Known_opt_diff: MPP-21346 |
| delete from direct_dispatch_foo where id * id = (select max(id2) from direct_dispatch_bar) AND id = 3; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 3) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- tests with subplans (MPP-22019) |
| CREATE TABLE MPP_22019_a ( i INT, j INT) DISTRIBUTED BY (i); |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| INSERT INTO MPP_22019_a ( |
| SELECT i, i * i FROM generate_series(1, 10) AS i); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| CREATE TABLE MPP_22019_b (i INT, j INT) DISTRIBUTED BY (i); |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| INSERT INTO MPP_22019_b (SELECT i, i * i FROM generate_series(1, 10) AS i); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| EXPLAIN SELECT a.* FROM MPP_22019_a a INNER JOIN MPP_22019_b b ON a.i = b.i WHERE a.j NOT IN (SELECT j FROM MPP_22019_a a2 where a2.j = b.j) and a.i = 1; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1765376.88 rows=1 width=8) |
| -> Result (cost=0.00..1765376.88 rows=1 width=8) |
| Filter: (SubPlan 1) |
| -> Hash Join (cost=0.00..862.00 rows=1 width=12) |
| Hash Cond: (a.i = b.i) |
| -> Seq Scan on mpp_22019_a a (cost=0.00..431.00 rows=1 width=8) |
| Filter: (i = 1) |
| -> Hash (cost=431.00..431.00 rows=1 width=8) |
| -> Seq Scan on mpp_22019_b b (cost=0.00..431.00 rows=1 width=8) |
| Filter: (i = 1) |
| SubPlan 1 |
| -> Result (cost=0.00..431.00 rows=1 width=4) |
| Filter: (a2.j = b.j) |
| -> Materialize (cost=0.00..431.00 rows=1 width=4) |
| -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=4) |
| -> Seq Scan on mpp_22019_a a2 (cost=0.00..431.00 rows=1 width=4) |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0 |
| (17 rows) |
| |
| SELECT a.* FROM MPP_22019_a a INNER JOIN MPP_22019_b b ON a.i = b.i WHERE a.j NOT IN (SELECT j FROM MPP_22019_a a2 where a2.j = b.j) and a.i = 1; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| i | j |
| ---+--- |
| (0 rows) |
| |
| SELECT a.* FROM MPP_22019_a a WHERE a.j NOT IN (SELECT j FROM MPP_22019_a a2 where a2.j = a.j) and a.i = 1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| i | j |
| ---+--- |
| (0 rows) |
| |
| -- |
| -- Test direct dispatch with volatile functions, and nextval(). |
| -- |
| -- Simple table. |
| create table ddtesttab (i int, j int, k int8) distributed by (k); |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| create sequence ddtestseq cache 1; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into ddtesttab values (1, 1, 5); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into ddtesttab values (1, 1, 5 + random()); -- volatile expression as distribution key |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into ddtesttab values (1, 1, nextval('ddtestseq')); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into ddtesttab values (1, 1, 5 + nextval('ddtestseq')); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| drop table ddtesttab; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- Partitioned table, with mixed distribution keys. |
| create table ddtesttab (i int, j int, k int8) distributed by (i) partition by |
| range(k) |
| (start(1) end(20) every(10)); |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into ddtesttab values (1, 1, 5); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into ddtesttab values (1, 1, 5 + random()); -- volatile expression as distribution key |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into ddtesttab values (1, 1, nextval('ddtestseq')); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into ddtesttab values (1, 1, 5 + nextval('ddtestseq')); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- One partition is randomly distributed, while others are distributed by key. |
| alter table ddtesttab_1_prt_2 set distributed randomly; |
| ERROR: can't set the distribution policy of "ddtesttab_1_prt_2" |
| HINT: Distribution policy of a partition can only be the same as its parent's. |
| insert into ddtesttab values (1, 1, 5); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into ddtesttab values (1, 1, 5 + random()); -- volatile expression as distribution key |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into ddtesttab values (1, 1, nextval('ddtestseq')); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into ddtesttab values (1, 1, 5 + nextval('ddtestseq')); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| drop table ddtesttab; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| drop sequence ddtestseq; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- Test prepare statement will choose custom plan instead of generic plan when |
| -- considering no direct dispatch cost. |
| create table test_prepare(i int, j int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- insert case |
| prepare p1 as insert into test_prepare values($1, 1); |
| execute p1(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| execute p1(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| execute p1(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| execute p1(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| execute p1(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- the first 5 execute will always use custom plan, focus on the 6th one. |
| execute p1(1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- update case |
| prepare p2 as update test_prepare set j =2 where i =$1; |
| execute p2(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| execute p2(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| execute p2(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| execute p2(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| execute p2(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| execute p2(1); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- select case |
| prepare p3 as select * from test_prepare where i =$1; |
| execute p3(1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| i | j |
| ---+--- |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| (6 rows) |
| |
| execute p3(1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| i | j |
| ---+--- |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| (6 rows) |
| |
| execute p3(1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| i | j |
| ---+--- |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| (6 rows) |
| |
| execute p3(1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| i | j |
| ---+--- |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| (6 rows) |
| |
| execute p3(1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| i | j |
| ---+--- |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| (6 rows) |
| |
| execute p3(1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| i | j |
| ---+--- |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| (6 rows) |
| |
| drop table test_prepare; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- Tests to check direct dispatch if the table is randomly distributed and the |
| -- filter has condition on gp_segment_id |
| -- NOTE: Only EXPLAIN query included, output of SELECT query is not shown. |
| -- Since the table is distributed randomly, the output of SELECT query |
| -- will differ everytime new table is created, and hence the during comparision |
| -- the tests will fail. |
| drop table if exists bar_randDistr; |
| NOTICE: table "bar_randdistr" does not exist, skipping |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| create table bar_randDistr(col1 int, col2 int) distributed randomly; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into bar_randDistr select i,i*2 from generate_series(1, 10)i; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- Case 1 : simple conditions on gp_segment_id |
| explain (costs off) select gp_segment_id, * from bar_randDistr where gp_segment_id=0; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on bar_randdistr |
| Filter: (gp_segment_id = 0) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select gp_segment_id, * from bar_randDistr where gp_segment_id=1 or gp_segment_id=2; |
| QUERY PLAN |
| -------------------------------------------------------------- |
| Gather Motion 2:1 (slice1; segments: 2) |
| -> Seq Scan on bar_randdistr |
| Filter: ((gp_segment_id = 1) OR (gp_segment_id = 2)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select gp_segment_id, count(*) from bar_randDistr group by gp_segment_id; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> GroupAggregate |
| Group Key: gp_segment_id |
| -> Sort |
| Sort Key: gp_segment_id |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: gp_segment_id |
| -> Seq Scan on bar_randdistr |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (9 rows) |
| |
| -- Case2: Conjunction scenario with filter condition on gp_segment_id and column |
| explain (costs off) select gp_segment_id, * from bar_randDistr where gp_segment_id=0 and col1 between 1 and 10; |
| QUERY PLAN |
| ------------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on bar_randdistr |
| Filter: ((gp_segment_id = 0) AND (col1 >= 1) AND (col1 <= 10)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| -- Case3: Disjunction scenario with filter condition on gp_segment_id and column |
| explain (costs off) select gp_segment_id, * from bar_randDistr where gp_segment_id=1 or (col1=6 and gp_segment_id=2); |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on bar_randdistr |
| Filter: ((gp_segment_id = 1) OR ((col1 = 6) AND (gp_segment_id = 2))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| -- Case4: Scenario with constant/variable column and constant/variable gp_segment_id |
| explain (costs off) select gp_segment_id, * from bar_randDistr where col1 =3 and gp_segment_id in (0,1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 2:1 (slice1; segments: 2) |
| -> Seq Scan on bar_randdistr |
| Filter: ((col1 = 3) AND (gp_segment_id = ANY ('{0,1}'::integer[]))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select gp_segment_id, * from bar_randDistr where col1 =3 and gp_segment_id <>1; |
| QUERY PLAN |
| ------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on bar_randdistr |
| Filter: ((col1 = 3) AND (gp_segment_id <> 1)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select gp_segment_id, * from bar_randDistr where col1 between 1 and 5 and gp_segment_id =0; |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on bar_randdistr |
| Filter: ((col1 >= 1) AND (col1 <= 5) AND (gp_segment_id = 0)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select gp_segment_id, * from bar_randDistr where col1 in (1,5) and gp_segment_id <> 0; |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on bar_randdistr |
| Filter: ((col1 = ANY ('{1,5}'::integer[])) AND (gp_segment_id <> 0)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select gp_segment_id, * from bar_randDistr where col1 in (1,5) and gp_segment_id in (0,1); |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------- |
| Gather Motion 2:1 (slice1; segments: 2) |
| -> Seq Scan on bar_randdistr |
| Filter: ((col1 = ANY ('{1,5}'::integer[])) AND (gp_segment_id = ANY ('{0,1}'::integer[]))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| -- Case5: Scenarios with special conditions |
| create function afunc() returns integer as $$ begin return 42; end; $$ language plpgsql; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| create function immutable_func() returns integer as $$ begin return 42; end; $$ language plpgsql immutable; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| explain (costs off) select * from bar_randDistr where col1 = 1; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on bar_randdistr |
| Filter: (col1 = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select * from bar_randDistr where gp_segment_id % 2 = 0; |
| QUERY PLAN |
| ------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on bar_randdistr |
| Filter: ((gp_segment_id % 2) = 0) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select * from bar_randDistr where gp_segment_id=immutable_func(); |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on bar_randdistr |
| Filter: (gp_segment_id = 42) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| explain (costs off) select * from bar_randDistr where gp_segment_id=afunc(); |
| QUERY PLAN |
| ------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on bar_randdistr |
| Filter: (gp_segment_id = afunc()) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| drop table if exists bar_randDistr; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- test direct dispatch via gp_segment_id qual |
| create table t_test_dd_via_segid(id int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into t_test_dd_via_segid select * from generate_series(1, 6); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| explain (costs off) select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=0; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid |
| Filter: (gp_segment_id = 0) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=0; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | id |
| ---------------+---- |
| 0 | 2 |
| 0 | 3 |
| 0 | 4 |
| (3 rows) |
| |
| explain (costs off) select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=1; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid |
| Filter: (gp_segment_id = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | id |
| ---------------+---- |
| 1 | 1 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=2; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid |
| Filter: (gp_segment_id = 2) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=2; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | id |
| ---------------+---- |
| 2 | 5 |
| 2 | 6 |
| (2 rows) |
| |
| explain (costs off) select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=1 or gp_segment_id=2; |
| QUERY PLAN |
| -------------------------------------------------------------- |
| Gather Motion 2:1 (slice1; segments: 2) |
| -> Seq Scan on t_test_dd_via_segid |
| Filter: ((gp_segment_id = 1) OR (gp_segment_id = 2)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=1 or gp_segment_id=2; |
| INFO: (slice 1) Dispatch command to PARTIAL contents: 1 2 |
| gp_segment_id | id |
| ---------------+---- |
| 1 | 1 |
| 2 | 5 |
| 2 | 6 |
| (3 rows) |
| |
| explain (costs off) select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=1 or gp_segment_id=2 or gp_segment_id=3; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------- |
| Gather Motion 2:1 (slice1; segments: 2) |
| -> Seq Scan on t_test_dd_via_segid |
| Filter: ((gp_segment_id = 1) OR (gp_segment_id = 2) OR (gp_segment_id = 3)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, id from t_test_dd_via_segid where gp_segment_id=1 or gp_segment_id=2 or gp_segment_id=3; |
| INFO: (slice 1) Dispatch command to PARTIAL contents: 1 2 |
| gp_segment_id | id |
| ---------------+---- |
| 1 | 1 |
| 2 | 5 |
| 2 | 6 |
| (3 rows) |
| |
| explain (costs off) select t1.gp_segment_id, t2.gp_segment_id, * from t_test_dd_via_segid t1, t_test_dd_via_segid t2 where t1.gp_segment_id=t2.id; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (t1.gp_segment_id = t2.id) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: t1.gp_segment_id |
| -> Seq Scan on t_test_dd_via_segid t1 |
| -> Hash |
| -> Seq Scan on t_test_dd_via_segid t2 |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (9 rows) |
| |
| select t1.gp_segment_id, t2.gp_segment_id, * from t_test_dd_via_segid t1, t_test_dd_via_segid t2 where t1.gp_segment_id=t2.id; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | gp_segment_id | id | id |
| ---------------+---------------+----+---- |
| 1 | 1 | 1 | 1 |
| 2 | 0 | 5 | 2 |
| 2 | 0 | 6 | 2 |
| (3 rows) |
| |
| -- flaky tests |
| -- explain (costs off) select gp_segment_id, count(*) from t_test_dd_via_segid group by gp_segment_id; |
| -- select gp_segment_id, count(*) from t_test_dd_via_segid group by gp_segment_id; |
| -- test direct dispatch via gp_segment_id qual with conjunction |
| create table t_test_dd_via_segid_conj(a int, b int); |
| 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into t_test_dd_via_segid_conj select i,i from generate_series(1, 10)i; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where gp_segment_id=0 and a between 1 and 10; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((gp_segment_id = 0) AND (a >= 1) AND (a <= 10)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where gp_segment_id=0 and a between 1 and 10; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 0 | 2 | 2 |
| 0 | 3 | 3 |
| 0 | 4 | 4 |
| 0 | 7 | 7 |
| 0 | 8 | 8 |
| (5 rows) |
| |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where b between 1 and 5 and gp_segment_id=2 and a between 1 and 10; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((b >= 1) AND (b <= 5) AND (gp_segment_id = 2) AND (a >= 1) AND (a <= 10)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where b between 1 and 5 and gp_segment_id=2 and a between 1 and 10; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 2 | 5 | 5 |
| (1 row) |
| |
| --test direct dispatch via gp_segment_id with disjunction |
| explain (costs off) select * from t_test_dd_via_segid_conj where gp_segment_id=1 or (a=3 and gp_segment_id=2); |
| QUERY PLAN |
| ---------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((gp_segment_id = 1) OR ((a = 3) AND (gp_segment_id = 2))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select * from t_test_dd_via_segid_conj where gp_segment_id=1 or (a=3 and gp_segment_id=2); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| --test direct dispatch with constant distribution column and constant/variable gp_segment_id condition |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where a =3 and b between 1 and 10 and gp_segment_id in (0,1); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((a = 3) AND (b >= 1) AND (b <= 10) AND (gp_segment_id = ANY ('{0,1}'::integer[]))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where a =3 and b between 1 and 10 and gp_segment_id in (0,1); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 0 | 3 | 3 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where a =3 and b between 1 and 10 and gp_segment_id <>1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((a = 3) AND (b >= 1) AND (b <= 10) AND (gp_segment_id <> 1)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where a =3 and b between 1 and 10 and gp_segment_id <>1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 0 | 3 | 3 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where a =3 and b between 1 and 100 and gp_segment_id =0; |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((a = 3) AND (b >= 1) AND (b <= 100) AND (gp_segment_id = 0)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where a =3 and b between 1 and 100 and gp_segment_id =0; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 0 | 3 | 3 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where a in (1,3) and gp_segment_id <> 0; |
| QUERY PLAN |
| --------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((a = ANY ('{1,3}'::integer[])) AND (gp_segment_id <> 0)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where a in (1,3) and gp_segment_id <> 0; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t_test_dd_via_segid_conj where a in (1,3) and gp_segment_id in (0,1); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------- |
| Gather Motion 2:1 (slice1; segments: 2) |
| -> Seq Scan on t_test_dd_via_segid_conj |
| Filter: ((a = ANY ('{1,3}'::integer[])) AND (gp_segment_id = ANY ('{0,1}'::integer[]))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t_test_dd_via_segid_conj where a in (1,3) and gp_segment_id in (0,1); |
| INFO: (slice 1) Dispatch command to PARTIAL contents: 0 1 |
| gp_segment_id | a | b |
| ---------------+---+--- |
| 1 | 1 | 1 |
| 0 | 3 | 3 |
| (2 rows) |
| |
| --test direct dispatch if distribution column is of varchar type |
| drop table if exists t1_varchar; |
| NOTICE: table "t1_varchar" does not exist, skipping |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| create table t1_varchar(col1_varchar varchar, col2_int int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1_varchar' 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into t1_varchar values ('a',1); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into t1_varchar values ('b',2); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into t1_varchar values ('c',3); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into t1_varchar values ('d',4); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into t1_varchar values ('e',5); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| insert into t1_varchar values ('97',6); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 'c'; |
| QUERY PLAN |
| ---------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t1_varchar |
| Filter: ((col1_varchar)::text = 'c'::text) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 'c'; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | c | 3 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar <>'c'; |
| QUERY PLAN |
| ----------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: ((col1_varchar)::text <> 'c'::text) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar <>'c'; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 1 | b | 2 |
| 1 | e | 5 |
| 0 | d | 4 |
| 2 | a | 1 |
| 2 | 97 | 6 |
| (5 rows) |
| |
| --test direct dispatch if distribution column is of varchar type and disjunction scenario |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar in ('a','b'); |
| QUERY PLAN |
| ---------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: ((col1_varchar)::text = ANY ('{a,b}'::text[])) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar in ('a','b'); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 1 | b | 2 |
| 2 | a | 1 |
| (2 rows) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 'a' or col1_varchar = 'b'; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: (((col1_varchar)::text = 'a'::text) OR ((col1_varchar)::text = 'b'::text)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 'a' or col1_varchar = 'b'; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 1 | b | 2 |
| 2 | a | 1 |
| (2 rows) |
| |
| --test direct dispatch if distribution column is of varchar type, having disjunction condition |
| -- or an additional conjunction constraint using another table column or both |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 'c' and col2_int=3; |
| QUERY PLAN |
| ------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t1_varchar |
| Filter: (((col1_varchar)::text = 'c'::text) AND (col2_int = 3)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 'c' and col2_int=3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | c | 3 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 'a' and col2_int in (1,3); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t1_varchar |
| Filter: (((col1_varchar)::text = 'a'::text) AND (col2_int = ANY ('{1,3}'::integer[]))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 'a' and col2_int in (1,3); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | a | 1 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 'a' and col2_int not in (2,3); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t1_varchar |
| Filter: (((col1_varchar)::text = 'a'::text) AND (col2_int <> ALL ('{2,3}'::integer[]))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 'a' and col2_int not in (2,3); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | a | 1 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar in ('a', 'b') and col2_int=2; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: (((col1_varchar)::text = ANY ('{a,b}'::text[])) AND (col2_int = 2)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar in ('a', 'b') and col2_int=2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 1 | b | 2 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where (col1_varchar = 'a' or col1_varchar = 'b') and col2_int=1; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: ((((col1_varchar)::text = 'a'::text) OR ((col1_varchar)::text = 'b'::text)) AND (col2_int = 1)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where (col1_varchar = 'a' or col1_varchar = 'b') and col2_int=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | a | 1 |
| (1 row) |
| |
| --Test direct dispatch with explicit typecasting |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 97::VARCHAR; |
| QUERY PLAN |
| ----------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on t1_varchar |
| Filter: ((col1_varchar)::text = '97'::text) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 97::VARCHAR; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | 97 | 6 |
| (1 row) |
| |
| -- varchar hash and bpchar hash belong to different opfamilies |
| -- hash distribution of col1_varchar and col1_varchar::bpchar |
| -- could assign values to different segments. Therefore, the |
| -- gather motion applies to all 3 segments, and no direct |
| -- dispatch occurs. |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = 'c'::char; |
| QUERY PLAN |
| -------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: ((col1_varchar)::bpchar = 'c'::character(1)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = 'c'::char; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| 2 | c | 3 |
| (1 row) |
| |
| explain (costs off) select gp_segment_id, * from t1_varchar where col1_varchar = '2'::char; |
| QUERY PLAN |
| -------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t1_varchar |
| Filter: ((col1_varchar)::bpchar = '2'::character(1)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select gp_segment_id, * from t1_varchar where col1_varchar = '2'::char; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| gp_segment_id | col1_varchar | col2_int |
| ---------------+--------------+---------- |
| (0 rows) |
| |
| --No direct dispatch case, scenario: cast exists but not binary coercible |
| drop table if exists t3; |
| NOTICE: table "t3" does not exist, skipping |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| create table t3 (c1 timestamp without time zone); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| insert into t3 values ('2015-07-03 00:00:00'::timestamp without time zone); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| explain (costs off) select c1 from t3 where c1 = '2015-07-03'::date; |
| QUERY PLAN |
| ------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on t3 |
| Filter: (c1 = '07-03-2015'::date) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select c1 from t3 where c1 = '2015-07-03'::date; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| c1 |
| -------------------------- |
| Fri Jul 03 00:00:00 2015 |
| (1 row) |
| |
| drop table t3; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| drop table t1_varchar; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| --check direct dispatch working based on the distribution policy of relation |
| drop extension if exists citext cascade; |
| NOTICE: extension "citext" does not exist, skipping |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| drop table if exists srt_dd; |
| NOTICE: table "srt_dd" does not exist, skipping |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| CREATE EXTENSION citext; |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| create table srt_dd (name CITEXT); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| INSERT INTO srt_dd (name) |
| VALUES ('abb'), |
| ('ABA'), |
| ('ABC'), |
| ('abd'); |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| -- text hash/btree and citext hash/btree belong to different opfamilies |
| -- hash distribution of name and name::text could assign values to |
| -- different segments. Therefore, the gather motion applies to all 3 |
| -- segments, and no direct dispatch occurs. |
| explain (costs off) select LOWER(name) as aba FROM srt_dd WHERE name = 'ABA'::text; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on srt_dd |
| Filter: ((name)::text = 'ABA'::text) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select LOWER(name) as aba FROM srt_dd WHERE name = 'ABA'::text; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| aba |
| ----- |
| aba |
| (1 row) |
| |
| explain (costs off) delete from srt_dd where name='ABA'::text; |
| QUERY PLAN |
| ---------------------------------------------- |
| Delete on srt_dd |
| -> Seq Scan on srt_dd |
| Filter: ((name)::text = 'ABA'::text) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| delete from srt_dd where name='ABA'::text; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| drop extension if exists citext cascade; |
| NOTICE: drop cascades to table srt_dd |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| drop table if exists srt_dd; |
| NOTICE: table "srt_dd" does not exist, skipping |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2 |
| -- test direct dispatch via SQLValueFunction and FuncExpr for single row insertion. |
| create table t_sql_value_function1 (a int, b date); |
| 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| create table t_sql_value_function2 (a date); |
| 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| explain (costs off) insert into t_sql_value_function1 values(1, current_timestamp); |
| QUERY PLAN |
| --------------------------------------- |
| Insert on t_sql_value_function1 |
| -> Result |
| -> Result |
| -> Result |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| insert into t_sql_value_function1 values(1, current_timestamp); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| explain (costs off) insert into t_sql_value_function2 values(current_timestamp); |
| QUERY PLAN |
| --------------------------------------- |
| Insert on t_sql_value_function2 |
| -> Result |
| -> Result |
| -> Result |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| insert into t_sql_value_function2 values(current_timestamp); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| explain (costs off) insert into t_sql_value_function1 values(2, now()); |
| QUERY PLAN |
| --------------------------------------- |
| Insert on t_sql_value_function1 |
| -> Result |
| -> Result |
| -> Result |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| insert into t_sql_value_function1 values(2, now()); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| explain (costs off) insert into t_sql_value_function2 values(now()); |
| QUERY PLAN |
| --------------------------------------- |
| Insert on t_sql_value_function2 |
| -> Result |
| -> Result |
| -> Result |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| insert into t_sql_value_function2 values(now()); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- Convert policy of root to random when one of its children has a different distribution policy. |
| -- The only allowed difference between parent and children is for the parent to be hash distributed, |
| -- and its child part to be randomly distributed. |
| create table t_hash_partition |
| ( |
| r_regionkey integer not null, |
| r_name char(25) |
| ) |
| partition by range (r_regionkey) |
| ( |
| partition region1 start (0), |
| partition region2 start (3), |
| partition region3 start (5) end (8) |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'r_regionkey' 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. |
| INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2 |
| INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2 |
| set allow_system_table_mods=true; |
| -- select: root & leaf are all the same hash dist, will direct dispatch |
| begin; |
| select * from t_hash_partition where r_regionkey=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| r_regionkey | r_name |
| -------------+-------- |
| (0 rows) |
| |
| select * from t_hash_partition_1_prt_region1 where r_regionkey=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| r_regionkey | r_name |
| -------------+-------- |
| (0 rows) |
| |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| -- delete: root & leaf are all the same hash dist, will direct dispatch |
| begin; |
| -- orca does not handle direct dispatch for DELETE or UPDATE now |
| -- also orca does not handle DELETE/UPDATE for partitioned tables now. |
| explain (costs off) delete from t_hash_partition where r_regionkey=1; |
| QUERY PLAN |
| --------------------------------------------------------------------- |
| Delete on t_hash_partition |
| Delete on t_hash_partition_1_prt_region1 t_hash_partition_1 |
| -> Seq Scan on t_hash_partition_1_prt_region1 t_hash_partition_1 |
| Filter: (r_regionkey = 1) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| delete from t_hash_partition where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to SINGLE content |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| begin; |
| -- orca does not handle direct dispatch for DELETE or UPDATE now |
| delete from t_hash_partition_1_prt_region1 where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to SINGLE content |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| -- update: root & leaf are all the same hash dist, will direct dispatch |
| begin; |
| -- orca does not handle direct dispatch for DELETE or UPDATE now |
| -- also orca does not handle DELETE/UPDATE for partitioned tables now. |
| explain (costs off) update t_hash_partition set r_name = 'CHINA' where r_regionkey=1; |
| QUERY PLAN |
| --------------------------------------------------------------------- |
| Update on t_hash_partition |
| Update on t_hash_partition_1_prt_region1 t_hash_partition_1 |
| -> Seq Scan on t_hash_partition_1_prt_region1 t_hash_partition_1 |
| Filter: (r_regionkey = 1) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| update t_hash_partition set r_name = 'CHINA' where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to SINGLE content |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| begin; |
| -- orca does not handle direct dispatch for DELETE or UPDATE now |
| update t_hash_partition_1_prt_region1 set r_name = 'CHINA' where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| -- insert |
| begin; |
| -- only consider target's policy, will direct dispatch |
| insert into t_hash_partition values(1,'CHINA'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| -- leaf is also hash dist, will direct dispatch |
| insert into t_hash_partition_1_prt_region1 values(1,'CHINA'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| -- can not alter distributed of child partition, we change one child policy to random |
| update gp_distribution_policy set distkey='',distclass='' where localoid::regclass::text = 't_hash_partition_1_prt_region1'; |
| -- select |
| begin; |
| -- root & leaf policy mismatch, will not direct dispatch |
| select * from t_hash_partition where r_regionkey=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| r_regionkey | r_name |
| -------------+-------- |
| (0 rows) |
| |
| -- the leaf is randomly dist now, will not direct dispatch |
| select * from t_hash_partition_1_prt_region1 where r_regionkey=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| r_regionkey | r_name |
| -------------+-------- |
| (0 rows) |
| |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| -- delete |
| begin; |
| -- root & leaf policy mismatch, will not direct dispatch |
| delete from t_hash_partition where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| begin; |
| -- this leaf is randomly dist, will not direct dispatch |
| delete from t_hash_partition_1_prt_region1 where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| -- update |
| begin; |
| -- root & leaf policy mismatch, will not direct dispatch |
| update t_hash_partition set r_name = 'CHINA' where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| begin; |
| -- this leaf is randomly dist, will not direct dispatch |
| update t_hash_partition_1_prt_region1 set r_name = 'CHINA' where r_regionkey=1; |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| -- insert |
| begin; |
| -- only consider target's policy, will direct dispatch |
| insert into t_hash_partition values(1,'CHINA'); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content |
| begin; |
| -- this leaf is randomly dist, will not direct dispatch |
| insert into t_hash_partition_1_prt_region1 values(1,'CHINA'); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 |
| abort; |
| INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 |
| -- cleanup |
| set test_print_direct_dispatch_info=off; |
| set allow_system_table_mods=off; |
| -- https://github.com/greenplum-db/gpdb/issues/14887 |
| -- If opno of clause does not belong to opfamily of distributed key, |
| -- do not use direct dispatch to resolve wrong result |
| create table t_14887(a varchar); |
| 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 t_14887 values('a '); |
| explain select * from t_14887 where a = 'a'::bpchar; |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8) |
| -> Seq Scan on t_14887 (cost=0.00..431.00 rows=1 width=8) |
| Filter: ((a)::bpchar = 'a'::bpchar) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select * from t_14887 where a = 'a'::bpchar; |
| a |
| ------ |
| a |
| (1 row) |
| |
| -- texteq does not belong to the hash opfamily of the table's citext distkey. |
| -- But from the implementation can deduce: texteq ==> citext_eq, and we can |
| -- do the direct dispatch. |
| -- But we do not have the kind of implication rule in Postgres: texteq ==> citext_eq. |
| -- Also partition table with citext as hash key and condition with text type |
| -- does not do partition prune. |
| CREATE EXTENSION if not exists citext; |
| drop table t_14887; |
| create table t_14887(a citext); |
| 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 t_14887 values('A'),('a'); |
| explain select * from t_14887 where a = 'a'::text; |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8) |
| -> Seq Scan on t_14887 (cost=0.00..431.00 rows=1 width=8) |
| Filter: ((a)::text = 'a'::text) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 rows) |
| |
| select * from t_14887 where a = 'a'::text; |
| a |
| --- |
| a |
| (1 row) |
| |
| drop table t_14887; |
| create table t_14887 (a citext) partition by hash (a); |
| 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 t0_14887 partition of t_14887 for values with (modulus 3,remainder 0); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table t1_14887 partition of t_14887 for values with (modulus 3,remainder 1); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table t2_14887 partition of t_14887 for values with (modulus 3,remainder 2); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| explain select * from t_14887 where a = 'a'::text; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..764.38 rows=158 width=32) |
| -> Append (cost=0.00..762.26 rows=53 width=32) |
| -> Seq Scan on t0_14887 t_14887_1 (cost=0.00..254.00 rows=18 width=32) |
| Filter: ((a)::text = 'a'::text) |
| -> Seq Scan on t1_14887 t_14887_2 (cost=0.00..254.00 rows=18 width=32) |
| Filter: ((a)::text = 'a'::text) |
| -> Seq Scan on t2_14887 t_14887_3 (cost=0.00..254.00 rows=18 width=32) |
| Filter: ((a)::text = 'a'::text) |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| begin; |
| drop table if exists direct_test; |
| drop table if exists direct_test_two_column; |
| drop table if exists direct_test_bitmap; |
| drop table if exists direct_test_partition; |
| drop table if exists direct_test_range_partition; |
| drop table if exists direct_dispatch_foo; |
| drop table if exists direct_dispatch_bar; |
| drop table if exists MPP_22019_a; |
| drop table if exists MPP_22019_b; |
| drop table if exists t_sql_value_function1; |
| drop table if exists t_sql_value_function2; |
| drop table if exists t_hash_partition; |
| drop table if exists t_14887; |
| drop extension if exists citext cascade; |
| commit; |
