Horton

test_case_pwj.sql

application/octet-stream

Download
Filename: test_case_pwj.sql
Type: application/octet-stream
Part: 1
Message: Re: Partition-wise join for join between (declaratively) partitioned tables 
SET enable_partition_wise_join = true;;
CREATE TABLE prt (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE prt_p1 PARTITION OF prt FOR VALUES FROM (0) TO (2500);
CREATE TABLE prt_p2 PARTITION OF prt FOR VALUES FROM (2500) TO (5000);
CREATE TABLE prt_p3 PARTITION OF prt FOR VALUES FROM (5000) TO (7500);
CREATE TABLE prt_p4 PARTITION OF prt FOR VALUES FROM (7500) TO (10000);
CREATE TABLE prt_p5 PARTITION OF prt FOR VALUES FROM (10000) TO (12500);
CREATE TABLE prt_p6 PARTITION OF prt FOR VALUES FROM (12500) TO (17500);
CREATE TABLE prt_p7 PARTITION OF prt FOR VALUES FROM (17500) TO (20000);
CREATE TABLE prt_p8 PARTITION OF prt FOR VALUES FROM (20000) TO (25000);
CREATE TABLE prt_p9 PARTITION OF prt FOR VALUES FROM (25000) TO (27500);
CREATE TABLE prt_p10 PARTITION OF prt FOR VALUES FROM (27500) TO (30000);
CREATE TABLE prt_p11 PARTITION OF prt FOR VALUES FROM (30000) TO (32500);
CREATE TABLE prt_p12 PARTITION OF prt FOR VALUES FROM (32500) TO (35000);
CREATE TABLE prt_p13 PARTITION OF prt FOR VALUES FROM (35000) TO (37500);
CREATE TABLE prt_p14 PARTITION OF prt FOR VALUES FROM (37500) TO (40000);
CREATE TABLE prt_p21 PARTITION OF prt FOR VALUES FROM (40000) TO (42500);
CREATE TABLE prt_p15 PARTITION OF prt FOR VALUES FROM (42500) TO (45000);
CREATE TABLE prt_p22 PARTITION OF prt FOR VALUES FROM (45000) TO (47500);
CREATE TABLE prt_p16 PARTITION OF prt FOR VALUES FROM (47500) TO (50000);
CREATE TABLE prt_p23 PARTITION OF prt FOR VALUES FROM (50000) TO (52500);
CREATE TABLE prt_p17 PARTITION OF prt FOR VALUES FROM (52500) TO (55000);
CREATE TABLE prt_p18 PARTITION OF prt FOR VALUES FROM (55000) TO (57500);
CREATE TABLE prt_p24 PARTITION OF prt FOR VALUES FROM (57500) TO (60000);
CREATE TABLE prt_p19 PARTITION OF prt FOR VALUES FROM (60000) TO (62500);
CREATE TABLE prt_p25 PARTITION OF prt FOR VALUES FROM (62500) TO (67500);
CREATE TABLE prt_p20 PARTITION OF prt FOR VALUES FROM (67500) TO (70000);

INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));
INSERT INTO prt values (generate_series(0,70000)*random(), generate_series(7000, 77000));

CREATE TABLE uprt AS SELECT * FROM prt;

CREATE TABLE prt2 (a int, b int) PARTITION BY RANGE(b);
CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (2500);
CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (2500) TO (5000);
CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (5000) TO (7500);
CREATE TABLE prt2_p4 PARTITION OF prt2 FOR VALUES FROM (7500) TO (10000);
CREATE TABLE prt2_p5 PARTITION OF prt2 FOR VALUES FROM (10000) TO (12500);
CREATE TABLE prt2_p6 PARTITION OF prt2 FOR VALUES FROM (12500) TO (17500);
CREATE TABLE prt2_p7 PARTITION OF prt2 FOR VALUES FROM (17500) TO (20000);
CREATE TABLE prt2_p8 PARTITION OF prt2 FOR VALUES FROM (20000) TO (25000);
CREATE TABLE prt2_p9 PARTITION OF prt2 FOR VALUES FROM (25000) TO (27500);
CREATE TABLE prt2_p10 PARTITION OF prt2 FOR VALUES FROM (27500) TO (30000);
CREATE TABLE prt2_p11 PARTITION OF prt2 FOR VALUES FROM (30000) TO (32500);
CREATE TABLE prt2_p12 PARTITION OF prt2 FOR VALUES FROM (32500) TO (35000);
CREATE TABLE prt2_p13 PARTITION OF prt2 FOR VALUES FROM (35000) TO (37500);
CREATE TABLE prt2_p14 PARTITION OF prt2 FOR VALUES FROM (37500) TO (40000);
CREATE TABLE prt2_p21 PARTITION OF prt2 FOR VALUES FROM (40000) TO (42500);
CREATE TABLE prt2_p15 PARTITION OF prt2 FOR VALUES FROM (42500) TO (45000);
CREATE TABLE prt2_p16 PARTITION OF prt2 FOR VALUES FROM (47500) TO (50000);
CREATE TABLE prt2_p17 PARTITION OF prt2 FOR VALUES FROM (52500) TO (55000);
CREATE TABLE prt2_p18 PARTITION OF prt2 FOR VALUES FROM (55000) TO (57500);
CREATE TABLE prt2_p19 PARTITION OF prt2 FOR VALUES FROM (60000) TO (62500);
CREATE TABLE prt2_p20 PARTITION OF prt2 FOR VALUES FROM (67500) TO (70000);
CREATE TABLE prt2_p22 PARTITION OF prt2 FOR VALUES FROM (45000) TO (47500);
CREATE TABLE prt2_p23 PARTITION OF prt2 FOR VALUES FROM (50000) TO (52500);
CREATE TABLE prt2_p24 PARTITION OF prt2 FOR VALUES FROM (57500) TO (60000);
CREATE TABLE prt2_p25 PARTITION OF prt2 FOR VALUES FROM (62500) TO (67500);

INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());
INSERT INTO prt2 values (generate_series(7000,77000)*random(), generate_series(0, 70000)*random());

CREATE TABLE uprt2 AS SELECT * FROM prt2;
create index p_idx on uprt(a);
create index p1_idx on prt_p1(a);
create index p2_idx on prt_p2(a);
create index p3_idx on prt_p3(a);
create index p4_idx on prt_p4(a);
create index p6_idx on prt_p6(a);
create index p5_idx on prt_p5(a);
create index p7_idx on prt_p7(a);
create index p8_idx on prt_p8(a);
create index p9_idx on prt_p9(a);
create index p10_idx on prt_p10(a);
create index p11_idx on prt_p1(a);
create index p12_idx on prt_p2(a);
create index p13_idx on prt_p3(a);
create index p14_idx on prt_p4(a);
create index p16_idx on prt_p6(a);
create index p15_idx on prt_p5(a);
create index p17_idx on prt_p7(a);
create index p18_idx on prt_p8(a);
create index p19_idx on prt_p9(a);
create index p20_idx on prt_p10(a);
create index p21_idx on prt_p5(a);
create index p22_idx on prt_p7(a);
create index p23_idx on prt_p8(a);
create index p24_idx on prt_p9(a);
create index p25_idx on prt_p10(a);

create index prt2_idx on uprt2(a);
create index p222_idx on prt2_p2(a);
create index p221_idx on prt2_p1(a);
create index p223_idx on prt2_p3(a);
create index p224_idx on prt2_p4(a);
create index p26_idx on prt2_p6(a);
create index p225_idx on prt2_p5(a);
create index p27_idx on prt2_p7(a);
create index p28_idx on prt2_p8(a);
create index p29_idx on prt2_p9(a);
create index p210_idx on prt2_p10(a);
create index p212_idx on prt2_p2(a);
create index p211_idx on prt2_p1(a);
create index p213_idx on prt2_p3(a);
create index p214_idx on prt2_p4(a);
create index p216_idx on prt2_p6(a);
create index p215_idx on prt2_p5(a);
create index p217_idx on prt2_p7(a);
create index p218_idx on prt2_p8(a);
create index p219_idx on prt2_p9(a);
create index p220_idx on prt2_p10(a);
create index p235_idx on prt2_p5(a);
create index p237_idx on prt2_p7(a);
create index p238_idx on prt2_p8(a);
create index p239_idx on prt2_p9(a);
create index p230_idx on prt2_p10(a);

vacuum analyse;