github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/rules/limit (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) STORING (j) 11 ) 12 ---- 13 14 # --------------------------------------------------- 15 # GenerateLimitedScans / PushLimitIntoConstrainedScan 16 # --------------------------------------------------- 17 18 opt 19 SELECT * FROM a LIMIT 1 20 ---- 21 scan a 22 ├── columns: k:1!null i:2 f:3 s:4 j:5 23 ├── limit: 1 24 ├── key: () 25 └── fd: ()-->(1-5) 26 27 # Combine limit with needed columns. 28 opt 29 SELECT s FROM a LIMIT 1 30 ---- 31 scan a@s_idx 32 ├── columns: s:4 33 ├── limit: 1 34 ├── key: () 35 └── fd: ()-->(4) 36 37 # Combine limit with constraint. 38 opt 39 SELECT s FROM a WHERE s='foo' LIMIT 1 40 ---- 41 scan a@s_idx 42 ├── columns: s:4!null 43 ├── constraint: /4/1: [/'foo' - /'foo'] 44 ├── limit: 1 45 ├── key: () 46 └── fd: ()-->(4) 47 48 # Limit of a limit. 49 opt 50 SELECT s FROM (SELECT s, i FROM a ORDER BY s LIMIT 10) a ORDER BY s, i LIMIT 1 51 ---- 52 limit 53 ├── columns: s:4 [hidden: i:2] 54 ├── internal-ordering: +4,+2 55 ├── cardinality: [0 - 1] 56 ├── key: () 57 ├── fd: ()-->(2,4) 58 ├── sort (segmented) 59 │ ├── columns: i:2 s:4 60 │ ├── cardinality: [0 - 10] 61 │ ├── ordering: +4,+2 62 │ ├── limit hint: 1.00 63 │ └── scan a@s_idx 64 │ ├── columns: i:2 s:4 65 │ ├── limit: 10 66 │ └── ordering: +4 67 └── 1 68 69 # Don't push when scan doesn't satisfy limit's ordering. 70 opt 71 SELECT s FROM a ORDER BY f LIMIT 1 72 ---- 73 limit 74 ├── columns: s:4 [hidden: f:3] 75 ├── internal-ordering: +3 76 ├── cardinality: [0 - 1] 77 ├── key: () 78 ├── fd: ()-->(3,4) 79 ├── sort 80 │ ├── columns: f:3 s:4 81 │ ├── ordering: +3 82 │ ├── limit hint: 1.00 83 │ └── scan a@s_idx 84 │ └── columns: f:3 s:4 85 └── 1 86 87 # Don't push when limit is not a constant. 88 opt 89 SELECT s FROM a LIMIT (SELECT k FROM a LIMIT 1) 90 ---- 91 limit 92 ├── columns: s:4 93 ├── immutable, side-effects 94 ├── scan a@s_idx 95 │ └── columns: s:4 96 └── subquery 97 └── scan a@s_idx 98 ├── columns: k:6!null 99 ├── limit: 1 100 ├── key: () 101 └── fd: ()-->(6) 102 103 memo 104 SELECT s FROM a WHERE s='foo' LIMIT 1 105 ---- 106 memo (optimized, ~7KB, required=[presentation: s:4]) 107 ├── G1: (limit G2 G3) (scan a@s_idx,cols=(4),constrained,lim=1) (scan a@si_idx,cols=(4),constrained,lim=1) 108 │ └── [presentation: s:4] 109 │ ├── best: (scan a@s_idx,cols=(4),constrained,lim=1) 110 │ └── cost: 1.06 111 ├── G2: (select G4 G5) (scan a@s_idx,cols=(4),constrained) (scan a@si_idx,cols=(4),constrained) 112 │ └── [limit hint: 1.00] 113 │ ├── best: (scan a@s_idx,cols=(4),constrained) 114 │ └── cost: 2.11 115 ├── G3: (const 1) 116 ├── G4: (scan a,cols=(4)) (scan a@s_idx,cols=(4)) (scan a@si_idx,cols=(4)) 117 │ └── [limit hint: 100.00] 118 │ ├── best: (scan a@s_idx,cols=(4)) 119 │ └── cost: 210.02 120 ├── G5: (filters G6) 121 ├── G6: (eq G7 G8) 122 ├── G7: (variable s) 123 └── G8: (const 'foo') 124 125 # GenerateLimitedScans propagates row-level locking information. 126 opt 127 SELECT * FROM a LIMIT 1 FOR UPDATE 128 ---- 129 scan a 130 ├── columns: k:1!null i:2 f:3 s:4 j:5 131 ├── limit: 1 132 ├── locking: for-update 133 ├── volatile, side-effects 134 ├── key: () 135 └── fd: ()-->(1-5) 136 137 # PushLimitIntoConstrainedScan propagates row-level locking information. 138 opt 139 SELECT s FROM a WHERE s='foo' LIMIT 1 FOR UPDATE 140 ---- 141 scan a@s_idx 142 ├── columns: s:4!null 143 ├── constraint: /4/1: [/'foo' - /'foo'] 144 ├── limit: 1 145 ├── locking: for-update 146 ├── volatile, side-effects 147 ├── key: () 148 └── fd: ()-->(4) 149 150 # -------------------------------------------------- 151 # PushLimitIntoIndexJoin 152 # -------------------------------------------------- 153 154 exec-ddl 155 CREATE TABLE kuv (k INT PRIMARY KEY, u INT, v INT, INDEX (u)) 156 ---- 157 158 opt 159 SELECT * FROM kuv ORDER BY u LIMIT 5 160 ---- 161 index-join kuv 162 ├── columns: k:1!null u:2 v:3 163 ├── cardinality: [0 - 5] 164 ├── key: (1) 165 ├── fd: (1)-->(2,3) 166 ├── ordering: +2 167 └── scan kuv@secondary 168 ├── columns: k:1!null u:2 169 ├── limit: 5 170 ├── key: (1) 171 ├── fd: (1)-->(2) 172 └── ordering: +2 173 174 # Verify we don't push the limit if the ordering depends on a column not in the 175 # input index. 176 opt 177 SELECT * FROM kuv WHERE u > 1 AND u < 10 ORDER BY u, v LIMIT 5 178 ---- 179 limit 180 ├── columns: k:1!null u:2!null v:3 181 ├── internal-ordering: +2,+3 182 ├── cardinality: [0 - 5] 183 ├── key: (1) 184 ├── fd: (1)-->(2,3) 185 ├── ordering: +2,+3 186 ├── sort (segmented) 187 │ ├── columns: k:1!null u:2!null v:3 188 │ ├── key: (1) 189 │ ├── fd: (1)-->(2,3) 190 │ ├── ordering: +2,+3 191 │ ├── limit hint: 5.00 192 │ └── index-join kuv 193 │ ├── columns: k:1!null u:2!null v:3 194 │ ├── key: (1) 195 │ ├── fd: (1)-->(2,3) 196 │ ├── ordering: +2 197 │ └── scan kuv@secondary 198 │ ├── columns: k:1!null u:2!null 199 │ ├── constraint: /2/1: [/2 - /9] 200 │ ├── key: (1) 201 │ ├── fd: (1)-->(2) 202 │ └── ordering: +2 203 └── 5 204 205 exec-ddl 206 CREATE TABLE abcd ( 207 a INT PRIMARY KEY, 208 b INT, 209 c INT, 210 d INT, 211 INDEX b (b), 212 INDEX cd (c,d), 213 UNIQUE INDEX bcd (b,c,d) 214 ) 215 ---- 216 217 opt 218 EXPLAIN SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30 ORDER BY b DESC LIMIT 5 219 ---- 220 explain 221 ├── columns: tree:5 field:6 description:7 222 └── limit 223 ├── columns: a:1!null b:2 c:3 d:4 224 ├── internal-ordering: -2 225 ├── cardinality: [0 - 5] 226 ├── key: (1) 227 ├── fd: (1)-->(2-4), (2-4)~~>(1) 228 ├── ordering: -2 229 ├── select 230 │ ├── columns: a:1!null b:2 c:3 d:4 231 │ ├── cardinality: [0 - 11] 232 │ ├── key: (1) 233 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 234 │ ├── ordering: -2 235 │ ├── limit hint: 5.00 236 │ ├── index-join abcd 237 │ │ ├── columns: a:1!null b:2 c:3 d:4 238 │ │ ├── key: (1) 239 │ │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 240 │ │ ├── ordering: -2 241 │ │ ├── limit hint: 454.55 242 │ │ └── scan abcd@b,rev 243 │ │ ├── columns: a:1!null b:2 244 │ │ ├── flags: force-index=b 245 │ │ ├── key: (1) 246 │ │ ├── fd: (1)-->(2) 247 │ │ ├── ordering: -2 248 │ │ └── limit hint: 454.55 249 │ └── filters 250 │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 251 └── 5 252 253 optsteps 254 EXPLAIN SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30 ORDER BY b DESC LIMIT 5 255 ---- 256 ================================================================================ 257 Initial expression 258 Cost: 10000000000000000159028911097599180468360808563945281389781327557747838772170381060813469985856815104.00 259 ================================================================================ 260 explain 261 ├── columns: tree:5 field:6 description:7 262 └── sort 263 ├── columns: a:1!null b:2 c:3 d:4 264 ├── cardinality: [0 - 5] 265 ├── key: (1) 266 ├── fd: (1)-->(2-4), (2-4)~~>(1) 267 ├── ordering: -2 268 └── limit 269 ├── columns: a:1!null b:2 c:3 d:4 270 ├── internal-ordering: -2 271 ├── cardinality: [0 - 5] 272 ├── key: (1) 273 ├── fd: (1)-->(2-4), (2-4)~~>(1) 274 ├── sort 275 │ ├── columns: a:1!null b:2 c:3 d:4 276 │ ├── cardinality: [0 - 11] 277 │ ├── key: (1) 278 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 279 │ ├── ordering: -2 280 │ ├── limit hint: 5.00 281 │ └── select 282 │ ├── columns: a:1!null b:2 c:3 d:4 283 │ ├── cardinality: [0 - 11] 284 │ ├── key: (1) 285 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 286 │ ├── scan abcd 287 │ │ ├── columns: a:1!null b:2 c:3 d:4 288 │ │ ├── flags: force-index=b 289 │ │ ├── key: (1) 290 │ │ └── fd: (1)-->(2-4), (2-4)~~>(1) 291 │ └── filters 292 │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 293 └── 5 294 ================================================================================ 295 SimplifySelectFilters 296 Cost: 10000000000000000159028911097599180468360808563945281389781327557747838772170381060813469985856815104.00 297 ================================================================================ 298 explain 299 ├── columns: tree:5 field:6 description:7 300 └── sort 301 ├── columns: a:1!null b:2 c:3 d:4 302 ├── cardinality: [0 - 5] 303 ├── key: (1) 304 ├── fd: (1)-->(2-4), (2-4)~~>(1) 305 ├── ordering: -2 306 └── limit 307 ├── columns: a:1!null b:2 c:3 d:4 308 ├── internal-ordering: -2 309 ├── cardinality: [0 - 5] 310 ├── key: (1) 311 ├── fd: (1)-->(2-4), (2-4)~~>(1) 312 ├── sort 313 │ ├── columns: a:1!null b:2 c:3 d:4 314 - │ ├── cardinality: [0 - 11] 315 │ ├── key: (1) 316 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 317 │ ├── ordering: -2 318 │ ├── limit hint: 5.00 319 │ └── select 320 │ ├── columns: a:1!null b:2 c:3 d:4 321 - │ ├── cardinality: [0 - 11] 322 │ ├── key: (1) 323 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 324 │ ├── scan abcd 325 │ │ ├── columns: a:1!null b:2 c:3 d:4 326 │ │ ├── flags: force-index=b 327 │ │ ├── key: (1) 328 │ │ └── fd: (1)-->(2-4), (2-4)~~>(1) 329 │ └── filters 330 - │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 331 + │ ├── a:1 >= 20 [outer=(1), constraints=(/1: [/20 - ]; tight)] 332 + │ └── a:1 <= 30 [outer=(1), constraints=(/1: (/NULL - /30]; tight)] 333 └── 5 334 ================================================================================ 335 ConsolidateSelectFilters 336 Cost: 10000000000000000159028911097599180468360808563945281389781327557747838772170381060813469985856815104.00 337 ================================================================================ 338 explain 339 ├── columns: tree:5 field:6 description:7 340 └── sort 341 ├── columns: a:1!null b:2 c:3 d:4 342 ├── cardinality: [0 - 5] 343 ├── key: (1) 344 ├── fd: (1)-->(2-4), (2-4)~~>(1) 345 ├── ordering: -2 346 └── limit 347 ├── columns: a:1!null b:2 c:3 d:4 348 ├── internal-ordering: -2 349 ├── cardinality: [0 - 5] 350 ├── key: (1) 351 ├── fd: (1)-->(2-4), (2-4)~~>(1) 352 ├── sort 353 │ ├── columns: a:1!null b:2 c:3 d:4 354 + │ ├── cardinality: [0 - 11] 355 │ ├── key: (1) 356 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 357 │ ├── ordering: -2 358 │ ├── limit hint: 5.00 359 │ └── select 360 │ ├── columns: a:1!null b:2 c:3 d:4 361 + │ ├── cardinality: [0 - 11] 362 │ ├── key: (1) 363 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 364 │ ├── scan abcd 365 │ │ ├── columns: a:1!null b:2 c:3 d:4 366 │ │ ├── flags: force-index=b 367 │ │ ├── key: (1) 368 │ │ └── fd: (1)-->(2-4), (2-4)~~>(1) 369 │ └── filters 370 - │ ├── a:1 >= 20 [outer=(1), constraints=(/1: [/20 - ]; tight)] 371 - │ └── a:1 <= 30 [outer=(1), constraints=(/1: (/NULL - /30]; tight)] 372 + │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 373 └── 5 374 ================================================================================ 375 GenerateIndexScans 376 Cost: 5134.91 377 ================================================================================ 378 explain 379 ├── columns: tree:5 field:6 description:7 380 - └── sort 381 + └── limit 382 ├── columns: a:1!null b:2 c:3 d:4 383 + ├── internal-ordering: -2 384 ├── cardinality: [0 - 5] 385 ├── key: (1) 386 ├── fd: (1)-->(2-4), (2-4)~~>(1) 387 ├── ordering: -2 388 - └── limit 389 - ├── columns: a:1!null b:2 c:3 d:4 390 - ├── internal-ordering: -2 391 - ├── cardinality: [0 - 5] 392 - ├── key: (1) 393 - ├── fd: (1)-->(2-4), (2-4)~~>(1) 394 - ├── sort 395 - │ ├── columns: a:1!null b:2 c:3 d:4 396 - │ ├── cardinality: [0 - 11] 397 - │ ├── key: (1) 398 - │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 399 - │ ├── ordering: -2 400 - │ ├── limit hint: 5.00 401 - │ └── select 402 - │ ├── columns: a:1!null b:2 c:3 d:4 403 - │ ├── cardinality: [0 - 11] 404 - │ ├── key: (1) 405 - │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 406 - │ ├── scan abcd 407 - │ │ ├── columns: a:1!null b:2 c:3 d:4 408 - │ │ ├── flags: force-index=b 409 - │ │ ├── key: (1) 410 - │ │ └── fd: (1)-->(2-4), (2-4)~~>(1) 411 - │ └── filters 412 - │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 413 - └── 5 414 + ├── select 415 + │ ├── columns: a:1!null b:2 c:3 d:4 416 + │ ├── cardinality: [0 - 11] 417 + │ ├── key: (1) 418 + │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 419 + │ ├── ordering: -2 420 + │ ├── limit hint: 5.00 421 + │ ├── index-join abcd 422 + │ │ ├── columns: a:1!null b:2 c:3 d:4 423 + │ │ ├── key: (1) 424 + │ │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 425 + │ │ ├── ordering: -2 426 + │ │ ├── limit hint: 454.55 427 + │ │ └── scan abcd@b,rev 428 + │ │ ├── columns: a:1!null b:2 429 + │ │ ├── flags: force-index=b 430 + │ │ ├── key: (1) 431 + │ │ ├── fd: (1)-->(2) 432 + │ │ ├── ordering: -2 433 + │ │ └── limit hint: 454.55 434 + │ └── filters 435 + │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 436 + └── 5 437 -------------------------------------------------------------------------------- 438 GenerateZigzagJoins (no changes) 439 -------------------------------------------------------------------------------- 440 -------------------------------------------------------------------------------- 441 GenerateConstrainedScans (no changes) 442 -------------------------------------------------------------------------------- 443 ================================================================================ 444 Final best expression 445 Cost: 5134.91 446 ================================================================================ 447 explain 448 ├── columns: tree:5 field:6 description:7 449 └── limit 450 ├── columns: a:1!null b:2 c:3 d:4 451 ├── internal-ordering: -2 452 ├── cardinality: [0 - 5] 453 ├── key: (1) 454 ├── fd: (1)-->(2-4), (2-4)~~>(1) 455 ├── ordering: -2 456 ├── select 457 │ ├── columns: a:1!null b:2 c:3 d:4 458 │ ├── cardinality: [0 - 11] 459 │ ├── key: (1) 460 │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 461 │ ├── ordering: -2 462 │ ├── limit hint: 5.00 463 │ ├── index-join abcd 464 │ │ ├── columns: a:1!null b:2 c:3 d:4 465 │ │ ├── key: (1) 466 │ │ ├── fd: (1)-->(2-4), (2-4)~~>(1) 467 │ │ ├── ordering: -2 468 │ │ ├── limit hint: 454.55 469 │ │ └── scan abcd@b,rev 470 │ │ ├── columns: a:1!null b:2 471 │ │ ├── flags: force-index=b 472 │ │ ├── key: (1) 473 │ │ ├── fd: (1)-->(2) 474 │ │ ├── ordering: -2 475 │ │ └── limit hint: 454.55 476 │ └── filters 477 │ └── (a:1 >= 20) AND (a:1 <= 30) [outer=(1), constraints=(/1: [/20 - /30]; tight)] 478 └── 5 479 480 # -------------------------------------------------- 481 # PushLimitIntoOffset + GenerateLimitedScans 482 # -------------------------------------------------- 483 484 # Regression testing for #30416. 485 # The limit is pushed down the offset and so an appropriate index scan is used 486 # over a primary key scan. 487 opt 488 SELECT * from a ORDER BY s LIMIT 10 OFFSET 10 489 ---- 490 offset 491 ├── columns: k:1!null i:2 f:3 s:4 j:5 492 ├── internal-ordering: +4 493 ├── cardinality: [0 - 10] 494 ├── key: (1) 495 ├── fd: (1)-->(2-5) 496 ├── ordering: +4 497 ├── index-join a 498 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 499 │ ├── cardinality: [0 - 20] 500 │ ├── key: (1) 501 │ ├── fd: (1)-->(2-5) 502 │ ├── ordering: +4 503 │ └── scan a@s_idx 504 │ ├── columns: k:1!null i:2 f:3 s:4 505 │ ├── limit: 20 506 │ ├── key: (1) 507 │ ├── fd: (1)-->(2-4) 508 │ └── ordering: +4 509 └── 10 510 511 # The right index is used for the limited scan based on the order. 512 opt 513 SELECT * from a ORDER BY s DESC LIMIT 10 OFFSET 10 514 ---- 515 offset 516 ├── columns: k:1!null i:2 f:3 s:4 j:5 517 ├── internal-ordering: -4 518 ├── cardinality: [0 - 10] 519 ├── key: (1) 520 ├── fd: (1)-->(2-5) 521 ├── ordering: -4 522 ├── index-join a 523 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 524 │ ├── cardinality: [0 - 20] 525 │ ├── key: (1) 526 │ ├── fd: (1)-->(2-5) 527 │ ├── ordering: -4 528 │ └── scan a@si_idx 529 │ ├── columns: k:1!null i:2 s:4 j:5 530 │ ├── limit: 20 531 │ ├── key: (1) 532 │ ├── fd: (1)-->(2,4,5) 533 │ └── ordering: -4 534 └── 10 535 536 # PushLimitIntoIndexJoin propagates row-level locking information. 537 opt 538 SELECT * FROM kuv ORDER BY u LIMIT 5 FOR UPDATE 539 ---- 540 index-join kuv 541 ├── columns: k:1!null u:2 v:3 542 ├── cardinality: [0 - 5] 543 ├── volatile, side-effects 544 ├── key: (1) 545 ├── fd: (1)-->(2,3) 546 ├── ordering: +2 547 └── scan kuv@secondary 548 ├── columns: k:1!null u:2 549 ├── limit: 5 550 ├── locking: for-update 551 ├── volatile, side-effects 552 ├── key: (1) 553 ├── fd: (1)-->(2) 554 └── ordering: +2