create schema bfv_partition; set search_path=bfv_partition; -- -- Initial setup for all the partitioning test for this suite -- -- start_ignore create language plpythonu; ERROR: language "plpythonu" already exists -- end_ignore create or replace function count_operator(query text, operator text) returns int as $$ rv = plpy.execute('EXPLAIN ' + query) search_text = operator result = 0 for i in range(len(rv)): cur_line = rv[i]['QUERY PLAN'] if search_text.lower() in cur_line.lower(): result = result+1 return result $$ language plpythonu; create or replace function find_operator(query text, operator_name text) returns text as $$ rv = plpy.execute('EXPLAIN' + query) search_text = operator_name result = ['false'] for i in range(len(rv)): cur_line = rv[i]['QUERY PLAN'] if search_text.lower() in cur_line.lower(): result = ['true'] break return result $$ language plpythonu; -- -- Tests if it produces SIGSEGV from "select from partition_table group by rollup or cube function" -- -- SETUP -- start_ignore drop table if exists mpp7980; NOTICE: table "mpp7980" does not exist, skipping -- end_ignore create table mpp7980 ( month_id date, bill_stmt_id character varying(30), cust_type character varying(10), subscription_status character varying(30), voice_call_min numeric(15,2), minute_per_call numeric(15,2), subscription_id character varying(15) ) distributed by (subscription_id, bill_stmt_id) PARTITION BY RANGE(month_id) ( start ('2009-02-01'::date) end ('2009-08-01'::date) exclusive EVERY (INTERVAL '1 month') ); NOTICE: CREATE TABLE will create partition "mpp7980_1_prt_1" for table "mpp7980" NOTICE: CREATE TABLE will create partition "mpp7980_1_prt_2" for table "mpp7980" NOTICE: CREATE TABLE will create partition "mpp7980_1_prt_3" for table "mpp7980" NOTICE: CREATE TABLE will create partition "mpp7980_1_prt_4" for table "mpp7980" NOTICE: CREATE TABLE will create partition "mpp7980_1_prt_5" for table "mpp7980" NOTICE: CREATE TABLE will create partition "mpp7980_1_prt_6" for table "mpp7980" -- TEST select count_operator('select cust_type, subscription_status,count(distinct subscription_id),sum(voice_call_min),sum(minute_per_call) from mpp7980 where month_id =E''2009-04-01'' group by rollup(1,2);','SIGSEGV'); count_operator ---------------- 0 (1 row) insert into mpp7980 values('2009-04-01','xyz','zyz','1',1,1,'1'); insert into mpp7980 values('2009-04-01','zxyz','zyz','2',2,1,'1'); insert into mpp7980 values('2009-03-03','xyz','zyz','4',1,3,'1'); select cust_type, subscription_status,count(distinct subscription_id),sum(voice_call_min),sum(minute_per_call) from mpp7980 where month_id ='2009-04-01' group by rollup(1,2); cust_type | subscription_status | count | sum | sum -----------+---------------------+-------+------+------ | | 1 | 3.00 | 2.00 zyz | | 1 | 3.00 | 2.00 zyz | 1 | 1 | 1.00 | 1.00 zyz | 2 | 1 | 2.00 | 1.00 (4 rows) -- CLEANUP -- start_ignore drop table mpp7980; -- end_ignore -- -- Tests if it is using casting comparator for partition selector with compatible types -- -- SETUP CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL (i1 int, f2 timestamp) partition by range (f2) ( start ('2000-01-01'::timestamp) INCLUSIVE end (date '2001-01-01'::timestamp) EXCLUSIVE every ('1 month'::interval) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_1" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_2" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_3" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_4" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_5" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_6" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_7" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_8" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_9" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_10" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_11" for table "timestamp_month_rangep_startincl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_12" for table "timestamp_month_rangep_startincl" CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL (i1 int, f2 timestamp) partition by range (f2) ( start ('2000-01-01'::timestamp) EXCLUSIVE end (date '2001-01-01'::timestamp) INCLUSIVE every ('1 month'::interval) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_1" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_2" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_3" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_4" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_5" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_6" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_7" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_8" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_9" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_10" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_11" for table "timestamp_month_rangep_startexcl" NOTICE: CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_12" for table "timestamp_month_rangep_startexcl" CREATE TABLE TIMESTAMP_MONTH_listp (i1 int, f2 timestamp) partition by list (f2) ( partition jan1 values ('2000-01-01'::timestamp), partition jan2 values ('2000-01-02'::timestamp), partition jan3 values ('2000-01-03'::timestamp), partition jan4 values ('2000-01-04'::timestamp), partition jan5 values ('2000-01-05'::timestamp) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan1" for table "timestamp_month_listp" NOTICE: CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan2" for table "timestamp_month_listp" NOTICE: CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan3" for table "timestamp_month_listp" NOTICE: CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan4" for table "timestamp_month_listp" NOTICE: CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan5" for table "timestamp_month_listp" -- TEST -- Middle of a middle range INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (1, '2000-07-16'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-07-16'; i1 | f2 ----+-------------------------- 1 | Sun Jul 16 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 1 | Sun Jul 16 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 1 | Sun Jul 16 00:00:00 2000 (1 row) -- Beginning of the first range INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (2, '2000-01-01'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-01'; i1 | f2 ----+-------------------------- 2 | Sat Jan 01 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 2 | Sat Jan 01 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 2 | Sat Jan 01 00:00:00 2000 (1 row) INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (3, '2000-01-02'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-02'; i1 | f2 ----+-------------------------- 3 | Sun Jan 02 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 3 | Sun Jan 02 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 3 | Sun Jan 02 00:00:00 2000 (1 row) -- End of the last range INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (4, '2000-12-31'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-12-31'; i1 | f2 ----+-------------------------- 4 | Sun Dec 31 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 4 | Sun Dec 31 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 4 | Sun Dec 31 00:00:00 2000 (1 row) INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (5, '2001-01-01'); -- should fail, no such partition ERROR: no partition for partitioning key (seg1 office-4-160.pa.pivotal.io:25433 pid=9457) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2001-01-01'; i1 | f2 ----+---- (0 rows) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD'); i1 | f2 ----+---- (0 rows) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD'); i1 | f2 ----+---- (0 rows) -- Range partitioning: START EXCLUSIVE, END INCLUSIVE -- Middle of a middle range INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (1, '2000-07-16'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-07-16'; i1 | f2 ----+-------------------------- 1 | Sun Jul 16 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 1 | Sun Jul 16 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 1 | Sun Jul 16 00:00:00 2000 (1 row) -- Beginning of the first range INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (2, '2000-01-01'); -- should fail, no such partition ERROR: no partition for partitioning key (seg0 office-4-160.pa.pivotal.io:25432 pid=9456) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-01'; i1 | f2 ----+---- (0 rows) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD'); i1 | f2 ----+---- (0 rows) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD'); i1 | f2 ----+---- (0 rows) INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (3, '2000-01-02'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-02'; i1 | f2 ----+-------------------------- 3 | Sun Jan 02 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 3 | Sun Jan 02 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 3 | Sun Jan 02 00:00:00 2000 (1 row) -- End of the last range INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (4, '2000-12-31'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-12-31'; i1 | f2 ----+-------------------------- 4 | Sun Dec 31 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 4 | Sun Dec 31 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 4 | Sun Dec 31 00:00:00 2000 (1 row) INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (5, '2001-01-01'); SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2001-01-01'; i1 | f2 ----+-------------------------- 5 | Mon Jan 01 00:00:00 2001 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 5 | Mon Jan 01 00:00:00 2001 (1 row) SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 5 | Mon Jan 01 00:00:00 2001 (1 row) -- List partitioning INSERT INTO TIMESTAMP_MONTH_listp values (1, '2000-01-03'); SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = '2000-01-03'; i1 | f2 ----+-------------------------- 1 | Mon Jan 03 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_TIMESTAMP('2000-01-03', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 1 | Mon Jan 03 00:00:00 2000 (1 row) SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_DATE('2000-01-03', 'YYYY-MM-DD'); i1 | f2 ----+-------------------------- 1 | Mon Jan 03 00:00:00 2000 (1 row) -- CLEANUP -- start_ignore DROP TABLE TIMESTAMP_MONTH_listp; DROP TABLE TIMESTAMP_MONTH_rangep_STARTEXCL; DROP TABLE TIMESTAMP_MONTH_rangep_STARTINCL; -- end_ignore -- -- Data Engineer can see partition key in psql -- -- SETUP -- start_ignore DROP TABLE IF EXISTS T26002_T1; NOTICE: table "t26002_t1" does not exist, skipping DROP TABLE IF EXISTS T26002_T2; NOTICE: table "t26002_t2" does not exist, skipping -- end_ignore CREATE TABLE T26002_T1 (empid int, departmentid int, year int, region varchar(20)) DISTRIBUTED BY (empid) PARTITION BY RANGE (year) SUBPARTITION BY LIST (region, departmentid) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES (('usa', 1)), SUBPARTITION europe VALUES (('europe', 2)), SUBPARTITION asia VALUES (('asia', 3)), DEFAULT SUBPARTITION other_regions) ( START (2012) END (2015) EVERY (3), DEFAULT PARTITION outlying_years); NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years" for table "t26002_t1" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_2" for table "t26002_t1" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_usa" for table "t26002_t1_1_prt_outlying_years" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_europe" for table "t26002_t1_1_prt_outlying_years" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_asia" for table "t26002_t1_1_prt_outlying_years" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_other_regions" for table "t26002_t1_1_prt_outlying_years" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_usa" for table "t26002_t1_1_prt_2" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_europe" for table "t26002_t1_1_prt_2" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_asia" for table "t26002_t1_1_prt_2" NOTICE: CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_other_regions" for table "t26002_t1_1_prt_2" -- TEST -- expected to see the partition key \d T26002_T1; Table "bfv_partition.t26002_t1" Column | Type | Modifiers --------------+-----------------------+----------- empid | integer | departmentid | integer | year | integer | region | character varying(20) | Number of child tables: 2 (Use \d+ to list them.) Distributed by: (empid) Partition by: (year) \d t26002_t1_1_prt_2; Table "bfv_partition.t26002_t1_1_prt_2" Column | Type | Modifiers --------------+-----------------------+----------- empid | integer | departmentid | integer | year | integer | region | character varying(20) | Check constraints: "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015) Inherits: t26002_t1 Number of child tables: 4 (Use \d+ to list them.) Distributed by: (empid) Partition by: (region, departmentid) \d t26002_t1_1_prt_2_2_prt_asia; Table "bfv_partition.t26002_t1_1_prt_2_2_prt_asia" Column | Type | Modifiers --------------+-----------------------+----------- empid | integer | departmentid | integer | year | integer | region | character varying(20) | Check constraints: "t26002_t1_1_prt_2_2_prt_asia_check" CHECK (region::text = 'asia'::text AND departmentid = 3) "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015) Inherits: t26002_t1_1_prt_2 Distributed by: (empid) \d+ T26002_T1; Table "bfv_partition.t26002_t1" Column | Type | Modifiers | Storage | Description --------------+-----------------------+-----------+----------+------------- empid | integer | | plain | departmentid | integer | | plain | year | integer | | plain | region | character varying(20) | | extended | Child tables: t26002_t1_1_prt_2, t26002_t1_1_prt_outlying_years Has OIDs: no Distributed by: (empid) Partition by: (year) \d+ t26002_t1_1_prt_2; Table "bfv_partition.t26002_t1_1_prt_2" Column | Type | Modifiers | Storage | Description --------------+-----------------------+-----------+----------+------------- empid | integer | | plain | departmentid | integer | | plain | year | integer | | plain | region | character varying(20) | | extended | Check constraints: "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015) Inherits: t26002_t1 Child tables: t26002_t1_1_prt_2_2_prt_asia, t26002_t1_1_prt_2_2_prt_europe, t26002_t1_1_prt_2_2_prt_other_regions, t26002_t1_1_prt_2_2_prt_usa Has OIDs: no Distributed by: (empid) Partition by: (region, departmentid) \d+ t26002_t1_1_prt_2_2_prt_asia; Table "bfv_partition.t26002_t1_1_prt_2_2_prt_asia" Column | Type | Modifiers | Storage | Description --------------+-----------------------+-----------+----------+------------- empid | integer | | plain | departmentid | integer | | plain | year | integer | | plain | region | character varying(20) | | extended | Check constraints: "t26002_t1_1_prt_2_2_prt_asia_check" CHECK (region::text = 'asia'::text AND departmentid = 3) "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015) Inherits: t26002_t1_1_prt_2 Has OIDs: no Distributed by: (empid) /* -- test 2: Data Engineer won't see partition key for non-partitioned table GIVEN I am a Data Engineer WHEN I run \d on a non-partitioned table THEN I should NOT see the partition key in the output */ CREATE TABLE T26002_T2 (empid int, departmentid int, year int, region varchar(20)) DISTRIBUTED BY (empid); \d T26002_T2; Table "bfv_partition.t26002_t2" Column | Type | Modifiers --------------+-----------------------+----------- empid | integer | departmentid | integer | year | integer | region | character varying(20) | Distributed by: (empid) \d+ T26002_T2; Table "bfv_partition.t26002_t2" Column | Type | Modifiers | Storage | Description --------------+-----------------------+-----------+----------+------------- empid | integer | | plain | departmentid | integer | | plain | year | integer | | plain | region | character varying(20) | | extended | Has OIDs: no Distributed by: (empid) -- CLEANUP -- start_ignore DROP TABLE IF EXISTS T26002_T1; DROP TABLE IF EXISTS T26002_T2; -- end_ignore -- ************ORCA ENABLED********** -- -- MPP-23195 -- -- SETUP -- start_ignore set optimizer_enable_bitmapscan=on; set optimizer_enable_indexjoin=on; drop table if exists mpp23195_t1; NOTICE: table "mpp23195_t1" does not exist, skipping drop table if exists mpp23195_t2; NOTICE: table "mpp23195_t2" does not exist, skipping -- end_ignore create table mpp23195_t1 (i int) partition by range(i) (partition pt1 start(1) end(10), partition pt2 start(10) end(20)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "mpp23195_t1_1_prt_pt1" for table "mpp23195_t1" NOTICE: CREATE TABLE will create partition "mpp23195_t1_1_prt_pt2" for table "mpp23195_t1" create index index_mpp23195_t1_i on mpp23195_t1(i); NOTICE: building index for child partition "mpp23195_t1_1_prt_pt1" NOTICE: building index for child partition "mpp23195_t1_1_prt_pt2" create table mpp23195_t2(i int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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 mpp23195_t1 values (generate_series(1,19)); insert into mpp23195_t2 values (1); -- TEST select find_operator('select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;', 'Dynamic Index Scan'); find_operator --------------- ['false'] (1 row) select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i; i | i ---+--- (0 rows) -- CLEANUP -- start_ignore drop table if exists mpp23195_t1; drop table if exists mpp23195_t2; set optimizer_enable_bitmapscan=off; set optimizer_enable_indexjoin=off; -- end_ignore -- -- Check we have Dynamic Index Scan operator and check we have Nest loop operator -- -- SETUP -- start_ignore drop table if exists mpp21834_t1; NOTICE: table "mpp21834_t1" does not exist, skipping drop table if exists mpp21834_t2; NOTICE: table "mpp21834_t2" does not exist, skipping -- end_ignore create table mpp21834_t1 (i int, j int) partition by range(i) (partition pp1 start(1) end(10), partition pp2 start(10) end(20)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "mpp21834_t1_1_prt_pp1" for table "mpp21834_t1" NOTICE: CREATE TABLE will create partition "mpp21834_t1_1_prt_pp2" for table "mpp21834_t1" create index index_1 on mpp21834_t1(i); NOTICE: building index for child partition "mpp21834_t1_1_prt_pp1" NOTICE: building index for child partition "mpp21834_t1_1_prt_pp2" create index index_2 on mpp21834_t1(j); NOTICE: building index for child partition "mpp21834_t1_1_prt_pp1" NOTICE: building index for child partition "mpp21834_t1_1_prt_pp2" create table mpp21834_t2(i int, j int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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. -- TEST -- start_ignore select disable_xform('CXformInnerJoin2HashJoin'); disable_xform -------------------------------------- CXformInnerJoin2HashJoin is disabled (1 row) -- end_ignore select find_operator('analyze select * from mpp21834_t2,mpp21834_t1 where mpp21834_t2.i < mpp21834_t1.i;','Dynamic Index Scan'); find_operator --------------- ['false'] (1 row) select find_operator('analyze select * from mpp21834_t2,mpp21834_t1 where mpp21834_t2.i < mpp21834_t1.i;','Nested Loop'); find_operator --------------- ['true'] (1 row) -- CLEANUP -- start_ignore drop index index_2; WARNING: Only dropped the index "index_2" HINT: To drop other indexes on child partitions, drop each one explicitly. drop index index_1; WARNING: Only dropped the index "index_1" HINT: To drop other indexes on child partitions, drop each one explicitly. drop table if exists mpp21834_t2; drop table if exists mpp21834_t1; select enable_xform('CXformInnerJoin2HashJoin'); enable_xform ------------------------------------- CXformInnerJoin2HashJoin is enabled (1 row) -- end_ignore -- -- A rescanning of DTS with its own partition selector (under sequence node) -- -- SETUP -- start_ignore set optimizer_enable_broadcast_nestloop_outer_child=on; drop table if exists mpp23288; NOTICE: table "mpp23288" does not exist, skipping -- end_ignore create table mpp23288(a int, b int) partition by range (a) ( PARTITION pfirst END(5) INCLUSIVE, PARTITION pinter START(5) EXCLUSIVE END (10) INCLUSIVE, PARTITION plast START (10) EXCLUSIVE ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "mpp23288_1_prt_pfirst" for table "mpp23288" NOTICE: CREATE TABLE will create partition "mpp23288_1_prt_pinter" for table "mpp23288" NOTICE: CREATE TABLE will create partition "mpp23288_1_prt_plast" for table "mpp23288" insert into mpp23288(a) select generate_series(1,20); analyze mpp23288; -- TEST select count_operator('select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and t2.a =10) order by t2.a, t1.a;','Dynamic Table Scan'); count_operator ---------------- 0 (1 row) select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and t2.a =10) order by t2.a, t1.a; a | a ----+--- 10 | 1 10 | 2 10 | 3 10 | 4 10 | 5 10 | 6 10 | 7 10 | 8 10 | 9 (9 rows) select count_operator('select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and (t2.a = 10 or t2.a = 5 or t2.a = 12)) order by t2.a, t1.a;','Dynamic Table Scan'); count_operator ---------------- 0 (1 row) select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and (t2.a = 10 or t2.a = 5 or t2.a = 12)) order by t2.a, t1.a; a | a ----+---- 5 | 1 5 | 2 5 | 3 5 | 4 10 | 1 10 | 2 10 | 3 10 | 4 10 | 5 10 | 6 10 | 7 10 | 8 10 | 9 12 | 1 12 | 2 12 | 3 12 | 4 12 | 5 12 | 6 12 | 7 12 | 8 12 | 9 12 | 10 12 | 11 (24 rows) select count_operator('select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on t1.a < t2.a and t2.a = 1 or t2.a < 10 order by t2.a, t1.a;','Dynamic Table Scan'); count_operator ---------------- 0 (1 row) select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on t1.a < t2.a and t2.a = 1 or t2.a < 10 order by t2.a, t1.a; a | a ---+---- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 1 | 6 1 | 7 1 | 8 1 | 9 1 | 10 1 | 11 1 | 12 1 | 13 1 | 14 1 | 15 1 | 16 1 | 17 1 | 18 1 | 19 1 | 20 2 | 1 2 | 2 2 | 3 2 | 4 2 | 5 2 | 6 2 | 7 2 | 8 2 | 9 2 | 10 2 | 11 2 | 12 2 | 13 2 | 14 2 | 15 2 | 16 2 | 17 2 | 18 2 | 19 2 | 20 3 | 1 3 | 2 3 | 3 3 | 4 3 | 5 3 | 6 3 | 7 3 | 8 3 | 9 3 | 10 3 | 11 3 | 12 3 | 13 3 | 14 3 | 15 3 | 16 3 | 17 3 | 18 3 | 19 3 | 20 4 | 1 4 | 2 4 | 3 4 | 4 4 | 5 4 | 6 4 | 7 4 | 8 4 | 9 4 | 10 4 | 11 4 | 12 4 | 13 4 | 14 4 | 15 4 | 16 4 | 17 4 | 18 4 | 19 4 | 20 5 | 1 5 | 2 5 | 3 5 | 4 5 | 5 5 | 6 5 | 7 5 | 8 5 | 9 5 | 10 5 | 11 5 | 12 5 | 13 5 | 14 5 | 15 5 | 16 5 | 17 5 | 18 5 | 19 5 | 20 6 | 1 6 | 2 6 | 3 6 | 4 6 | 5 6 | 6 6 | 7 6 | 8 6 | 9 6 | 10 6 | 11 6 | 12 6 | 13 6 | 14 6 | 15 6 | 16 6 | 17 6 | 18 6 | 19 6 | 20 7 | 1 7 | 2 7 | 3 7 | 4 7 | 5 7 | 6 7 | 7 7 | 8 7 | 9 7 | 10 7 | 11 7 | 12 7 | 13 7 | 14 7 | 15 7 | 16 7 | 17 7 | 18 7 | 19 7 | 20 8 | 1 8 | 2 8 | 3 8 | 4 8 | 5 8 | 6 8 | 7 8 | 8 8 | 9 8 | 10 8 | 11 8 | 12 8 | 13 8 | 14 8 | 15 8 | 16 8 | 17 8 | 18 8 | 19 8 | 20 9 | 1 9 | 2 9 | 3 9 | 4 9 | 5 9 | 6 9 | 7 9 | 8 9 | 9 9 | 10 9 | 11 9 | 12 9 | 13 9 | 14 9 | 15 9 | 16 9 | 17 9 | 18 9 | 19 9 | 20 (180 rows) -- CLEANUP -- start_ignore drop table if exists mpp23288; set optimizer_enable_broadcast_nestloop_outer_child=off; -- end_ignore -- -- Testing whether test gives wrong results with partition tables when sub-partitions are distributed differently than the parent partition. -- -- SETUP -- start_ignore drop table if exists pt; NOTICE: table "pt" does not exist, skipping drop table if exists t; NOTICE: table "t" does not exist, skipping -- end_ignore create table pt(a int, b int, c int) distributed by (a) partition by range(b) (start(0) end(10) every (2)); NOTICE: CREATE TABLE will create partition "pt_1_prt_1" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_2" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_3" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_4" for table "pt" NOTICE: CREATE TABLE will create partition "pt_1_prt_5" for table "pt" alter table pt_1_prt_1 set distributed randomly; create table t(a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 pt select g%10, g%2 + 1, g*2 from generate_series(1,20) g; insert into pt values(1,1,3); insert into t select g%10, g%2 + 1 from generate_series(1,20) g; create index pt_c on pt(c); NOTICE: building index for child partition "pt_1_prt_1" NOTICE: building index for child partition "pt_1_prt_2" NOTICE: building index for child partition "pt_1_prt_3" NOTICE: building index for child partition "pt_1_prt_4" NOTICE: building index for child partition "pt_1_prt_5" analyze t; analyze pt; -- TEST SELECT COUNT(*) FROM pt, t WHERE pt.a = t.a; count ------- 42 (1 row) SELECT COUNT(*) FROM pt, t WHERE pt.a = t.a and pt.c=4; count ------- 2 (1 row) select a, count(*) from pt group by a; a | count ---+------- 9 | 2 8 | 2 2 | 2 1 | 3 0 | 2 3 | 2 6 | 2 7 | 2 5 | 2 4 | 2 (10 rows) select b, count(*) from pt group by b; b | count ---+------- 2 | 10 1 | 11 (2 rows) select a, count(*) from pt where a<2 group by a; a | count ---+------- 1 | 3 0 | 2 (2 rows) -- CLEANUP -- start_ignore drop index pt_c; WARNING: Only dropped the index "pt_c" HINT: To drop other indexes on child partitions, drop each one explicitly. drop table if exists pt; drop table if exists t; -- end_ignore -- -- Tests if DynamicIndexScan sets tuple descriptor of the planstate->ps_ResultTupleSlot -- -- SETUP -- start_ignore drop table if exists mpp24151_t; NOTICE: table "mpp24151_t" does not exist, skipping drop table if exists mpp24151_pt; NOTICE: table "mpp24151_pt" does not exist, skipping -- end_ignore create table mpp24151_t(dist int, tid int, t1 text, t2 text); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Greenplum Database 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 mpp24151_pt(dist int, pt1 text, pt2 text, pt3 text, ptid int) DISTRIBUTED BY (dist) PARTITION BY RANGE(ptid) ( START (0) END (5) EVERY (1), DEFAULT PARTITION junk_data ) ; NOTICE: CREATE TABLE will create partition "mpp24151_pt_1_prt_junk_data" for table "mpp24151_pt" NOTICE: CREATE TABLE will create partition "mpp24151_pt_1_prt_2" for table "mpp24151_pt" NOTICE: CREATE TABLE will create partition "mpp24151_pt_1_prt_3" for table "mpp24151_pt" NOTICE: CREATE TABLE will create partition "mpp24151_pt_1_prt_4" for table "mpp24151_pt" NOTICE: CREATE TABLE will create partition "mpp24151_pt_1_prt_5" for table "mpp24151_pt" NOTICE: CREATE TABLE will create partition "mpp24151_pt_1_prt_6" for table "mpp24151_pt" create index pt1_idx on mpp24151_pt using btree (pt1); NOTICE: building index for child partition "mpp24151_pt_1_prt_junk_data" NOTICE: building index for child partition "mpp24151_pt_1_prt_2" NOTICE: building index for child partition "mpp24151_pt_1_prt_3" NOTICE: building index for child partition "mpp24151_pt_1_prt_4" NOTICE: building index for child partition "mpp24151_pt_1_prt_5" NOTICE: building index for child partition "mpp24151_pt_1_prt_6" create index ptid_idx on mpp24151_pt using btree (ptid); NOTICE: building index for child partition "mpp24151_pt_1_prt_junk_data" NOTICE: building index for child partition "mpp24151_pt_1_prt_2" NOTICE: building index for child partition "mpp24151_pt_1_prt_3" NOTICE: building index for child partition "mpp24151_pt_1_prt_4" NOTICE: building index for child partition "mpp24151_pt_1_prt_5" NOTICE: building index for child partition "mpp24151_pt_1_prt_6" insert into mpp24151_pt select i, 'hello' || 0, 'world', 'drop this', i % 6 from generate_series(0,100)i; insert into mpp24151_pt select i, 'hello' || i, 'world', 'drop this', i % 6 from generate_series(0,200000)i; insert into mpp24151_t select i, i % 6, 'hello' || i, 'bar' from generate_series(0,10)i; analyze mpp24151_pt; analyze mpp24151_t; -- TEST -- start_ignore select disable_xform('CXformDynamicGet2DynamicTableScan'); disable_xform ----------------------------------------------- CXformDynamicGet2DynamicTableScan is disabled (1 row) -- end_ignore select count_operator('select * from mpp24151_t, mpp24151_pt where tid = ptid and pt1 = E''hello0'';','Result'); count_operator ---------------- 6 (1 row) select * from mpp24151_t, mpp24151_pt where tid = ptid and pt1 = 'hello0'; dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid ------+-----+---------+-----+------+--------+-------+-----------+------ 5 | 5 | hello5 | bar | 5 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 41 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 53 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 77 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 89 | hello0 | world | drop this | 5 6 | 0 | hello6 | bar | 96 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 96 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 90 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 90 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 54 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 54 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 42 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 42 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 18 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 18 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 6 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 6 | hello0 | world | drop this | 0 7 | 1 | hello7 | bar | 7 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 7 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 19 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 19 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 55 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 55 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 73 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 73 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 91 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 91 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 97 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 97 | hello0 | world | drop this | 1 2 | 2 | hello2 | bar | 20 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 20 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 38 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 38 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 56 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 56 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 74 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 74 | hello0 | world | drop this | 2 3 | 3 | hello3 | bar | 3 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 3 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 21 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 21 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 39 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 39 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 57 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 57 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 75 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 75 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 87 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 87 | hello0 | world | drop this | 3 4 | 4 | hello4 | bar | 4 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 4 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 22 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 22 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 40 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 40 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 76 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 76 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 88 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 88 | hello0 | world | drop this | 4 5 | 5 | hello5 | bar | 17 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 29 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 35 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 71 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 83 | hello0 | world | drop this | 5 6 | 0 | hello6 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 84 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 84 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 72 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 72 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 48 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 48 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 36 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 36 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 30 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 30 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 0 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 7 | 1 | hello7 | bar | 1 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 1 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 13 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 13 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 31 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 31 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 37 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 37 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 49 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 49 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 85 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 85 | hello0 | world | drop this | 1 2 | 2 | hello2 | bar | 2 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 2 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 14 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 14 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 50 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 50 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 68 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 68 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 86 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 86 | hello0 | world | drop this | 2 3 | 3 | hello3 | bar | 15 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 15 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 33 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 33 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 51 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 51 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 63 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 63 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 69 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 69 | hello0 | world | drop this | 3 4 | 4 | hello4 | bar | 16 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 16 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 28 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 28 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 34 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 34 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 52 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 52 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 70 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 70 | hello0 | world | drop this | 4 5 | 5 | hello5 | bar | 11 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 23 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 47 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 59 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 65 | hello0 | world | drop this | 5 5 | 5 | hello5 | bar | 95 | hello0 | world | drop this | 5 6 | 0 | hello6 | bar | 78 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 78 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 66 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 66 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 60 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 60 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 24 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 24 | hello0 | world | drop this | 0 6 | 0 | hello6 | bar | 12 | hello0 | world | drop this | 0 0 | 0 | hello0 | bar | 12 | hello0 | world | drop this | 0 7 | 1 | hello7 | bar | 25 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 25 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 43 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 43 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 61 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 61 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 67 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 67 | hello0 | world | drop this | 1 7 | 1 | hello7 | bar | 79 | hello0 | world | drop this | 1 1 | 1 | hello1 | bar | 79 | hello0 | world | drop this | 1 2 | 2 | hello2 | bar | 8 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 8 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 26 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 26 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 32 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 32 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 44 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 44 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 62 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 62 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 80 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 80 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 92 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 92 | hello0 | world | drop this | 2 2 | 2 | hello2 | bar | 98 | hello0 | world | drop this | 2 8 | 2 | hello8 | bar | 98 | hello0 | world | drop this | 2 3 | 3 | hello3 | bar | 9 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 9 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 27 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 27 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 45 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 45 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 81 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 81 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 93 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 93 | hello0 | world | drop this | 3 3 | 3 | hello3 | bar | 99 | hello0 | world | drop this | 3 9 | 3 | hello9 | bar | 99 | hello0 | world | drop this | 3 4 | 4 | hello4 | bar | 10 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 10 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 46 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 46 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 58 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 58 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 64 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 64 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 82 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 82 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 94 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 94 | hello0 | world | drop this | 4 4 | 4 | hello4 | bar | 100 | hello0 | world | drop this | 4 10 | 4 | hello10 | bar | 100 | hello0 | world | drop this | 4 (188 rows) -- CLEANUP -- start_ignore drop index ptid_idx; WARNING: Only dropped the index "ptid_idx" HINT: To drop other indexes on child partitions, drop each one explicitly. drop index pt1_idx; WARNING: Only dropped the index "pt1_idx" HINT: To drop other indexes on child partitions, drop each one explicitly. drop table if exists mpp24151_t; drop table if exists mpp24151_pt; select enable_xform('CXformDynamicGet2DynamicTableScan'); enable_xform ---------------------------------------------- CXformDynamicGet2DynamicTableScan is enabled (1 row) -- end_ignore -- -- No DPE (Dynamic Partition Elimination) on second child of a union under a join -- -- SETUP -- start_ignore drop table if exists t; NOTICE: table "t" does not exist, skipping drop table if exists p1; NOTICE: table "p1" does not exist, skipping drop table if exists p2; NOTICE: table "p2" does not exist, skipping drop table if exists p3; NOTICE: table "p3" does not exist, skipping drop table if exists p; NOTICE: table "p" does not exist, skipping -- end_ignore create table p1 (a int, b int) partition by range(b) (start (1) end(100) every (20)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "p1_1_prt_1" for table "p1" NOTICE: CREATE TABLE will create partition "p1_1_prt_2" for table "p1" NOTICE: CREATE TABLE will create partition "p1_1_prt_3" for table "p1" NOTICE: CREATE TABLE will create partition "p1_1_prt_4" for table "p1" NOTICE: CREATE TABLE will create partition "p1_1_prt_5" for table "p1" create table p2 (a int, b int) partition by range(b) (start (1) end(100) every (20)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "p2_1_prt_1" for table "p2" NOTICE: CREATE TABLE will create partition "p2_1_prt_2" for table "p2" NOTICE: CREATE TABLE will create partition "p2_1_prt_3" for table "p2" NOTICE: CREATE TABLE will create partition "p2_1_prt_4" for table "p2" NOTICE: CREATE TABLE will create partition "p2_1_prt_5" for table "p2" create table p3 (a int, b int) partition by range(b) (start (1) end(100) every (20)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "p3_1_prt_1" for table "p3" NOTICE: CREATE TABLE will create partition "p3_1_prt_2" for table "p3" NOTICE: CREATE TABLE will create partition "p3_1_prt_3" for table "p3" NOTICE: CREATE TABLE will create partition "p3_1_prt_4" for table "p3" NOTICE: CREATE TABLE will create partition "p3_1_prt_5" for table "p3" create table p (a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 t(a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 select g, g*10 from generate_series(1,100) g; insert into p1 select g, g%99 +1 from generate_series(1,10000) g; insert into p2 select g, g%99 +1 from generate_series(1,10000) g; insert into p3 select g, g%99 +1 from generate_series(1,10000) g; insert into p select g, g%99 +1 from generate_series(1,10000) g; analyze t; analyze p1; analyze p2; analyze p3; analyze p; -- TEST select count_operator('select * from (select * from p1 union all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 union select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 except all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 except select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 intersect all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 union select * from p2 union all select * from p3) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 union select * from p2 union all select * from p) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 union select * from p union all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 union select * from p2 intersect all select * from p3) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) select count_operator('select * from (select * from p1 union select * from p intersect all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); count_operator ---------------- 0 (1 row) -- CLEANUP -- start_ignore drop table t; drop table p1; drop table p2; drop table p3; drop table p; -- end_ignore -- -- Gracefully handle NULL partition set from BitmapTableScan, DynamicTableScan and DynamicIndexScan -- -- SETUP -- start_ignore drop table if exists dts; NOTICE: table "dts" does not exist, skipping drop table if exists dis; NOTICE: table "dis" does not exist, skipping drop table if exists dbs; NOTICE: table "dbs" does not exist, skipping -- end_ignore create table dts(c1 int, c2 int) partition by range(c2) (start(1) end(11) every(1)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "dts_1_prt_1" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_2" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_3" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_4" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_5" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_6" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_7" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_8" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_9" for table "dts" NOTICE: CREATE TABLE will create partition "dts_1_prt_10" for table "dts" create table dis(c1 int, c2 int, c3 int) partition by range(c2) (start(1) end(11) every(1)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "dis_1_prt_1" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_2" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_3" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_4" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_5" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_6" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_7" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_8" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_9" for table "dis" NOTICE: CREATE TABLE will create partition "dis_1_prt_10" for table "dis" create index dis_index on dis(c3); NOTICE: building index for child partition "dis_1_prt_1" NOTICE: building index for child partition "dis_1_prt_2" NOTICE: building index for child partition "dis_1_prt_3" NOTICE: building index for child partition "dis_1_prt_4" NOTICE: building index for child partition "dis_1_prt_5" NOTICE: building index for child partition "dis_1_prt_6" NOTICE: building index for child partition "dis_1_prt_7" NOTICE: building index for child partition "dis_1_prt_8" NOTICE: building index for child partition "dis_1_prt_9" NOTICE: building index for child partition "dis_1_prt_10" CREATE TABLE dbs(c1 int, c2 int, c3 int) partition by range(c2) (start(1) end(11) every(1)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "dbs_1_prt_1" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_2" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_3" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_4" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_5" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_6" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_7" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_8" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_9" for table "dbs" NOTICE: CREATE TABLE will create partition "dbs_1_prt_10" for table "dbs" create index dbs_index on dbs using bitmap(c3); NOTICE: building index for child partition "dbs_1_prt_1" NOTICE: building index for child partition "dbs_1_prt_2" NOTICE: building index for child partition "dbs_1_prt_3" NOTICE: building index for child partition "dbs_1_prt_4" NOTICE: building index for child partition "dbs_1_prt_5" NOTICE: building index for child partition "dbs_1_prt_6" NOTICE: building index for child partition "dbs_1_prt_7" NOTICE: building index for child partition "dbs_1_prt_8" NOTICE: building index for child partition "dbs_1_prt_9" NOTICE: building index for child partition "dbs_1_prt_10" -- TEST select find_operator('(select * from dts where c2 = 1) union (select * from dts where c2 = 2) union (select * from dts where c2 = 3) union (select * from dts where c2 = 4) union (select * from dts where c2 = 5) union (select * from dts where c2 = 6) union (select * from dts where c2 = 7) union (select * from dts where c2 = 8) union (select * from dts where c2 = 9) union (select * from dts where c2 = 10);', 'Dynamic Table Scan'); find_operator --------------- ['false'] (1 row) (select * from dts where c2 = 1) union (select * from dts where c2 = 2) union (select * from dts where c2 = 3) union (select * from dts where c2 = 4) union (select * from dts where c2 = 5) union (select * from dts where c2 = 6) union (select * from dts where c2 = 7) union (select * from dts where c2 = 8) union (select * from dts where c2 = 9) union (select * from dts where c2 = 10); c1 | c2 ----+---- (0 rows) -- start_ignore select disable_xform('CXformDynamicGet2DynamicTableScan'); disable_xform ----------------------------------------------- CXformDynamicGet2DynamicTableScan is disabled (1 row) -- end_ignore select find_operator('(select * from dis where c3 = 1) union (select * from dis where c3 = 2) union (select * from dis where c3 = 3) union (select * from dis where c3 = 4) union (select * from dis where c3 = 5) union (select * from dis where c3 = 6) union (select * from dis where c3 = 7) union (select * from dis where c3 = 8) union (select * from dis where c3 = 9) union (select * from dis where c3 = 10);', 'Dynamic Index Scan'); find_operator --------------- ['false'] (1 row) (select * from dis where c3 = 1) union (select * from dis where c3 = 2) union (select * from dis where c3 = 3) union (select * from dis where c3 = 4) union (select * from dis where c3 = 5) union (select * from dis where c3 = 6) union (select * from dis where c3 = 7) union (select * from dis where c3 = 8) union (select * from dis where c3 = 9) union (select * from dis where c3 = 10); c1 | c2 | c3 ----+----+---- (0 rows) select find_operator('select * from dbs where c2= 15 and c3 = 5;', 'Bitmap Table Scan'); find_operator --------------- ['false'] (1 row) select * from dbs where c2= 15 and c3 = 5; c1 | c2 | c3 ----+----+---- (0 rows) -- CLEANUP -- start_ignore drop index dbs_index; WARNING: Only dropped the index "dbs_index" HINT: To drop other indexes on child partitions, drop each one explicitly. drop table if exists dbs; drop index dis_index; WARNING: Only dropped the index "dis_index" HINT: To drop other indexes on child partitions, drop each one explicitly. drop table if exists dis; drop table if exists dts; select enable_xform('CXformDynamicGet2DynamicTableScan'); enable_xform ---------------------------------------------- CXformDynamicGet2DynamicTableScan is enabled (1 row) -- end_ignore -- -- Partition elimination for heterogenous DynamicIndexScans -- -- SETUP -- start_ignore drop table if exists pp; NOTICE: table "pp" does not exist, skipping drop index if exists pp_1_prt_1_idx; NOTICE: index "pp_1_prt_1_idx" does not exist, skipping drop index if exists pp_rest_1_idx; NOTICE: index "pp_rest_1_idx" does not exist, skipping drop index if exists pp_rest_2_idx; NOTICE: index "pp_rest_2_idx" does not exist, skipping set optimizer_segments=2; set optimizer_partition_selection_log=on; -- end_ignore create table pp(a int, b int, c int) partition by range(b) (start(1) end(15) every(5)); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "pp_1_prt_1" for table "pp" NOTICE: CREATE TABLE will create partition "pp_1_prt_2" for table "pp" NOTICE: CREATE TABLE will create partition "pp_1_prt_3" for table "pp" insert into pp values (1,1,2),(2,6,2), (3,11,2); -- Heterogeneous Index on the partition table create index pp_1_prt_1_idx on pp_1_prt_1(c); -- Create other indexes so that we can automate the repro for MPP-21069 by disabling tablescan create index pp_rest_1_idx on pp_1_prt_2(c,a); create index pp_rest_2_idx on pp_1_prt_3(c,a); -- TEST -- start_ignore select disable_xform('CXformDynamicGet2DynamicTableScan') ; disable_xform ----------------------------------------------- CXformDynamicGet2DynamicTableScan is disabled (1 row) -- end_ignore select * from pp where b=2 and c=2; a | b | c ---+---+--- (0 rows) select count_operator('select * from pp where b=2 and c=2;','Partition Selector'); count_operator ---------------- 0 (1 row) -- CLEANUP -- start_ignore drop index if exists pp_rest_2_idx; drop index if exists pp_rest_1_idx; drop index if exists pp_1_prt_1_idx; drop table if exists pp; select enable_xform('CXformDynamicGet2DynamicTableScan') ; enable_xform ---------------------------------------------- CXformDynamicGet2DynamicTableScan is enabled (1 row) reset optimizer_segments; set optimizer_partition_selection_log=off; select enable_xform('CXformDynamicGet2DynamicTableScan') ; enable_xform ---------------------------------------------- CXformDynamicGet2DynamicTableScan is enabled (1 row) -- end_ignore -- -- Partition elimination with implicit CAST on the partitioning key -- -- SETUP -- start_ignore set optimizer_segments=2; set optimizer_partition_selection_log=on; DROP TABLE IF EXISTS ds_4; NOTICE: table "ds_4" does not exist, skipping -- end_ignore CREATE TABLE ds_4 ( month_id character varying(6), cust_group_acc numeric(10), mobile_no character varying(10) ) DISTRIBUTED BY (cust_group_acc, mobile_no) PARTITION BY LIST(month_id) ( PARTITION p200800 VALUES('200800'), PARTITION p200801 VALUES('200801'), PARTITION p200802 VALUES('200802'), PARTITION p200803 VALUES('200803') ); NOTICE: CREATE TABLE will create partition "ds_4_1_prt_p200800" for table "ds_4" NOTICE: CREATE TABLE will create partition "ds_4_1_prt_p200801" for table "ds_4" NOTICE: CREATE TABLE will create partition "ds_4_1_prt_p200802" for table "ds_4" NOTICE: CREATE TABLE will create partition "ds_4_1_prt_p200803" for table "ds_4" -- TEST select * from ds_4 where month_id = '200800'; month_id | cust_group_acc | mobile_no ----------+----------------+----------- (0 rows) select count_operator('select * from ds_4 where month_id = E''200800'';','Partition Selector'); count_operator ---------------- 0 (1 row) select * from ds_4 where month_id > '200800'; month_id | cust_group_acc | mobile_no ----------+----------------+----------- (0 rows) select count_operator('select * from ds_4 where month_id > E''200800'';','Partition Selector'); count_operator ---------------- 0 (1 row) select * from ds_4 where month_id <= '200800'; month_id | cust_group_acc | mobile_no ----------+----------------+----------- (0 rows) select count_operator('select * from ds_4 where month_id <= E''200800'';','Partition Selector'); count_operator ---------------- 0 (1 row) select * from ds_4 a1,ds_4 a2 where a1.month_id = a2.month_id and a1.month_id > '200800'; month_id | cust_group_acc | mobile_no | month_id | cust_group_acc | mobile_no ----------+----------------+-----------+----------+----------------+----------- (0 rows) select count_operator('select * from ds_4 a1,ds_4 a2 where a1.month_id = a2.month_id and a1.month_id > E''200800'';','Partition Selector'); count_operator ---------------- 1 (1 row) -- CLEANUP -- start_ignore DROP TABLE IF EXISTS ds_4; set optimizer_partition_selection_log=off; reset optimizer_segments; drop function if exists find_operator(query text, operator_name text); drop function if exists count_operator(query text, operator_name text); -- end_ignore -- -- Partition table with appendonly leaf, full join -- -- SETUP -- start_ignore DROP TABLE IF EXISTS foo; NOTICE: table "foo" does not exist, skipping DROP TABLE IF EXISTS bar; NOTICE: table "bar" does not exist, skipping CREATE TABLE foo (a int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 bar (b int, c int) PARTITION BY RANGE (b) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE ( START (1) END (10) WITH (appendonly=true), START (10) END (20) ) ( START (1) END (10) , START (10) END (20) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "bar_1_prt_1" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_1_2_prt_1" for table "bar_1_prt_1" NOTICE: CREATE TABLE will create partition "bar_1_prt_1_2_prt_2" for table "bar_1_prt_1" NOTICE: CREATE TABLE will create partition "bar_1_prt_2" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_2_2_prt_1" for table "bar_1_prt_2" NOTICE: CREATE TABLE will create partition "bar_1_prt_2_2_prt_2" for table "bar_1_prt_2" -- end_ignore INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (2,3); SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b; a | b | c ---+---+--- 1 | | | 2 | 3 (2 rows) -- CLEANUP -- start_ignore DROP TABLE IF EXISTS foo; DROP TABLE IF EXISTS bar; -- end_ignore -- -- Partition table with appendonly set at middlevel partition, full join -- -- SETUP -- start_ignore DROP TABLE IF EXISTS foo; NOTICE: table "foo" does not exist, skipping DROP TABLE IF EXISTS bar; NOTICE: table "bar" does not exist, skipping CREATE TABLE foo (a int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 bar (b int, c int) PARTITION BY RANGE (b) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE ( START (1) END (10), START (10) END (20) ) ( START (1) END (10) WITH (appendonly=true), START (10) END (20) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "bar_1_prt_1" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_1_2_prt_1" for table "bar_1_prt_1" NOTICE: CREATE TABLE will create partition "bar_1_prt_1_2_prt_2" for table "bar_1_prt_1" NOTICE: CREATE TABLE will create partition "bar_1_prt_2" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_2_2_prt_1" for table "bar_1_prt_2" NOTICE: CREATE TABLE will create partition "bar_1_prt_2_2_prt_2" for table "bar_1_prt_2" -- end_ignore INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (2,3); SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b; a | b | c ---+---+--- 1 | | | 2 | 3 (2 rows) -- CLEANUP -- start_ignore DROP TABLE IF EXISTS foo; DROP TABLE IF EXISTS bar; -- end_ignore -- -- Partition table with appendonly set at root partition, full join -- -- SETUP -- start_ignore DROP TABLE IF EXISTS foo; NOTICE: table "foo" does not exist, skipping DROP TABLE IF EXISTS bar; NOTICE: table "bar" does not exist, skipping CREATE TABLE foo (a int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 bar (b int, c int) WITH (appendonly=true) PARTITION BY RANGE (b) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE ( START (1) END (10), START (10) END (20) ) ( START (1) END (10), START (10) END (20) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "bar_1_prt_1" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_1_2_prt_1" for table "bar_1_prt_1" NOTICE: CREATE TABLE will create partition "bar_1_prt_1_2_prt_2" for table "bar_1_prt_1" NOTICE: CREATE TABLE will create partition "bar_1_prt_2" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_2_2_prt_1" for table "bar_1_prt_2" NOTICE: CREATE TABLE will create partition "bar_1_prt_2_2_prt_2" for table "bar_1_prt_2" -- end_ignore INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (2,3); SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b; a | b | c ---+---+--- 1 | | | 2 | 3 (2 rows) -- -- Test a hash agg that has a Sequence + Partition Selector below it. -- -- SETUP -- start_ignore DROP TABLE IF EXISTS bar; -- end_ignore CREATE TABLE bar (b int, c int) PARTITION BY RANGE (b) ( START (0) END (10), START (10) END (20) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "bar_1_prt_1" for table "bar" NOTICE: CREATE TABLE will create partition "bar_1_prt_2" for table "bar" INSERT INTO bar SELECT g % 20, g % 20 from generate_series(1, 1000) g; ANALYZE bar; SELECT b FROM bar GROUP BY b; b ---- 7 4 19 3 5 18 6 11 9 8 12 10 17 1 0 2 16 15 14 13 (20 rows) EXPLAIN SELECT b FROM bar GROUP BY b; QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=18.50..18.60 rows=10 width=4) -> HashAggregate (cost=18.50..18.60 rows=4 width=4) Group By: bfv_partition.bar.b -> Append (cost=0.00..16.00 rows=334 width=4) -> Seq Scan on bar_1_prt_1 bar (cost=0.00..8.00 rows=167 width=4) -> Seq Scan on bar_1_prt_2 bar (cost=0.00..8.00 rows=167 width=4) Optimizer status: legacy query optimizer (7 rows) -- CLEANUP DROP TABLE IF EXISTS foo; DROP TABLE IF EXISTS bar; -- Test EXPLAIN ANALYZE on a partitioned table. There used to be a bug, where -- you got an internal error with this, because the EXPLAIN ANALYZE sends the -- stats from QEs to the QD at the end of query, but because the subnodes are -- terminated earlier, their stats were already gone. create table mpp8031 (oid integer, odate timestamp without time zone, cid integer) PARTITION BY RANGE(odate) ( PARTITION foo START ('2005-05-01 00:00:00'::timestamp without time zone) END ('2005-07-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval), START ('2005-07-01 00:00:00'::timestamp without time zone) END ('2006-01-01 00:00:00'::timestamp without time zone) EVERY ('2 mons'::interval) ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'oid' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "mpp8031_1_prt_foo" for table "mpp8031" NOTICE: CREATE TABLE will create partition "mpp8031_1_prt_2" for table "mpp8031" NOTICE: CREATE TABLE will create partition "mpp8031_1_prt_3" for table "mpp8031" NOTICE: CREATE TABLE will create partition "mpp8031_1_prt_4" for table "mpp8031" explain analyze select a.* from mpp8031 a, mpp8031 b where a.oid = b.oid; QUERY PLAN ------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=6799.00..1021796.00 rows=80883360 width=16) Rows out: 0 rows at destination with 1.011 ms to end, start offset by 0.365 ms. -> Hash Join (cost=6799.00..1021796.00 rows=26961120 width=16) Hash Cond: a.oid = b.oid Rows out: 0 rows (seg0) with 0.051 ms to end, start offset by 1.246 ms. -> Append (cost=0.00..3244.00 rows=94800 width=16) Rows out: 0 rows (seg0) with 0.051 ms to end, start offset by 1.246 ms. -> Seq Scan on mpp8031_1_prt_foo a (cost=0.00..811.00 rows=23700 width=16) Rows out: 0 rows (seg0) with 0.022 ms to end, start offset by 1.247 ms. -> Seq Scan on mpp8031_1_prt_2 a (cost=0.00..811.00 rows=23700 width=16) Rows out: 0 rows (seg0) with 0.009 ms to end, start offset by 1.270 ms. -> Seq Scan on mpp8031_1_prt_3 a (cost=0.00..811.00 rows=23700 width=16) Rows out: 0 rows (seg0) with 0.009 ms to end, start offset by 1.279 ms. -> Seq Scan on mpp8031_1_prt_4 a (cost=0.00..811.00 rows=23700 width=16) Rows out: 0 rows (seg0) with 0.008 ms to end, start offset by 1.288 ms. -> Hash (cost=3244.00..3244.00 rows=94800 width=4) Rows in: (No row requested) 0 rows (seg0) with 0 ms to end. -> Append (cost=0.00..3244.00 rows=94800 width=4) Rows out: (No row requested) 0 rows (seg0) with 0 ms to end. -> Seq Scan on mpp8031_1_prt_foo b (cost=0.00..811.00 rows=23700 width=4) Rows out: (No row requested) 0 rows (seg0) with 0 ms to end. -> Seq Scan on mpp8031_1_prt_2 b (cost=0.00..811.00 rows=23700 width=4) Rows out: (No row requested) 0 rows (seg0) with 0 ms to end. -> Seq Scan on mpp8031_1_prt_3 b (cost=0.00..811.00 rows=23700 width=4) Rows out: (No row requested) 0 rows (seg0) with 0 ms to end. -> Seq Scan on mpp8031_1_prt_4 b (cost=0.00..811.00 rows=23700 width=4) Rows out: (No row requested) 0 rows (seg0) with 0 ms to end. Slice statistics: (slice0) Executor memory: 382K bytes. (slice1) Executor memory: 146K bytes avg x 3 workers, 146K bytes max (seg0). Statement statistics: Memory used: 128000K bytes Optimizer status: legacy query optimizer Total runtime: 2.045 ms (34 rows) drop table mpp8031; -- CLEANUP -- start_ignore drop schema if exists bfv_partition; -- end_ignore