github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/array/array_index_knn.result (about) 1 drop database if exists vecdb3; 2 create database vecdb3; 3 use vecdb3; 4 SET GLOBAL experimental_ivf_index = 1; 5 drop table if exists t6; 6 create table t6(a int primary key,b vecf32(4), c varchar(3) ); 7 insert into t6 values(1, "[1,0,0,0]" , "1"); 8 insert into t6 values(2, "[2,0,0,0]", "2"); 9 insert into t6 values(3, "[3,0,0,0]", "3"); 10 insert into t6 values(4, "[1,1,0,0]", "4"); 11 insert into t6 values(5, "[2,2,0,0]", "5"); 12 insert into t6 values(6, "[3,3,0,0]", "6"); 13 SET GLOBAL experimental_ivf_index = 0; 14 create index idx6 using ivfflat on t6(b) lists=2 op_type "vector_l2_ops"; 15 internal error: IVF index is not enabled 16 SET GLOBAL experimental_ivf_index = 1; 17 create index idx6 using ivfflat on t6(b) lists=2 op_type "vector_l2_ops"; 18 select a, b from t6 order by l2_distance(b, "[1,0,0,0]") limit 4; 19 a b 20 1 [1, 0, 0, 0] 21 2 [2, 0, 0, 0] 22 3 [3, 0, 0, 0] 23 drop table if exists t1; 24 create table t1(a int primary key,b vecf32(4), c varchar(3) ); 25 insert into t1 values(1, "[1,0,0,0]" , "1"); 26 insert into t1 values(2, "[2,0,0,0]", "2"); 27 insert into t1 values(3, "[3,0,0,0]", "3"); 28 insert into t1 values(4, "[1,1,0,0]", "4"); 29 insert into t1 values(5, "[2,2,0,0]", "5"); 30 insert into t1 values(6, "[3,3,0,0]", "6"); 31 insert into t1 values(7, "[1,1,1,0]", "7"); 32 insert into t1 values(8, "[2,2,2,0]", "8"); 33 insert into t1 values(9, "[3,3,3,0]", "9"); 34 select a, b from t1 order by l2_distance(normalize_l2(b), normalize_l2("[1,1,1,0]")) limit 3; 35 a b 36 8 [2, 2, 2, 0] 37 9 [3, 3, 3, 0] 38 7 [1, 1, 1, 0] 39 create index idx1 using ivfflat on t1(b) lists=3 op_type "vector_l2_ops"; 40 select a, b from t1 order by l2_distance(b, "[1,1,1,0]") limit 4; 41 a b 42 7 [1, 1, 1, 0] 43 8 [2, 2, 2, 0] 44 9 [3, 3, 3, 0] 45 insert into t1 values(10, "[4,4,4,0]", "10"); 46 select a, b from t1 order by l2_distance(b, "[1,1,1,0]") limit 4; 47 a b 48 7 [1, 1, 1, 0] 49 8 [2, 2, 2, 0] 50 9 [3, 3, 3, 0] 51 10 [4, 4, 4, 0] 52 select a,b,c from t1 order by l2_distance(b, "[1,1,1,0]") limit 4; 53 a b c 54 7 [1, 1, 1, 0] 7 55 8 [2, 2, 2, 0] 8 56 9 [3, 3, 3, 0] 9 57 10 [4, 4, 4, 0] 10 58 insert into t1 values(11, "[1,1,1,1]", "11"); 59 insert into t1 values(12, "[2,2,2,2]", "12"); 60 insert into t1 values(13, "[3,3,3,3]", "13"); 61 alter table t1 alter reindex idx1 ivfflat lists=4; 62 select a, b from t1 order by l2_distance(b, "[1,0,0,0]") limit 4; 63 a b 64 1 [1, 0, 0, 0] 65 2 [2, 0, 0, 0] 66 3 [3, 0, 0, 0] 67 select a, b from t1 order by l2_distance(b, "[1,1,0,0]") limit 4; 68 a b 69 4 [1, 1, 0, 0] 70 5 [2, 2, 0, 0] 71 6 [3, 3, 0, 0] 72 select a, b from t1 order by l2_distance(b, "[1,1,1,0]") limit 4; 73 a b 74 7 [1, 1, 1, 0] 75 8 [2, 2, 2, 0] 76 9 [3, 3, 3, 0] 77 10 [4, 4, 4, 0] 78 select a, b from t1 order by l2_distance(b, "[1,1,1,1]") limit 4; 79 a b 80 11 [1, 1, 1, 1] 81 12 [2, 2, 2, 2] 82 13 [3, 3, 3, 3] 83 drop table if exists t3; 84 create table t2(a int,b vecf32(4), c varchar(3), d int, primary key(a,d)); 85 insert into t2 values(1, "[1,0,0,0]" , "1", 1); 86 insert into t2 values(2, "[2,0,0,0]", "2", 2); 87 insert into t2 values(3, "[3,0,0,0]", "3", 3); 88 insert into t2 values(4, "[1,1,0,0]", "4", 4); 89 insert into t2 values(5, "[2,2,0,0]", "5", 5); 90 insert into t2 values(6, "[3,3,0,0]", "6", 6); 91 insert into t2 values(7, "[1,1,1,0]", "7", 7); 92 insert into t2 values(8, "[2,2,2,0]", "8", 8); 93 insert into t2 values(9, "[3,3,3,0]", "9", 9); 94 select a, b from t2 order by l2_distance(normalize_l2(b), normalize_l2("[1,1,1,0]")) limit 3; 95 a b 96 8 [2, 2, 2, 0] 97 9 [3, 3, 3, 0] 98 7 [1, 1, 1, 0] 99 create index idx2 using ivfflat on t2(b) lists=3 op_type "vector_l2_ops"; 100 select b from t2 order by l2_distance(b, "[1,1,1,0]") limit 4; -- speed path (should output only 3 rows) 101 b 102 [1, 1, 1, 0] 103 [2, 2, 2, 0] 104 [3, 3, 3, 0] 105 select a, b from t2 order by l2_distance(b, "[1,1,1,0]") limit 4; -- slow path 106 a b 107 7 [1, 1, 1, 0] 108 8 [2, 2, 2, 0] 109 9 [3, 3, 3, 0] 110 insert into t2 values(10, "[4,4,4,0]", "10", 10); 111 select b from t2 order by l2_distance(b, "[1,1,1,0]") limit 4; -- speed path 112 b 113 [1, 1, 1, 0] 114 [2, 2, 2, 0] 115 [3, 3, 3, 0] 116 [4, 4, 4, 0] 117 select a, b from t2 order by l2_distance(b, "[1,1,1,0]") limit 4; -- slow path 118 a b 119 7 [1, 1, 1, 0] 120 8 [2, 2, 2, 0] 121 9 [3, 3, 3, 0] 122 10 [4, 4, 4, 0] 123 select a,b,c from t2 order by l2_distance(b, "[1,1,1,0]") limit 4; 124 a b c 125 7 [1, 1, 1, 0] 7 126 8 [2, 2, 2, 0] 8 127 9 [3, 3, 3, 0] 9 128 10 [4, 4, 4, 0] 10 129 insert into t2 values(11, "[1,1,1,1]", "11", 11); 130 insert into t2 values(12, "[2,2,2,2]", "12", 12); 131 insert into t2 values(13, "[3,3,3,3]", "13", 13); 132 alter table t2 alter reindex idx2 ivfflat lists=4; 133 select a, b from t2 order by l2_distance(b, "[1,0,0,0]") limit 4; 134 a b 135 1 [1, 0, 0, 0] 136 2 [2, 0, 0, 0] 137 3 [3, 0, 0, 0] 138 select a, b from t2 order by l2_distance(b, "[1,1,0,0]") limit 4; 139 a b 140 4 [1, 1, 0, 0] 141 5 [2, 2, 0, 0] 142 6 [3, 3, 0, 0] 143 select a, b from t2 order by l2_distance(b, "[1,1,1,0]") limit 4; 144 a b 145 7 [1, 1, 1, 0] 146 8 [2, 2, 2, 0] 147 9 [3, 3, 3, 0] 148 10 [4, 4, 4, 0] 149 select a, b from t2 order by l2_distance(b, "[1,1,1,1]") limit 4; 150 a b 151 11 [1, 1, 1, 1] 152 12 [2, 2, 2, 2] 153 13 [3, 3, 3, 3] 154 drop table if exists t3; 155 create table t3(a int,b vecf32(4), c varchar(3)); 156 insert into t3 values(1, "[1,0,0,0]" , "1"); 157 insert into t3 values(2, "[2,0,0,0]", "2"); 158 insert into t3 values(3, "[3,0,0,0]", "3"); 159 insert into t3 values(4, "[1,1,0,0]", "4"); 160 insert into t3 values(5, "[2,2,0,0]", "5"); 161 insert into t3 values(6, "[3,3,0,0]", "6"); 162 insert into t3 values(7, "[1,1,1,0]", "7"); 163 insert into t3 values(8, "[2,2,2,0]", "8"); 164 insert into t3 values(9, "[3,3,3,0]", "9"); 165 select a, b from t3 order by l2_distance(normalize_l2(b), normalize_l2("[1,1,1,0]")) limit 3; 166 a b 167 8 [2, 2, 2, 0] 168 9 [3, 3, 3, 0] 169 7 [1, 1, 1, 0] 170 create index idx3 using ivfflat on t3(b) lists=3 op_type "vector_l2_ops"; 171 select a, b from t3 order by l2_distance(b, "[1,1,1,0]") limit 4; 172 a b 173 7 [1, 1, 1, 0] 174 8 [2, 2, 2, 0] 175 9 [3, 3, 3, 0] 176 insert into t3 values(10, "[4,4,4,0]", "10"); 177 select a, b from t3 order by l2_distance(b, "[1,1,1,0]") limit 4; 178 a b 179 7 [1, 1, 1, 0] 180 8 [2, 2, 2, 0] 181 9 [3, 3, 3, 0] 182 10 [4, 4, 4, 0] 183 select a,b,c from t3 order by l2_distance(b, "[1,1,1,0]") limit 4; 184 a b c 185 7 [1, 1, 1, 0] 7 186 8 [2, 2, 2, 0] 8 187 9 [3, 3, 3, 0] 9 188 10 [4, 4, 4, 0] 10 189 insert into t3 values(11, "[1,1,1,1]", "11"); 190 insert into t3 values(12, "[2,2,2,2]", "12"); 191 insert into t3 values(13, "[3,3,3,3]", "13"); 192 alter table t3 alter reindex idx3 ivfflat lists=4; 193 select a, b from t3 order by l2_distance(b, "[1,0,0,0]") limit 4; 194 a b 195 1 [1, 0, 0, 0] 196 2 [2, 0, 0, 0] 197 3 [3, 0, 0, 0] 198 select a, b from t3 order by l2_distance(b, "[1,1,0,0]") limit 4; 199 a b 200 4 [1, 1, 0, 0] 201 5 [2, 2, 0, 0] 202 6 [3, 3, 0, 0] 203 select a, b from t3 order by l2_distance(b, "[1,1,1,0]") limit 4; 204 a b 205 7 [1, 1, 1, 0] 206 8 [2, 2, 2, 0] 207 9 [3, 3, 3, 0] 208 10 [4, 4, 4, 0] 209 select a, b from t3 order by l2_distance(b, "[1,1,1,1]") limit 4; 210 a b 211 11 [1, 1, 1, 1] 212 12 [2, 2, 2, 2] 213 13 [3, 3, 3, 3] 214 drop table if exists t4; 215 create table t4(a int primary key,b vecf32(4), c varchar(3) ); 216 insert into t4 values(1, "[1,0,0,0]" , "1"); 217 insert into t4 values(2, "[2,0,0,0]", "2"); 218 insert into t4 values(3, "[3,0,0,0]", "3"); 219 insert into t4 values(4, "[1,1,0,0]", "4"); 220 insert into t4 values(5, "[2,2,0,0]", "5"); 221 insert into t4 values(6, "[3,3,0,0]", "6"); 222 insert into t4 values(7, "[1,1,1,0]", "7"); 223 insert into t4 values(8, "[2,2,2,0]", "8"); 224 insert into t4 values(9, NULL, "9"); 225 select a, b from t4 order by l2_distance(normalize_l2(b), normalize_l2("[1,1,1,0]")) limit 3; 226 a b 227 9 null 228 7 [1, 1, 1, 0] 229 8 [2, 2, 2, 0] 230 create index idx4 using ivfflat on t4(b) lists=3 op_type "vector_l2_ops"; 231 select a, b from t4 order by l2_distance(b, "[1,1,1,0]") limit 4; 232 a b 233 7 [1, 1, 1, 0] 234 8 [2, 2, 2, 0] 235 insert into t4 values(10, "[4,4,4,0]", "10"); 236 select a, b from t4 order by l2_distance(b, "[1,1,1,0]") limit 4; 237 a b 238 7 [1, 1, 1, 0] 239 8 [2, 2, 2, 0] 240 10 [4, 4, 4, 0] 241 drop table if exists t5; 242 create table t5(a int primary key,b vecf32(4), c varchar(3) ); 243 insert into t5 values(1, "[1,0,0,0]" , "1"); 244 insert into t5 values(2, "[2,0,0,0]", "2"); 245 insert into t5 values(3, "[3,0,0,0]", "3"); 246 insert into t5 values(4, "[1,1,0,0]", "4"); 247 insert into t5 values(5, "[2,2,0,0]", "5"); 248 insert into t5 values(6, "[3,3,0,0]", "6"); 249 insert into t5 values(7, "[1,1,1,0]", "7"); 250 insert into t5 values(8, "[2,2,2,0]", "8"); 251 insert into t5 values(9, "[3,3,3,3]", "9"); 252 insert into t5 values(10, "[0,0,0,0]", "10"); 253 select a, b from t5 order by l2_distance(normalize_l2(b), normalize_l2("[1,1,1,0]")) limit 3; 254 a b 255 8 [2, 2, 2, 0] 256 7 [1, 1, 1, 0] 257 9 [3, 3, 3, 3] 258 create index idx5 using ivfflat on t5(b) lists=3 op_type "vector_l2_ops"; 259 select a, b from t5 order by l2_distance(b, "[1,1,1,0]") limit 7; 260 a b 261 7 [1, 1, 1, 0] 262 4 [1, 1, 0, 0] 263 5 [2, 2, 0, 0] 264 8 [2, 2, 2, 0] 265 6 [3, 3, 0, 0] 266 9 [3, 3, 3, 3] 267 insert into t5 values(11, "[4,4,4,0]", "11"); 268 select a, b from t5 order by l2_distance(b, "[1,1,1,0]") limit 7; 269 a b 270 7 [1, 1, 1, 0] 271 4 [1, 1, 0, 0] 272 5 [2, 2, 0, 0] 273 8 [2, 2, 2, 0] 274 6 [3, 3, 0, 0] 275 9 [3, 3, 3, 3] 276 11 [4, 4, 4, 0] 277 SET GLOBAL experimental_ivf_index = 0; 278 drop database vecdb3;