github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimizer/top.result (about) 1 drop database if exists d1; 2 create database d1; 3 use d1; 4 drop table if exists t1; 5 create table t1(c1 int primary key, c2 int, c3 int); 6 create table t2(c1 int primary key, c2 int, c3 int); 7 insert into t1 select *,*,* from generate_series(1000000) g; 8 insert into t2 select *,*,* from generate_series(1000000) g; 9 select mo_ctl('dn', 'flush', 'd1.t1'); 10 mo_ctl(dn, flush, d1.t1) 11 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 12 select mo_ctl('dn', 'flush', 'd1.t2'); 13 mo_ctl(dn, flush, d1.t2) 14 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 15 select Sleep(1); 16 sleep(1) 17 0 18 explain select * from t1 order by c1 limit 100 offset 200; 19 QUERY PLAN 20 Project 21 -> Sort 22 Sort Key: t1.c1 INTERNAL 23 Limit: 100, Offset: 200 24 Send Message: [tag 1 , type MsgTopValue] 25 -> Table Scan on d1.t1 26 Sort Key: c1 INTERNAL 27 Recv Message: [tag 1 , type MsgTopValue] 28 explain select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 44444 order by t1.c1 limit 10 offset 10; 29 QUERY PLAN 30 Project 31 -> Sort 32 Sort Key: t1.c1 INTERNAL 33 Limit: 10, Offset: 10 34 Send Message: [tag 2 , type MsgTopValue] 35 -> Join 36 Join Type: INNER hashOnPK 37 Join Cond: (t1.c1 = t2.c1) 38 Runtime Filter Build: #[-1,0] 39 -> Table Scan on d1.t1 40 Sort Key: c1 INTERNAL 41 Runtime Filter Probe: t1.c1 42 Recv Message: [tag 2 , type MsgTopValue] 43 -> Table Scan on d1.t2 44 Filter Cond: t2.c2 BETWEEN 33333 AND 44444 45 Block Filter Cond: t2.c2 BETWEEN 33333 AND 44444 46 select * from t1 order by c1 limit 10 ; 47 c1 c2 c3 48 1 1 1 49 2 2 2 50 3 3 3 51 4 4 4 52 5 5 5 53 6 6 6 54 7 7 7 55 8 8 8 56 9 9 9 57 10 10 10 58 select * from t1 order by c1 limit 10 offset 200; 59 c1 c2 c3 60 201 201 201 61 202 202 202 62 203 203 203 63 204 204 204 64 205 205 205 65 206 206 206 66 207 207 207 67 208 208 208 68 209 209 209 69 210 210 210 70 select * from t1 order by c1 desc limit 10 ; 71 c1 c2 c3 72 1000000 1000000 1000000 73 999999 999999 999999 74 999998 999998 999998 75 999997 999997 999997 76 999996 999996 999996 77 999995 999995 999995 78 999994 999994 999994 79 999993 999993 999993 80 999992 999992 999992 81 999991 999991 999991 82 select * from t1 order by c1 desc limit 10 offset 200; 83 c1 c2 c3 84 999800 999800 999800 85 999799 999799 999799 86 999798 999798 999798 87 999797 999797 999797 88 999796 999796 999796 89 999795 999795 999795 90 999794 999794 999794 91 999793 999793 999793 92 999792 999792 999792 93 999791 999791 999791 94 select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 22 and 44 order by t1.c1 limit 10; 95 c1 c2 c3 c1 c2 c3 96 22 22 22 22 22 22 97 23 23 23 23 23 23 98 24 24 24 24 24 24 99 25 25 25 25 25 25 100 26 26 26 26 26 26 101 27 27 27 27 27 27 102 28 28 28 28 28 28 103 29 29 29 29 29 29 104 30 30 30 30 30 30 105 31 31 31 31 31 31 106 select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 22 and 44 order by t1.c1 desc limit 10 offset 10; 107 c1 c2 c3 c1 c2 c3 108 34 34 34 34 34 34 109 33 33 33 33 33 33 110 32 32 32 32 32 32 111 31 31 31 31 31 31 112 30 30 30 30 30 30 113 29 29 29 29 29 29 114 28 28 28 28 28 28 115 27 27 27 27 27 27 116 26 26 26 26 26 26 117 25 25 25 25 25 25 118 select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 44444 order by t1.c1 limit 10; 119 c1 c2 c3 c1 c2 c3 120 33333 33333 33333 33333 33333 33333 121 33334 33334 33334 33334 33334 33334 122 33335 33335 33335 33335 33335 33335 123 33336 33336 33336 33336 33336 33336 124 33337 33337 33337 33337 33337 33337 125 33338 33338 33338 33338 33338 33338 126 33339 33339 33339 33339 33339 33339 127 33340 33340 33340 33340 33340 33340 128 33341 33341 33341 33341 33341 33341 129 33342 33342 33342 33342 33342 33342 130 select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 44444 order by t1.c1 desc limit 10 offset 10; 131 c1 c2 c3 c1 c2 c3 132 44434 44434 44434 44434 44434 44434 133 44433 44433 44433 44433 44433 44433 134 44432 44432 44432 44432 44432 44432 135 44431 44431 44431 44431 44431 44431 136 44430 44430 44430 44430 44430 44430 137 44429 44429 44429 44429 44429 44429 138 44428 44428 44428 44428 44428 44428 139 44427 44427 44427 44427 44427 44427 140 44426 44426 44426 44426 44426 44426 141 44425 44425 44425 44425 44425 44425 142 select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 555555 order by t1.c1 limit 10; 143 c1 c2 c3 c1 c2 c3 144 33333 33333 33333 33333 33333 33333 145 33334 33334 33334 33334 33334 33334 146 33335 33335 33335 33335 33335 33335 147 33336 33336 33336 33336 33336 33336 148 33337 33337 33337 33337 33337 33337 149 33338 33338 33338 33338 33338 33338 150 33339 33339 33339 33339 33339 33339 151 33340 33340 33340 33340 33340 33340 152 33341 33341 33341 33341 33341 33341 153 33342 33342 33342 33342 33342 33342 154 explain select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 555555 order by t1.c1 desc limit 10 offset 10; 155 QUERY PLAN 156 Project 157 -> Sort 158 Sort Key: t1.c1 DESC 159 Limit: 10, Offset: 10 160 Send Message: [tag 1 , type MsgTopValue] 161 -> Join 162 Join Type: INNER hashOnPK 163 Join Cond: (t1.c1 = t2.c1) shuffle: range(t1.c1) 164 -> Table Scan on d1.t1 165 Sort Key: c1 DESC 166 Recv Message: [tag 1 , type MsgTopValue] 167 -> Table Scan on d1.t2 168 Filter Cond: t2.c2 BETWEEN 33333 AND 555555 169 Block Filter Cond: t2.c2 BETWEEN 33333 AND 555555 170 select * from t1,t2 where t1.c1=t2.c1 and t2.c2 between 33333 and 555555 order by t1.c1 desc limit 10 offset 10; 171 c1 c2 c3 c1 c2 c3 172 555545 555545 555545 555545 555545 555545 173 555544 555544 555544 555544 555544 555544 174 555543 555543 555543 555543 555543 555543 175 555542 555542 555542 555542 555542 555542 176 555541 555541 555541 555541 555541 555541 177 555540 555540 555540 555540 555540 555540 178 555539 555539 555539 555539 555539 555539 179 555538 555538 555538 555538 555538 555538 180 555537 555537 555537 555537 555537 555537 181 555536 555536 555536 555536 555536 555536 182 drop database if exists d1;