github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/physprops/ordering (about) 1 exec-ddl 2 CREATE TABLE a 3 ( 4 x INT, 5 y FLOAT, 6 z DECIMAL, 7 s STRING NOT NULL, 8 PRIMARY KEY (x, y DESC) 9 ) 10 ---- 11 12 exec-ddl 13 CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, b, c)) 14 ---- 15 16 exec-ddl 17 CREATE TABLE xyz (x INT, y INT, z INT, PRIMARY KEY (x, y, z)) 18 ---- 19 20 exec-ddl 21 CREATE TABLE abcd (a INT, b INT, c INT, d INT, INDEX ab(a, b) STORING (c, d), INDEX cd(c, d) STORING (a, b)) 22 ---- 23 24 # -------------------------------------------------- 25 # Scan operator. 26 # -------------------------------------------------- 27 28 # Order by entire key, in same order as key. 29 opt 30 SELECT * FROM a ORDER BY x, y DESC 31 ---- 32 scan a 33 ├── columns: x:1!null y:2!null z:3 s:4!null 34 ├── key: (1,2) 35 ├── fd: (1,2)-->(3,4) 36 └── ordering: +1,-2 37 38 # Order by prefix. 39 opt 40 SELECT * FROM a ORDER BY x 41 ---- 42 scan a 43 ├── columns: x:1!null y:2!null z:3 s:4!null 44 ├── key: (1,2) 45 ├── fd: (1,2)-->(3,4) 46 └── ordering: +1 47 48 # Order by additional column (should be dropped by optimizer). 49 opt 50 SELECT * FROM a ORDER BY x, y DESC, z 51 ---- 52 scan a 53 ├── columns: x:1!null y:2!null z:3 s:4!null 54 ├── key: (1,2) 55 ├── fd: (1,2)-->(3,4) 56 └── ordering: +1,-2 57 58 # Order by suffix (scan shouldn't be able to provide). 59 opt 60 SELECT * FROM a ORDER BY y DESC 61 ---- 62 sort 63 ├── columns: x:1!null y:2!null z:3 s:4!null 64 ├── key: (1,2) 65 ├── fd: (1,2)-->(3,4) 66 ├── ordering: -2 67 └── scan a 68 ├── columns: x:1!null y:2!null z:3 s:4!null 69 ├── key: (1,2) 70 └── fd: (1,2)-->(3,4) 71 72 # Order by suffix, don't project prefix (scan shouldn't be able to provide). 73 opt 74 SELECT y FROM a ORDER BY y DESC 75 ---- 76 sort 77 ├── columns: y:2!null 78 ├── ordering: -2 79 └── scan a 80 └── columns: y:2!null 81 82 # -------------------------------------------------- 83 # Select operator (pass through). 84 # -------------------------------------------------- 85 86 # Pass through ordering to scan operator that can support it. 87 opt 88 SELECT * FROM a WHERE x>y ORDER BY x, y DESC 89 ---- 90 select 91 ├── columns: x:1!null y:2!null z:3 s:4!null 92 ├── key: (1,2) 93 ├── fd: (1,2)-->(3,4) 94 ├── ordering: +1,-2 95 ├── scan a 96 │ ├── columns: x:1!null y:2!null z:3 s:4!null 97 │ ├── key: (1,2) 98 │ ├── fd: (1,2)-->(3,4) 99 │ └── ordering: +1,-2 100 └── filters 101 └── x:1 > y:2 [outer=(1,2)] 102 103 # Pass through ordering to scan operator that can't support it. 104 opt 105 SELECT * FROM a WHERE x>y ORDER BY z DESC 106 ---- 107 sort 108 ├── columns: x:1!null y:2!null z:3 s:4!null 109 ├── key: (1,2) 110 ├── fd: (1,2)-->(3,4) 111 ├── ordering: -3 112 └── select 113 ├── columns: x:1!null y:2!null z:3 s:4!null 114 ├── key: (1,2) 115 ├── fd: (1,2)-->(3,4) 116 ├── scan a 117 │ ├── columns: x:1!null y:2!null z:3 s:4!null 118 │ ├── key: (1,2) 119 │ └── fd: (1,2)-->(3,4) 120 └── filters 121 └── x:1 > y:2 [outer=(1,2)] 122 123 # -------------------------------------------------- 124 # Project operator (pass through). 125 # -------------------------------------------------- 126 127 # Pass through ordering to scan operator that can support it. 128 opt 129 SELECT x+1 AS r, y FROM a ORDER BY x, y DESC 130 ---- 131 project 132 ├── columns: r:5!null y:2!null [hidden: x:1!null] 133 ├── key: (1,2) 134 ├── fd: (1)-->(5) 135 ├── ordering: +1,-2 136 ├── scan a 137 │ ├── columns: x:1!null y:2!null 138 │ ├── key: (1,2) 139 │ └── ordering: +1,-2 140 └── projections 141 └── x:1 + 1 [as=r:5, outer=(1)] 142 143 # Pass through ordering to scan operator that can't support it. 144 opt 145 SELECT y, x, z+1 AS r FROM a ORDER BY x, y 146 ---- 147 sort (segmented) 148 ├── columns: y:2!null x:1!null r:5 149 ├── key: (1,2) 150 ├── fd: (1,2)-->(5) 151 ├── ordering: +1,+2 152 └── project 153 ├── columns: r:5 x:1!null y:2!null 154 ├── key: (1,2) 155 ├── fd: (1,2)-->(5) 156 ├── ordering: +1 157 ├── scan a 158 │ ├── columns: x:1!null y:2!null z:3 159 │ ├── key: (1,2) 160 │ ├── fd: (1,2)-->(3) 161 │ └── ordering: +1 162 └── projections 163 └── z:3 + 1 [as=r:5, outer=(3)] 164 165 # Ordering cannot be passed through because it includes computed column. 166 opt 167 SELECT x, y+1 AS computed, y FROM a ORDER BY x, computed 168 ---- 169 sort (segmented) 170 ├── columns: x:1!null computed:5!null y:2!null 171 ├── key: (1,2) 172 ├── fd: (1,2)-->(5) 173 ├── ordering: +1,+5 174 └── project 175 ├── columns: computed:5!null x:1!null y:2!null 176 ├── key: (1,2) 177 ├── fd: (1,2)-->(5) 178 ├── ordering: +1 179 ├── scan a 180 │ ├── columns: x:1!null y:2!null 181 │ ├── key: (1,2) 182 │ └── ordering: +1 183 └── projections 184 └── y:2 + 1.0 [as=computed:5, outer=(2)] 185 186 # Ordering on an expression that gets constant-folded to a simple variable. 187 # Example from #43360: a boolean (possibly a placeholder) indicates the sort 188 # direction. 189 opt 190 SELECT * FROM a ORDER BY CASE WHEN false THEN x END ASC, CASE WHEN NOT false THEN x END DESC 191 ---- 192 project 193 ├── columns: x:1!null y:2!null z:3 s:4!null [hidden: column6:6!null] 194 ├── key: (1,2) 195 ├── fd: (1,2)-->(3,4), (1)==(6), (6)==(1) 196 ├── ordering: -(1|6) [actual: -1] 197 ├── scan a,rev 198 │ ├── columns: x:1!null y:2!null z:3 s:4!null 199 │ ├── key: (1,2) 200 │ ├── fd: (1,2)-->(3,4) 201 │ └── ordering: -1 202 └── projections 203 └── x:1 [as=column6:6, outer=(1)] 204 205 opt 206 SELECT * FROM a ORDER BY CASE WHEN true THEN x END ASC, CASE WHEN NOT true THEN x END DESC 207 ---- 208 project 209 ├── columns: x:1!null y:2!null z:3 s:4!null [hidden: column5:5!null] 210 ├── key: (1,2) 211 ├── fd: (1,2)-->(3,4), (1)==(5), (5)==(1) 212 ├── ordering: +(1|5) [actual: +1] 213 ├── scan a 214 │ ├── columns: x:1!null y:2!null z:3 s:4!null 215 │ ├── key: (1,2) 216 │ ├── fd: (1,2)-->(3,4) 217 │ └── ordering: +1 218 └── projections 219 └── x:1 [as=column5:5, outer=(1)] 220 221 # Similar case, except the equivalent input column is not being projected. 222 opt 223 SELECT 1 FROM a ORDER BY CASE WHEN false THEN x END ASC, CASE WHEN NOT false THEN x END DESC 224 ---- 225 project 226 ├── columns: "?column?":5!null [hidden: column7:7!null] 227 ├── fd: ()-->(5) 228 ├── ordering: -7 opt(5) [actual: -7] 229 ├── scan a,rev 230 │ ├── columns: x:1!null 231 │ └── ordering: -1 232 └── projections 233 ├── 1 [as="?column?":5] 234 └── x:1 [as=column7:7, outer=(1)] 235 236 # -------------------------------------------------- 237 # Select + Project operators (pass through both). 238 # -------------------------------------------------- 239 240 # Pass through ordering to scan operator that can support it. 241 opt 242 SELECT y, x-1 AS z FROM a WHERE x>y ORDER BY x, y DESC 243 ---- 244 project 245 ├── columns: y:2!null z:5!null [hidden: x:1!null] 246 ├── key: (1,2) 247 ├── fd: (1)-->(5) 248 ├── ordering: +1,-2 249 ├── select 250 │ ├── columns: x:1!null y:2!null 251 │ ├── key: (1,2) 252 │ ├── ordering: +1,-2 253 │ ├── scan a 254 │ │ ├── columns: x:1!null y:2!null 255 │ │ ├── key: (1,2) 256 │ │ └── ordering: +1,-2 257 │ └── filters 258 │ └── x:1 > y:2 [outer=(1,2)] 259 └── projections 260 └── x:1 - 1 [as=z:5, outer=(1)] 261 262 memo 263 SELECT y, x-1 AS z FROM a WHERE x>y ORDER BY x, y DESC 264 ---- 265 memo (optimized, ~5KB, required=[presentation: y:2,z:5] [ordering: +1,-2]) 266 ├── G1: (project G2 G3 x y) 267 │ ├── [presentation: y:2,z:5] [ordering: +1,-2] 268 │ │ ├── best: (project G2="[ordering: +1,-2]" G3 x y) 269 │ │ └── cost: 1076.71 270 │ └── [] 271 │ ├── best: (project G2 G3 x y) 272 │ └── cost: 1076.71 273 ├── G2: (select G4 G5) 274 │ ├── [ordering: +1,-2] 275 │ │ ├── best: (select G4="[ordering: +1,-2]" G5) 276 │ │ └── cost: 1070.03 277 │ └── [] 278 │ ├── best: (select G4 G5) 279 │ └── cost: 1070.03 280 ├── G3: (projections G6) 281 ├── G4: (scan a,cols=(1,2)) 282 │ ├── [ordering: +1,-2] 283 │ │ ├── best: (scan a,cols=(1,2)) 284 │ │ └── cost: 1060.02 285 │ └── [] 286 │ ├── best: (scan a,cols=(1,2)) 287 │ └── cost: 1060.02 288 ├── G5: (filters G7) 289 ├── G6: (minus G8 G9) 290 ├── G7: (gt G8 G10) 291 ├── G8: (variable x) 292 ├── G9: (const 1) 293 └── G10: (variable y) 294 295 # Pass through ordering to scan operator that can't support it. 296 opt 297 SELECT y, z FROM a WHERE x>y ORDER BY y 298 ---- 299 sort 300 ├── columns: y:2!null z:3 301 ├── ordering: +2 302 └── project 303 ├── columns: y:2!null z:3 304 └── select 305 ├── columns: x:1!null y:2!null z:3 306 ├── key: (1,2) 307 ├── fd: (1,2)-->(3) 308 ├── scan a 309 │ ├── columns: x:1!null y:2!null z:3 310 │ ├── key: (1,2) 311 │ └── fd: (1,2)-->(3) 312 └── filters 313 └── x:1 > y:2 [outer=(1,2)] 314 315 memo 316 SELECT y, z FROM a WHERE x>y ORDER BY y 317 ---- 318 memo (optimized, ~5KB, required=[presentation: y:2,z:3] [ordering: +2]) 319 ├── G1: (project G2 G3 y z) 320 │ ├── [presentation: y:2,z:3] [ordering: +2] 321 │ │ ├── best: (sort G1) 322 │ │ └── cost: 1145.92 323 │ └── [] 324 │ ├── best: (project G2 G3 y z) 325 │ └── cost: 1083.37 326 ├── G2: (select G4 G5) 327 │ ├── [ordering: +2] 328 │ │ ├── best: (sort G2) 329 │ │ └── cost: 1142.58 330 │ └── [] 331 │ ├── best: (select G4 G5) 332 │ └── cost: 1080.03 333 ├── G3: (projections) 334 ├── G4: (scan a,cols=(1-3)) 335 │ ├── [ordering: +2] 336 │ │ ├── best: (sort G4) 337 │ │ └── cost: 1289.35 338 │ └── [] 339 │ ├── best: (scan a,cols=(1-3)) 340 │ └── cost: 1070.02 341 ├── G5: (filters G6) 342 ├── G6: (gt G7 G8) 343 ├── G7: (variable x) 344 └── G8: (variable y) 345 346 # -------------------------------------------------- 347 # GroupBy operator. 348 # -------------------------------------------------- 349 350 # Verify that the internal ordering is required of the input. 351 opt 352 SELECT array_agg(z) FROM (SELECT * FROM a ORDER BY y) 353 ---- 354 scalar-group-by 355 ├── columns: array_agg:5 356 ├── internal-ordering: +2 357 ├── cardinality: [1 - 1] 358 ├── key: () 359 ├── fd: ()-->(5) 360 ├── sort 361 │ ├── columns: y:2!null z:3 362 │ ├── ordering: +2 363 │ └── scan a 364 │ └── columns: y:2!null z:3 365 └── aggregations 366 └── array-agg [as=array_agg:5, outer=(3)] 367 └── z:3 368 369 opt 370 SELECT array_agg(x) FROM (SELECT * FROM a ORDER BY x, y DESC) 371 ---- 372 scalar-group-by 373 ├── columns: array_agg:5 374 ├── internal-ordering: +1,-2 375 ├── cardinality: [1 - 1] 376 ├── key: () 377 ├── fd: ()-->(5) 378 ├── scan a 379 │ ├── columns: x:1!null y:2!null 380 │ ├── key: (1,2) 381 │ └── ordering: +1,-2 382 └── aggregations 383 └── array-agg [as=array_agg:5, outer=(1)] 384 └── x:1 385 386 # Pass through ordering on grouping columns. 387 opt 388 SELECT a, min(b) FROM abc GROUP BY a ORDER BY a 389 ---- 390 group-by 391 ├── columns: a:1!null min:4!null 392 ├── grouping columns: a:1!null 393 ├── key: (1) 394 ├── fd: (1)-->(4) 395 ├── ordering: +1 396 ├── scan abc 397 │ ├── columns: a:1!null b:2!null 398 │ └── ordering: +1 399 └── aggregations 400 └── min [as=min:4, outer=(2)] 401 └── b:2 402 403 opt 404 SELECT a, b, min(c) FROM abc GROUP BY a, b ORDER BY a 405 ---- 406 group-by 407 ├── columns: a:1!null b:2!null min:4!null 408 ├── grouping columns: a:1!null b:2!null 409 ├── internal-ordering: +1,+2 410 ├── key: (1,2) 411 ├── fd: (1,2)-->(4) 412 ├── ordering: +1 413 ├── scan abc 414 │ ├── columns: a:1!null b:2!null c:3!null 415 │ ├── key: (1-3) 416 │ └── ordering: +1,+2 417 └── aggregations 418 └── min [as=min:4, outer=(3)] 419 └── c:3 420 421 opt 422 SELECT a, b, min(c) FROM abc GROUP BY a, b ORDER BY a, b 423 ---- 424 group-by 425 ├── columns: a:1!null b:2!null min:4!null 426 ├── grouping columns: a:1!null b:2!null 427 ├── key: (1,2) 428 ├── fd: (1,2)-->(4) 429 ├── ordering: +1,+2 430 ├── scan abc 431 │ ├── columns: a:1!null b:2!null c:3!null 432 │ ├── key: (1-3) 433 │ └── ordering: +1,+2 434 └── aggregations 435 └── min [as=min:4, outer=(3)] 436 └── c:3 437 438 opt 439 SELECT a, b, min(c) FROM abc GROUP BY b, a ORDER BY a, b 440 ---- 441 group-by 442 ├── columns: a:1!null b:2!null min:4!null 443 ├── grouping columns: a:1!null b:2!null 444 ├── key: (1,2) 445 ├── fd: (1,2)-->(4) 446 ├── ordering: +1,+2 447 ├── scan abc 448 │ ├── columns: a:1!null b:2!null c:3!null 449 │ ├── key: (1-3) 450 │ └── ordering: +1,+2 451 └── aggregations 452 └── min [as=min:4, outer=(3)] 453 └── c:3 454 455 # We can't pass through the ordering if it refers to aggregation results. 456 opt 457 SELECT a, b, min(c) AS m FROM abc GROUP BY a, b ORDER BY a, m 458 ---- 459 sort (segmented) 460 ├── columns: a:1!null b:2!null m:4!null 461 ├── key: (1,2) 462 ├── fd: (1,2)-->(4) 463 ├── ordering: +1,+4 464 └── group-by 465 ├── columns: a:1!null b:2!null min:4!null 466 ├── grouping columns: a:1!null b:2!null 467 ├── internal-ordering: +1,+2 468 ├── key: (1,2) 469 ├── fd: (1,2)-->(4) 470 ├── ordering: +1 471 ├── scan abc 472 │ ├── columns: a:1!null b:2!null c:3!null 473 │ ├── key: (1-3) 474 │ └── ordering: +1,+2 475 └── aggregations 476 └── min [as=min:4, outer=(3)] 477 └── c:3 478 479 # Satisfy both the required and the internal orderings by requiring a+,b+,c+. 480 opt 481 SELECT a, b, array_agg(c) FROM (SELECT * FROM abc ORDER BY c) GROUP BY a, b ORDER BY a, b 482 ---- 483 group-by 484 ├── columns: a:1!null b:2!null array_agg:4!null 485 ├── grouping columns: a:1!null b:2!null 486 ├── internal-ordering: +3 opt(1,2) 487 ├── key: (1,2) 488 ├── fd: (1,2)-->(4) 489 ├── ordering: +1,+2 490 ├── scan abc 491 │ ├── columns: a:1!null b:2!null c:3!null 492 │ ├── key: (1-3) 493 │ └── ordering: +1,+2,+3 494 └── aggregations 495 └── array-agg [as=array_agg:4, outer=(3)] 496 └── c:3 497 498 opt 499 SELECT a, b, array_agg(c) FROM (SELECT * FROM abc ORDER BY a, b, c) GROUP BY a, b ORDER BY a, b 500 ---- 501 group-by 502 ├── columns: a:1!null b:2!null array_agg:4!null 503 ├── grouping columns: a:1!null b:2!null 504 ├── internal-ordering: +3 opt(1,2) 505 ├── key: (1,2) 506 ├── fd: (1,2)-->(4) 507 ├── ordering: +1,+2 508 ├── scan abc 509 │ ├── columns: a:1!null b:2!null c:3!null 510 │ ├── key: (1-3) 511 │ └── ordering: +1,+2,+3 512 └── aggregations 513 └── array-agg [as=array_agg:4, outer=(3)] 514 └── c:3 515 516 opt 517 SELECT a, b, array_agg(c) FROM (SELECT * FROM abc ORDER BY b, c, a) GROUP BY b, a ORDER BY a, b 518 ---- 519 group-by 520 ├── columns: a:1!null b:2!null array_agg:4!null 521 ├── grouping columns: a:1!null b:2!null 522 ├── internal-ordering: +3 opt(1,2) 523 ├── key: (1,2) 524 ├── fd: (1,2)-->(4) 525 ├── ordering: +1,+2 526 ├── scan abc 527 │ ├── columns: a:1!null b:2!null c:3!null 528 │ ├── key: (1-3) 529 │ └── ordering: +1,+2,+3 530 └── aggregations 531 └── array-agg [as=array_agg:4, outer=(3)] 532 └── c:3 533 534 # Verify that the GroupBy child ordering is simplified according to the child's 535 # FD set. 536 opt 537 SELECT sum(c) FROM abc WHERE a = 1 GROUP BY b ORDER BY b 538 ---- 539 group-by 540 ├── columns: sum:4!null [hidden: b:2!null] 541 ├── grouping columns: b:2!null 542 ├── key: (2) 543 ├── fd: (2)-->(4) 544 ├── ordering: +2 545 ├── scan abc 546 │ ├── columns: a:1!null b:2!null c:3!null 547 │ ├── constraint: /1/2/3: [/1 - /1] 548 │ ├── key: (2,3) 549 │ ├── fd: ()-->(1) 550 │ └── ordering: +2 opt(1) [actual: +2] 551 └── aggregations 552 └── sum [as=sum:4, outer=(3)] 553 └── c:3 554 555 # Verify we do a streaming group-by using the a, b ordering. 556 opt 557 SELECT sum(d) FROM abcd GROUP BY a, b, c 558 ---- 559 project 560 ├── columns: sum:6 561 └── group-by 562 ├── columns: a:1 b:2 c:3 sum:6 563 ├── grouping columns: a:1 b:2 c:3 564 ├── internal-ordering: +1,+2 565 ├── key: (1-3) 566 ├── fd: (1-3)-->(6) 567 ├── scan abcd@ab 568 │ ├── columns: a:1 b:2 c:3 d:4 569 │ └── ordering: +1,+2 570 └── aggregations 571 └── sum [as=sum:6, outer=(4)] 572 └── d:4 573 574 # Verify we do a streaming group-by using the c, d ordering. 575 opt 576 SELECT sum(a) FROM abcd GROUP BY b, c, d 577 ---- 578 project 579 ├── columns: sum:6 580 └── group-by 581 ├── columns: b:2 c:3 d:4 sum:6 582 ├── grouping columns: b:2 c:3 d:4 583 ├── internal-ordering: +3,+4 584 ├── key: (2-4) 585 ├── fd: (2-4)-->(6) 586 ├── scan abcd@cd 587 │ ├── columns: a:1 b:2 c:3 d:4 588 │ └── ordering: +3,+4 589 └── aggregations 590 └── sum [as=sum:6, outer=(1)] 591 └── a:1 592 593 opt 594 SELECT array_agg(d) FROM (SELECT * FROM abcd ORDER BY c) GROUP BY a, b 595 ---- 596 project 597 ├── columns: array_agg:6 598 └── group-by 599 ├── columns: a:1 b:2 array_agg:6 600 ├── grouping columns: a:1 b:2 601 ├── internal-ordering: +3 opt(1,2) 602 ├── key: (1,2) 603 ├── fd: (1,2)-->(6) 604 ├── scan abcd@cd 605 │ ├── columns: a:1 b:2 c:3 d:4 606 │ └── ordering: +3 opt(1,2) [actual: +3] 607 └── aggregations 608 └── array-agg [as=array_agg:6, outer=(4)] 609 └── d:4 610 611 # -------------------------------------------------- 612 # Explain operator. 613 # -------------------------------------------------- 614 opt 615 EXPLAIN (VERBOSE) SELECT * FROM a ORDER BY y 616 ---- 617 explain 618 ├── columns: tree:5 field:8 description:9 columns:10 ordering:11 [hidden: level:6 node_type:7] 619 ├── mode: verbose 620 └── sort 621 ├── columns: x:1!null y:2!null z:3 s:4!null 622 ├── key: (1,2) 623 ├── fd: (1,2)-->(3,4) 624 ├── ordering: +2 625 └── scan a 626 ├── columns: x:1!null y:2!null z:3 s:4!null 627 ├── key: (1,2) 628 └── fd: (1,2)-->(3,4) 629 630 memo 631 EXPLAIN (VERBOSE) SELECT * FROM a ORDER BY y 632 ---- 633 memo (optimized, ~2KB, required=[presentation: tree:5,field:8,description:9,columns:10,ordering:11]) 634 ├── G1: (explain G2 [presentation: x:1,y:2,z:3,s:4] [ordering: +2]) 635 │ └── [presentation: tree:5,field:8,description:9,columns:10,ordering:11] 636 │ ├── best: (explain G2="[presentation: x:1,y:2,z:3,s:4] [ordering: +2]" [presentation: x:1,y:2,z:3,s:4] [ordering: +2]) 637 │ └── cost: 1299.36 638 └── G2: (scan a) 639 ├── [presentation: x:1,y:2,z:3,s:4] [ordering: +2] 640 │ ├── best: (sort G2) 641 │ └── cost: 1299.35 642 └── [] 643 ├── best: (scan a) 644 └── cost: 1080.02 645 646 # -------------------------------------------------- 647 # With Ordinality 648 # -------------------------------------------------- 649 650 memo 651 SELECT y FROM a WITH ORDINALITY ORDER BY ordinality 652 ---- 653 memo (optimized, ~4KB, required=[presentation: y:2] [ordering: +5]) 654 ├── G1: (ordinality G2) 655 │ ├── [presentation: y:2] [ordering: +5] 656 │ │ ├── best: (ordinality G2) 657 │ │ └── cost: 1060.03 658 │ └── [] 659 │ ├── best: (ordinality G2) 660 │ └── cost: 1060.03 661 └── G2: (scan a,cols=(2)) 662 └── [] 663 ├── best: (scan a,cols=(2)) 664 └── cost: 1050.02 665 666 memo 667 SELECT y FROM a WITH ORDINALITY ORDER BY -ordinality 668 ---- 669 memo (optimized, ~5KB, required=[presentation: y:2] [ordering: +6]) 670 ├── G1: (project G2 G3 y) 671 │ ├── [presentation: y:2] [ordering: +6] 672 │ │ ├── best: (sort G1) 673 │ │ └── cost: 1299.37 674 │ └── [] 675 │ ├── best: (project G2 G3 y) 676 │ └── cost: 1080.04 677 ├── G2: (ordinality G4) 678 │ └── [] 679 │ ├── best: (ordinality G4) 680 │ └── cost: 1060.03 681 ├── G3: (projections G5) 682 ├── G4: (scan a,cols=(2)) 683 │ └── [] 684 │ ├── best: (scan a,cols=(2)) 685 │ └── cost: 1050.02 686 ├── G5: (unary-minus G6) 687 └── G6: (variable ordinality) 688 689 memo 690 SELECT y FROM a WITH ORDINALITY ORDER BY ordinality, x 691 ---- 692 memo (optimized, ~6KB, required=[presentation: y:2] [ordering: +5]) 693 ├── G1: (ordinality G2) 694 │ ├── [presentation: y:2] [ordering: +5] 695 │ │ ├── best: (ordinality G2) 696 │ │ └── cost: 1060.03 697 │ └── [] 698 │ ├── best: (ordinality G2) 699 │ └── cost: 1060.03 700 └── G2: (scan a,cols=(2)) 701 └── [] 702 ├── best: (scan a,cols=(2)) 703 └── cost: 1050.02 704 705 memo 706 SELECT y FROM (SELECT * FROM a ORDER BY y) WITH ORDINALITY ORDER BY y, ordinality 707 ---- 708 memo (optimized, ~4KB, required=[presentation: y:2] [ordering: +2,+5]) 709 ├── G1: (ordinality G2 ordering=+2) 710 │ ├── [presentation: y:2] [ordering: +2,+5] 711 │ │ ├── best: (ordinality G2="[ordering: +2]" ordering=+2) 712 │ │ └── cost: 1279.36 713 │ └── [] 714 │ ├── best: (ordinality G2="[ordering: +2]" ordering=+2) 715 │ └── cost: 1279.36 716 └── G2: (scan a,cols=(2)) 717 ├── [ordering: +2] 718 │ ├── best: (sort G2) 719 │ └── cost: 1269.35 720 └── [] 721 ├── best: (scan a,cols=(2)) 722 └── cost: 1050.02 723 724 memo 725 SELECT y FROM (SELECT * FROM a ORDER BY y) WITH ORDINALITY ORDER BY ordinality, y 726 ---- 727 memo (optimized, ~4KB, required=[presentation: y:2] [ordering: +5]) 728 ├── G1: (ordinality G2 ordering=+2) 729 │ ├── [presentation: y:2] [ordering: +5] 730 │ │ ├── best: (ordinality G2="[ordering: +2]" ordering=+2) 731 │ │ └── cost: 1279.36 732 │ └── [] 733 │ ├── best: (ordinality G2="[ordering: +2]" ordering=+2) 734 │ └── cost: 1279.36 735 └── G2: (scan a,cols=(2)) 736 ├── [ordering: +2] 737 │ ├── best: (sort G2) 738 │ └── cost: 1269.35 739 └── [] 740 ├── best: (scan a,cols=(2)) 741 └── cost: 1050.02 742 743 memo 744 SELECT y FROM a WITH ORDINALITY ORDER BY ordinality DESC 745 ---- 746 memo (optimized, ~4KB, required=[presentation: y:2] [ordering: -5]) 747 ├── G1: (ordinality G2) 748 │ ├── [presentation: y:2] [ordering: -5] 749 │ │ ├── best: (sort G1) 750 │ │ └── cost: 1279.36 751 │ └── [] 752 │ ├── best: (ordinality G2) 753 │ └── cost: 1060.03 754 └── G2: (scan a,cols=(2)) 755 └── [] 756 ├── best: (scan a,cols=(2)) 757 └── cost: 1050.02 758 759 # -------------------------------------------------- 760 # Merge Join 761 # -------------------------------------------------- 762 763 opt 764 SELECT * FROM abc JOIN xyz ON a=x ORDER BY a 765 ---- 766 inner-join (merge) 767 ├── columns: a:1!null b:2!null c:3!null x:4!null y:5!null z:6!null 768 ├── left ordering: +1 769 ├── right ordering: +4 770 ├── key: (2-6) 771 ├── fd: (1)==(4), (4)==(1) 772 ├── ordering: +(1|4) [actual: +1] 773 ├── scan abc 774 │ ├── columns: a:1!null b:2!null c:3!null 775 │ ├── key: (1-3) 776 │ └── ordering: +1 777 ├── scan xyz 778 │ ├── columns: x:4!null y:5!null z:6!null 779 │ ├── key: (4-6) 780 │ └── ordering: +4 781 └── filters (true) 782 783 opt 784 SELECT * FROM abc JOIN xyz ON a=x ORDER BY x 785 ---- 786 inner-join (merge) 787 ├── columns: a:1!null b:2!null c:3!null x:4!null y:5!null z:6!null 788 ├── left ordering: +1 789 ├── right ordering: +4 790 ├── key: (2-6) 791 ├── fd: (1)==(4), (4)==(1) 792 ├── ordering: +(1|4) [actual: +1] 793 ├── scan abc 794 │ ├── columns: a:1!null b:2!null c:3!null 795 │ ├── key: (1-3) 796 │ └── ordering: +1 797 ├── scan xyz 798 │ ├── columns: x:4!null y:5!null z:6!null 799 │ ├── key: (4-6) 800 │ └── ordering: +4 801 └── filters (true) 802 803 # A left join guarantees an ordering on the left side. 804 opt 805 SELECT * FROM abc LEFT JOIN xyz ON a=x ORDER BY a 806 ---- 807 left-join (merge) 808 ├── columns: a:1!null b:2!null c:3!null x:4 y:5 z:6 809 ├── left ordering: +1 810 ├── right ordering: +4 811 ├── key: (1-6) 812 ├── ordering: +1 813 ├── scan abc 814 │ ├── columns: a:1!null b:2!null c:3!null 815 │ ├── key: (1-3) 816 │ └── ordering: +1 817 ├── scan xyz 818 │ ├── columns: x:4!null y:5!null z:6!null 819 │ ├── key: (4-6) 820 │ └── ordering: +4 821 └── filters (true) 822 823 # A left join doesn't guarantee an ordering on x (some rows will have NULLs). 824 opt 825 SELECT * FROM abc LEFT JOIN xyz ON a=x ORDER BY x 826 ---- 827 sort 828 ├── columns: a:1!null b:2!null c:3!null x:4 y:5 z:6 829 ├── key: (1-6) 830 ├── ordering: +4 831 └── left-join (merge) 832 ├── columns: a:1!null b:2!null c:3!null x:4 y:5 z:6 833 ├── left ordering: +1 834 ├── right ordering: +4 835 ├── key: (1-6) 836 ├── scan abc 837 │ ├── columns: a:1!null b:2!null c:3!null 838 │ ├── key: (1-3) 839 │ └── ordering: +1 840 ├── scan xyz 841 │ ├── columns: x:4!null y:5!null z:6!null 842 │ ├── key: (4-6) 843 │ └── ordering: +4 844 └── filters (true) 845 846 # A right join doesn't guarantee an ordering on a (some rows will have NULLs). 847 opt 848 SELECT * FROM abc RIGHT JOIN xyz ON a=x ORDER BY a 849 ---- 850 sort 851 ├── columns: a:1 b:2 c:3 x:4!null y:5!null z:6!null 852 ├── key: (1-6) 853 ├── ordering: +1 854 └── left-join (merge) 855 ├── columns: a:1 b:2 c:3 x:4!null y:5!null z:6!null 856 ├── left ordering: +4 857 ├── right ordering: +1 858 ├── key: (1-6) 859 ├── scan xyz 860 │ ├── columns: x:4!null y:5!null z:6!null 861 │ ├── key: (4-6) 862 │ └── ordering: +4 863 ├── scan abc 864 │ ├── columns: a:1!null b:2!null c:3!null 865 │ ├── key: (1-3) 866 │ └── ordering: +1 867 └── filters (true) 868 869 opt 870 SELECT * FROM abc RIGHT JOIN xyz ON a=x ORDER BY x 871 ---- 872 left-join (merge) 873 ├── columns: a:1 b:2 c:3 x:4!null y:5!null z:6!null 874 ├── left ordering: +4 875 ├── right ordering: +1 876 ├── key: (1-6) 877 ├── ordering: +4 878 ├── scan xyz 879 │ ├── columns: x:4!null y:5!null z:6!null 880 │ ├── key: (4-6) 881 │ └── ordering: +4 882 ├── scan abc 883 │ ├── columns: a:1!null b:2!null c:3!null 884 │ ├── key: (1-3) 885 │ └── ordering: +1 886 └── filters (true) 887 888 opt 889 SELECT * FROM abc FULL OUTER JOIN xyz ON a=x ORDER BY a 890 ---- 891 sort 892 ├── columns: a:1 b:2 c:3 x:4 y:5 z:6 893 ├── key: (1-6) 894 ├── ordering: +1 895 └── full-join (merge) 896 ├── columns: a:1 b:2 c:3 x:4 y:5 z:6 897 ├── left ordering: +1 898 ├── right ordering: +4 899 ├── key: (1-6) 900 ├── scan abc 901 │ ├── columns: a:1!null b:2!null c:3!null 902 │ ├── key: (1-3) 903 │ └── ordering: +1 904 ├── scan xyz 905 │ ├── columns: x:4!null y:5!null z:6!null 906 │ ├── key: (4-6) 907 │ └── ordering: +4 908 └── filters (true) 909 910 opt 911 SELECT * FROM abc JOIN xyz ON a=x AND b=y ORDER BY a 912 ---- 913 inner-join (merge) 914 ├── columns: a:1!null b:2!null c:3!null x:4!null y:5!null z:6!null 915 ├── left ordering: +1,+2 916 ├── right ordering: +4,+5 917 ├── key: (3-6) 918 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2) 919 ├── ordering: +(1|4) [actual: +1] 920 ├── scan abc 921 │ ├── columns: a:1!null b:2!null c:3!null 922 │ ├── key: (1-3) 923 │ └── ordering: +1,+2 924 ├── scan xyz 925 │ ├── columns: x:4!null y:5!null z:6!null 926 │ ├── key: (4-6) 927 │ └── ordering: +4,+5 928 └── filters (true) 929 930 opt 931 SELECT * FROM abc JOIN xyz ON a=x AND b=y ORDER BY a, b 932 ---- 933 inner-join (merge) 934 ├── columns: a:1!null b:2!null c:3!null x:4!null y:5!null z:6!null 935 ├── left ordering: +1,+2 936 ├── right ordering: +4,+5 937 ├── key: (3-6) 938 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2) 939 ├── ordering: +(1|4),+(2|5) [actual: +1,+2] 940 ├── scan abc 941 │ ├── columns: a:1!null b:2!null c:3!null 942 │ ├── key: (1-3) 943 │ └── ordering: +1,+2 944 ├── scan xyz 945 │ ├── columns: x:4!null y:5!null z:6!null 946 │ ├── key: (4-6) 947 │ └── ordering: +4,+5 948 └── filters (true) 949 950 opt 951 SELECT * FROM abc JOIN xyz ON a=x AND b=y ORDER BY a, y 952 ---- 953 inner-join (merge) 954 ├── columns: a:1!null b:2!null c:3!null x:4!null y:5!null z:6!null 955 ├── left ordering: +1,+2 956 ├── right ordering: +4,+5 957 ├── key: (3-6) 958 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2) 959 ├── ordering: +(1|4),+(2|5) [actual: +1,+2] 960 ├── scan abc 961 │ ├── columns: a:1!null b:2!null c:3!null 962 │ ├── key: (1-3) 963 │ └── ordering: +1,+2 964 ├── scan xyz 965 │ ├── columns: x:4!null y:5!null z:6!null 966 │ ├── key: (4-6) 967 │ └── ordering: +4,+5 968 └── filters (true) 969 970 # -------------------------------------------------- 971 # Limit / Offset 972 # -------------------------------------------------- 973 974 # Basic cases. 975 976 opt 977 SELECT * FROM abc ORDER BY a, b LIMIT 10 978 ---- 979 scan abc 980 ├── columns: a:1!null b:2!null c:3!null 981 ├── limit: 10 982 ├── key: (1-3) 983 └── ordering: +1,+2 984 985 # The filter prevents pushing of the limit into the scan. 986 opt 987 SELECT * FROM abc WHERE a+b>c ORDER BY a, b LIMIT 10 988 ---- 989 limit 990 ├── columns: a:1!null b:2!null c:3!null 991 ├── internal-ordering: +1,+2 992 ├── cardinality: [0 - 10] 993 ├── key: (1-3) 994 ├── ordering: +1,+2 995 ├── select 996 │ ├── columns: a:1!null b:2!null c:3!null 997 │ ├── key: (1-3) 998 │ ├── ordering: +1,+2 999 │ ├── limit hint: 10.00 1000 │ ├── scan abc 1001 │ │ ├── columns: a:1!null b:2!null c:3!null 1002 │ │ ├── key: (1-3) 1003 │ │ ├── ordering: +1,+2 1004 │ │ └── limit hint: 30.00 1005 │ └── filters 1006 │ └── c:3 < (a:1 + b:2) [outer=(1-3)] 1007 └── 10 1008 1009 opt 1010 SELECT * FROM abc ORDER BY a, b OFFSET 10 1011 ---- 1012 offset 1013 ├── columns: a:1!null b:2!null c:3!null 1014 ├── internal-ordering: +1,+2 1015 ├── key: (1-3) 1016 ├── ordering: +1,+2 1017 ├── scan abc 1018 │ ├── columns: a:1!null b:2!null c:3!null 1019 │ ├── key: (1-3) 1020 │ └── ordering: +1,+2 1021 └── 10 1022 1023 1024 # Cases where the requirement on Limit/Offset is incompatible with the 1025 # internal requirement. 1026 1027 opt 1028 SELECT * FROM (SELECT * FROM abc ORDER BY a, b LIMIT 10) ORDER BY b 1029 ---- 1030 sort 1031 ├── columns: a:1!null b:2!null c:3!null 1032 ├── cardinality: [0 - 10] 1033 ├── key: (1-3) 1034 ├── ordering: +2 1035 └── scan abc 1036 ├── columns: a:1!null b:2!null c:3!null 1037 ├── limit: 10 1038 └── key: (1-3) 1039 1040 opt 1041 SELECT * FROM (SELECT * FROM abc WHERE a+b>c ORDER BY a, b LIMIT 10) ORDER BY b 1042 ---- 1043 sort 1044 ├── columns: a:1!null b:2!null c:3!null 1045 ├── cardinality: [0 - 10] 1046 ├── key: (1-3) 1047 ├── ordering: +2 1048 └── limit 1049 ├── columns: a:1!null b:2!null c:3!null 1050 ├── internal-ordering: +1,+2 1051 ├── cardinality: [0 - 10] 1052 ├── key: (1-3) 1053 ├── select 1054 │ ├── columns: a:1!null b:2!null c:3!null 1055 │ ├── key: (1-3) 1056 │ ├── ordering: +1,+2 1057 │ ├── limit hint: 10.00 1058 │ ├── scan abc 1059 │ │ ├── columns: a:1!null b:2!null c:3!null 1060 │ │ ├── key: (1-3) 1061 │ │ ├── ordering: +1,+2 1062 │ │ └── limit hint: 30.00 1063 │ └── filters 1064 │ └── c:3 < (a:1 + b:2) [outer=(1-3)] 1065 └── 10 1066 1067 opt 1068 SELECT * FROM (SELECT * FROM abc ORDER BY a, b OFFSET 10) ORDER BY b 1069 ---- 1070 sort 1071 ├── columns: a:1!null b:2!null c:3!null 1072 ├── key: (1-3) 1073 ├── ordering: +2 1074 └── offset 1075 ├── columns: a:1!null b:2!null c:3!null 1076 ├── internal-ordering: +1,+2 1077 ├── key: (1-3) 1078 ├── scan abc 1079 │ ├── columns: a:1!null b:2!null c:3!null 1080 │ ├── key: (1-3) 1081 │ └── ordering: +1,+2 1082 └── 10 1083 1084 1085 # Cases where the requirement on Limit/Offset is weaker than the 1086 # internal requirement. 1087 1088 opt 1089 SELECT * FROM (SELECT * FROM abc ORDER BY a, b LIMIT 10) ORDER BY a 1090 ---- 1091 scan abc 1092 ├── columns: a:1!null b:2!null c:3!null 1093 ├── limit: 10 1094 ├── key: (1-3) 1095 └── ordering: +1 1096 1097 opt 1098 SELECT * FROM (SELECT * FROM abc WHERE a+b>c ORDER BY a, b LIMIT 10) ORDER BY a 1099 ---- 1100 limit 1101 ├── columns: a:1!null b:2!null c:3!null 1102 ├── internal-ordering: +1,+2 1103 ├── cardinality: [0 - 10] 1104 ├── key: (1-3) 1105 ├── ordering: +1 1106 ├── select 1107 │ ├── columns: a:1!null b:2!null c:3!null 1108 │ ├── key: (1-3) 1109 │ ├── ordering: +1,+2 1110 │ ├── limit hint: 10.00 1111 │ ├── scan abc 1112 │ │ ├── columns: a:1!null b:2!null c:3!null 1113 │ │ ├── key: (1-3) 1114 │ │ ├── ordering: +1,+2 1115 │ │ └── limit hint: 30.00 1116 │ └── filters 1117 │ └── c:3 < (a:1 + b:2) [outer=(1-3)] 1118 └── 10 1119 1120 opt 1121 SELECT * FROM (SELECT * FROM abc ORDER BY a, b OFFSET 10) ORDER BY a 1122 ---- 1123 offset 1124 ├── columns: a:1!null b:2!null c:3!null 1125 ├── internal-ordering: +1,+2 1126 ├── key: (1-3) 1127 ├── ordering: +1 1128 ├── scan abc 1129 │ ├── columns: a:1!null b:2!null c:3!null 1130 │ ├── key: (1-3) 1131 │ └── ordering: +1,+2 1132 └── 10 1133 1134 # Cases where the requirement on Limit/Offset is stronger than the 1135 # internal requirement. 1136 1137 opt 1138 SELECT * FROM (SELECT * FROM abc ORDER BY a, b LIMIT 10) ORDER BY a, b, c 1139 ---- 1140 scan abc 1141 ├── columns: a:1!null b:2!null c:3!null 1142 ├── limit: 10 1143 ├── key: (1-3) 1144 └── ordering: +1,+2,+3 1145 1146 opt 1147 SELECT * FROM (SELECT * FROM abc WHERE a+b>c ORDER BY a, b LIMIT 10) ORDER BY a, b, c 1148 ---- 1149 limit 1150 ├── columns: a:1!null b:2!null c:3!null 1151 ├── internal-ordering: +1,+2 1152 ├── cardinality: [0 - 10] 1153 ├── key: (1-3) 1154 ├── ordering: +1,+2,+3 1155 ├── select 1156 │ ├── columns: a:1!null b:2!null c:3!null 1157 │ ├── key: (1-3) 1158 │ ├── ordering: +1,+2,+3 1159 │ ├── limit hint: 10.00 1160 │ ├── scan abc 1161 │ │ ├── columns: a:1!null b:2!null c:3!null 1162 │ │ ├── key: (1-3) 1163 │ │ ├── ordering: +1,+2,+3 1164 │ │ └── limit hint: 30.00 1165 │ └── filters 1166 │ └── c:3 < (a:1 + b:2) [outer=(1-3)] 1167 └── 10 1168 1169 opt 1170 SELECT * FROM (SELECT * FROM abc ORDER BY a, b OFFSET 10) ORDER BY a, b, c 1171 ---- 1172 offset 1173 ├── columns: a:1!null b:2!null c:3!null 1174 ├── internal-ordering: +1,+2 1175 ├── key: (1-3) 1176 ├── ordering: +1,+2,+3 1177 ├── scan abc 1178 │ ├── columns: a:1!null b:2!null c:3!null 1179 │ ├── key: (1-3) 1180 │ └── ordering: +1,+2,+3 1181 └── 10 1182 1183 # -------------------------------------------------- 1184 # DistinctOn 1185 # -------------------------------------------------- 1186 1187 # DISTINCT doesn't require any particular ordering of its input. It could pass 1188 # through the requirement, but that doesn't improve the estimated cost in this 1189 # case. 1190 opt 1191 SELECT DISTINCT b, c FROM abc ORDER BY b 1192 ---- 1193 distinct-on 1194 ├── columns: b:2!null c:3!null 1195 ├── grouping columns: b:2!null c:3!null 1196 ├── key: (2,3) 1197 ├── ordering: +2 1198 └── sort 1199 ├── columns: b:2!null c:3!null 1200 ├── ordering: +2 1201 └── scan abc 1202 └── columns: b:2!null c:3!null 1203 1204 # In this case the ordering is passed through. 1205 opt 1206 SELECT DISTINCT a, b, c FROM abc ORDER BY a, b 1207 ---- 1208 scan abc 1209 ├── columns: a:1!null b:2!null c:3!null 1210 ├── key: (1-3) 1211 └── ordering: +1,+2 1212 1213 # DISTINCT ON requires the ordering of its input, as it affects the results 1214 # (values of a in this case). 1215 opt 1216 SELECT DISTINCT ON (b, c) a, b, c FROM abc ORDER BY b 1217 ---- 1218 distinct-on 1219 ├── columns: a:1!null b:2!null c:3!null 1220 ├── grouping columns: b:2!null c:3!null 1221 ├── key: (2,3) 1222 ├── fd: (2,3)-->(1) 1223 ├── ordering: +2 1224 ├── sort 1225 │ ├── columns: a:1!null b:2!null c:3!null 1226 │ ├── key: (1-3) 1227 │ ├── ordering: +2 1228 │ └── scan abc 1229 │ ├── columns: a:1!null b:2!null c:3!null 1230 │ └── key: (1-3) 1231 └── aggregations 1232 └── first-agg [as=a:1, outer=(1)] 1233 └── a:1 1234 1235 opt 1236 SELECT DISTINCT ON (b, c) a, b, c FROM abc ORDER BY b, c, a 1237 ---- 1238 distinct-on 1239 ├── columns: a:1!null b:2!null c:3!null 1240 ├── grouping columns: b:2!null c:3!null 1241 ├── internal-ordering: +1 opt(2,3) 1242 ├── key: (2,3) 1243 ├── fd: (2,3)-->(1) 1244 ├── ordering: +2,+3 1245 ├── sort 1246 │ ├── columns: a:1!null b:2!null c:3!null 1247 │ ├── key: (1-3) 1248 │ ├── ordering: +2,+3,+1 1249 │ └── scan abc 1250 │ ├── columns: a:1!null b:2!null c:3!null 1251 │ └── key: (1-3) 1252 └── aggregations 1253 └── first-agg [as=a:1, outer=(1)] 1254 └── a:1 1255 1256 opt 1257 SELECT DISTINCT ON (a) a, c FROM abc ORDER BY a, c DESC, b 1258 ---- 1259 distinct-on 1260 ├── columns: a:1!null c:3!null 1261 ├── grouping columns: a:1!null 1262 ├── internal-ordering: -3,+2 opt(1) 1263 ├── key: (1) 1264 ├── fd: (1)-->(3) 1265 ├── ordering: +1 1266 ├── sort (segmented) 1267 │ ├── columns: a:1!null b:2!null c:3!null 1268 │ ├── key: (1-3) 1269 │ ├── ordering: +1,-3,+2 1270 │ └── scan abc 1271 │ ├── columns: a:1!null b:2!null c:3!null 1272 │ ├── key: (1-3) 1273 │ └── ordering: +1 1274 └── aggregations 1275 └── first-agg [as=c:3, outer=(3)] 1276 └── c:3 1277 1278 # Pass through the ordering from above. 1279 opt 1280 SELECT * FROM (SELECT DISTINCT ON (a, b) a, b, c FROM abc) ORDER BY a 1281 ---- 1282 distinct-on 1283 ├── columns: a:1!null b:2!null c:3!null 1284 ├── grouping columns: a:1!null b:2!null 1285 ├── internal-ordering: +1,+2 1286 ├── key: (1,2) 1287 ├── fd: (1,2)-->(3) 1288 ├── ordering: +1 1289 ├── scan abc 1290 │ ├── columns: a:1!null b:2!null c:3!null 1291 │ ├── key: (1-3) 1292 │ └── ordering: +1,+2 1293 └── aggregations 1294 └── first-agg [as=c:3, outer=(3)] 1295 └── c:3 1296 1297 # Internal orderings that refer just to ON columns can be ignored. 1298 opt 1299 SELECT * FROM (SELECT DISTINCT ON (a, b) a, b, c FROM abc ORDER BY a) ORDER BY a, b 1300 ---- 1301 distinct-on 1302 ├── columns: a:1!null b:2!null c:3!null 1303 ├── grouping columns: a:1!null b:2!null 1304 ├── key: (1,2) 1305 ├── fd: (1,2)-->(3) 1306 ├── ordering: +1,+2 1307 ├── scan abc 1308 │ ├── columns: a:1!null b:2!null c:3!null 1309 │ ├── key: (1-3) 1310 │ └── ordering: +1,+2 1311 └── aggregations 1312 └── first-agg [as=c:3, outer=(3)] 1313 └── c:3 1314 1315 opt 1316 SELECT * FROM (SELECT DISTINCT ON (a, b) a, b, c FROM abc ORDER BY a, b) ORDER BY a 1317 ---- 1318 distinct-on 1319 ├── columns: a:1!null b:2!null c:3!null 1320 ├── grouping columns: a:1!null b:2!null 1321 ├── internal-ordering: +1,+2 1322 ├── key: (1,2) 1323 ├── fd: (1,2)-->(3) 1324 ├── ordering: +1 1325 ├── scan abc 1326 │ ├── columns: a:1!null b:2!null c:3!null 1327 │ ├── key: (1-3) 1328 │ └── ordering: +1,+2 1329 └── aggregations 1330 └── first-agg [as=c:3, outer=(3)] 1331 └── c:3 1332 1333 # The c,b part of the inner ordering can be ignored. 1334 opt 1335 SELECT * FROM (SELECT DISTINCT ON (b, c) a, b, c FROM abc ORDER BY c, b, a) ORDER BY a 1336 ---- 1337 distinct-on 1338 ├── columns: a:1!null b:2!null c:3!null 1339 ├── grouping columns: b:2!null c:3!null 1340 ├── internal-ordering: +1 opt(2,3) 1341 ├── key: (2,3) 1342 ├── fd: (2,3)-->(1) 1343 ├── ordering: +1 1344 ├── scan abc 1345 │ ├── columns: a:1!null b:2!null c:3!null 1346 │ ├── key: (1-3) 1347 │ └── ordering: +1 1348 └── aggregations 1349 └── first-agg [as=a:1, outer=(1)] 1350 └── a:1 1351 1352 # There is no ordering that satisfies both the intra-group ordering of c+ and the 1353 # inter-group ordering of a+; we have to sort twice. 1354 opt 1355 SELECT * FROM (SELECT DISTINCT ON (b) a, b, c FROM abc ORDER BY b, c) ORDER BY a 1356 ---- 1357 sort 1358 ├── columns: a:1!null b:2!null c:3!null 1359 ├── key: (2) 1360 ├── fd: (2)-->(1,3) 1361 ├── ordering: +1 1362 └── distinct-on 1363 ├── columns: a:1!null b:2!null c:3!null 1364 ├── grouping columns: b:2!null 1365 ├── internal-ordering: +3 opt(2) 1366 ├── key: (2) 1367 ├── fd: (2)-->(1,3) 1368 ├── sort 1369 │ ├── columns: a:1!null b:2!null c:3!null 1370 │ ├── key: (1-3) 1371 │ ├── ordering: +3 opt(2) [actual: +3] 1372 │ └── scan abc 1373 │ ├── columns: a:1!null b:2!null c:3!null 1374 │ └── key: (1-3) 1375 └── aggregations 1376 ├── first-agg [as=a:1, outer=(1)] 1377 │ └── a:1 1378 └── first-agg [as=c:3, outer=(3)] 1379 └── c:3 1380 1381 # Same as above, except we can use the index ordering for the distinct input. 1382 opt 1383 SELECT * FROM (SELECT DISTINCT ON (a) a, b, c FROM abc ORDER BY a, b) ORDER BY c 1384 ---- 1385 sort 1386 ├── columns: a:1!null b:2!null c:3!null 1387 ├── key: (1) 1388 ├── fd: (1)-->(2,3) 1389 ├── ordering: +3 1390 └── distinct-on 1391 ├── columns: a:1!null b:2!null c:3!null 1392 ├── grouping columns: a:1!null 1393 ├── internal-ordering: +1,+2 1394 ├── key: (1) 1395 ├── fd: (1)-->(2,3) 1396 ├── scan abc 1397 │ ├── columns: a:1!null b:2!null c:3!null 1398 │ ├── key: (1-3) 1399 │ └── ordering: +1,+2 1400 └── aggregations 1401 ├── first-agg [as=b:2, outer=(2)] 1402 │ └── b:2 1403 └── first-agg [as=c:3, outer=(3)] 1404 └── c:3 1405 1406 # Verify that we simplify the child ordering of DistinctOn. 1407 opt 1408 SELECT DISTINCT ON(a) a, b FROM abc WHERE a=c ORDER BY a 1409 ---- 1410 distinct-on 1411 ├── columns: a:1!null b:2!null 1412 ├── grouping columns: a:1!null 1413 ├── key: (1) 1414 ├── fd: (1)-->(2) 1415 ├── ordering: +1 1416 ├── select 1417 │ ├── columns: a:1!null b:2!null c:3!null 1418 │ ├── key: (2,3) 1419 │ ├── fd: (1)==(3), (3)==(1) 1420 │ ├── ordering: +(1|3) [actual: +1] 1421 │ ├── scan abc 1422 │ │ ├── columns: a:1!null b:2!null c:3!null 1423 │ │ ├── key: (1-3) 1424 │ │ └── ordering: +1 1425 │ └── filters 1426 │ └── a:1 = c:3 [outer=(1,3), fd=(1)==(3), (3)==(1)] 1427 └── aggregations 1428 └── first-agg [as=b:2, outer=(2)] 1429 └── b:2 1430 1431 # -------------------------------------------------- 1432 # Insert operator. 1433 # -------------------------------------------------- 1434 1435 # Verify that external ordering is passed through to input. 1436 opt 1437 SELECT * FROM [INSERT INTO abc SELECT * FROM xyz ORDER BY y, z LIMIT 2 RETURNING *] ORDER BY b 1438 ---- 1439 sort 1440 ├── columns: a:7!null b:8!null c:9!null 1441 ├── cardinality: [0 - 2] 1442 ├── volatile, side-effects, mutations 1443 ├── key: (7-9) 1444 ├── ordering: +8 1445 └── with &1 1446 ├── columns: a:7!null b:8!null c:9!null 1447 ├── cardinality: [0 - 2] 1448 ├── volatile, side-effects, mutations 1449 ├── key: (7-9) 1450 ├── insert abc 1451 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null 1452 │ ├── insert-mapping: 1453 │ │ ├── x:4 => abc.a:1 1454 │ │ ├── y:5 => abc.b:2 1455 │ │ └── z:6 => abc.c:3 1456 │ ├── cardinality: [0 - 2] 1457 │ ├── volatile, side-effects, mutations 1458 │ ├── key: (1-3) 1459 │ └── limit 1460 │ ├── columns: x:4!null y:5!null z:6!null 1461 │ ├── internal-ordering: +5,+6 1462 │ ├── cardinality: [0 - 2] 1463 │ ├── key: (4-6) 1464 │ ├── sort 1465 │ │ ├── columns: x:4!null y:5!null z:6!null 1466 │ │ ├── key: (4-6) 1467 │ │ ├── ordering: +5,+6 1468 │ │ ├── limit hint: 2.00 1469 │ │ └── scan xyz 1470 │ │ ├── columns: x:4!null y:5!null z:6!null 1471 │ │ └── key: (4-6) 1472 │ └── 2 1473 └── with-scan &1 1474 ├── columns: a:7!null b:8!null c:9!null 1475 ├── mapping: 1476 │ ├── abc.a:1 => a:7 1477 │ ├── abc.b:2 => b:8 1478 │ └── abc.c:3 => c:9 1479 ├── cardinality: [0 - 2] 1480 └── key: (7-9) 1481 1482 # Verify that provided orderings are derived correctly. 1483 opt 1484 SELECT * 1485 FROM [INSERT INTO xyz SELECT b, c, d FROM abcd ORDER BY c, d LIMIT 2 RETURNING *] 1486 ORDER BY y 1487 ---- 1488 sort 1489 ├── columns: x:9!null y:10!null z:11!null 1490 ├── cardinality: [0 - 2] 1491 ├── volatile, side-effects, mutations 1492 ├── ordering: +10 1493 └── with &1 1494 ├── columns: x:9!null y:10!null z:11!null 1495 ├── cardinality: [0 - 2] 1496 ├── volatile, side-effects, mutations 1497 ├── insert xyz 1498 │ ├── columns: xyz.x:1!null xyz.y:2!null xyz.z:3!null 1499 │ ├── insert-mapping: 1500 │ │ ├── b:5 => xyz.x:1 1501 │ │ ├── c:6 => xyz.y:2 1502 │ │ └── d:7 => xyz.z:3 1503 │ ├── cardinality: [0 - 2] 1504 │ ├── volatile, side-effects, mutations 1505 │ └── scan abcd@cd 1506 │ ├── columns: b:5 c:6 d:7 1507 │ └── limit: 2 1508 └── with-scan &1 1509 ├── columns: x:9!null y:10!null z:11!null 1510 ├── mapping: 1511 │ ├── xyz.x:1 => x:9 1512 │ ├── xyz.y:2 => y:10 1513 │ └── xyz.z:3 => z:11 1514 └── cardinality: [0 - 2] 1515 1516 # Verify that provided orderings are derived correctly with equivalence FD. 1517 # TODO(radu): Use interesting orderings to get rid of top-level sort. 1518 opt 1519 SELECT * 1520 FROM [INSERT INTO xyz SELECT b, c, d FROM abcd ORDER BY c, d LIMIT 2 RETURNING *] 1521 WHERE x=y 1522 ORDER BY y 1523 ---- 1524 sort 1525 ├── columns: x:9!null y:10!null z:11!null 1526 ├── cardinality: [0 - 2] 1527 ├── volatile, side-effects, mutations 1528 ├── fd: (9)==(10), (10)==(9) 1529 ├── ordering: +(9|10) [actual: +9] 1530 └── with &1 1531 ├── columns: x:9!null y:10!null z:11!null 1532 ├── cardinality: [0 - 2] 1533 ├── volatile, side-effects, mutations 1534 ├── fd: (9)==(10), (10)==(9) 1535 ├── insert xyz 1536 │ ├── columns: xyz.x:1!null xyz.y:2!null xyz.z:3!null 1537 │ ├── insert-mapping: 1538 │ │ ├── b:5 => xyz.x:1 1539 │ │ ├── c:6 => xyz.y:2 1540 │ │ └── d:7 => xyz.z:3 1541 │ ├── cardinality: [0 - 2] 1542 │ ├── volatile, side-effects, mutations 1543 │ └── scan abcd@cd 1544 │ ├── columns: b:5 c:6 d:7 1545 │ └── limit: 2 1546 └── select 1547 ├── columns: x:9!null y:10!null z:11!null 1548 ├── cardinality: [0 - 2] 1549 ├── fd: (9)==(10), (10)==(9) 1550 ├── with-scan &1 1551 │ ├── columns: x:9!null y:10!null z:11!null 1552 │ ├── mapping: 1553 │ │ ├── xyz.x:1 => x:9 1554 │ │ ├── xyz.y:2 => y:10 1555 │ │ └── xyz.z:3 => z:11 1556 │ └── cardinality: [0 - 2] 1557 └── filters 1558 └── x:9 = y:10 [outer=(9,10), fd=(9)==(10), (10)==(9)] 1559 1560 # Ignore internal ordering. 1561 opt 1562 SELECT * FROM [INSERT INTO abc SELECT * FROM xyz ORDER BY y, z RETURNING *] 1563 ---- 1564 with &1 1565 ├── columns: a:7!null b:8!null c:9!null 1566 ├── volatile, side-effects, mutations 1567 ├── key: (7-9) 1568 ├── insert abc 1569 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null 1570 │ ├── insert-mapping: 1571 │ │ ├── x:4 => abc.a:1 1572 │ │ ├── y:5 => abc.b:2 1573 │ │ └── z:6 => abc.c:3 1574 │ ├── volatile, side-effects, mutations 1575 │ ├── key: (1-3) 1576 │ └── scan xyz 1577 │ ├── columns: x:4!null y:5!null z:6!null 1578 │ └── key: (4-6) 1579 └── with-scan &1 1580 ├── columns: a:7!null b:8!null c:9!null 1581 ├── mapping: 1582 │ ├── abc.a:1 => a:7 1583 │ ├── abc.b:2 => b:8 1584 │ └── abc.c:3 => c:9 1585 └── key: (7-9) 1586 1587 # -------------------------------------------------- 1588 # Update operator. 1589 # -------------------------------------------------- 1590 1591 # Verify that the external ordering is passed through to input. 1592 opt 1593 SELECT * FROM [UPDATE abcd SET (a, b)=(1, 2) RETURNING *] ORDER BY c 1594 ---- 1595 sort 1596 ├── columns: a:13!null b:14!null c:15 d:16 1597 ├── volatile, side-effects, mutations 1598 ├── fd: ()-->(13,14) 1599 ├── ordering: +15 opt(13,14) [actual: +15] 1600 └── with &1 1601 ├── columns: a:13!null b:14!null c:15 d:16 1602 ├── volatile, side-effects, mutations 1603 ├── fd: ()-->(13,14) 1604 ├── project 1605 │ ├── columns: abcd.a:1!null abcd.b:2!null abcd.c:3 abcd.d:4 1606 │ ├── volatile, side-effects, mutations 1607 │ ├── fd: ()-->(1,2) 1608 │ └── update abcd 1609 │ ├── columns: abcd.a:1!null abcd.b:2!null abcd.c:3 abcd.d:4 rowid:5!null 1610 │ ├── fetch columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10 1611 │ ├── update-mapping: 1612 │ │ ├── a_new:11 => abcd.a:1 1613 │ │ └── b_new:12 => abcd.b:2 1614 │ ├── volatile, side-effects, mutations 1615 │ ├── key: (5) 1616 │ ├── fd: ()-->(1,2), (5)-->(3,4) 1617 │ └── project 1618 │ ├── columns: a_new:11!null b_new:12!null abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1619 │ ├── key: (10) 1620 │ ├── fd: ()-->(11,12), (10)-->(6-9) 1621 │ ├── scan abcd 1622 │ │ ├── columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1623 │ │ ├── key: (10) 1624 │ │ └── fd: (10)-->(6-9) 1625 │ └── projections 1626 │ ├── 1 [as=a_new:11] 1627 │ └── 2 [as=b_new:12] 1628 └── with-scan &1 1629 ├── columns: a:13!null b:14!null c:15 d:16 1630 ├── mapping: 1631 │ ├── abcd.a:1 => a:13 1632 │ ├── abcd.b:2 => b:14 1633 │ ├── abcd.c:3 => c:15 1634 │ └── abcd.d:4 => d:16 1635 └── fd: ()-->(13,14) 1636 1637 # Verify that provided orderings are derived correctly. 1638 opt 1639 SELECT * 1640 FROM [UPDATE abcd SET b=b+1 ORDER BY c LIMIT 10 RETURNING *] 1641 ORDER BY c, d 1642 ---- 1643 sort 1644 ├── columns: a:12 b:13 c:14 d:15 1645 ├── cardinality: [0 - 10] 1646 ├── volatile, side-effects, mutations 1647 ├── ordering: +14,+15 1648 └── with &1 1649 ├── columns: a:12 b:13 c:14 d:15 1650 ├── cardinality: [0 - 10] 1651 ├── volatile, side-effects, mutations 1652 ├── project 1653 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 1654 │ ├── cardinality: [0 - 10] 1655 │ ├── volatile, side-effects, mutations 1656 │ └── update abcd 1657 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 rowid:5!null 1658 │ ├── fetch columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10 1659 │ ├── update-mapping: 1660 │ │ └── b_new:11 => abcd.b:2 1661 │ ├── cardinality: [0 - 10] 1662 │ ├── volatile, side-effects, mutations 1663 │ ├── key: (5) 1664 │ ├── fd: (5)-->(1-4) 1665 │ └── project 1666 │ ├── columns: b_new:11 abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1667 │ ├── cardinality: [0 - 10] 1668 │ ├── key: (10) 1669 │ ├── fd: (10)-->(6-9), (7)-->(11) 1670 │ ├── scan abcd@cd 1671 │ │ ├── columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1672 │ │ ├── limit: 10 1673 │ │ ├── key: (10) 1674 │ │ └── fd: (10)-->(6-9) 1675 │ └── projections 1676 │ └── abcd.b:7 + 1 [as=b_new:11, outer=(7)] 1677 └── with-scan &1 1678 ├── columns: a:12 b:13 c:14 d:15 1679 ├── mapping: 1680 │ ├── abcd.a:1 => a:12 1681 │ ├── abcd.b:2 => b:13 1682 │ ├── abcd.c:3 => c:14 1683 │ └── abcd.d:4 => d:15 1684 └── cardinality: [0 - 10] 1685 1686 # Verify that provided orderings are derived correctly with equivalence FD. 1687 # TODO(radu): Use interesting orderings to get rid of top-level sort. 1688 opt 1689 SELECT * 1690 FROM [UPDATE abcd SET b=b+1 ORDER BY c, d LIMIT 10 RETURNING *] 1691 WHERE b=c 1692 ORDER BY b, d 1693 ---- 1694 sort 1695 ├── columns: a:12 b:13!null c:14!null d:15 1696 ├── cardinality: [0 - 10] 1697 ├── volatile, side-effects, mutations 1698 ├── fd: (13)==(14), (14)==(13) 1699 ├── ordering: +(13|14),+15 [actual: +13,+15] 1700 └── with &1 1701 ├── columns: a:12 b:13!null c:14!null d:15 1702 ├── cardinality: [0 - 10] 1703 ├── volatile, side-effects, mutations 1704 ├── fd: (13)==(14), (14)==(13) 1705 ├── project 1706 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 1707 │ ├── cardinality: [0 - 10] 1708 │ ├── volatile, side-effects, mutations 1709 │ └── update abcd 1710 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 rowid:5!null 1711 │ ├── fetch columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10 1712 │ ├── update-mapping: 1713 │ │ └── b_new:11 => abcd.b:2 1714 │ ├── cardinality: [0 - 10] 1715 │ ├── volatile, side-effects, mutations 1716 │ ├── key: (5) 1717 │ ├── fd: (5)-->(1-4) 1718 │ └── project 1719 │ ├── columns: b_new:11 abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1720 │ ├── cardinality: [0 - 10] 1721 │ ├── key: (10) 1722 │ ├── fd: (10)-->(6-9), (7)-->(11) 1723 │ ├── scan abcd@cd 1724 │ │ ├── columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1725 │ │ ├── limit: 10 1726 │ │ ├── key: (10) 1727 │ │ └── fd: (10)-->(6-9) 1728 │ └── projections 1729 │ └── abcd.b:7 + 1 [as=b_new:11, outer=(7)] 1730 └── select 1731 ├── columns: a:12 b:13!null c:14!null d:15 1732 ├── cardinality: [0 - 10] 1733 ├── fd: (13)==(14), (14)==(13) 1734 ├── with-scan &1 1735 │ ├── columns: a:12 b:13 c:14 d:15 1736 │ ├── mapping: 1737 │ │ ├── abcd.a:1 => a:12 1738 │ │ ├── abcd.b:2 => b:13 1739 │ │ ├── abcd.c:3 => c:14 1740 │ │ └── abcd.d:4 => d:15 1741 │ └── cardinality: [0 - 10] 1742 └── filters 1743 └── b:13 = c:14 [outer=(13,14), fd=(13)==(14), (14)==(13)] 1744 1745 # -------------------------------------------------- 1746 # Upsert operator. 1747 # -------------------------------------------------- 1748 1749 # Verify that no ordering is provided once ON CONFLICT clause is added. 1750 opt 1751 SELECT * 1752 FROM 1753 [ 1754 INSERT INTO abc 1755 SELECT * FROM xyz ORDER BY y, z LIMIT 2 1756 ON CONFLICT (a, b, c) 1757 DO UPDATE SET a=10 1758 RETURNING * 1759 ] 1760 ORDER BY b 1761 ---- 1762 sort 1763 ├── columns: a:14!null b:15!null c:16!null 1764 ├── volatile, side-effects, mutations 1765 ├── ordering: +15 1766 └── with &1 1767 ├── columns: a:14!null b:15!null c:16!null 1768 ├── volatile, side-effects, mutations 1769 ├── upsert abc 1770 │ ├── columns: abc.a:1!null abc.b:2!null abc.c:3!null 1771 │ ├── canary column: 7 1772 │ ├── fetch columns: abc.a:7 abc.b:8 abc.c:9 1773 │ ├── insert-mapping: 1774 │ │ ├── x:4 => abc.a:1 1775 │ │ ├── y:5 => abc.b:2 1776 │ │ └── z:6 => abc.c:3 1777 │ ├── update-mapping: 1778 │ │ └── upsert_a:11 => abc.a:1 1779 │ ├── return-mapping: 1780 │ │ ├── upsert_a:11 => abc.a:1 1781 │ │ ├── upsert_b:12 => abc.b:2 1782 │ │ └── upsert_c:13 => abc.c:3 1783 │ ├── volatile, side-effects, mutations 1784 │ └── project 1785 │ ├── columns: upsert_a:11!null upsert_b:12 upsert_c:13 x:4!null y:5!null z:6!null abc.a:7 abc.b:8 abc.c:9 1786 │ ├── key: (4-6) 1787 │ ├── fd: (4-6)-->(7-9), (4,7)-->(11), (5,7,8)-->(12), (6,7,9)-->(13) 1788 │ ├── left-join (lookup abc) 1789 │ │ ├── columns: x:4!null y:5!null z:6!null abc.a:7 abc.b:8 abc.c:9 1790 │ │ ├── key columns: [4 5 6] = [7 8 9] 1791 │ │ ├── lookup columns are key 1792 │ │ ├── key: (4-6) 1793 │ │ ├── fd: (4-6)-->(7-9) 1794 │ │ ├── ensure-upsert-distinct-on 1795 │ │ │ ├── columns: x:4!null y:5!null z:6!null 1796 │ │ │ ├── grouping columns: x:4!null y:5!null z:6!null 1797 │ │ │ ├── internal-ordering: +5,+6 1798 │ │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1799 │ │ │ ├── cardinality: [0 - 2] 1800 │ │ │ ├── key: (4-6) 1801 │ │ │ └── limit 1802 │ │ │ ├── columns: x:4!null y:5!null z:6!null 1803 │ │ │ ├── internal-ordering: +5,+6 1804 │ │ │ ├── cardinality: [0 - 2] 1805 │ │ │ ├── key: (4-6) 1806 │ │ │ ├── ordering: +5,+6 1807 │ │ │ ├── sort 1808 │ │ │ │ ├── columns: x:4!null y:5!null z:6!null 1809 │ │ │ │ ├── key: (4-6) 1810 │ │ │ │ ├── ordering: +5,+6 1811 │ │ │ │ ├── limit hint: 2.00 1812 │ │ │ │ └── scan xyz 1813 │ │ │ │ ├── columns: x:4!null y:5!null z:6!null 1814 │ │ │ │ └── key: (4-6) 1815 │ │ │ └── 2 1816 │ │ └── filters (true) 1817 │ └── projections 1818 │ ├── CASE WHEN abc.a:7 IS NULL THEN x:4 ELSE 10 END [as=upsert_a:11, outer=(4,7)] 1819 │ ├── CASE WHEN abc.a:7 IS NULL THEN y:5 ELSE abc.b:8 END [as=upsert_b:12, outer=(5,7,8)] 1820 │ └── CASE WHEN abc.a:7 IS NULL THEN z:6 ELSE abc.c:9 END [as=upsert_c:13, outer=(6,7,9)] 1821 └── with-scan &1 1822 ├── columns: a:14!null b:15!null c:16!null 1823 └── mapping: 1824 ├── abc.a:1 => a:14 1825 ├── abc.b:2 => b:15 1826 └── abc.c:3 => c:16 1827 1828 # -------------------------------------------------- 1829 # Delete operator. 1830 # -------------------------------------------------- 1831 1832 # Verify that the external ordering is passed through to input. 1833 opt 1834 SELECT * FROM [DELETE FROM abcd RETURNING *] ORDER BY c 1835 ---- 1836 sort 1837 ├── columns: a:11 b:12 c:13 d:14 1838 ├── volatile, side-effects, mutations 1839 ├── ordering: +13 1840 └── with &1 1841 ├── columns: a:11 b:12 c:13 d:14 1842 ├── volatile, side-effects, mutations 1843 ├── project 1844 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 1845 │ ├── volatile, side-effects, mutations 1846 │ └── delete abcd 1847 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 rowid:5!null 1848 │ ├── fetch columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10 1849 │ ├── volatile, side-effects, mutations 1850 │ ├── key: (5) 1851 │ ├── fd: (5)-->(1-4) 1852 │ └── scan abcd 1853 │ ├── columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1854 │ ├── key: (10) 1855 │ └── fd: (10)-->(6-9) 1856 └── with-scan &1 1857 ├── columns: a:11 b:12 c:13 d:14 1858 └── mapping: 1859 ├── abcd.a:1 => a:11 1860 ├── abcd.b:2 => b:12 1861 ├── abcd.c:3 => c:13 1862 └── abcd.d:4 => d:14 1863 1864 # Verify that provided orderings are derived correctly. 1865 opt 1866 SELECT * 1867 FROM [DELETE FROM abcd ORDER BY c LIMIT 10 RETURNING *] 1868 ORDER BY c, d 1869 ---- 1870 sort 1871 ├── columns: a:11 b:12 c:13 d:14 1872 ├── cardinality: [0 - 10] 1873 ├── volatile, side-effects, mutations 1874 ├── ordering: +13,+14 1875 └── with &1 1876 ├── columns: a:11 b:12 c:13 d:14 1877 ├── cardinality: [0 - 10] 1878 ├── volatile, side-effects, mutations 1879 ├── project 1880 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 1881 │ ├── cardinality: [0 - 10] 1882 │ ├── volatile, side-effects, mutations 1883 │ └── delete abcd 1884 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 rowid:5!null 1885 │ ├── fetch columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10 1886 │ ├── cardinality: [0 - 10] 1887 │ ├── volatile, side-effects, mutations 1888 │ ├── key: (5) 1889 │ ├── fd: (5)-->(1-4) 1890 │ └── scan abcd@cd 1891 │ ├── columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1892 │ ├── limit: 10 1893 │ ├── key: (10) 1894 │ └── fd: (10)-->(6-9) 1895 └── with-scan &1 1896 ├── columns: a:11 b:12 c:13 d:14 1897 ├── mapping: 1898 │ ├── abcd.a:1 => a:11 1899 │ ├── abcd.b:2 => b:12 1900 │ ├── abcd.c:3 => c:13 1901 │ └── abcd.d:4 => d:14 1902 └── cardinality: [0 - 10] 1903 1904 # Verify that provided orderings are derived correctly with equivalence FD. 1905 # TODO(radu): Use interesting orderings to get rid of top-level sort. 1906 opt 1907 SELECT * 1908 FROM [DELETE FROM abcd ORDER BY c, d LIMIT 10 RETURNING *] 1909 WHERE b=c 1910 ORDER BY b, d 1911 ---- 1912 sort 1913 ├── columns: a:11 b:12!null c:13!null d:14 1914 ├── cardinality: [0 - 10] 1915 ├── volatile, side-effects, mutations 1916 ├── fd: (12)==(13), (13)==(12) 1917 ├── ordering: +(12|13),+14 [actual: +12,+14] 1918 └── with &1 1919 ├── columns: a:11 b:12!null c:13!null d:14 1920 ├── cardinality: [0 - 10] 1921 ├── volatile, side-effects, mutations 1922 ├── fd: (12)==(13), (13)==(12) 1923 ├── project 1924 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 1925 │ ├── cardinality: [0 - 10] 1926 │ ├── volatile, side-effects, mutations 1927 │ └── delete abcd 1928 │ ├── columns: abcd.a:1 abcd.b:2 abcd.c:3 abcd.d:4 rowid:5!null 1929 │ ├── fetch columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10 1930 │ ├── cardinality: [0 - 10] 1931 │ ├── volatile, side-effects, mutations 1932 │ ├── key: (5) 1933 │ ├── fd: (5)-->(1-4) 1934 │ └── scan abcd@cd 1935 │ ├── columns: abcd.a:6 abcd.b:7 abcd.c:8 abcd.d:9 rowid:10!null 1936 │ ├── limit: 10 1937 │ ├── key: (10) 1938 │ └── fd: (10)-->(6-9) 1939 └── select 1940 ├── columns: a:11 b:12!null c:13!null d:14 1941 ├── cardinality: [0 - 10] 1942 ├── fd: (12)==(13), (13)==(12) 1943 ├── with-scan &1 1944 │ ├── columns: a:11 b:12 c:13 d:14 1945 │ ├── mapping: 1946 │ │ ├── abcd.a:1 => a:11 1947 │ │ ├── abcd.b:2 => b:12 1948 │ │ ├── abcd.c:3 => c:13 1949 │ │ └── abcd.d:4 => d:14 1950 │ └── cardinality: [0 - 10] 1951 └── filters 1952 └── b:12 = c:13 [outer=(12,13), fd=(12)==(13), (13)==(12)] 1953 1954 1955 # Regression test for #36219: lookup join with ON condition that imposes an 1956 # equality on two input columns (which isn't pushed down). 1957 opt disable=(PushFilterIntoJoinLeftAndRight,PushFilterIntoJoinLeft,PushFilterIntoJoinRight,MapFilterIntoJoinLeft,MapFilterIntoJoinRight) 1958 SELECT * FROM abc JOIN xyz ON a=x AND x=z ORDER BY z 1959 ---- 1960 inner-join (merge) 1961 ├── columns: a:1!null b:2!null c:3!null x:4!null y:5!null z:6!null 1962 ├── left ordering: +1 1963 ├── right ordering: +4 1964 ├── key: (2,3,5,6) 1965 ├── fd: (1)==(4,6), (4)==(1,6), (6)==(1,4) 1966 ├── ordering: +(1|4|6) [actual: +1] 1967 ├── scan abc 1968 │ ├── columns: a:1!null b:2!null c:3!null 1969 │ ├── key: (1-3) 1970 │ └── ordering: +1 1971 ├── scan xyz 1972 │ ├── columns: x:4!null y:5!null z:6!null 1973 │ ├── key: (4-6) 1974 │ └── ordering: +4 1975 └── filters 1976 └── x:4 = z:6 [outer=(4,6), fd=(4)==(6), (6)==(4)] 1977 1978 # TODO(justin): figure out when it is that window functions can preserve their 1979 # input ordering. 1980 opt 1981 SELECT *, row_number() OVER() FROM abc ORDER BY a 1982 ---- 1983 sort 1984 ├── columns: a:1!null b:2!null c:3!null row_number:4 1985 ├── key: (1-3) 1986 ├── ordering: +1 1987 └── window partition=() 1988 ├── columns: a:1!null b:2!null c:3!null row_number:4 1989 ├── key: (1-3) 1990 ├── scan abc 1991 │ ├── columns: a:1!null b:2!null c:3!null 1992 │ └── key: (1-3) 1993 └── windows 1994 └── row-number [as=row_number:4] 1995 1996 # Regression test for #44469 (DistinctOn needs to remap the provided ordering). 1997 exec-ddl 1998 CREATE TABLE t44469_a (a INT, INDEX (a)) 1999 ---- 2000 2001 exec-ddl 2002 CREATE TABLE t44469_b (b INT, INDEX (b)) 2003 ---- 2004 2005 exec-ddl 2006 CREATE TABLE t44469_cd (c INT, d INT, INDEX (c, d)); 2007 ---- 2008 2009 opt 2010 SELECT DISTINCT ON (b) b 2011 FROM t44469_a INNER LOOKUP JOIN t44469_b ON a = b INNER LOOKUP JOIN t44469_cd ON c = 1 AND d = a 2012 ORDER BY b 2013 ---- 2014 distinct-on 2015 ├── columns: b:3!null 2016 ├── grouping columns: b:3!null 2017 ├── key: (3) 2018 ├── ordering: +3 2019 └── inner-join (lookup t44469_cd@secondary) 2020 ├── columns: a:1!null b:3!null c:5!null d:6!null 2021 ├── flags: force lookup join (into right side) 2022 ├── key columns: [8 1] = [5 6] 2023 ├── fd: ()-->(5), (1)==(3,6), (3)==(1,6), (6)==(1,3) 2024 ├── ordering: +(1|3|6) opt(5) [actual: +1] 2025 ├── project 2026 │ ├── columns: "project_const_col_@5":8!null a:1!null b:3!null 2027 │ ├── fd: ()-->(8), (1)==(3), (3)==(1) 2028 │ ├── ordering: +(1|3) [actual: +1] 2029 │ ├── inner-join (lookup t44469_b@secondary) 2030 │ │ ├── columns: a:1!null b:3!null 2031 │ │ ├── flags: force lookup join (into right side) 2032 │ │ ├── key columns: [1] = [3] 2033 │ │ ├── fd: (1)==(3), (3)==(1) 2034 │ │ ├── ordering: +(1|3) [actual: +1] 2035 │ │ ├── scan t44469_a@secondary 2036 │ │ │ ├── columns: a:1 2037 │ │ │ └── ordering: +1 2038 │ │ └── filters (true) 2039 │ └── projections 2040 │ └── 1 [as="project_const_col_@5":8] 2041 └── filters (true) 2042 2043 # Regression test for #47041: factor check constraints into the (canonical) 2044 # scan FDs; otherwise operators above won't always be able to remap a provided 2045 # ordering. 2046 exec-ddl 2047 CREATE TABLE t47041 ( 2048 k INT8 PRIMARY KEY, 2049 a INT8 NOT NULL, 2050 b INT8 NOT NULL CHECK (b = 0), 2051 INDEX ba (b, a) 2052 ) 2053 ---- 2054 2055 opt 2056 SELECT 1 FROM t47041 WHERE a > 1 AND k > 0 GROUP BY b, a ORDER BY b 2057 ---- 2058 project 2059 ├── columns: "?column?":4!null 2060 ├── fd: ()-->(4) 2061 ├── distinct-on 2062 │ ├── columns: a:2!null 2063 │ ├── grouping columns: a:2!null 2064 │ ├── key: (2) 2065 │ └── select 2066 │ ├── columns: k:1!null a:2!null 2067 │ ├── key: (1) 2068 │ ├── fd: (1)-->(2) 2069 │ ├── scan t47041@ba 2070 │ │ ├── columns: k:1!null a:2!null 2071 │ │ ├── constraint: /3/2/1: [/0/2/1 - /0] 2072 │ │ ├── key: (1) 2073 │ │ └── fd: (1)-->(2) 2074 │ └── filters 2075 │ └── k:1 > 0 [outer=(1)] 2076 └── projections 2077 └── 1 [as="?column?":4]