create schema inlist2join_cost_base;
set current_schema=inlist2join_cost_base;
set qrw_inlist2join_optmode=disable;
set qrw_inlist2join_optmode=rule_base;
set qrw_inlist2join_optmode=-1;
set qrw_inlist2join_optmode=cost_base;
create table t1(c1 int, c2 int, c3 int);
copy t1 from '@abs_srcdir@/data/inlist2join.data';
create table t2(c1 int, c2 int, c3 int);
insert into t2 select * from t1;
ANALYZE t1;
ANALYZE t2;
-- T_HashJoin T_Stream T_SubqueryScan
explain (costs off) select t2.c3, t2.c2 from t2 where t2.c1 > 1 AND t2.c2 in (select t1.c2 from t1 where t1.c1 IN (22376, 7697,91842,32482,49627,89493,83303,64430,62630,12468,41219,86264,57512,26467,83933,90814,96326,94490,16793,30622,54667,71608,83944,40124,90224,11409,47022,93382,33348,22693,40793,84920,13919,79189, 6675,49103,65433,12945,31682, 2106,42248,61990,66384,86546,62487,60545,74527,73315,42924,47691,88203,67892,26852,72041,35315,33527,57991, 5747, 45109,74104,17051,73311,67981,72523,89732,97553, 7721, 2093,60644,66857,18634,79095,44341,11184,72487, 3615,48173,17393,27185, 6279,69965,22133, 2138,33295,32935,49062,76352,27384,49675,39611,11423, 3726,43077,97087,70532,47240,74421,83989,59023,29279)) order by 1,2;
-- T_MergeJoin
set enable_hashjoin =off;
explain (costs off) select t2.c3, t2.c2 from t2 where t2.c1 > 1 AND t2.c2 in (select t1.c2 from t1 where t1.c1 IN (22376, 7697,91842,32482,49627,89493,83303,64430,62630,12468,41219,86264,57512,26467,83933,90814,96326,94490,16793,30622,54667,71608,83944,40124,90224,11409,47022,93382,33348,22693,40793,84920,13919,79189, 6675,49103,65433,12945,31682, 2106,42248,61990,66384,86546,62487,60545,74527,73315,42924,47691,88203,67892,26852,72041,35315,33527,57991, 5747, 45109,74104,17051,73311,67981,72523,89732,97553, 7721, 2093,60644,66857,18634,79095,44341,11184,72487, 3615,48173,17393,27185, 6279,69965,22133, 2138,33295,32935,49062,76352,27384,49675,39611,11423, 3726,43077,97087,70532,47240,74421,83989,59023,29279)) order by 1,2;
-- T_MergeJoin T_Material
set enable_mergejoin = off;
explain (costs off) select t2.c3, t2.c2 from t2 where t2.c1 > 1 AND t2.c2 in (select t1.c2 from t1 where t1.c1 IN (22376, 7697,91842,32482,49627,89493,83303,64430,62630,12468,41219,86264,57512,26467,83933,90814,96326,94490,16793,30622,54667,71608,83944,40124,90224,11409,47022,93382,33348,22693,40793,84920,13919,79189, 6675,49103,65433,12945,31682, 2106,42248,61990,66384,86546,62487,60545,74527,73315,42924,47691,88203,67892,26852,72041,35315,33527,57991, 5747, 45109,74104,17051,73311,67981,72523,89732,97553, 7721, 2093,60644,66857,18634,79095,44341,11184,72487, 3615,48173,17393,27185, 6279,69965,22133, 2138,33295,32935,49062,76352,27384,49675,39611,11423, 3726,43077,97087,70532,47240,74421,83989,59023,29279)) order by 1,2;
create index index1 on t1(c1);
create index index2 on t2(c1);
-- T_Append
set enable_nestloop =on;
set enable_hashjoin = on;
set enable_bitmapscan = off;
explain (costs off) select * from (select * from t1 where t1.c1 in (22376, 7697,91842,32482,49627,89493,83303,64430,62630,12468,41219,86264,57512,26467,83933,90814,96326,94490,16793) union all select * from t2) as dt where dt.c1 in ( 2093,60644,66857,18634,79095,44341,11184,72487, 3615,48173,17393,27185, 6279,69965,22133, 2138,33295,32935,49062,76352,27384,49675,39611,11423, 3726,43077,97087,70532,47240,74421,83989,59023,29279) order by 1;
-- T_MergeAppend
-- partition table
create table t1_p(c1 int, c2 int, c3 int)
PARTITION BY RANGE(c1)
(
PARTITION P1 VALUES LESS THAN(20000),
PARTITION P2 VALUES LESS THAN(40000),
PARTITION P3 VALUES LESS THAN(60000),
PARTITION P4 VALUES LESS THAN(80000),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
);
create table t2_p(c1 int, c2 int, c3 int)
PARTITION BY RANGE(c1)
(
PARTITION P1 VALUES LESS THAN(30000),
PARTITION P2 VALUES LESS THAN(60000),
PARTITION P3 VALUES LESS THAN(90000),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
);
insert into t1_p select * from t1;
insert into t2_p select * from t1;
ANALYZE t1_p;
ANALYZE t2_p;
-- T_PartIterator
explain (costs off) select t2_p.c2 from t2_p join t1_p on t1_p.c1=t2_p.c2 and t2_p.c2 in (1,2,3,4,5,6,7,8,9,10,11);
set enable_nestloop =off;
explain (costs off) select t2_p.c2 from t2_p join t1_p on t1_p.c1=t2_p.c2 and t2_p.c2 in (22376, 7697,91842,32482,49627,89493,83303,64430,62630,12468,41219,86264,57512,26467,83933,90814,96326,94490,16793,30622,54667,71608,83944,40124,90224,11409,47022,93382,33348,22693,40793,84920,13919,79189, 6675,49103,65433,12945,31682, 2106,42248,61990,66384,86546,62487,60545,74527,73315,42924,47691,88203,67892,26852,72041,35315,33527,57991, 5747, 45109,74104,17051,73311,67981,72523,89732,97553, 7721, 2093,60644,66857,18634,79095,44341,11184,72487, 3615,48173,17393,27185, 6279,69965,22133, 2138,33295,32935,49062,76352,27384,49675,39611,11423, 3726,43077,97087,70532,47240,74421,83989,59023,29279);
set enable_nestloop =on;
-- T_WindowAgg plan
explain (costs off) select c1,c2,avg(c3) OVER (PARTITION BY c1) from t1 where t1.c1 in (1,2,3,4,5,6,7,8,9,10,11) and t1.c2 in (1,2);
drop schema inlist2join_cost_base cascade;