github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/rules/scan (about) 1 exec-ddl 2 CREATE TABLE a 3 ( 4 k INT PRIMARY KEY, 5 i INT, 6 f FLOAT, 7 s STRING, 8 j JSON, 9 INDEX s_idx (s) STORING (i, f), 10 INDEX si_idx (s DESC, i DESC) STORING (j), 11 INVERTED INDEX inv_idx_j (j) 12 ) 13 ---- 14 15 # -------------------------------------------------- 16 # GenerateIndexScans 17 # -------------------------------------------------- 18 19 # Revscan won't be used here because there is no index with f 20 # sorted by ASC, k DESC 21 opt 22 SELECT k,f FROM a ORDER BY f DESC, k ASC LIMIT 10 23 ---- 24 limit 25 ├── columns: k:1!null f:3 26 ├── internal-ordering: -3,+1 27 ├── cardinality: [0 - 10] 28 ├── key: (1) 29 ├── fd: (1)-->(3) 30 ├── ordering: -3,+1 31 ├── sort 32 │ ├── columns: k:1!null f:3 33 │ ├── key: (1) 34 │ ├── fd: (1)-->(3) 35 │ ├── ordering: -3,+1 36 │ ├── limit hint: 10.00 37 │ └── scan a@s_idx 38 │ ├── columns: k:1!null f:3 39 │ ├── key: (1) 40 │ └── fd: (1)-->(3) 41 └── 10 42 43 opt 44 SELECT k,f from a ORDER BY k DESC LIMIT 10 45 ---- 46 scan a,rev 47 ├── columns: k:1!null f:3 48 ├── limit: 10(rev) 49 ├── key: (1) 50 ├── fd: (1)-->(3) 51 └── ordering: -1 52 53 memo 54 SELECT k,f FROM a ORDER BY k DESC LIMIT 10 55 ---- 56 memo (optimized, ~3KB, required=[presentation: k:1,f:3] [ordering: -1]) 57 ├── G1: (limit G2 G3 ordering=-1) (scan a,rev,cols=(1,3),lim=10(rev)) 58 │ ├── [presentation: k:1,f:3] [ordering: -1] 59 │ │ ├── best: (scan a,rev,cols=(1,3),lim=10(rev)) 60 │ │ └── cost: 11.05 61 │ └── [] 62 │ ├── best: (scan a,rev,cols=(1,3),lim=10(rev)) 63 │ └── cost: 11.05 64 ├── G2: (scan a,cols=(1,3)) (scan a@s_idx,cols=(1,3)) 65 │ ├── [ordering: -1] [limit hint: 10.00] 66 │ │ ├── best: (scan a,rev,cols=(1,3)) 67 │ │ └── cost: 22.28 68 │ └── [] 69 │ ├── best: (scan a@s_idx,cols=(1,3)) 70 │ └── cost: 1060.02 71 └── G3: (const 10) 72 73 74 opt 75 SELECT s FROM a ORDER BY k DESC 76 ---- 77 scan a,rev 78 ├── columns: s:4 [hidden: k:1!null] 79 ├── key: (1) 80 ├── fd: (1)-->(4) 81 └── ordering: -1 82 83 opt 84 SELECT k FROM a ORDER BY k ASC 85 ---- 86 scan a 87 ├── columns: k:1!null 88 ├── key: (1) 89 └── ordering: +1 90 91 opt 92 SELECT k FROM a ORDER BY k DESC 93 ---- 94 scan a,rev 95 ├── columns: k:1!null 96 ├── key: (1) 97 └── ordering: -1 98 99 opt 100 SELECT s,i,k,j FROM a ORDER BY s DESC, i DESC, k ASC 101 ---- 102 scan a@si_idx 103 ├── columns: s:4 i:2 k:1!null j:5 104 ├── key: (1) 105 ├── fd: (1)-->(2,4,5) 106 └── ordering: -4,-2,+1 107 108 # Revscan node won't be used because ordering is 109 # only partial (reverse) match with existing indices 110 opt 111 SELECT s,i,k,j FROM a ORDER BY s DESC, i DESC, k DESC 112 ---- 113 sort (segmented) 114 ├── columns: s:4 i:2 k:1!null j:5 115 ├── key: (1) 116 ├── fd: (1)-->(2,4,5) 117 ├── ordering: -4,-2,-1 118 └── scan a@si_idx 119 ├── columns: k:1!null i:2 s:4 j:5 120 ├── key: (1) 121 ├── fd: (1)-->(2,4,5) 122 └── ordering: -4,-2 123 124 # Revscan node won't be used because ordering is 125 # only partial (reverse) match with existing indices 126 opt 127 SELECT s,i,k,j FROM a ORDER BY s DESC, i ASC, k DESC 128 ---- 129 sort (segmented) 130 ├── columns: s:4 i:2 k:1!null j:5 131 ├── key: (1) 132 ├── fd: (1)-->(2,4,5) 133 ├── ordering: -4,+2,-1 134 └── scan a@si_idx 135 ├── columns: k:1!null i:2 s:4 j:5 136 ├── key: (1) 137 ├── fd: (1)-->(2,4,5) 138 └── ordering: -4 139 140 opt 141 SELECT s,i,k,j FROM a ORDER BY s ASC, i ASC, k DESC 142 ---- 143 scan a@si_idx,rev 144 ├── columns: s:4 i:2 k:1!null j:5 145 ├── key: (1) 146 ├── fd: (1)-->(2,4,5) 147 └── ordering: +4,+2,-1 148 149 memo 150 SELECT k FROM a ORDER BY k ASC 151 ---- 152 memo (optimized, ~2KB, required=[presentation: k:1] [ordering: +1]) 153 └── G1: (scan a,cols=(1)) (scan a@s_idx,cols=(1)) (scan a@si_idx,cols=(1)) 154 ├── [presentation: k:1] [ordering: +1] 155 │ ├── best: (scan a,cols=(1)) 156 │ └── cost: 1060.02 157 └── [] 158 ├── best: (scan a@s_idx,cols=(1)) 159 └── cost: 1050.02 160 161 # Scan of secondary index is lowest cost. 162 opt 163 SELECT s, i, f FROM a ORDER BY s, k, i 164 ---- 165 scan a@s_idx 166 ├── columns: s:4 i:2 f:3 [hidden: k:1!null] 167 ├── key: (1) 168 ├── fd: (1)-->(2-4) 169 └── ordering: +4,+1 170 171 memo 172 SELECT s, i, f FROM a ORDER BY s, k, i 173 ---- 174 memo (optimized, ~2KB, required=[presentation: s:4,i:2,f:3] [ordering: +4,+1]) 175 └── G1: (scan a,cols=(1-4)) (scan a@s_idx,cols=(1-4)) 176 ├── [presentation: s:4,i:2,f:3] [ordering: +4,+1] 177 │ ├── best: (scan a@s_idx,cols=(1-4)) 178 │ └── cost: 1080.02 179 └── [] 180 ├── best: (scan a@s_idx,cols=(1-4)) 181 └── cost: 1080.02 182 183 # No index-join should be generated for a@si_idx, since it is not constrained. 184 exploretrace rule=GenerateIndexScans 185 SELECT s, i, f FROM a ORDER BY s, k, i 186 ---- 187 ---- 188 ================================================================================ 189 GenerateIndexScans 190 ================================================================================ 191 Source expression: 192 sort 193 ├── columns: s:4 i:2 f:3 [hidden: k:1!null] 194 ├── key: (1) 195 ├── fd: (1)-->(2-4) 196 ├── ordering: +4,+1 197 └── scan a 198 ├── columns: k:1!null i:2 f:3 s:4 199 ├── key: (1) 200 └── fd: (1)-->(2-4) 201 202 New expression 1 of 1: 203 scan a@s_idx 204 ├── columns: s:4 i:2 f:3 [hidden: k:1!null] 205 ├── key: (1) 206 ├── fd: (1)-->(2-4) 207 └── ordering: +4,+1 208 ---- 209 ---- 210 211 # -------------------------------------------------- 212 # GenerateConstrainedScans 213 # -------------------------------------------------- 214 215 # Constrain the a@si_idx so that an index join is generated. 216 exploretrace rule=GenerateConstrainedScans 217 SELECT s, i, f FROM a WHERE s='foo' ORDER BY s, k, i 218 ---- 219 ---- 220 ================================================================================ 221 GenerateConstrainedScans 222 ================================================================================ 223 Source expression: 224 select 225 ├── columns: s:4!null i:2 f:3 [hidden: k:1!null] 226 ├── key: (1) 227 ├── fd: ()-->(4), (1)-->(2,3) 228 ├── ordering: +1 opt(4) [actual: +1] 229 ├── scan a@s_idx 230 │ ├── columns: k:1!null i:2 f:3 s:4 231 │ ├── key: (1) 232 │ ├── fd: (1)-->(2-4) 233 │ └── ordering: +1 opt(4) [actual: +4,+1] 234 └── filters 235 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 236 237 New expression 1 of 2: 238 scan a@s_idx 239 ├── columns: s:4!null i:2 f:3 [hidden: k:1!null] 240 ├── constraint: /4/1: [/'foo' - /'foo'] 241 ├── key: (1) 242 ├── fd: ()-->(4), (1)-->(2,3) 243 └── ordering: +1 opt(4) [actual: +1] 244 245 New expression 2 of 2: 246 sort 247 ├── columns: s:4!null i:2 f:3 [hidden: k:1!null] 248 ├── key: (1) 249 ├── fd: ()-->(4), (1)-->(2,3) 250 ├── ordering: +1 opt(4) [actual: +1] 251 └── index-join a 252 ├── columns: k:1!null i:2 f:3 s:4!null 253 ├── key: (1) 254 ├── fd: ()-->(4), (1)-->(2,3) 255 └── scan a@si_idx 256 ├── columns: k:1!null i:2 s:4!null 257 ├── constraint: /-4/-2/1: [/'foo' - /'foo'] 258 ├── key: (1) 259 └── fd: ()-->(4), (1)-->(2) 260 ---- 261 ---- 262 263 memo 264 SELECT s, i, f FROM a ORDER BY f 265 ---- 266 memo (optimized, ~2KB, required=[presentation: s:4,i:2,f:3] [ordering: +3]) 267 └── G1: (scan a,cols=(2-4)) (scan a@s_idx,cols=(2-4)) 268 ├── [presentation: s:4,i:2,f:3] [ordering: +3] 269 │ ├── best: (sort G1) 270 │ └── cost: 1289.35 271 └── [] 272 ├── best: (scan a@s_idx,cols=(2-4)) 273 └── cost: 1070.02 274 275 memo 276 SELECT s, i, f FROM a ORDER BY s DESC, i 277 ---- 278 memo (optimized, ~2KB, required=[presentation: s:4,i:2,f:3] [ordering: -4,+2]) 279 └── G1: (scan a,cols=(2-4)) (scan a@s_idx,cols=(2-4)) 280 ├── [presentation: s:4,i:2,f:3] [ordering: -4,+2] 281 │ ├── best: (sort G1="[ordering: -4]") 282 │ └── cost: 1256.33 283 ├── [ordering: -4] 284 │ ├── best: (scan a@s_idx,rev,cols=(2-4)) 285 │ └── cost: 1169.68 286 └── [] 287 ├── best: (scan a@s_idx,cols=(2-4)) 288 └── cost: 1070.02 289 290 memo 291 SELECT s, i, f FROM a WHERE s='foo' ORDER BY s DESC, i 292 ---- 293 memo (optimized, ~6KB, required=[presentation: s:4,i:2,f:3] [ordering: +2 opt(4)]) 294 ├── G1: (select G2 G3) (scan a@s_idx,cols=(2-4),constrained) (index-join G4 a,cols=(2-4)) 295 │ ├── [presentation: s:4,i:2,f:3] [ordering: +2 opt(4)] 296 │ │ ├── best: (sort G1) 297 │ │ └── cost: 11.58 298 │ └── [] 299 │ ├── best: (scan a@s_idx,cols=(2-4),constrained) 300 │ └── cost: 10.71 301 ├── G2: (scan a,cols=(2-4)) (scan a@s_idx,cols=(2-4)) 302 │ ├── [ordering: +2 opt(4)] 303 │ │ ├── best: (sort G2) 304 │ │ └── cost: 1289.35 305 │ └── [] 306 │ ├── best: (scan a@s_idx,cols=(2-4)) 307 │ └── cost: 1070.02 308 ├── G3: (filters G5) 309 ├── G4: (scan a@si_idx,cols=(1,2,4),constrained) 310 │ ├── [ordering: +2 opt(4)] 311 │ │ ├── best: (scan a@si_idx,rev,cols=(1,2,4),constrained) 312 │ │ └── cost: 11.04 313 │ └── [] 314 │ ├── best: (scan a@si_idx,cols=(1,2,4),constrained) 315 │ └── cost: 10.71 316 ├── G5: (eq G6 G7) 317 ├── G6: (variable s) 318 └── G7: (const 'foo') 319 320 # Force an index in order to ensure that an index join is created. 321 opt 322 SELECT * FROM a@si_idx 323 ---- 324 index-join a 325 ├── columns: k:1!null i:2 f:3 s:4 j:5 326 ├── key: (1) 327 ├── fd: (1)-->(2-5) 328 └── scan a@si_idx 329 ├── columns: k:1!null i:2 s:4 j:5 330 ├── flags: force-index=si_idx 331 ├── key: (1) 332 └── fd: (1)-->(2,4,5) 333 334 exec-ddl 335 CREATE TABLE abc ( 336 a INT, 337 b INT, 338 c INT, 339 d CHAR, 340 PRIMARY KEY (a, b, c), 341 UNIQUE INDEX bc (b, c), 342 INDEX ba (b, a), 343 FAMILY (a, b, c), 344 FAMILY (d) 345 ) 346 ---- 347 348 memo 349 SELECT d FROM abc ORDER BY lower(d) 350 ---- 351 memo (optimized, ~3KB, required=[presentation: d:4] [ordering: +5]) 352 ├── G1: (project G2 G3 d) 353 │ ├── [presentation: d:4] [ordering: +5] 354 │ │ ├── best: (sort G1) 355 │ │ └── cost: 1289.36 356 │ └── [] 357 │ ├── best: (project G2 G3 d) 358 │ └── cost: 1070.03 359 ├── G2: (scan abc,cols=(4)) 360 │ └── [] 361 │ ├── best: (scan abc,cols=(4)) 362 │ └── cost: 1050.02 363 ├── G3: (projections G4) 364 ├── G4: (function G5 lower) 365 ├── G5: (scalar-list G6) 366 └── G6: (variable d) 367 368 memo 369 SELECT j FROM a WHERE s = 'foo' 370 ---- 371 memo (optimized, ~7KB, required=[presentation: j:5]) 372 ├── G1: (project G2 G3 j) 373 │ └── [presentation: j:5] 374 │ ├── best: (project G2 G3 j) 375 │ └── cost: 10.72 376 ├── G2: (select G4 G5) (index-join G6 a,cols=(4,5)) (scan a@si_idx,cols=(4,5),constrained) 377 │ └── [] 378 │ ├── best: (scan a@si_idx,cols=(4,5),constrained) 379 │ └── cost: 10.61 380 ├── G3: (projections) 381 ├── G4: (scan a,cols=(4,5)) (scan a@si_idx,cols=(4,5)) 382 │ └── [] 383 │ ├── best: (scan a@si_idx,cols=(4,5)) 384 │ └── cost: 1060.02 385 ├── G5: (filters G7) 386 ├── G6: (scan a@s_idx,cols=(1,4),constrained) 387 │ └── [] 388 │ ├── best: (scan a@s_idx,cols=(1,4),constrained) 389 │ └── cost: 10.61 390 ├── G7: (eq G8 G9) 391 ├── G8: (variable s) 392 └── G9: (const 'foo') 393 394 # Scan of primary index is lowest cost. 395 opt 396 SELECT s, i, f FROM a ORDER BY k, i, s 397 ---- 398 scan a 399 ├── columns: s:4 i:2 f:3 [hidden: k:1!null] 400 ├── key: (1) 401 ├── fd: (1)-->(2-4) 402 └── ordering: +1 403 404 memo 405 SELECT s, i, f FROM a ORDER BY k, i, s 406 ---- 407 memo (optimized, ~2KB, required=[presentation: s:4,i:2,f:3] [ordering: +1]) 408 └── G1: (scan a,cols=(1-4)) (scan a@s_idx,cols=(1-4)) 409 ├── [presentation: s:4,i:2,f:3] [ordering: +1] 410 │ ├── best: (scan a,cols=(1-4)) 411 │ └── cost: 1090.02 412 └── [] 413 ├── best: (scan a@s_idx,cols=(1-4)) 414 └── cost: 1080.02 415 416 # Secondary index has right order 417 opt 418 SELECT s, j FROM a ORDER BY s 419 ---- 420 scan a@si_idx,rev 421 ├── columns: s:4 j:5 422 └── ordering: +4 423 424 memo 425 SELECT s, j FROM a ORDER BY s 426 ---- 427 memo (optimized, ~2KB, required=[presentation: s:4,j:5] [ordering: +4]) 428 └── G1: (scan a,cols=(4,5)) (scan a@si_idx,cols=(4,5)) 429 ├── [presentation: s:4,j:5] [ordering: +4] 430 │ ├── best: (scan a@si_idx,rev,cols=(4,5)) 431 │ └── cost: 1159.68 432 └── [] 433 ├── best: (scan a@si_idx,cols=(4,5)) 434 └── cost: 1060.02 435 436 # Consider three different indexes, and pick index with multiple keys. 437 opt 438 SELECT i, k FROM a ORDER BY s DESC, i, k 439 ---- 440 sort (segmented) 441 ├── columns: i:2 k:1!null [hidden: s:4] 442 ├── key: (1) 443 ├── fd: (1)-->(2,4) 444 ├── ordering: -4,+2,+1 445 └── scan a@si_idx 446 ├── columns: k:1!null i:2 s:4 447 ├── key: (1) 448 ├── fd: (1)-->(2,4) 449 └── ordering: -4 450 451 memo 452 SELECT i, k FROM a ORDER BY s DESC, i, k 453 ---- 454 memo (optimized, ~2KB, required=[presentation: i:2,k:1] [ordering: -4,+2,+1]) 455 └── G1: (scan a,cols=(1,2,4)) (scan a@s_idx,cols=(1,2,4)) (scan a@si_idx,cols=(1,2,4)) 456 ├── [presentation: i:2,k:1] [ordering: -4,+2,+1] 457 │ ├── best: (sort G1="[ordering: -4]") 458 │ └── cost: 1161.00 459 ├── [ordering: -4] 460 │ ├── best: (scan a@si_idx,cols=(1,2,4)) 461 │ └── cost: 1070.02 462 └── [] 463 ├── best: (scan a@s_idx,cols=(1,2,4)) 464 └── cost: 1070.02 465 466 memo 467 SELECT i, k FROM a WHERE s >= 'foo' 468 ---- 469 memo (optimized, ~6KB, required=[presentation: i:2,k:1]) 470 ├── G1: (project G2 G3 k i) 471 │ └── [presentation: i:2,k:1] 472 │ ├── best: (project G2 G3 k i) 473 │ └── cost: 360.02 474 ├── G2: (select G4 G5) (scan a@s_idx,cols=(1,2,4),constrained) (scan a@si_idx,cols=(1,2,4),constrained) 475 │ └── [] 476 │ ├── best: (scan a@s_idx,cols=(1,2,4),constrained) 477 │ └── cost: 356.68 478 ├── G3: (projections) 479 ├── G4: (scan a,cols=(1,2,4)) (scan a@s_idx,cols=(1,2,4)) (scan a@si_idx,cols=(1,2,4)) 480 │ └── [] 481 │ ├── best: (scan a@s_idx,cols=(1,2,4)) 482 │ └── cost: 1070.02 483 ├── G5: (filters G6) 484 ├── G6: (ge G7 G8) 485 ├── G7: (variable s) 486 └── G8: (const 'foo') 487 488 # GenerateIndexScans propagates row-level locking information. 489 opt 490 SELECT s, i, f FROM a ORDER BY s FOR UPDATE 491 ---- 492 scan a@s_idx 493 ├── columns: s:4 i:2 f:3 494 ├── locking: for-update 495 ├── volatile, side-effects 496 └── ordering: +4 497 498 # Collated strings are treated properly. 499 exec-ddl 500 CREATE TABLE x (s STRING COLLATE en_u_ks_level1 PRIMARY KEY) 501 ---- 502 503 opt 504 SELECT s FROM x WHERE s < 'hello' COLLATE en_u_ks_level1 505 ---- 506 scan x 507 ├── columns: s:1!null 508 ├── constraint: /1: [ - /'hello' COLLATE en_u_ks_level1) 509 └── key: (1) 510 511 opt 512 SELECT s FROM x WHERE s = 'hello' COLLATE en_u_ks_level1 513 ---- 514 scan x 515 ├── columns: s:1!null 516 ├── constraint: /1: [/'hello' COLLATE en_u_ks_level1 - /'hello' COLLATE en_u_ks_level1] 517 ├── cardinality: [0 - 1] 518 ├── key: () 519 └── fd: ()-->(1) 520 521 # Can't generate spans for other collations. 522 opt 523 SELECT s FROM x WHERE s COLLATE en = 'hello' COLLATE en 524 ---- 525 select 526 ├── columns: s:1!null 527 ├── key: (1) 528 ├── scan x 529 │ ├── columns: s:1!null 530 │ └── key: (1) 531 └── filters 532 └── s:1 COLLATE en = 'hello' COLLATE en [outer=(1)] 533 534 # Realistic example where using constraints as filters help. 535 # An even more realistic exmple would have a creation timestamp instead of a 536 # seq_num integer, but that makes the plans much more cluttered. 537 exec-ddl 538 CREATE TABLE "orders" ( 539 region STRING NOT NULL, 540 id INT NOT NULL, 541 total DECIMAL NOT NULL, 542 seq_num INT NOT NULL, 543 PRIMARY KEY (region, id), 544 UNIQUE INDEX orders_by_seq_num (region, seq_num, id) STORING (total), 545 CHECK (region IN ('us-east1', 'us-west1', 'europe-west2')) 546 ) 547 ---- 548 549 exec-ddl 550 ALTER TABLE "orders" INJECT STATISTICS '[ 551 { 552 "columns": ["region"], 553 "distinct_count": 3, 554 "null_count": 0, 555 "row_count": 100, 556 "created_at": "2018-01-01 1:00:00.00000+00:00" 557 }, 558 { 559 "columns": ["id"], 560 "distinct_count": 100, 561 "null_count": 0, 562 "row_count": 100, 563 "created_at": "2018-01-01 1:00:00.00000+00:00" 564 }, 565 { 566 "columns": ["total"], 567 "distinct_count": 100, 568 "null_count": 0, 569 "row_count": 100, 570 "created_at": "2018-01-01 1:00:00.00000+00:00" 571 }, 572 { 573 "columns": ["seq_num"], 574 "distinct_count": 50, 575 "null_count": 0, 576 "row_count": 100, 577 "created_at": "2018-01-01 1:00:00.00000+00:00" 578 } 579 ]' 580 ---- 581 582 opt 583 SELECT sum(total) FROM "orders" WHERE seq_num >= 100 AND seq_num < 200 584 ---- 585 scalar-group-by 586 ├── columns: sum:5 587 ├── cardinality: [1 - 1] 588 ├── key: () 589 ├── fd: ()-->(5) 590 ├── scan orders@orders_by_seq_num 591 │ ├── columns: total:3!null seq_num:4!null 592 │ └── constraint: /1/4/2 593 │ ├── [/'europe-west2'/100 - /'europe-west2'/199] 594 │ ├── [/'us-east1'/100 - /'us-east1'/199] 595 │ └── [/'us-west1'/100 - /'us-west1'/199] 596 └── aggregations 597 └── sum [as=sum:5, outer=(3)] 598 └── total:3 599 600 exec-ddl 601 CREATE TABLE xyz ( 602 x INT PRIMARY KEY, 603 y INT NOT NULL, 604 z STRING NOT NULL, 605 CHECK (x < 10 AND x > 1), 606 CHECK (y < 10 AND y > 1), 607 CHECK (z in ('first', 'second')), 608 INDEX secondary (y, x), 609 INDEX tertiary (z, y, x)) 610 ---- 611 612 opt 613 SELECT x, y FROM xyz WHERE x > 5 614 ---- 615 select 616 ├── columns: x:1!null y:2!null 617 ├── key: (1) 618 ├── fd: (1)-->(2) 619 ├── scan xyz@tertiary 620 │ ├── columns: x:1!null y:2!null 621 │ ├── constraint: /3/2/1 622 │ │ ├── [/'first'/2/6 - /'first'/9/9] 623 │ │ └── [/'second'/2/6 - /'second'/9/9] 624 │ ├── key: (1) 625 │ └── fd: (1)-->(2) 626 └── filters 627 └── x:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)] 628 629 # TODO(ridwanmsharif): Confirm if this makes sense. I would've expected that the primary index 630 # would be used here. But it isn't the plan being picked. Curious. 631 opt 632 SELECT * FROM xyz WHERE x > 5 633 ---- 634 select 635 ├── columns: x:1!null y:2!null z:3!null 636 ├── key: (1) 637 ├── fd: (1)-->(2,3) 638 ├── scan xyz@tertiary 639 │ ├── columns: x:1!null y:2!null z:3!null 640 │ ├── constraint: /3/2/1 641 │ │ ├── [/'first'/2/6 - /'first'/9/9] 642 │ │ └── [/'second'/2/6 - /'second'/9/9] 643 │ ├── key: (1) 644 │ └── fd: (1)-->(2,3) 645 └── filters 646 └── x:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)] 647 648 # Check constraint used only for the non nullable column. Constraints on x are ignored. 649 exec-ddl 650 CREATE TABLE xy ( 651 x INT, 652 y INT NOT NULL, 653 CHECK (x < 10 AND x > 1), 654 CHECK (y < 10 AND y > 1), 655 INDEX secondary (y, x)) 656 ---- 657 658 opt 659 SELECT x, y FROM xy WHERE x > 5 660 ---- 661 select 662 ├── columns: x:1!null y:2!null 663 ├── scan xy@secondary 664 │ ├── columns: x:1 y:2!null 665 │ └── constraint: /2/1/3: [/2/6 - /9] 666 └── filters 667 └── x:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)] 668 669 # Check constraints that can evaluate to NULL are ignored. 670 exec-ddl 671 CREATE TABLE null_constraint ( 672 y INT NOT NULL, 673 CHECK (y IN (1, 2, NULL)), 674 INDEX index_1 (y)) 675 ---- 676 677 opt 678 SELECT y FROM null_constraint WHERE y > 0 679 ---- 680 scan null_constraint@index_1 681 ├── columns: y:1!null 682 └── constraint: /1/2: [/1 - ] 683 684 exec-ddl 685 CREATE TABLE null_constraint_2 ( 686 y INT NOT NULL, 687 CHECK ((y IN (1, 2, NULL)) AND (y > 10)), 688 CHECK (y < 15), 689 INDEX index_1 (y)) 690 ---- 691 692 opt 693 SELECT y FROM null_constraint_2 WHERE y > 0 694 ---- 695 scan null_constraint_2@index_1 696 ├── columns: y:1!null 697 └── constraint: /1/2: [/1 - /14] 698 699 # Unvalidated constraints are ignored. 700 exec-ddl 701 CREATE TABLE check_constraint_validity ( 702 a int NOT NULL, 703 INDEX secondary (a), 704 CONSTRAINT "check:unvalidated" CHECK (a < 10), 705 CONSTRAINT "check:validated" CHECK (a < 20)) 706 ---- 707 708 opt 709 SELECT * FROM check_constraint_validity WHERE a > 6 710 ---- 711 scan check_constraint_validity@secondary 712 ├── columns: a:1!null 713 └── constraint: /1/2: [/7 - /19]