diff options
| -rw-r--r-- | src/test/regress/expected/inherit.out | 408 |
1 files changed, 214 insertions, 194 deletions
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 8fcd39a0dc..b30b38a986 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1941,63 +1941,69 @@ create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); explain (costs off) select * from list_parted; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on part_ab_cd - -> Seq Scan on part_ef_gh - -> Seq Scan on part_null_xy -(4 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_ab_cd + -> Seq Scan on part_ef_gh + -> Seq Scan on part_null_xy +(5 rows) explain (costs off) select * from list_parted where a is null; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on part_null_xy - Filter: (a IS NULL) -(3 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_null_xy + Filter: (a IS NULL) +(4 rows) explain (costs off) select * from list_parted where a is not null; - QUERY PLAN ---------------------------------- - Append - -> Seq Scan on part_ab_cd - Filter: (a IS NOT NULL) - -> Seq Scan on part_ef_gh - Filter: (a IS NOT NULL) - -> Seq Scan on part_null_xy - Filter: (a IS NOT NULL) -(7 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_ab_cd + Filter: (a IS NOT NULL) + -> Seq Scan on part_ef_gh + Filter: (a IS NOT NULL) + -> Seq Scan on part_null_xy + Filter: (a IS NOT NULL) +(8 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); - QUERY PLAN ----------------------------------------------------------- - Append - -> Seq Scan on part_ab_cd - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) - -> Seq Scan on part_ef_gh - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(5 rows) + QUERY PLAN +---------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_ab_cd + Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) + -> Seq Scan on part_ef_gh + Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) +(6 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); - QUERY PLAN ---------------------------------------------------------------------------------------- - Append - -> Seq Scan on part_ab_cd - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) - -> Seq Scan on part_ef_gh - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) - -> Seq Scan on part_null_xy - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(7 rows) + QUERY PLAN +--------------------------------------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_ab_cd + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) + -> Seq Scan on part_ef_gh + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) + -> Seq Scan on part_null_xy + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) +(8 rows) explain (costs off) select * from list_parted where a = 'ab'; - QUERY PLAN ------------------------------------------- - Append - -> Seq Scan on part_ab_cd - Filter: ((a)::text = 'ab'::text) -(3 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_ab_cd + Filter: ((a)::text = 'ab'::text) +(4 rows) create table range_list_parted ( a int, @@ -2017,55 +2023,59 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on part_1_10_ab - -> Seq Scan on part_1_10_cd - -> Seq Scan on part_10_20_ab - -> Seq Scan on part_10_20_cd - -> Seq Scan on part_21_30_ab - -> Seq Scan on part_21_30_cd - -> Seq Scan on part_40_inf_ab - -> Seq Scan on part_40_inf_cd - -> Seq Scan on part_40_inf_null -(10 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_1_10_ab + -> Seq Scan on part_1_10_cd + -> Seq Scan on part_10_20_ab + -> Seq Scan on part_10_20_cd + -> Seq Scan on part_21_30_ab + -> Seq Scan on part_21_30_cd + -> Seq Scan on part_40_inf_ab + -> Seq Scan on part_40_inf_cd + -> Seq Scan on part_40_inf_null +(11 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN --------------------------------- - Append - -> Seq Scan on part_1_10_ab - Filter: (a = 5) - -> Seq Scan on part_1_10_cd - Filter: (a = 5) -(5 rows) + QUERY PLAN +------------------------------------------ + Remote Subquery Scan on all (datanode_1) + -> Append + -> Seq Scan on part_1_10_ab + Filter: (a = 5) + -> Seq Scan on part_1_10_cd + Filter: (a = 5) +(6 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on part_1_10_ab - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20_ab - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30_ab - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf_ab - Filter: (b = 'ab'::bpchar) -(9 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_1_10_ab + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_10_20_ab + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_21_30_ab + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_40_inf_ab + Filter: (b = 'ab'::bpchar) +(10 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); - QUERY PLAN ------------------------------------------------------------------ - Append - -> Seq Scan on part_1_10_ab - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20_ab - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30_ab - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(7 rows) + QUERY PLAN +----------------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_1_10_ab + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_10_20_ab + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_21_30_ab + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) +(8 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -2077,48 +2087,51 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on part_40_inf_null - Filter: (b IS NULL) -(3 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_40_inf_null + Filter: (b IS NULL) +(4 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; - QUERY PLAN ------------------------------------------------- - Append - -> Seq Scan on part_1_10_ab - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10_cd - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20_ab - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20_cd - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30_ab - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30_cd - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf_ab - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf_cd - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf_null - Filter: ((a IS NOT NULL) AND (a < 67)) -(19 rows) + QUERY PLAN +------------------------------------------------------ + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_1_10_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_1_10_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_10_20_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_10_20_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_21_30_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_21_30_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf_null + Filter: ((a IS NOT NULL) AND (a < 67)) +(20 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on part_40_inf_ab - Filter: (a >= 30) - -> Seq Scan on part_40_inf_cd - Filter: (a >= 30) - -> Seq Scan on part_40_inf_null - Filter: (a >= 30) -(7 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on part_40_inf_ab + Filter: (a >= 30) + -> Seq Scan on part_40_inf_cd + Filter: (a >= 30) + -> Seq Scan on part_40_inf_null + Filter: (a >= 30) +(8 rows) drop table list_parted; drop table range_list_parted; @@ -2132,84 +2145,91 @@ create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20 create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded); explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0 - QUERY PLAN ------------------------------- - Append - -> Seq Scan on mcrparted0 - Filter: (a = 0) -(3 rows) + QUERY PLAN +------------------------------------------ + Remote Subquery Scan on all (datanode_2) + -> Append + -> Seq Scan on mcrparted0 + Filter: (a = 0) +(4 rows) explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1 - QUERY PLAN ---------------------------------------------- - Append - -> Seq Scan on mcrparted1 - Filter: ((a = 10) AND (abs(b) < 5)) -(3 rows) + QUERY PLAN +--------------------------------------------------- + Remote Subquery Scan on all (datanode_2) + -> Append + -> Seq Scan on mcrparted1 + Filter: ((a = 10) AND (abs(b) < 5)) +(4 rows) explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2 - QUERY PLAN ---------------------------------------------- - Append - -> Seq Scan on mcrparted1 - Filter: ((a = 10) AND (abs(b) = 5)) - -> Seq Scan on mcrparted2 - Filter: ((a = 10) AND (abs(b) = 5)) -(5 rows) + QUERY PLAN +--------------------------------------------------- + Remote Subquery Scan on all (datanode_2) + -> Append + -> Seq Scan on mcrparted1 + Filter: ((a = 10) AND (abs(b) = 5)) + -> Seq Scan on mcrparted2 + Filter: ((a = 10) AND (abs(b) = 5)) +(6 rows) explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions - QUERY PLAN ------------------------------- - Append - -> Seq Scan on mcrparted0 - Filter: (abs(b) = 5) - -> Seq Scan on mcrparted1 - Filter: (abs(b) = 5) - -> Seq Scan on mcrparted2 - Filter: (abs(b) = 5) - -> Seq Scan on mcrparted3 - Filter: (abs(b) = 5) - -> Seq Scan on mcrparted5 - Filter: (abs(b) = 5) -(11 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on mcrparted0 + Filter: (abs(b) = 5) + -> Seq Scan on mcrparted1 + Filter: (abs(b) = 5) + -> Seq Scan on mcrparted2 + Filter: (abs(b) = 5) + -> Seq Scan on mcrparted3 + Filter: (abs(b) = 5) + -> Seq Scan on mcrparted5 + Filter: (abs(b) = 5) +(12 rows) explain (costs off) select * from mcrparted where a > -1; -- scans all partitions - QUERY PLAN -------------------------------------- - Append - -> Seq Scan on mcrparted0 - Filter: (a > '-1'::integer) - -> Seq Scan on mcrparted1 - Filter: (a > '-1'::integer) - -> Seq Scan on mcrparted2 - Filter: (a > '-1'::integer) - -> Seq Scan on mcrparted3 - Filter: (a > '-1'::integer) - -> Seq Scan on mcrparted4 - Filter: (a > '-1'::integer) - -> Seq Scan on mcrparted5 - Filter: (a > '-1'::integer) -(13 rows) + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Append + -> Seq Scan on mcrparted0 + Filter: (a > '-1'::integer) + -> Seq Scan on mcrparted1 + Filter: (a > '-1'::integer) + -> Seq Scan on mcrparted2 + Filter: (a > '-1'::integer) + -> Seq Scan on mcrparted3 + Filter: (a > '-1'::integer) + -> Seq Scan on mcrparted4 + Filter: (a > '-1'::integer) + -> Seq Scan on mcrparted5 + Filter: (a > '-1'::integer) +(14 rows) explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 - QUERY PLAN ------------------------------------------------------------ - Append - -> Seq Scan on mcrparted4 - Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) -(3 rows) + QUERY PLAN +----------------------------------------------------------------- + Remote Subquery Scan on all (datanode_2) + -> Append + -> Seq Scan on mcrparted4 + Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) +(4 rows) explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5 - QUERY PLAN ------------------------------------------ - Append - -> Seq Scan on mcrparted3 - Filter: ((c > 20) AND (a = 20)) - -> Seq Scan on mcrparted4 - Filter: ((c > 20) AND (a = 20)) - -> Seq Scan on mcrparted5 - Filter: ((c > 20) AND (a = 20)) -(7 rows) + QUERY PLAN +----------------------------------------------- + Remote Subquery Scan on all (datanode_2) + -> Append + -> Seq Scan on mcrparted3 + Filter: ((c > 20) AND (a = 20)) + -> Seq Scan on mcrparted4 + Filter: ((c > 20) AND (a = 20)) + -> Seq Scan on mcrparted5 + Filter: ((c > 20) AND (a = 20)) +(8 rows) drop table mcrparted; -- check that partitioned table Appends cope with being referenced in |