| -- start_matchsubs |
| -- m/((Mon|Tue|Wed|Thu|Fri|Sat|Sun) (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) (0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9](.[0-9]+)? (?!0000)[0-9]{4}.*)+(['"])/ |
| -- s/((Mon|Tue|Wed|Thu|Fri|Sat|Sun) (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) (0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9](.[0-9]+)? (?!0000)[0-9]{4}.*)+(['"])/xxx xx xx xx:xx:xx xxxx"/ |
| -- m/Memory Usage: \d+\w?B/ |
| -- s/Memory Usage: \d+\w?B/Memory Usage: ###B/ |
| -- m/Memory: \d+kB/ |
| -- s/Memory: \d+kB/Memory: ###kB/ |
| -- m/Max: \d+kB/ |
| -- s/Max: \d+kB/Max: ###kB/ |
| -- m/Buckets: \d+/ |
| -- s/Buckets: \d+/Buckets: ###/ |
| -- m/Batches: \d+/ |
| -- s/Batches: \d+/Batches: ###/ |
| -- m/segment \d+/ |
| -- s/segment \d+/segment ###/ |
| -- m/using \d+ of \d+ buckets/ |
| -- s/using \d+ of \d+ buckets/using ## of ### buckets/ |
| -- end_matchsubs |
| |
| create schema bfv_partition_plans; |
| set search_path=bfv_partition_plans; |
| SET optimizer_trace_fallback=on; |
| |
| -- |
| -- Initial setup for all the partitioning test for this suite |
| -- |
| -- start_ignore |
| create language plpython3u; |
| -- 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 plpython3u; |
| |
| 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 plpython3u; |
| |
| |
| -- Test UPDATE that moves row from one partition to another. The partitioning |
| -- key is also the distribution key in this case. |
| create table mpp3061 (i int) partition by range(i) (start(1) end(5) every(1)); |
| insert into mpp3061 values(1); |
| update mpp3061 set i = 2 where i = 1; |
| select tableoid::regclass, * from mpp3061 where i = 2; |
| drop table mpp3061; |
| |
| -- |
| -- Tests if it produces SIGSEGV from "select from partition_table group by rollup or cube function" |
| -- |
| |
| -- SETUP |
| 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') |
| ); |
| |
| -- 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'); |
| |
| 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); |
| |
| -- CLEANUP |
| drop table mpp7980; |
| |
| |
| -- ************ORCA ENABLED********** |
| |
| |
| -- |
| -- MPP-23195 |
| -- |
| |
| -- SETUP |
| -- start_ignore |
| set optimizer_enable_bitmapscan=on; |
| set optimizer_enable_dynamicbitmapscan=on; |
| set optimizer_enable_indexjoin=on; |
| drop table if exists mpp23195_t1; |
| drop table if exists mpp23195_t2; |
| -- end_ignore |
| |
| create table mpp23195_t1 (i int) partition by range(i) (partition pt1 start(1) end(10), partition pt2 start(10) end(20)); |
| create index index_mpp23195_t1_i on mpp23195_t1(i); |
| create table mpp23195_t2(i int); |
| |
| 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'); |
| select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i; |
| |
| vacuum mpp23195_t1; |
| select find_operator('select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;', 'Dynamic Index Only Scan'); |
| select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i; |
| |
| -- CLEANUP |
| -- start_ignore |
| drop table if exists mpp23195_t1; |
| drop table if exists mpp23195_t2; |
| set optimizer_enable_bitmapscan=off; |
| set optimizer_enable_dynamicbitmapscan=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; |
| drop table if exists mpp21834_t2; |
| -- 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)); |
| |
| create index index_1 on mpp21834_t1(i); |
| |
| create index index_2 on mpp21834_t1(j); |
| |
| create table mpp21834_t2(i int, j int); |
| |
| -- TEST |
| set optimizer_enable_hashjoin = off; |
| select find_operator('analyze select * from mpp21834_t2,mpp21834_t1 where mpp21834_t2.i < mpp21834_t1.i;','Dynamic Index Scan'); |
| select find_operator('analyze select * from mpp21834_t2,mpp21834_t1 where mpp21834_t2.i < mpp21834_t1.i;','Nested Loop'); |
| |
| -- CLEANUP |
| drop index index_2; |
| drop index index_1; |
| drop table if exists mpp21834_t2; |
| drop table if exists mpp21834_t1; |
| reset optimizer_enable_hashjoin; |
| |
| |
| -- |
| -- 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; |
| -- end_ignore |
| |
| create table mpp23288(a int, b int) |
| partition by range (a) |
| ( |
| PARTITION pfirst END(5) INCLUSIVE, |
| PARTITION pinter START(6) END (10) INCLUSIVE, |
| PARTITION plast START (11) |
| ); |
| |
| 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 Seq Scan'); |
| 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; |
| |
| 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 Seq Scan'); |
| 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; |
| |
| 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 Seq Scan'); |
| 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; |
| |
| -- CLEANUP |
| -- start_ignore |
| drop table if exists mpp23288; |
| set optimizer_enable_broadcast_nestloop_outer_child=off; |
| -- end_ignore |
| |
| -- |
| -- No DPE (Dynamic Partition Elimination) on second child of a union under a join |
| -- |
| |
| -- SETUP |
| -- start_ignore |
| drop table if exists t; |
| drop table if exists p1; |
| drop table if exists p2; |
| drop table if exists p3; |
| drop table if exists p; |
| -- end_ignore |
| |
| create table p1 (a int, b int) partition by range(b) (start (1) end(100) every (20)); |
| create table p2 (a int, b int) partition by range(b) (start (1) end(100) every (20)); |
| create table p3 (a int, b int) partition by range(b) (start (1) end(100) every (20)); |
| create table p (a int, b int); |
| create table t(a int, b int); |
| |
| 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 |
| -- If force parallel, we won't have partition selector since we will use parallel join. |
| -- We need to disable parallel before doing this query. |
| set enable_parallel to false; |
| 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'); |
| reset enable_parallel; |
| |
| select count_operator('select * from (select * from p1 union select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); |
| |
| 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'); |
| |
| select count_operator('select * from (select * from p1 except select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector'); |
| |
| 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'); |
| |
| 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'); |
| |
| 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'); |
| |
| 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'); |
| |
| 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'); |
| |
| 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'); |
| |
| -- 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; |
| drop table if exists dis; |
| drop table if exists dbs; |
| -- end_ignore |
| |
| create table dts(c1 int, c2 int) partition by range(c2) (start(1) end(11) every(1)); |
| create table dis(c1 int, c2 int, c3 int) partition by range(c2) (start(1) end(11) every(1)); |
| create index dis_index on dis(c3); |
| CREATE TABLE dbs(c1 int, c2 int, c3 int) partition by range(c2) (start(1) end(11) every(1)); |
| create index dbs_index on dbs using bitmap(c3); |
| |
| |
| -- 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 Seq Scan'); |
| |
| (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); |
| |
| set optimizer_enable_dynamictablescan = off; |
| 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'); |
| |
| (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); |
| |
| select find_operator('select * from dbs where c2= 15 and c3 = 5;', 'Bitmap Heap Scan'); |
| |
| select * from dbs where c2= 15 and c3 = 5; |
| |
| -- CLEANUP |
| drop index dbs_index; |
| drop table if exists dbs; |
| drop index dis_index; |
| drop table if exists dis; |
| drop table if exists dts; |
| reset optimizer_enable_dynamictablescan; |
| |
| -- |
| -- Partition elimination for heterogenous DynamicIndexScans |
| -- |
| |
| -- SETUP |
| -- start_ignore |
| drop table if exists pp; |
| drop index if exists pp_1_prt_1_idx; |
| drop index if exists pp_rest_1_idx; |
| drop index if exists pp_rest_2_idx; |
| 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)); |
| 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 |
| set optimizer_enable_dynamictablescan = off; |
| select * from pp where b=2 and c=2; |
| select count_operator('select * from pp where b=2 and c=2;','Partition Selector'); |
| |
| -- 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; |
| reset optimizer_enable_dynamictablescan; |
| reset optimizer_segments; |
| set optimizer_partition_selection_log=off; |
| -- 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; |
| -- 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') |
| ); |
| |
| -- TEST |
| select * from ds_4 where month_id = '200800'; |
| select count_operator('select * from ds_4 where month_id = E''200800'';','Partition Selector'); |
| |
| select * from ds_4 where month_id > '200800'; |
| select count_operator('select * from ds_4 where month_id > E''200800'';','Partition Selector'); |
| |
| select * from ds_4 where month_id <= '200800'; |
| select count_operator('select * from ds_4 where month_id <= E''200800'';','Partition Selector'); |
| |
| select * from ds_4 a1,ds_4 a2 where a1.month_id = a2.month_id and a1.month_id > '200800'; |
| 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'); |
| |
| -- CLEANUP |
| -- start_ignore |
| DROP TABLE IF EXISTS ds_4; |
| set optimizer_partition_selection_log=off; |
| reset optimizer_segments; |
| |
| -- end_ignore |
| |
| -- |
| -- 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) |
| ); |
| |
| INSERT INTO bar SELECT g % 20, g % 20 from generate_series(1, 1000) g; |
| ANALYZE bar; |
| |
| SELECT b FROM bar GROUP BY b; |
| |
| EXPLAIN SELECT b FROM bar GROUP BY b; |
| |
| |
| -- 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) |
| ); |
| explain analyze select a.* from mpp8031 a, mpp8031 b where a.oid = b.oid; |
| drop table mpp8031; |
| |
| -- Partitioned tables with default partitions and indexes on all parts, |
| -- queries on them with a predicate on index column must not consider the scan |
| -- as partial and should not fallback. |
| CREATE TABLE part_tbl |
| ( |
| time_client_key numeric(16,0) NOT NULL, |
| ngin_service_key numeric NOT NULL, |
| profile_key numeric NOT NULL |
| ) |
| DISTRIBUTED BY (time_client_key) |
| PARTITION BY RANGE(time_client_key) |
| SUBPARTITION BY LIST (ngin_service_key) |
| SUBPARTITION TEMPLATE |
| ( |
| SUBPARTITION Package5 VALUES (479534741), |
| DEFAULT SUBPARTITION other_services |
| ) |
| ( |
| PARTITION p20151110 START (2015111000::numeric) |
| END (2015111100::numeric) WITH (appendonly=false) |
| ); |
| INSERT INTO part_tbl VALUES (2015111000, 479534741, 99999999); |
| INSERT INTO part_tbl VALUES (2015111000, 479534742, 99999999); |
| CREATE INDEX part_tbl_idx |
| ON part_tbl(profile_key); |
| -- start_ignore |
| analyze part_tbl; |
| -- end_ignore |
| EXPLAIN SELECT * FROM part_tbl WHERE profile_key = 99999999; |
| SELECT * FROM part_tbl WHERE profile_key = 99999999; |
| DROP TABLE part_tbl; |
| |
| -- |
| -- Test partition elimination, MPP-7891 |
| -- |
| |
| -- cleanup |
| -- start_ignore |
| drop table if exists r_part; |
| drop table if exists r_co; |
| |
| deallocate f1; |
| deallocate f2; |
| deallocate f3; |
| -- end_ignore |
| |
| create table r_part(a int, b int) partition by range(a) (start (1) end(10) every(1)); |
| create table r_co(a int, b int) with (orientation=column, appendonly=true) partition by range(a) (start (1) end(10) every(1)) ; |
| |
| insert into r_part values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8); |
| |
| -- following tests rely on the data distribution, verify them |
| select gp_segment_id, * from r_part order by a,b; |
| |
| analyze r_part; |
| |
| explain select * from r_part r1, r_part r2 where r1.a=1; -- should eliminate partitions in the r1 copy of r_part |
| |
| -- the numbers in the filter should be both on segment 0 |
| explain select * from r_part where a in (7,8); -- should eliminate partitions |
| |
| -- Test partition elimination in prepared statements |
| prepare f1(int) as select * from r_part where a = 1 order by a,b; |
| prepare f2(int) as select * from r_part where a = $1 order by a,b; |
| |
| execute f1(1); |
| execute f2(1); |
| execute f2(2); |
| |
| |
| explain select * from r_part where a = 1 order by a,b; -- should eliminate partitions |
| --force_explain |
| explain execute f1(1); -- should eliminate partitions |
| --force_explain |
| explain execute f2(2); -- should eliminate partitions |
| |
| |
| -- Test partition elimination on CO tables |
| insert into r_co values (1,1), (2,2), (3,3); |
| analyze r_co; |
| explain select * from r_co where a=2; -- should eliminate partitions |
| |
| -- test partition elimination in prepared statements on CO tables |
| prepare f3(int) as select * from r_co where a = $1 order by a,b; |
| --force_explain |
| explain execute f3(2); -- should eliminate partitions |
| |
| -- start_ignore |
| drop table r_part; |
| drop table r_co; |
| deallocate f1; |
| deallocate f2; |
| deallocate f3; |
| -- end_ignore |
| |
| -- |
| -- Test partition elimination, MPP-7891 |
| -- |
| |
| -- start_ignore |
| drop table if exists fact; |
| deallocate f1; |
| |
| create table fact(x int, dd date, dt text) distributed by (x) partition by range (dd) ( start('2008-01-01') end ('2320-01-01') every(interval '100 years')); |
| -- end_ignore |
| |
| analyze fact; |
| |
| select '2009-01-02'::date = to_date('2009-01-02','YYYY-MM-DD'); -- ensure that both are in fact equal |
| |
| explain select * from fact where dd < '2009-01-02'::date; -- partitions eliminated |
| |
| explain select * from fact where dd < to_date('2009-01-02','YYYY-MM-DD'); -- partitions eliminated |
| |
| explain select * from fact where dd < current_date; --partitions eliminated |
| |
| -- Test partition elimination in prepared statements |
| |
| prepare f1(date) as select * from fact where dd < $1; |
| |
| -- force_explain |
| explain execute f1('2009-01-02'::date); -- should eliminate partitions |
| -- force_explain |
| explain execute f1(to_date('2009-01-02', 'YYYY-MM-DD')); -- should eliminate partitions |
| |
| -- start_ignore |
| drop table fact; |
| deallocate f1; |
| -- end_ignore |
| |
| -- MPP-6247 |
| -- Delete Using on partitioned table causes repetitive scans on using table |
| create table mpp6247_foo ( c1 int, dt date ) distributed by ( c1 ) partition by range (dt) ( start ( date '2009-05-01' ) end ( date '2009-05-11' ) every ( interval '1 day' ) ); |
| create table mpp6247_bar (like mpp6247_foo); |
| |
| -- EXPECT: Single HJ after partition elimination instead of sequence of HJ under Append |
| select count_operator('delete from mpp6247_foo using mpp6247_bar where mpp6247_foo.c1 = mpp6247_bar.c1 and mpp6247_foo.dt = ''2009-05-03''', 'Hash Join'); |
| |
| drop table mpp6247_bar; |
| drop table mpp6247_foo; |
| |
| -- Validate that basic DELETE on partition table with index functions properly |
| |
| CREATE TABLE delete_from_indexed_pt (a int, b int) PARTITION BY RANGE(b) (START (0) END (7) EVERY (3)); |
| CREATE INDEX index_delete_from_indexed_pt ON delete_from_indexed_pt USING bitmap(b); |
| |
| INSERT INTO delete_from_indexed_pt SELECT i, i%6 FROM generate_series(1, 10)i; |
| |
| EXPLAIN (COSTS OFF) DELETE FROM delete_from_indexed_pt WHERE b=1; |
| DELETE FROM delete_from_indexed_pt WHERE b=1; |
| |
| SELECT * FROM delete_from_indexed_pt; |
| |
| -- Validate that basic DELETE on partition table using DPE functions properly |
| CREATE TABLE delete_from_pt (a int, b int) PARTITION BY RANGE(b) (START (0) END (7) EVERY (3)); |
| CREATE TABLE t(a int); |
| |
| INSERT INTO delete_from_pt SELECT i, i%6 FROM generate_series(1, 10)i; |
| INSERT INTO t VALUES (1); |
| |
| ANALYZE delete_from_pt, t; |
| EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF, ANALYZE) DELETE FROM delete_from_pt WHERE b IN (SELECT b FROM delete_from_pt, t WHERE t.a=delete_from_pt.b); |
| |
| SELECT * FROM delete_from_pt order by a; |
| |
| RESET optimizer_trace_fallback; |
| |
| -- CLEANUP |
| -- start_ignore |
| drop schema if exists bfv_partition_plans cascade; |
| -- end_ignore |
