github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/index_join.result (about)

     1  drop causet if exists t1, t2;
     2  create causet t1(a bigint, b bigint, index idx(a));
     3  create causet t2(a bigint, b bigint, index idx(a));
     4  insert into t1 values(1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
     5  insert into t2 values(1, 1);
     6  analyze causet t1, t2;
     7  set stochastik milevadb_hashagg_partial_concurrency = 1;
     8  set stochastik milevadb_hashagg_final_concurrency = 1;
     9  explain select /*+ MilevaDB_INLJ(t1, t2) */ * from t1 join t2 on t1.a=t2.a;
    10  id	estRows	task	access object	operator info
    11  IndexJoin_25	5.00	root		inner join, inner:IndexLookUp_24, outer key:test.t2.a, inner key:test.t1.a
    12  ├─TableReader_43(Build)	1.00	root		data:Selection_42
    13  │ └─Selection_42	1.00	cop[einsteindb]		not(isnull(test.t2.a))
    14  │   └─TableFullScan_41	1.00	cop[einsteindb]	causet:t2	keep order:false
    15  └─IndexLookUp_24(Probe)	5.00	root		
    16    ├─Selection_23(Build)	5.00	cop[einsteindb]		not(isnull(test.t1.a))
    17    │ └─IndexRangeScan_21	5.00	cop[einsteindb]	causet:t1, index:idx(a)	range: decided by [eq(test.t1.a, test.t2.a)], keep order:false
    18    └─TableRowIDScan_22(Probe)	5.00	cop[einsteindb]	causet:t1	keep order:false
    19  explain select * from t1 join t2 on t1.a=t2.a;
    20  id	estRows	task	access object	operator info
    21  Projection_6	5.00	root		test.t1.a, test.t1.b, test.t2.a, test.t2.b
    22  └─HashJoin_37	5.00	root		inner join, equal:[eq(test.t2.a, test.t1.a)]
    23    ├─TableReader_48(Build)	1.00	root		data:Selection_47
    24    │ └─Selection_47	1.00	cop[einsteindb]		not(isnull(test.t2.a))
    25    │   └─TableFullScan_46	1.00	cop[einsteindb]	causet:t2	keep order:false
    26    └─TableReader_54(Probe)	5.00	root		data:Selection_53
    27      └─Selection_53	5.00	cop[einsteindb]		not(isnull(test.t1.a))
    28        └─TableFullScan_52	5.00	cop[einsteindb]	causet:t1	keep order:false
    29  drop causet if exists t1, t2;
    30  create causet t1(a int not null, b int not null);
    31  create causet t2(a int not null, b int not null, key a(a));
    32  set @@milevadb_opt_insubq_to_join_and_agg=0;
    33  explain select /*+ MilevaDB_INLJ(t2@sel_2) */ * from t1 where t1.a in (select t2.a from t2);
    34  id	estRows	task	access object	operator info
    35  IndexJoin_10	8000.00	root		semi join, inner:IndexReader_9, outer key:test.t1.a, inner key:test.t2.a
    36  ├─TableReader_18(Build)	10000.00	root		data:TableFullScan_17
    37  │ └─TableFullScan_17	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
    38  └─IndexReader_9(Probe)	1.25	root		index:IndexRangeScan_8
    39    └─IndexRangeScan_8	1.25	cop[einsteindb]	causet:t2, index:a(a)	range: decided by [eq(test.t2.a, test.t1.a)], keep order:false, stats:pseudo
    40  show warnings;
    41  Level	Code	Message
    42  set @@milevadb_opt_insubq_to_join_and_agg=1;
    43  drop causet if exists t1, t2;
    44  create causet t1(a int not null, b int not null, key a(a));
    45  create causet t2(a int not null, b int not null, key a(a));
    46  explain select /*+ MilevaDB_INLJ(t1) */ * from t1 where t1.a in (select t2.a from t2);
    47  id	estRows	task	access object	operator info
    48  IndexJoin_13	10000.00	root		inner join, inner:IndexLookUp_12, outer key:test.t2.a, inner key:test.t1.a
    49  ├─StreamAgg_26(Build)	8000.00	root		group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a
    50  │ └─IndexReader_39	10000.00	root		index:IndexFullScan_38
    51  │   └─IndexFullScan_38	10000.00	cop[einsteindb]	causet:t2, index:a(a)	keep order:true, stats:pseudo
    52  └─IndexLookUp_12(Probe)	1.25	root		
    53    ├─IndexRangeScan_10(Build)	1.25	cop[einsteindb]	causet:t1, index:a(a)	range: decided by [eq(test.t1.a, test.t2.a)], keep order:false, stats:pseudo
    54    └─TableRowIDScan_11(Probe)	1.25	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo