| -- |
| -- Direct Dispatch Test when optimizer is on |
| -- |
| -- start_ignore |
| set optimizer_log=on; |
| -- end_ignore |
| set test_print_direct_dispatch_info=on; |
| set gp_autostats_mode = 'None'; |
| -- create table with distribution on a single table |
| create table dd_singlecol_1(a int, b int) distributed by (a); |
| 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 dd_singlecol_1 select g, g%15 from generate_series(1,100) g; |
| 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 dd_singlecol_1 values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| analyze dd_singlecol_1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| -- ctas tests |
| create table dd_ctas_1 as select * from dd_singlecol_1 where a=1 distributed by (a); |
| 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 dd_ctas_2 as select * from dd_singlecol_1 where a is NULL distributed by (a); |
| 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 dd_ctas_1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select * from dd_ctas_2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| | |
| (1 row) |
| |
| drop table dd_ctas_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 |
| drop table dd_ctas_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 |
| -- direct dispatch and queries having with clause |
| with cte as (select * from dd_singlecol_1 where a=1) select * from cte; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| with cte as (select * from dd_singlecol_1) select * from cte where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| with cte1 as (with cte2 as (select * from dd_singlecol_1) select * from cte2 where a=1) select * from cte1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| -- negative tests: joins not supported |
| with cte as (select * from dd_singlecol_1) select * from cte c1, cte c2 where c1.a=1 and c2.a=1 and c1.a=c2.a limit 10; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| -- cte with function scans |
| with cte as (select generate_series(1,10) g) select * from dd_singlecol_1 t1, cte where t1.a=cte.g and t1.a=1 limit 100; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | g |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| -- single column distr key |
| select * from dd_singlecol_1 where a in (10,11,12); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ----+---- |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| (3 rows) |
| |
| select * from dd_singlecol_1 where a=10 or a=11 or a=12; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ----+---- |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| (3 rows) |
| |
| select * from dd_singlecol_1 where a is null or a=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| 1 | 1 |
| | |
| (2 rows) |
| |
| -- projections and disjunction |
| select b from dd_singlecol_1 where a=1 or a=2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| b |
| --- |
| 1 |
| 2 |
| (2 rows) |
| |
| -- single column distr key, values hash to the same segment |
| select * from dd_singlecol_1 where a=10 or a=11; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ----+---- |
| 10 | 10 |
| 11 | 11 |
| (2 rows) |
| |
| select * from dd_singlecol_1 where a in (10, 11); |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ----+---- |
| 10 | 10 |
| 11 | 11 |
| (2 rows) |
| |
| select * from dd_singlecol_1 where a is null or a=2; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| 2 | 2 |
| | |
| (2 rows) |
| |
| select * from dd_singlecol_1 where (a,b) in ((10,2),(11,3)); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| (0 rows) |
| |
| select * from dd_singlecol_1 where a between 10 and 11; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ----+---- |
| 10 | 10 |
| 11 | 11 |
| (2 rows) |
| |
| -- partitioned tables |
| create table dd_part_singlecol(a int, b int, c int) distributed by (a) partition by range (b) |
| (start(1) end(100) every (20), default partition extra); |
| 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 dd_part_singlecol select g, g*2, g*3 from generate_series(1,49) g; |
| 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 dd_part_singlecol values (NULL, NULL); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| -- disjunction with partitioned tables |
| select * from dd_part_singlecol where a in (10,11,12); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ----+----+---- |
| 10 | 20 | 30 |
| 11 | 22 | 33 |
| 12 | 24 | 36 |
| (3 rows) |
| |
| select * from dd_part_singlecol where a=10 or a=11 or a=12; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ----+----+---- |
| 10 | 20 | 30 |
| 11 | 22 | 33 |
| 12 | 24 | 36 |
| (3 rows) |
| |
| select * from dd_part_singlecol where a is null or a=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| | | |
| 1 | 2 | 3 |
| (2 rows) |
| |
| -- simple predicates |
| select * from dd_part_singlecol where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| (1 row) |
| |
| select * from dd_part_singlecol where a=1 and b=2; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| (1 row) |
| |
| select * from dd_part_singlecol where a = 1 and b<10; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| (1 row) |
| |
| select * from dd_part_singlecol where a is null; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| | | |
| (1 row) |
| |
| select * from dd_part_singlecol where a is null and b is null; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| | | |
| (1 row) |
| |
| -- complex queries |
| -- projections |
| select b from dd_part_singlecol where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| b |
| --- |
| 2 |
| (1 row) |
| |
| |
| select a+b from dd_part_singlecol where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| ?column? |
| ---------- |
| 3 |
| (1 row) |
| |
| select 'one' from dd_part_singlecol where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| ?column? |
| ---------- |
| one |
| (1 row) |
| |
| select a, 'one' from dd_part_singlecol where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | ?column? |
| ---+---------- |
| 1 | one |
| (1 row) |
| |
| -- group by and sort |
| -- disable parallel for regress tests |
| set enable_parallel = off; |
| select a, count(*) from dd_part_singlecol where a=1 group by a; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| reset enable_parallel; |
| select a, count(*) from dd_part_singlecol where a=1 group by a order by a; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| -- indexes |
| create table dd_singlecol_idx(a int, b int, c int) distributed by (a); |
| 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 index sc_idx_b on dd_singlecol_idx(b); |
| 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 index sc_idx_bc on dd_singlecol_idx(b,c); |
| 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 dd_singlecol_idx select g, g%5,g%5 from generate_series(1,100) g; |
| 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 dd_singlecol_idx values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| create table dd_singlecol_idx2(a int, b int, c int) distributed by (a); |
| 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 index sc_idx_a on dd_singlecol_idx2(a); |
| 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 dd_singlecol_idx2 select g, g%5,g%5 from generate_series(1,100) g; |
| 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 dd_singlecol_idx2 values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| analyze dd_singlecol_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| analyze dd_singlecol_idx2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| -- disjunction with index scans |
| select * from dd_singlecol_idx where (a=1 or a=2) and b<2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select 'one' from dd_singlecol_idx where (a=1 or a=2) and b=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| ?column? |
| ---------- |
| one |
| (1 row) |
| |
| select a, count(*) from dd_singlecol_idx where (a=1 or a=2) and b=1 group by a; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| select count(*) from dd_singlecol_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| count |
| ------- |
| 101 |
| (1 row) |
| |
| -- create table with bitmap indexes |
| create table dd_singlecol_bitmap_idx(a int, b int, c int) distributed by (a); |
| 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 index sc_bitmap_idx_b on dd_singlecol_bitmap_idx using bitmap (b); |
| 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 index sc_bitmap_idx_c on dd_singlecol_bitmap_idx using bitmap (c); |
| 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 dd_singlecol_bitmap_idx select g, g%5,g%5 from generate_series(1,100) g; |
| 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 dd_singlecol_bitmap_idx values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| analyze dd_singlecol_bitmap_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| -- disjunction with bitmap index scans |
| select * from dd_singlecol_bitmap_idx where (a=1 or a=2) and b<2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_bitmap_idx where (a=1 or a=2) and b=2 and c=2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| 2 | 2 | 2 |
| (1 row) |
| |
| select * from dd_singlecol_bitmap_idx where (a=1 or a=2) and (b=2 or c=2); |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| 2 | 2 | 2 |
| (1 row) |
| |
| select * from dd_singlecol_bitmap_idx where a<5 and b=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| -- conjunction with bitmap indexes |
| select * from dd_singlecol_bitmap_idx where a=1 and b=0; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| select * from dd_singlecol_bitmap_idx where a=1 and b<3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_bitmap_idx where a=1 and b>=1 and c<2; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| |
| select * from dd_singlecol_bitmap_idx where a=1 and b=3 and c=3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| -- bitmap indexes on part tables |
| create table dd_singlecol_part_bitmap_idx(a int, b int, c int) |
| distributed by (a) |
| partition by range (b) |
| (start(1) end(100) every (20), default partition extra);; |
| 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 index sc_part_bitmap_idx_b on dd_singlecol_part_bitmap_idx using bitmap(b); |
| 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 dd_singlecol_part_bitmap_idx select g, g%5,g%5 from generate_series(1,100) g; |
| 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 dd_singlecol_part_bitmap_idx values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| analyze dd_singlecol_part_bitmap_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| -- bitmap indexes on partitioned tables |
| select * from dd_singlecol_part_bitmap_idx where a=1 and b=0; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| select * from dd_singlecol_part_bitmap_idx where a=1 and b<3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_part_bitmap_idx where a=1 and b>=1 and c=3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| -- bitmap bool op |
| select * from dd_singlecol_bitmap_idx |
| where a=1 and b=3 and c=3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| -- multi column index |
| create table dd_multicol_idx(a int, b int, c int) distributed by (a,b); |
| 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 index mc_idx_b on dd_multicol_idx(c); |
| 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 dd_multicol_idx |
| select g, g%5, g%5 from generate_series(1,100) g; |
| 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 dd_multicol_idx values(null, null); |
| 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 |
| analyze dd_multicol_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| select count(*) from dd_multicol_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| count |
| ------- |
| 101 |
| (1 row) |
| |
| -- simple index predicates |
| select * from dd_singlecol_idx where a=1 and b=0; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| select * from dd_singlecol_idx where a=1 and b<3; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_idx where a<5 and b=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_idx where a=1 and b>=1 and c<2; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_idx2 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_idx2 where a=1 and b>=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| -- projection |
| select 'one' from dd_singlecol_idx where a=1 and b=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| ?column? |
| ---------- |
| one |
| (1 row) |
| |
| select a+b from dd_singlecol_idx where a=1 and b=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| ?column? |
| ---------- |
| 2 |
| (1 row) |
| |
| -- group by |
| select a, count(*) from dd_singlecol_idx where a=1 and b=1 group by a; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| -- multicol |
| select * from dd_multicol_idx where a=1 and b=1 and c<5; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_multicol_idx where (a=10 or a=11) and (b=1 or b=5) and c=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | c |
| ----+---+--- |
| 11 | 1 | 1 |
| (1 row) |
| |
| -- indexes on partitioned tables |
| create table dd_singlecol_part_idx(a int, b int, c int) |
| distributed by (a) |
| partition by range (b) |
| (start(1) end(100) every (20), default partition extra);; |
| 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 index sc_part_idx_b on dd_singlecol_part_idx(b); |
| 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 dd_singlecol_part_idx select g, g%5,g%5 from generate_series(1,100) g; |
| 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 dd_singlecol_part_idx values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| create table dd_singlecol_part_idx2(a int, b int, c int) |
| distributed by (a) |
| partition by range (b) |
| (start(1) end(100) every (20), default partition extra);; |
| 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 index sc_part_idx_a on dd_singlecol_part_idx2(a); |
| 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 dd_singlecol_part_idx2 select g, g%5,g%5 from generate_series(1,100) g; |
| 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 dd_singlecol_part_idx2 values(null, null); |
| INFO: (slice 0) Dispatch command to SINGLE content |
| INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content |
| analyze dd_singlecol_part_idx; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| analyze dd_singlecol_part_idx2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| -- indexes on partitioned tables |
| select * from dd_singlecol_part_idx where a=1 and b>0; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_part_idx2 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_part_idx2 where a=1 and b>=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| create table dd_singlecol_2(a int, b int) distributed by (b); |
| 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 dd_singlecol_dropped(a int, b int, c int) distributed by (b); |
| 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 |
| alter table dd_singlecol_dropped drop column a; |
| 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 dd_singlecol_2 |
| select g, g%10 from generate_series(1,100) g; |
| 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 dd_singlecol_dropped |
| select g, g%5 from generate_series(1,100) g; |
| 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 |
| -- aggregates |
| select count(*) from dd_singlecol_1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| count |
| ------- |
| 101 |
| (1 row) |
| |
| select count(*) from dd_singlecol_2; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| select count(*) from dd_singlecol_dropped; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| count |
| ------- |
| 100 |
| (1 row) |
| |
| -- simple predicates |
| select * from dd_singlecol_1 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_2 where b=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ----+--- |
| 1 | 1 |
| 11 | 1 |
| 21 | 1 |
| 31 | 1 |
| 41 | 1 |
| 51 | 1 |
| 61 | 1 |
| 71 | 1 |
| 81 | 1 |
| 91 | 1 |
| (10 rows) |
| |
| select * from dd_singlecol_dropped where b=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| b | c |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_1 where a = 1 and b=2; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| (0 rows) |
| |
| select * from dd_singlecol_1 where a = 1 and b<10; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_1 where a is null; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | b |
| ---+--- |
| | |
| (1 row) |
| |
| -- projections |
| select b from dd_singlecol_1 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| b |
| --- |
| 1 |
| (1 row) |
| |
| |
| select a+b from dd_singlecol_1 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| ?column? |
| ---------- |
| 2 |
| (1 row) |
| |
| select 'one' from dd_singlecol_1 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| ?column? |
| ---------- |
| one |
| (1 row) |
| |
| select a, 'one' from dd_singlecol_1 where a=1; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | ?column? |
| ---+---------- |
| 1 | one |
| (1 row) |
| |
| -- group by and sort |
| select a, count(*) from dd_singlecol_1 where a=1 group by a; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| select a, count(*) from dd_singlecol_1 where a=1 group by a order by a; |
| INFO: (slice 1) Dispatch command to SINGLE content |
| a | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| -- inner joins |
| select * from dd_singlecol_1 t1, dd_singlecol_2 t2 where t1.a=t2.a and t1.a=1; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_1 t1, dd_singlecol_2 t2 where t1.a=t2.b and t1.a=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+----+--- |
| 1 | 1 | 91 | 1 |
| 1 | 1 | 81 | 1 |
| 1 | 1 | 71 | 1 |
| 1 | 1 | 61 | 1 |
| 1 | 1 | 51 | 1 |
| 1 | 1 | 41 | 1 |
| 1 | 1 | 31 | 1 |
| 1 | 1 | 21 | 1 |
| 1 | 1 | 11 | 1 |
| 1 | 1 | 1 | 1 |
| (10 rows) |
| |
| select * from dd_singlecol_1 t1, dd_singlecol_2 t2 where t1.b>t2.a and t1.a=1; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+---+--- |
| (0 rows) |
| |
| -- outer joins |
| select * from dd_singlecol_1 t1 left outer join dd_singlecol_2 t2 on (t1.a=t2.a) where t1.a=1; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+---+--- |
| 1 | 1 | 1 | 1 |
| (1 row) |
| |
| select * from dd_singlecol_1 t1 left outer join dd_singlecol_2 t2 on (t1.a=t2.b) where t1.a=1 and t2.b=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+----+--- |
| 1 | 1 | 91 | 1 |
| 1 | 1 | 81 | 1 |
| 1 | 1 | 71 | 1 |
| 1 | 1 | 61 | 1 |
| 1 | 1 | 51 | 1 |
| 1 | 1 | 41 | 1 |
| 1 | 1 | 31 | 1 |
| 1 | 1 | 21 | 1 |
| 1 | 1 | 11 | 1 |
| 1 | 1 | 1 | 1 |
| (10 rows) |
| |
| select * from dd_singlecol_1 t1 left outer join dd_singlecol_2 t2 on (t1.b=t2.b) where t1.a=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ---+---+----+--- |
| 1 | 1 | 91 | 1 |
| 1 | 1 | 81 | 1 |
| 1 | 1 | 71 | 1 |
| 1 | 1 | 61 | 1 |
| 1 | 1 | 51 | 1 |
| 1 | 1 | 41 | 1 |
| 1 | 1 | 31 | 1 |
| 1 | 1 | 21 | 1 |
| 1 | 1 | 11 | 1 |
| 1 | 1 | 1 | 1 |
| (10 rows) |
| |
| select * from dd_singlecol_2 t2 left outer join dd_singlecol_1 t1 on (t1.b=t2.b) where t1.a=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| a | b | a | b |
| ----+---+---+--- |
| 1 | 1 | 1 | 1 |
| 11 | 1 | 1 | 1 |
| 21 | 1 | 1 | 1 |
| 31 | 1 | 1 | 1 |
| 41 | 1 | 1 | 1 |
| 51 | 1 | 1 | 1 |
| 61 | 1 | 1 | 1 |
| 71 | 1 | 1 | 1 |
| 81 | 1 | 1 | 1 |
| 91 | 1 | 1 | 1 |
| (10 rows) |
| |
| -- subqueries |
| select * from dd_singlecol_1 t1 where a=1 and b < (select count(*) from dd_singlecol_2 t2 where t2.a=t1.a); |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| (0 rows) |
| |
| select * from dd_singlecol_1 t1 where a=1 and b in (select count(*) from dd_singlecol_2 t2 where t2.a<=t1.a); |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| select t1.a, t1.b, (select sum(t2.a+t2.b) from dd_singlecol_2 t2 where t2.b=1) from dd_singlecol_1 t1 where t1.a=1; |
| INFO: (slice 3) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 2) Dispatch command to SINGLE content |
| a | b | sum |
| ---+---+----- |
| 1 | 1 | 470 |
| (1 row) |
| |
| -- joins with function scans |
| select * from dd_singlecol_1 t1, generate_series(1,10) g where t1.a=g.g and t1.a=1 limit 10; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b | g |
| ---+---+--- |
| 1 | 1 | 1 |
| (1 row) |
| |
| -- negative cases |
| -- unsupported predicates |
| select * from dd_singlecol_1 where a>1 and a<5; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| 4 | 4 |
| 3 | 3 |
| 2 | 2 |
| (3 rows) |
| |
| select * from dd_singlecol_1 where a=1 or b=5; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ----+--- |
| 50 | 5 |
| 5 | 5 |
| 20 | 5 |
| 35 | 5 |
| 80 | 5 |
| 95 | 5 |
| 1 | 1 |
| 65 | 5 |
| (8 rows) |
| |
| -- group by and sort |
| select b, count(*) from dd_singlecol_1 where a=1 group by b; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| b | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| select b, count(*) from dd_singlecol_1 where a=1 group by b order by b; |
| INFO: (slice 2) Dispatch command to ALL contents: 0 1 2 |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| b | count |
| ---+------- |
| 1 | 1 |
| (1 row) |
| |
| -- randomly distributed tables |
| create table dd_random(a int, b 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 dd_random select g, g%15 from generate_series(1, 100) g; |
| 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 |
| -- non hash distributed tables |
| select * from dd_random where a=1; |
| INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 |
| a | b |
| ---+--- |
| 1 | 1 |
| (1 row) |
| |
| drop table dd_singlecol_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 |
| drop table dd_ctas_1; |
| ERROR: table "dd_ctas_1" does not exist |
| drop table dd_ctas_2; |
| ERROR: table "dd_ctas_2" does not exist |
| drop table dd_part_singlecol; |
| 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 dd_singlecol_idx; |
| 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 dd_singlecol_idx2; |
| 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 dd_singlecol_bitmap_idx; |
| 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 dd_singlecol_part_bitmap_idx; |
| 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 dd_multicol_idx; |
| 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 dd_singlecol_part_idx; |
| 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 dd_singlecol_part_idx2; |
| 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 dd_singlecol_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 table dd_singlecol_dropped; |
| 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 dd_random; |
| 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 |
