github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/select_index_flags (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE abcd ( 5 a INT PRIMARY KEY, 6 b INT, 7 c INT, 8 d INT, 9 INDEX b (b), 10 INDEX cd (c,d), 11 UNIQUE INDEX bcd (b,c,d) 12 ) 13 14 # No hint 15 query TTT 16 EXPLAIN SELECT * FROM abcd WHERE a >= 20 AND a <= 30 17 ---- 18 · distributed false 19 · vectorized true 20 scan · · 21 · table abcd@primary 22 · spans /20-/30/# 23 · parallel · 24 25 # No hint, reverse scan. 26 query TTT 27 EXPLAIN SELECT * FROM abcd WHERE a >= 20 AND a <= 30 ORDER BY a DESC 28 ---- 29 · distributed false 30 · vectorized true 31 revscan · · 32 · table abcd@primary 33 · spans /20-/30/# 34 · parallel · 35 36 # Force primary 37 query TTT 38 EXPLAIN SELECT * FROM abcd@primary WHERE a >= 20 AND a <= 30 39 ---- 40 · distributed false 41 · vectorized true 42 scan · · 43 · table abcd@primary 44 · spans /20-/30/# 45 · parallel · 46 47 # Force primary, reverse scan. 48 query TTT 49 EXPLAIN SELECT * FROM abcd@{FORCE_INDEX=primary,DESC} WHERE a >= 20 AND a <= 30 50 ---- 51 · distributed false 52 · vectorized true 53 revscan · · 54 · table abcd@primary 55 · spans /20-/30/# 56 · parallel · 57 58 # Force primary, allow reverse scan. 59 query TTT 60 EXPLAIN SELECT * FROM abcd@primary WHERE a >= 20 AND a <= 30 ORDER BY a DESC 61 ---- 62 · distributed false 63 · vectorized true 64 revscan · · 65 · table abcd@primary 66 · spans /20-/30/# 67 · parallel · 68 69 # Force primary, forward scan. 70 query TTT 71 EXPLAIN SELECT * FROM abcd@{FORCE_INDEX=primary,ASC} WHERE a >= 20 AND a <= 30 ORDER BY a DESC 72 ---- 73 · distributed false 74 · vectorized true 75 sort · · 76 │ order -a 77 └── scan · · 78 · table abcd@primary 79 · spans /20-/30/# 80 · parallel · 81 82 # Force index b 83 query TTT 84 EXPLAIN SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30 85 ---- 86 · distributed false 87 · vectorized true 88 filter · · 89 │ filter (a >= 20) AND (a <= 30) 90 └── index-join · · 91 │ table abcd@primary 92 │ key columns a 93 └── scan · · 94 · table abcd@b 95 · spans FULL SCAN 96 97 # Force index b, reverse scan. 98 query TTT 99 EXPLAIN SELECT * FROM abcd@{FORCE_INDEX=b,DESC} WHERE a >= 20 AND a <= 30 100 ---- 101 · distributed false 102 · vectorized true 103 filter · · 104 │ filter (a >= 20) AND (a <= 30) 105 └── index-join · · 106 │ table abcd@primary 107 │ key columns a 108 └── revscan · · 109 · table abcd@b 110 · spans FULL SCAN 111 112 # Force index b, allowing reverse scan. 113 query TTT 114 EXPLAIN SELECT * FROM abcd@b ORDER BY b DESC LIMIT 5 115 ---- 116 · distributed false 117 · vectorized true 118 index-join · · 119 │ table abcd@primary 120 │ key columns a 121 └── revscan · · 122 · table abcd@b 123 · spans LIMITED SCAN 124 · limit 5 125 126 # Force index b, reverse scan. 127 query TTT 128 EXPLAIN SELECT * FROM abcd@{FORCE_INDEX=b,DESC} ORDER BY b DESC LIMIT 5 129 ---- 130 · distributed false 131 · vectorized true 132 index-join · · 133 │ table abcd@primary 134 │ key columns a 135 └── revscan · · 136 · table abcd@b 137 · spans LIMITED SCAN 138 · limit 5 139 140 141 # Force index b, forward scan. 142 query TTT 143 EXPLAIN SELECT * FROM abcd@{FORCE_INDEX=b,ASC} ORDER BY b DESC LIMIT 5 144 ---- 145 · distributed false 146 · vectorized true 147 limit · · 148 │ count 5 149 └── sort · · 150 │ order -b 151 └── index-join · · 152 │ table abcd@primary 153 │ key columns a 154 └── scan · · 155 · table abcd@b 156 · spans FULL SCAN 157 158 # Force index cd 159 query TTT 160 EXPLAIN SELECT * FROM abcd@cd WHERE a >= 20 AND a <= 30 161 ---- 162 · distributed false 163 · vectorized true 164 filter · · 165 │ filter (a >= 20) AND (a <= 30) 166 └── index-join · · 167 │ table abcd@primary 168 │ key columns a 169 └── scan · · 170 · table abcd@cd 171 · spans FULL SCAN 172 173 # Force index bcd 174 query TTT 175 EXPLAIN SELECT * FROM abcd@bcd WHERE a >= 20 AND a <= 30 176 ---- 177 · distributed false 178 · vectorized true 179 scan · · 180 · table abcd@bcd 181 · spans FULL SCAN 182 · filter (a >= 20) AND (a <= 30) 183 184 # Force index b (covering) 185 query TTT 186 EXPLAIN SELECT b FROM abcd@b WHERE a >= 20 AND a <= 30 187 ---- 188 · distributed false 189 · vectorized true 190 render · · 191 └── scan · · 192 · table abcd@b 193 · spans FULL SCAN 194 · filter (a >= 20) AND (a <= 30) 195 196 # Force index b (non-covering due to WHERE clause) 197 query TTT 198 EXPLAIN SELECT b FROM abcd@b WHERE c >= 20 AND c <= 30 199 ---- 200 · distributed false 201 · vectorized true 202 render · · 203 └── filter · · 204 │ filter (c >= 20) AND (c <= 30) 205 └── index-join · · 206 │ table abcd@primary 207 │ key columns a 208 └── scan · · 209 · table abcd@b 210 · spans FULL SCAN 211 212 # No hint, should be using index cd 213 query TTT 214 EXPLAIN SELECT c, d FROM abcd WHERE c >= 20 AND c < 40 215 ---- 216 · distributed false 217 · vectorized true 218 scan · · 219 · table abcd@cd 220 · spans /20-/40 221 222 # Force primary index 223 query TTT 224 EXPLAIN SELECT c, d FROM abcd@primary WHERE c >= 20 AND c < 40 225 ---- 226 · distributed false 227 · vectorized true 228 scan · · 229 · table abcd@primary 230 · spans FULL SCAN 231 · filter (c >= 20) AND (c < 40) 232 233 # Force index b 234 query TTT 235 EXPLAIN SELECT c, d FROM abcd@b WHERE c >= 20 AND c < 40 236 ---- 237 · distributed false 238 · vectorized true 239 filter · · 240 │ filter (c >= 20) AND (c < 40) 241 └── index-join · · 242 │ table abcd@primary 243 │ key columns a 244 └── scan · · 245 · table abcd@b 246 · spans FULL SCAN 247 248 query TTT 249 EXPLAIN SELECT * FROM abcd@{FORCE_INDEX=b} WHERE a >= 20 AND a <= 30 250 ---- 251 · distributed false 252 · vectorized true 253 filter · · 254 │ filter (a >= 20) AND (a <= 30) 255 └── index-join · · 256 │ table abcd@primary 257 │ key columns a 258 └── scan · · 259 · table abcd@b 260 · spans FULL SCAN 261 262 query TTT 263 EXPLAIN SELECT b, c, d FROM abcd WHERE c = 10 264 ---- 265 · distributed false 266 · vectorized true 267 index-join · · 268 │ table abcd@primary 269 │ key columns a 270 └── scan · · 271 · table abcd@cd 272 · spans /10-/11 273 274 query TTT 275 EXPLAIN SELECT b, c, d FROM abcd@{NO_INDEX_JOIN} WHERE c = 10 276 ---- 277 · distributed false 278 · vectorized true 279 scan · · 280 · table abcd@primary 281 · spans FULL SCAN 282 · filter c = 10 283 284 query TTT 285 EXPLAIN SELECT b, c, d FROM abcd@{FORCE_INDEX=bcd} WHERE c = 10 286 ---- 287 · distributed false 288 · vectorized true 289 scan · · 290 · table abcd@bcd 291 · spans FULL SCAN 292 · filter c = 10 293 294 query TTT 295 EXPLAIN SELECT b, c, d FROM abcd@{FORCE_INDEX=primary} WHERE c = 10 296 ---- 297 · distributed false 298 · vectorized true 299 scan · · 300 · table abcd@primary 301 · spans FULL SCAN 302 · filter c = 10