github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/explain_indexmerge.result (about) 1 drop causet if exists t; 2 create causet t (a int primary key, b int, c int, d int, e int, f int); 3 create index tb on t (b); 4 create index tc on t (c); 5 create index td on t (d); 6 load stats 's/explain_indexmerge_stats_t.json'; 7 explain select * from t where a < 50 or b < 50; 8 id estRows task access object operator info 9 TableReader_7 98.00 root data:Selection_6 10 └─Selection_6 98.00 cop[einsteindb] or(lt(test.t.a, 50), lt(test.t.b, 50)) 11 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 12 explain select * from t where (a < 50 or b < 50) and f > 100; 13 id estRows task access object operator info 14 TableReader_7 98.00 root data:Selection_6 15 └─Selection_6 98.00 cop[einsteindb] gt(test.t.f, 100), or(lt(test.t.a, 50), lt(test.t.b, 50)) 16 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 17 explain select * from t where b < 50 or c < 50; 18 id estRows task access object operator info 19 TableReader_7 98.00 root data:Selection_6 20 └─Selection_6 98.00 cop[einsteindb] or(lt(test.t.b, 50), lt(test.t.c, 50)) 21 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 22 set stochastik milevadb_enable_index_merge = on; 23 explain select * from t where a < 50 or b < 50; 24 id estRows task access object operator info 25 IndexMerge_11 98.00 root 26 ├─TableRangeScan_8(Build) 49.00 cop[einsteindb] causet:t range:[-inf,50), keep order:false 27 ├─IndexRangeScan_9(Build) 49.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,50), keep order:false 28 └─TableRowIDScan_10(Probe) 98.00 cop[einsteindb] causet:t keep order:false 29 explain select * from t where (a < 50 or b < 50) and f > 100; 30 id estRows task access object operator info 31 IndexMerge_12 98.00 root 32 ├─TableRangeScan_8(Build) 49.00 cop[einsteindb] causet:t range:[-inf,50), keep order:false 33 ├─IndexRangeScan_9(Build) 49.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,50), keep order:false 34 └─Selection_11(Probe) 98.00 cop[einsteindb] gt(test.t.f, 100) 35 └─TableRowIDScan_10 98.00 cop[einsteindb] causet:t keep order:false 36 explain select * from t where a < 50 or b < 5000000; 37 id estRows task access object operator info 38 TableReader_7 4999999.00 root data:Selection_6 39 └─Selection_6 4999999.00 cop[einsteindb] or(lt(test.t.a, 50), lt(test.t.b, 5000000)) 40 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 41 explain select * from t where b < 50 or c < 50; 42 id estRows task access object operator info 43 IndexMerge_11 98.00 root 44 ├─IndexRangeScan_8(Build) 49.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,50), keep order:false 45 ├─IndexRangeScan_9(Build) 49.00 cop[einsteindb] causet:t, index:tc(c) range:[-inf,50), keep order:false 46 └─TableRowIDScan_10(Probe) 98.00 cop[einsteindb] causet:t keep order:false 47 explain select * from t where b < 50 or c < 5000000; 48 id estRows task access object operator info 49 TableReader_7 4999999.00 root data:Selection_6 50 └─Selection_6 4999999.00 cop[einsteindb] or(lt(test.t.b, 50), lt(test.t.c, 5000000)) 51 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 52 explain select * from t where a < 50 or b < 50 or c < 50; 53 id estRows task access object operator info 54 IndexMerge_12 147.00 root 55 ├─TableRangeScan_8(Build) 49.00 cop[einsteindb] causet:t range:[-inf,50), keep order:false 56 ├─IndexRangeScan_9(Build) 49.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,50), keep order:false 57 ├─IndexRangeScan_10(Build) 49.00 cop[einsteindb] causet:t, index:tc(c) range:[-inf,50), keep order:false 58 └─TableRowIDScan_11(Probe) 147.00 cop[einsteindb] causet:t keep order:false 59 explain select * from t where (b < 10000 or c < 10000) and (a < 10 or d < 10) and f < 10; 60 id estRows task access object operator info 61 IndexMerge_17 0.00 root 62 ├─TableRangeScan_13(Build) 9.00 cop[einsteindb] causet:t range:[-inf,10), keep order:false 63 ├─IndexRangeScan_14(Build) 9.00 cop[einsteindb] causet:t, index:td(d) range:[-inf,10), keep order:false 64 └─Selection_16(Probe) 0.00 cop[einsteindb] lt(test.t.f, 10), or(lt(test.t.b, 10000), lt(test.t.c, 10000)) 65 └─TableRowIDScan_15 18.00 cop[einsteindb] causet:t keep order:false 66 explain format="dot" select * from t where (a < 50 or b < 50) and f > 100; 67 dot contents 68 69 digraph IndexMerge_12 { 70 subgraph cluster12{ 71 node [style=filled, color=lightgrey] 72 color=black 73 label = "root" 74 "IndexMerge_12" 75 } 76 subgraph cluster8{ 77 node [style=filled, color=lightgrey] 78 color=black 79 label = "cop" 80 "TableRangeScan_8" 81 } 82 subgraph cluster9{ 83 node [style=filled, color=lightgrey] 84 color=black 85 label = "cop" 86 "IndexRangeScan_9" 87 } 88 subgraph cluster11{ 89 node [style=filled, color=lightgrey] 90 color=black 91 label = "cop" 92 "Selection_11" -> "TableRowIDScan_10" 93 } 94 "IndexMerge_12" -> "TableRangeScan_8" 95 "IndexMerge_12" -> "IndexRangeScan_9" 96 "IndexMerge_12" -> "Selection_11" 97 } 98 99 set stochastik milevadb_enable_index_merge = off; 100 explain select /*+ use_index_merge(t, tb, tc) */ * from t where b < 50 or c < 5000000; 101 id estRows task access object operator info 102 IndexMerge_8 5000000.00 root 103 ├─IndexRangeScan_5(Build) 49.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,50), keep order:false 104 ├─IndexRangeScan_6(Build) 4999999.00 cop[einsteindb] causet:t, index:tc(c) range:[-inf,5000000), keep order:false 105 └─TableRowIDScan_7(Probe) 5000000.00 cop[einsteindb] causet:t keep order:false 106 explain select /*+ use_index_merge(t, tb, tc) */ * from t where (b < 10000 or c < 10000) and (a < 10 or d < 10) and f < 10; 107 id estRows task access object operator info 108 IndexMerge_9 0.00 root 109 ├─IndexRangeScan_5(Build) 9999.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,10000), keep order:false 110 ├─IndexRangeScan_6(Build) 9999.00 cop[einsteindb] causet:t, index:tc(c) range:[-inf,10000), keep order:false 111 └─Selection_8(Probe) 0.00 cop[einsteindb] lt(test.t.f, 10), or(lt(test.t.a, 10), lt(test.t.d, 10)) 112 └─TableRowIDScan_7 19998.00 cop[einsteindb] causet:t keep order:false 113 explain select /*+ use_index_merge(t, tb) */ * from t where b < 50 or c < 5000000; 114 id estRows task access object operator info 115 TableReader_7 4999999.00 root data:Selection_6 116 └─Selection_6 4999999.00 cop[einsteindb] or(lt(test.t.b, 50), lt(test.t.c, 5000000)) 117 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 118 explain select /*+ no_index_merge(), use_index_merge(t, tb, tc) */ * from t where b < 50 or c < 5000000; 119 id estRows task access object operator info 120 TableReader_7 4999999.00 root data:Selection_6 121 └─Selection_6 4999999.00 cop[einsteindb] or(lt(test.t.b, 50), lt(test.t.c, 5000000)) 122 └─TableFullScan_5 5000000.00 cop[einsteindb] causet:t keep order:false 123 explain select /*+ use_index_merge(t, primary, tb) */ * from t where a < 50 or b < 5000000; 124 id estRows task access object operator info 125 IndexMerge_8 5000000.00 root 126 ├─TableRangeScan_5(Build) 49.00 cop[einsteindb] causet:t range:[-inf,50), keep order:false 127 ├─IndexRangeScan_6(Build) 4999999.00 cop[einsteindb] causet:t, index:tb(b) range:[-inf,5000000), keep order:false 128 └─TableRowIDScan_7(Probe) 5000000.00 cop[einsteindb] causet:t keep order:false