use test; drop table if exists t1, t2, t3; create table t1 (c1 int primary key, c2 int, c3 int, index c2 (c2)); load stats 's/explain_easy_stats_t1.json'; create table t2 (c1 int unique, c2 int); load stats 's/explain_easy_stats_t2.json'; create table t3 (a bigint, b bigint, c bigint, d bigint); load stats 's/explain_easy_stats_t3.json'; create table index_prune(a bigint(20) NOT NULL, b bigint(20) NOT NULL, c tinyint(4) NOT NULL, primary key(a, b), index idx_b_c_a(b, c, a)); load stats 's/explain_easy_stats_index_prune.json'; set @@session.tidb_opt_agg_push_down = 1; set @@session.tidb_opt_insubq_to_join_and_agg=1; set @@session.tidb_hashagg_partial_concurrency = 1; set @@session.tidb_hashagg_final_concurrency = 1; explain select * from t3 where exists (select s.a from t3 s having sum(s.a) = t3.a ); id estRows task access object operator info HashJoin_12 1600.00 root semi join, equal:[eq(Column#13, Column#11)] ├─StreamAgg_27(Build) 1.00 root funcs:sum(Column#16)->Column#11 │ └─TableReader_28 1.00 root data:StreamAgg_19 │ └─StreamAgg_19 1.00 cop[tikv] funcs:sum(test.t3.a)->Column#16 │ └─TableFullScan_26 2000.00 cop[tikv] table:s keep order:false └─Projection_13(Probe) 2000.00 root test.t3.a, test.t3.b, test.t3.c, test.t3.d, cast(test.t3.a, decimal(20,0) BINARY)->Column#13 └─TableReader_15 2000.00 root data:TableFullScan_14 └─TableFullScan_14 2000.00 cop[tikv] table:t3 keep order:false explain select * from t1; id estRows task access object operator info TableReader_5 1999.00 root data:TableFullScan_4 └─TableFullScan_4 1999.00 cop[tikv] table:t1 keep order:false explain select * from t1 order by c2; id estRows task access object operator info IndexLookUp_12 1999.00 root ├─IndexFullScan_10(Build) 1999.00 cop[tikv] table:t1, index:c2(c2) keep order:true └─TableRowIDScan_11(Probe) 1999.00 cop[tikv] table:t1 keep order:false explain select * from t2 order by c2; id estRows task access object operator info Sort_4 1985.00 root test.t2.c2:asc └─TableReader_8 1985.00 root data:TableFullScan_7 └─TableFullScan_7 1985.00 cop[tikv] table:t2 keep order:false explain select * from t1 where t1.c1 > 0; id estRows task access object operator info TableReader_6 1999.00 root data:TableRangeScan_5 └─TableRangeScan_5 1999.00 cop[tikv] table:t1 range:(0,+inf], keep order:false explain select t1.c1, t1.c2 from t1 where t1.c2 = 1; id estRows task access object operator info IndexReader_6 0.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 0.00 cop[tikv] table:t1, index:c2(c2) range:[1,1], keep order:false explain select * from t1 left join t2 on t1.c2 = t2.c1 where t1.c1 > 1; id estRows task access object operator info HashJoin_15 2481.25 root left outer join, equal:[eq(test.t1.c2, test.t2.c1)] ├─TableReader_29(Build) 1985.00 root data:Selection_28 │ └─Selection_28 1985.00 cop[tikv] not(isnull(test.t2.c1)) │ └─TableFullScan_27 1985.00 cop[tikv] table:t2 keep order:false └─TableReader_26(Probe) 1998.00 root data:TableRangeScan_25 └─TableRangeScan_25 1998.00 cop[tikv] table:t1 range:(1,+inf], keep order:false explain update t1 set t1.c2 = 2 where t1.c1 = 1; id estRows task access object operator info Update_2 N/A root N/A └─Point_Get_1 1.00 root table:t1 handle:1 explain delete from t1 where t1.c2 = 1; id estRows task access object operator info Delete_4 N/A root N/A └─IndexLookUp_11 0.00 root ├─IndexRangeScan_9(Build) 0.00 cop[tikv] table:t1, index:c2(c2) range:[1,1], keep order:false └─TableRowIDScan_10(Probe) 0.00 cop[tikv] table:t1 keep order:false explain select count(b.c2) from t1 a, t2 b where a.c1 = b.c2 group by a.c1; id estRows task access object operator info Projection_11 1985.00 root cast(Column#8, bigint(21) BINARY)->Column#7 └─HashJoin_17 1985.00 root inner join, equal:[eq(test.t1.c1, test.t2.c2)] ├─HashAgg_21(Build) 1985.00 root group by:test.t2.c2, funcs:count(test.t2.c2)->Column#8, funcs:firstrow(test.t2.c2)->test.t2.c2 │ └─TableReader_28 1985.00 root data:Selection_27 │ └─Selection_27 1985.00 cop[tikv] not(isnull(test.t2.c2)) │ └─TableFullScan_26 1985.00 cop[tikv] table:b keep order:false └─TableReader_30(Probe) 1999.00 root data:TableFullScan_29 └─TableFullScan_29 1999.00 cop[tikv] table:a keep order:false explain select * from t2 order by t2.c2 limit 0, 1; id estRows task access object operator info TopN_7 1.00 root test.t2.c2:asc, offset:0, count:1 └─TableReader_15 1.00 root data:TopN_14 └─TopN_14 1.00 cop[tikv] test.t2.c2:asc, offset:0, count:1 └─TableFullScan_13 1985.00 cop[tikv] table:t2 keep order:false explain select * from t1 where c1 > 1 and c2 = 1 and c3 < 1; id estRows task access object operator info IndexLookUp_11 0.00 root ├─IndexRangeScan_8(Build) 0.00 cop[tikv] table:t1, index:c2(c2) range:(1 1,1 +inf], keep order:false └─Selection_10(Probe) 0.00 cop[tikv] lt(test.t1.c3, 1) └─TableRowIDScan_9 0.00 cop[tikv] table:t1 keep order:false explain select * from t1 where c1 = 1 and c2 > 1; id estRows task access object operator info Selection_6 0.50 root gt(test.t1.c2, 1) └─Point_Get_5 1.00 root table:t1 handle:1 explain select c1 from t1 where c1 in (select c2 from t2); id estRows task access object operator info HashJoin_15 1985.00 root inner join, equal:[eq(test.t1.c1, test.t2.c2)] ├─HashAgg_19(Build) 1985.00 root group by:test.t2.c2, funcs:firstrow(test.t2.c2)->test.t2.c2 │ └─TableReader_26 1985.00 root data:Selection_25 │ └─Selection_25 1985.00 cop[tikv] not(isnull(test.t2.c2)) │ └─TableFullScan_24 1985.00 cop[tikv] table:t2 keep order:false └─TableReader_28(Probe) 1999.00 root data:TableFullScan_27 └─TableFullScan_27 1999.00 cop[tikv] table:t1 keep order:false explain select * from information_schema.columns; id estRows task access object operator info MemTableScan_4 10000.00 root table:COLUMNS explain select c2 = (select c2 from t2 where t1.c1 = t2.c1 order by c1 limit 1) from t1; id estRows task access object operator info Projection_12 1999.00 root eq(test.t1.c2, test.t2.c2)->Column#8 └─Apply_14 1999.00 root CARTESIAN left outer join ├─TableReader_16(Build) 1999.00 root data:TableFullScan_15 │ └─TableFullScan_15 1999.00 cop[tikv] table:t1 keep order:false └─Projection_43(Probe) 1.00 root test.t2.c1, test.t2.c2 └─IndexLookUp_42 1.00 root limit embedded(offset:0, count:1) ├─Limit_41(Build) 1.00 cop[tikv] offset:0, count:1 │ └─IndexRangeScan_39 1.25 cop[tikv] table:t2, index:c1(c1) range: decided by [eq(test.t1.c1, test.t2.c1)], keep order:true └─TableRowIDScan_40(Probe) 1.00 cop[tikv] table:t2 keep order:false, stats:pseudo explain select * from t1 order by c1 desc limit 1; id estRows task access object operator info Limit_10 1.00 root offset:0, count:1 └─TableReader_20 1.00 root data:Limit_19 └─Limit_19 1.00 cop[tikv] offset:0, count:1 └─TableFullScan_18 1.00 cop[tikv] table:t1 keep order:true, desc set @@session.tidb_opt_insubq_to_join_and_agg=0; explain select 1 in (select c2 from t2) from t1; id estRows task access object operator info HashJoin_7 1999.00 root CARTESIAN left outer semi join, other cond:eq(1, test.t2.c2) ├─TableReader_13(Build) 1985.00 root data:TableFullScan_12 │ └─TableFullScan_12 1985.00 cop[tikv] table:t2 keep order:false └─TableReader_9(Probe) 1999.00 root data:TableFullScan_8 └─TableFullScan_8 1999.00 cop[tikv] table:t1 keep order:false explain format="dot" select 1 in (select c2 from t2) from t1; dot contents digraph HashJoin_7 { subgraph cluster7{ node [style=filled, color=lightgrey] color=black label = "root" "HashJoin_7" -> "TableReader_9" "HashJoin_7" -> "TableReader_13" } subgraph cluster8{ node [style=filled, color=lightgrey] color=black label = "cop" "TableFullScan_8" } subgraph cluster12{ node [style=filled, color=lightgrey] color=black label = "cop" "TableFullScan_12" } "TableReader_9" -> "TableFullScan_8" "TableReader_13" -> "TableFullScan_12" } explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 1; id estRows task access object operator info Point_Get_1 1.00 root table:index_prune, index:PRIMARY(a, b) explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 0; id estRows task access object operator info TableDual_5 0.00 root rows:0 explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 1, 1; id estRows task access object operator info Limit_9 1.00 root offset:1, count:1 └─Point_Get_11 1.00 root table:index_prune, index:PRIMARY(a, b) explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 1, 0; id estRows task access object operator info Limit_9 0.00 root offset:1, count:0 └─Point_Get_11 1.00 root table:index_prune, index:PRIMARY(a, b) explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 LIMIT 0, 1; id estRows task access object operator info Point_Get_1 1.00 root table:index_prune, index:PRIMARY(a, b) explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 ORDER BY a; id estRows task access object operator info Point_Get_1 1.00 root table:index_prune, index:PRIMARY(a, b) explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 GROUP BY b; id estRows task access object operator info Point_Get_1 1.00 root table:index_prune, index:PRIMARY(a, b) explain select * from index_prune WHERE a = 1010010404050976781 AND b = 26467085526790 GROUP BY b ORDER BY a limit 1; id estRows task access object operator info Point_Get_1 1.00 root table:index_prune, index:PRIMARY(a, b) drop table if exists t1, t2, t3, index_prune; set @@session.tidb_opt_insubq_to_join_and_agg=1; drop table if exists tbl; create table tbl(column1 int, column2 int, index idx(column1, column2)); load stats 's/explain_easy_stats_tbl_dnf.json'; explain select * from tbl where (column1=0 and column2=1) or (column1=1 and column2=3) or (column1=2 and column2=5); id estRows task access object operator info IndexReader_6 3.00 root index:IndexRangeScan_5 └─IndexRangeScan_5 3.00 cop[tikv] table:tbl, index:idx(column1, column2) range:[0 1,0 1], [1 3,1 3], [2 5,2 5], keep order:false