github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/rules/groupby (about) 1 exec-ddl 2 CREATE TABLE abc ( 3 a CHAR PRIMARY KEY, 4 b FLOAT, 5 c BOOLEAN, 6 d DECIMAL 7 ) 8 ---- 9 10 exec-ddl 11 CREATE TABLE xyz ( 12 x INT PRIMARY KEY, 13 y INT, 14 z FLOAT, 15 INDEX xy (x, y), 16 INDEX zyx (z, y, x), 17 INDEX yy (y) 18 ) 19 ---- 20 21 exec-ddl 22 CREATE TABLE kuvw ( 23 k INT PRIMARY KEY, 24 u INT, 25 v INT, 26 w INT, 27 28 INDEX uvw(u,v,w), 29 INDEX wvu(w,v,u), 30 INDEX vw(v,w) STORING (u), 31 INDEX w(w) STORING (u,v) 32 ) 33 ---- 34 35 # -------------------------------------------------- 36 # ReplaceScalarMinMaxWithLimit (Min variations) 37 # -------------------------------------------------- 38 39 opt 40 SELECT min(a) FROM abc 41 ---- 42 scalar-group-by 43 ├── columns: min:5 44 ├── cardinality: [1 - 1] 45 ├── key: () 46 ├── fd: ()-->(5) 47 ├── scan abc 48 │ ├── columns: a:1!null 49 │ ├── limit: 1 50 │ ├── key: () 51 │ └── fd: ()-->(1) 52 └── aggregations 53 └── const-agg [as=min:5, outer=(1)] 54 └── a:1 55 56 # Verify the rule still fires even if DISTINCT is used. 57 opt 58 SELECT min(DISTINCT a) FROM abc 59 ---- 60 scalar-group-by 61 ├── columns: min:5 62 ├── cardinality: [1 - 1] 63 ├── key: () 64 ├── fd: ()-->(5) 65 ├── scan abc 66 │ ├── columns: a:1!null 67 │ ├── limit: 1 68 │ ├── key: () 69 │ └── fd: ()-->(1) 70 └── aggregations 71 └── const-agg [as=min:5, outer=(1)] 72 └── a:1 73 74 # Verify the rule does not fire when FILTER is used. 75 opt 76 SELECT min(a) FILTER (WHERE a > 'a') FROM abc 77 ---- 78 scalar-group-by 79 ├── columns: min:6 80 ├── cardinality: [1 - 1] 81 ├── key: () 82 ├── fd: ()-->(6) 83 ├── scan abc 84 │ ├── columns: a:1!null 85 │ ├── constraint: /1: [/e'a\x00' - ] 86 │ ├── limit: 1 87 │ ├── key: () 88 │ └── fd: ()-->(1) 89 └── aggregations 90 └── const-agg [as=min:6, outer=(1)] 91 └── a:1 92 93 opt 94 SELECT min(b) FROM abc 95 ---- 96 scalar-group-by 97 ├── columns: min:5 98 ├── cardinality: [1 - 1] 99 ├── key: () 100 ├── fd: ()-->(5) 101 ├── scan abc 102 │ └── columns: b:2 103 └── aggregations 104 └── min [as=min:5, outer=(2)] 105 └── b:2 106 107 opt 108 SELECT min(y) FROM xyz where z=7 109 ---- 110 scalar-group-by 111 ├── columns: min:4 112 ├── cardinality: [1 - 1] 113 ├── key: () 114 ├── fd: ()-->(4) 115 ├── scan xyz@zyx 116 │ ├── columns: y:2!null z:3!null 117 │ ├── constraint: /3/2/1: (/7.0/NULL - /7.0] 118 │ ├── limit: 1 119 │ ├── key: () 120 │ └── fd: ()-->(2,3) 121 └── aggregations 122 └── const-agg [as=min:4, outer=(2)] 123 └── y:2 124 125 # ReplaceScalarMaxWithLimit has the same behavior with max() as 126 # the previous min() query because z is the prefix of a unique key 127 opt 128 SELECT max(y) FROM xyz where z=7 129 ---- 130 scalar-group-by 131 ├── columns: max:4 132 ├── cardinality: [1 - 1] 133 ├── key: () 134 ├── fd: ()-->(4) 135 ├── scan xyz@zyx,rev 136 │ ├── columns: y:2!null z:3!null 137 │ ├── constraint: /3/2/1: (/7.0/NULL - /7.0] 138 │ ├── limit: 1(rev) 139 │ ├── key: () 140 │ └── fd: ()-->(2,3) 141 └── aggregations 142 └── const-agg [as=max:4, outer=(2)] 143 └── y:2 144 145 # We expect ReplaceScalarMinWithLimit not to be preferred here. 146 # This is because we know nothing about the ordering of y 147 # on the index xy after a scan on xy with x>7. 148 opt 149 SELECT min(y) FROM xyz@xy WHERE x>7 150 ---- 151 scalar-group-by 152 ├── columns: min:4 153 ├── cardinality: [1 - 1] 154 ├── key: () 155 ├── fd: ()-->(4) 156 ├── scan xyz@xy 157 │ ├── columns: x:1!null y:2 158 │ ├── constraint: /1/2: [/8 - ] 159 │ ├── flags: force-index=xy 160 │ ├── key: (1) 161 │ └── fd: (1)-->(2) 162 └── aggregations 163 └── min [as=min:4, outer=(2)] 164 └── y:2 165 166 # We expect ReplaceMaxWithLimit not to be preferred here. 167 # This is because we know nothing about the ordering of y 168 # on the index xy after a scan on xy with x>7 169 opt 170 SELECT max(y) FROM xyz@xy WHERE x>7 171 ---- 172 scalar-group-by 173 ├── columns: max:4 174 ├── cardinality: [1 - 1] 175 ├── key: () 176 ├── fd: ()-->(4) 177 ├── scan xyz@xy 178 │ ├── columns: x:1!null y:2 179 │ ├── constraint: /1/2: [/8 - ] 180 │ ├── flags: force-index=xy 181 │ ├── key: (1) 182 │ └── fd: (1)-->(2) 183 └── aggregations 184 └── max [as=max:4, outer=(2)] 185 └── y:2 186 187 opt 188 SELECT max(x) FROM xyz 189 ---- 190 scalar-group-by 191 ├── columns: max:4 192 ├── cardinality: [1 - 1] 193 ├── key: () 194 ├── fd: ()-->(4) 195 ├── scan xyz@xy,rev 196 │ ├── columns: x:1!null 197 │ ├── limit: 1(rev) 198 │ ├── key: () 199 │ └── fd: ()-->(1) 200 └── aggregations 201 └── const-agg [as=max:4, outer=(1)] 202 └── x:1 203 204 opt 205 SELECT min(x) FROM xyz 206 ---- 207 scalar-group-by 208 ├── columns: min:4 209 ├── cardinality: [1 - 1] 210 ├── key: () 211 ├── fd: ()-->(4) 212 ├── scan xyz@xy 213 │ ├── columns: x:1!null 214 │ ├── limit: 1 215 │ ├── key: () 216 │ └── fd: ()-->(1) 217 └── aggregations 218 └── const-agg [as=min:4, outer=(1)] 219 └── x:1 220 221 opt 222 SELECT min(x) FROM xyz WHERE x in (0, 4, 7) 223 ---- 224 scalar-group-by 225 ├── columns: min:4 226 ├── cardinality: [1 - 1] 227 ├── key: () 228 ├── fd: ()-->(4) 229 ├── scan xyz@xy 230 │ ├── columns: x:1!null 231 │ ├── constraint: /1/2 232 │ │ ├── [/0 - /0] 233 │ │ ├── [/4 - /4] 234 │ │ └── [/7 - /7] 235 │ ├── limit: 1 236 │ ├── key: () 237 │ └── fd: ()-->(1) 238 └── aggregations 239 └── const-agg [as=min:4, outer=(1)] 240 └── x:1 241 242 opt 243 SELECT max(x) FROM xyz WHERE x in (0, 4, 7) 244 ---- 245 scalar-group-by 246 ├── columns: max:4 247 ├── cardinality: [1 - 1] 248 ├── key: () 249 ├── fd: ()-->(4) 250 ├── scan xyz@xy,rev 251 │ ├── columns: x:1!null 252 │ ├── constraint: /1/2 253 │ │ ├── [/0 - /0] 254 │ │ ├── [/4 - /4] 255 │ │ └── [/7 - /7] 256 │ ├── limit: 1(rev) 257 │ ├── key: () 258 │ └── fd: ()-->(1) 259 └── aggregations 260 └── const-agg [as=max:4, outer=(1)] 261 └── x:1 262 263 opt 264 SELECT min(y) FROM xyz 265 ---- 266 scalar-group-by 267 ├── columns: min:4 268 ├── cardinality: [1 - 1] 269 ├── key: () 270 ├── fd: ()-->(4) 271 ├── scan xyz@yy 272 │ ├── columns: y:2!null 273 │ ├── constraint: /2/1: (/NULL - ] 274 │ ├── limit: 1 275 │ ├── key: () 276 │ └── fd: ()-->(2) 277 └── aggregations 278 └── const-agg [as=min:4, outer=(2)] 279 └── y:2 280 281 opt 282 SELECT min(y), min(y) FROM xyz 283 ---- 284 scalar-group-by 285 ├── columns: min:4 min:4 286 ├── cardinality: [1 - 1] 287 ├── key: () 288 ├── fd: ()-->(4) 289 ├── scan xyz@yy 290 │ ├── columns: y:2!null 291 │ ├── constraint: /2/1: (/NULL - ] 292 │ ├── limit: 1 293 │ ├── key: () 294 │ └── fd: ()-->(2) 295 └── aggregations 296 └── const-agg [as=min:4, outer=(2)] 297 └── y:2 298 299 # ReplaceScalarMinWithLimit does not apply when there is 300 # a grouping column 301 opt 302 SELECT min(y) FROM xyz GROUP BY y 303 ---- 304 project 305 ├── columns: min:4 306 └── group-by 307 ├── columns: y:2 min:4 308 ├── grouping columns: y:2 309 ├── internal-ordering: +2 310 ├── key: (2) 311 ├── fd: (2)-->(4) 312 ├── scan xyz@yy 313 │ ├── columns: y:2 314 │ └── ordering: +2 315 └── aggregations 316 └── min [as=min:4, outer=(2)] 317 └── y:2 318 319 # ReplaceScalarMaxWithLimit does not apply when there is 320 # a grouping column 321 opt 322 SELECT max(y) FROM xyz GROUP BY y 323 ---- 324 project 325 ├── columns: max:4 326 └── group-by 327 ├── columns: y:2 max:4 328 ├── grouping columns: y:2 329 ├── internal-ordering: +2 330 ├── key: (2) 331 ├── fd: (2)-->(4) 332 ├── scan xyz@yy 333 │ ├── columns: y:2 334 │ └── ordering: +2 335 └── aggregations 336 └── max [as=max:4, outer=(2)] 337 └── y:2 338 339 # ReplaceScalarMinWithLimit does not apply when there is 340 # a grouping column 341 opt 342 SELECT min(y) FROM xyz GROUP BY x 343 ---- 344 project 345 ├── columns: min:4 346 └── group-by 347 ├── columns: x:1!null min:4 348 ├── grouping columns: x:1!null 349 ├── internal-ordering: +1 350 ├── key: (1) 351 ├── fd: (1)-->(4) 352 ├── scan xyz@xy 353 │ ├── columns: x:1!null y:2 354 │ ├── key: (1) 355 │ ├── fd: (1)-->(2) 356 │ └── ordering: +1 357 └── aggregations 358 └── min [as=min:4, outer=(2)] 359 └── y:2 360 361 # ReplaceScalarMinWithLimit does not apply on multiple aggregations 362 # on different columns 363 opt 364 SELECT min(y), min(x) FROM xyz 365 ---- 366 scalar-group-by 367 ├── columns: min:4 min:5 368 ├── cardinality: [1 - 1] 369 ├── key: () 370 ├── fd: ()-->(4,5) 371 ├── scan xyz@xy 372 │ ├── columns: x:1!null y:2 373 │ ├── key: (1) 374 │ └── fd: (1)-->(2) 375 └── aggregations 376 ├── min [as=min:4, outer=(2)] 377 │ └── y:2 378 └── min [as=min:5, outer=(1)] 379 └── x:1 380 381 382 # ReplaceScalarMaxWithLimit does not apply on multiple aggregations 383 # on different columns 384 opt 385 SELECT max(y), max(x) FROM xyz 386 ---- 387 scalar-group-by 388 ├── columns: max:4 max:5 389 ├── cardinality: [1 - 1] 390 ├── key: () 391 ├── fd: ()-->(4,5) 392 ├── scan xyz@xy 393 │ ├── columns: x:1!null y:2 394 │ ├── key: (1) 395 │ └── fd: (1)-->(2) 396 └── aggregations 397 ├── max [as=max:4, outer=(2)] 398 │ └── y:2 399 └── max [as=max:5, outer=(1)] 400 └── x:1 401 402 # ReplaceScalarMinWithLimit does not apply with 403 # multiple grouping columns 404 opt 405 SELECT x,min(y) FROM xyz GROUP BY x,y 406 ---- 407 group-by 408 ├── columns: x:1!null min:4 409 ├── grouping columns: x:1!null 410 ├── internal-ordering: +1 411 ├── key: (1) 412 ├── fd: (1)-->(4) 413 ├── scan xyz@xy 414 │ ├── columns: x:1!null y:2 415 │ ├── key: (1) 416 │ ├── fd: (1)-->(2) 417 │ └── ordering: +1 418 └── aggregations 419 └── min [as=min:4, outer=(2)] 420 └── y:2 421 422 # ReplaceScalarMaxWithLimit does not apply with 423 # multiple grouping columns 424 opt 425 SELECT x,max(y) FROM xyz GROUP BY x,y 426 ---- 427 group-by 428 ├── columns: x:1!null max:4 429 ├── grouping columns: x:1!null 430 ├── internal-ordering: +1 431 ├── key: (1) 432 ├── fd: (1)-->(4) 433 ├── scan xyz@xy 434 │ ├── columns: x:1!null y:2 435 │ ├── key: (1) 436 │ ├── fd: (1)-->(2) 437 │ └── ordering: +1 438 └── aggregations 439 └── max [as=max:4, outer=(2)] 440 └── y:2 441 442 # ReplaceScalarMinWithLimit does not apply to non-scalar 443 # aggregates 444 opt 445 SELECT min(x), count(y) FROM xyz GROUP BY x,y 446 ---- 447 project 448 ├── columns: min:4!null count:5!null 449 └── group-by 450 ├── columns: x:1!null min:4!null count:5!null 451 ├── grouping columns: x:1!null 452 ├── internal-ordering: +1 453 ├── key: (1) 454 ├── fd: (1)-->(4,5) 455 ├── scan xyz@xy 456 │ ├── columns: x:1!null y:2 457 │ ├── key: (1) 458 │ ├── fd: (1)-->(2) 459 │ └── ordering: +1 460 └── aggregations 461 ├── min [as=min:4, outer=(1)] 462 │ └── x:1 463 └── count [as=count:5, outer=(2)] 464 └── y:2 465 466 # ReplaceScalarMaxWithLimit does not apply to non-scalar 467 # aggregates 468 opt 469 SELECT max(x), count(y) FROM xyz GROUP BY x,y 470 ---- 471 project 472 ├── columns: max:4!null count:5!null 473 └── group-by 474 ├── columns: x:1!null max:4!null count:5!null 475 ├── grouping columns: x:1!null 476 ├── internal-ordering: +1 477 ├── key: (1) 478 ├── fd: (1)-->(4,5) 479 ├── scan xyz@xy 480 │ ├── columns: x:1!null y:2 481 │ ├── key: (1) 482 │ ├── fd: (1)-->(2) 483 │ └── ordering: +1 484 └── aggregations 485 ├── max [as=max:4, outer=(1)] 486 │ └── x:1 487 └── count [as=count:5, outer=(2)] 488 └── y:2 489 490 memo 491 SELECT min(a) FROM abc 492 ---- 493 memo (optimized, ~5KB, required=[presentation: min:5]) 494 ├── G1: (scalar-group-by G2 G3 cols=()) (scalar-group-by G4 G5 cols=()) 495 │ └── [presentation: min:5] 496 │ ├── best: (scalar-group-by G4 G5 cols=()) 497 │ └── cost: 1.11 498 ├── G2: (scan abc,cols=(1)) 499 │ ├── [ordering: +1] [limit hint: 1.00] 500 │ │ ├── best: (scan abc,cols=(1)) 501 │ │ └── cost: 2.12 502 │ └── [] 503 │ ├── best: (scan abc,cols=(1)) 504 │ └── cost: 1050.02 505 ├── G3: (aggregations G6) 506 ├── G4: (limit G2 G7 ordering=+1) (scan abc,cols=(1),lim=1) 507 │ └── [] 508 │ ├── best: (scan abc,cols=(1),lim=1) 509 │ └── cost: 1.07 510 ├── G5: (aggregations G8) 511 ├── G6: (min G9) 512 ├── G7: (const 1) 513 ├── G8: (const-agg G9) 514 └── G9: (variable a) 515 516 memo 517 SELECT min(b) FROM abc 518 ---- 519 memo (optimized, ~6KB, required=[presentation: min:5]) 520 ├── G1: (scalar-group-by G2 G3 cols=()) (scalar-group-by G4 G5 cols=()) 521 │ └── [presentation: min:5] 522 │ ├── best: (scalar-group-by G2 G3 cols=()) 523 │ └── cost: 1060.05 524 ├── G2: (scan abc,cols=(2)) 525 │ ├── [ordering: +2] [limit hint: 1.01] 526 │ │ ├── best: (sort G2) 527 │ │ └── cost: 1269.35 528 │ └── [] 529 │ ├── best: (scan abc,cols=(2)) 530 │ └── cost: 1050.02 531 ├── G3: (aggregations G6) 532 ├── G4: (limit G7 G8 ordering=+2) 533 │ └── [] 534 │ ├── best: (limit G7="[ordering: +2] [limit hint: 1.00]" G8 ordering=+2) 535 │ └── cost: 1276.90 536 ├── G5: (aggregations G9) 537 ├── G6: (min G10) 538 ├── G7: (select G2 G11) 539 │ ├── [ordering: +2] [limit hint: 1.00] 540 │ │ ├── best: (sort G7) 541 │ │ └── cost: 1276.88 542 │ └── [] 543 │ ├── best: (select G2 G11) 544 │ └── cost: 1060.03 545 ├── G8: (const 1) 546 ├── G9: (const-agg G10) 547 ├── G10: (variable b) 548 ├── G11: (filters G12) 549 ├── G12: (is-not G10 G13) 550 └── G13: (null) 551 552 memo 553 SELECT max(a) FROM abc 554 ---- 555 memo (optimized, ~5KB, required=[presentation: max:5]) 556 ├── G1: (scalar-group-by G2 G3 cols=()) (scalar-group-by G4 G5 cols=()) 557 │ └── [presentation: max:5] 558 │ ├── best: (scalar-group-by G4 G5 cols=()) 559 │ └── cost: 1.11 560 ├── G2: (scan abc,cols=(1)) 561 │ ├── [ordering: -1] [limit hint: 1.00] 562 │ │ ├── best: (scan abc,rev,cols=(1)) 563 │ │ └── cost: 2.14 564 │ └── [] 565 │ ├── best: (scan abc,cols=(1)) 566 │ └── cost: 1050.02 567 ├── G3: (aggregations G6) 568 ├── G4: (limit G2 G7 ordering=-1) (scan abc,rev,cols=(1),lim=1(rev)) 569 │ └── [] 570 │ ├── best: (scan abc,rev,cols=(1),lim=1(rev)) 571 │ └── cost: 1.07 572 ├── G5: (aggregations G8) 573 ├── G6: (max G9) 574 ├── G7: (const 1) 575 ├── G8: (const-agg G9) 576 └── G9: (variable a) 577 578 memo 579 SELECT max(b) FROM abc 580 ---- 581 memo (optimized, ~6KB, required=[presentation: max:5]) 582 ├── G1: (scalar-group-by G2 G3 cols=()) (scalar-group-by G4 G5 cols=()) 583 │ └── [presentation: max:5] 584 │ ├── best: (scalar-group-by G2 G3 cols=()) 585 │ └── cost: 1060.05 586 ├── G2: (scan abc,cols=(2)) 587 │ ├── [ordering: -2] [limit hint: 1.01] 588 │ │ ├── best: (sort G2) 589 │ │ └── cost: 1269.35 590 │ └── [] 591 │ ├── best: (scan abc,cols=(2)) 592 │ └── cost: 1050.02 593 ├── G3: (aggregations G6) 594 ├── G4: (limit G7 G8 ordering=-2) 595 │ └── [] 596 │ ├── best: (limit G7="[ordering: -2] [limit hint: 1.00]" G8 ordering=-2) 597 │ └── cost: 1276.90 598 ├── G5: (aggregations G9) 599 ├── G6: (max G10) 600 ├── G7: (select G2 G11) 601 │ ├── [ordering: -2] [limit hint: 1.00] 602 │ │ ├── best: (sort G7) 603 │ │ └── cost: 1276.88 604 │ └── [] 605 │ ├── best: (select G2 G11) 606 │ └── cost: 1060.03 607 ├── G8: (const 1) 608 ├── G9: (const-agg G10) 609 ├── G10: (variable b) 610 ├── G11: (filters G12) 611 ├── G12: (is-not G10 G13) 612 └── G13: (null) 613 614 # -------------------------------------------------- 615 # ReplaceScalarMinMaxWithLimit (Max variations) 616 # -------------------------------------------------- 617 618 opt 619 SELECT max(a) FROM abc 620 ---- 621 scalar-group-by 622 ├── columns: max:5 623 ├── cardinality: [1 - 1] 624 ├── key: () 625 ├── fd: ()-->(5) 626 ├── scan abc,rev 627 │ ├── columns: a:1!null 628 │ ├── limit: 1(rev) 629 │ ├── key: () 630 │ └── fd: ()-->(1) 631 └── aggregations 632 └── const-agg [as=max:5, outer=(1)] 633 └── a:1 634 635 # Verify the rule still fires even if DISTINCT is used. 636 opt 637 SELECT max(DISTINCT a) FROM abc 638 ---- 639 scalar-group-by 640 ├── columns: max:5 641 ├── cardinality: [1 - 1] 642 ├── key: () 643 ├── fd: ()-->(5) 644 ├── scan abc,rev 645 │ ├── columns: a:1!null 646 │ ├── limit: 1(rev) 647 │ ├── key: () 648 │ └── fd: ()-->(1) 649 └── aggregations 650 └── const-agg [as=max:5, outer=(1)] 651 └── a:1 652 653 # Verify the rule does not fire when FILTER is used. 654 opt 655 SELECT max(a) FILTER (WHERE a > 'a') FROM abc 656 ---- 657 scalar-group-by 658 ├── columns: max:6 659 ├── cardinality: [1 - 1] 660 ├── key: () 661 ├── fd: ()-->(6) 662 ├── scan abc,rev 663 │ ├── columns: a:1!null 664 │ ├── constraint: /1: [/e'a\x00' - ] 665 │ ├── limit: 1(rev) 666 │ ├── key: () 667 │ └── fd: ()-->(1) 668 └── aggregations 669 └── const-agg [as=max:6, outer=(1)] 670 └── a:1 671 672 opt 673 SELECT max(b) FROM abc 674 ---- 675 scalar-group-by 676 ├── columns: max:5 677 ├── cardinality: [1 - 1] 678 ├── key: () 679 ├── fd: ()-->(5) 680 ├── scan abc 681 │ └── columns: b:2 682 └── aggregations 683 └── max [as=max:5, outer=(2)] 684 └── b:2 685 686 memo 687 SELECT max(b) FROM abc 688 ---- 689 memo (optimized, ~6KB, required=[presentation: max:5]) 690 ├── G1: (scalar-group-by G2 G3 cols=()) (scalar-group-by G4 G5 cols=()) 691 │ └── [presentation: max:5] 692 │ ├── best: (scalar-group-by G2 G3 cols=()) 693 │ └── cost: 1060.05 694 ├── G2: (scan abc,cols=(2)) 695 │ ├── [ordering: -2] [limit hint: 1.01] 696 │ │ ├── best: (sort G2) 697 │ │ └── cost: 1269.35 698 │ └── [] 699 │ ├── best: (scan abc,cols=(2)) 700 │ └── cost: 1050.02 701 ├── G3: (aggregations G6) 702 ├── G4: (limit G7 G8 ordering=-2) 703 │ └── [] 704 │ ├── best: (limit G7="[ordering: -2] [limit hint: 1.00]" G8 ordering=-2) 705 │ └── cost: 1276.90 706 ├── G5: (aggregations G9) 707 ├── G6: (max G10) 708 ├── G7: (select G2 G11) 709 │ ├── [ordering: -2] [limit hint: 1.00] 710 │ │ ├── best: (sort G7) 711 │ │ └── cost: 1276.88 712 │ └── [] 713 │ ├── best: (select G2 G11) 714 │ └── cost: 1060.03 715 ├── G8: (const 1) 716 ├── G9: (const-agg G10) 717 ├── G10: (variable b) 718 ├── G11: (filters G12) 719 ├── G12: (is-not G10 G13) 720 └── G13: (null) 721 722 # -------------------------------------------------- 723 # ReplaceMinWithLimit & ReplaceMaxWithLimit 724 # -------------------------------------------------- 725 726 # Basic min case (min function must take non-null column). 727 opt expect=ReplaceMinWithLimit 728 SELECT min(k) FROM kuvw WHERE w = 5 GROUP BY w 729 ---- 730 project 731 ├── columns: min:5!null 732 ├── cardinality: [0 - 1] 733 ├── key: () 734 ├── fd: ()-->(5) 735 ├── scan kuvw@w 736 │ ├── columns: k:1!null w:4!null 737 │ ├── constraint: /4/1: [/5 - /5] 738 │ ├── limit: 1 739 │ ├── key: () 740 │ └── fd: ()-->(1,4) 741 └── projections 742 └── k:1 [as=min:5, outer=(1)] 743 744 # Basic max case. 745 opt expect=ReplaceMaxWithLimit 746 SELECT max(w) FROM kuvw WHERE v = 5 GROUP BY v 747 ---- 748 project 749 ├── columns: max:5 750 ├── cardinality: [0 - 1] 751 ├── key: () 752 ├── fd: ()-->(5) 753 ├── scan kuvw@vw,rev 754 │ ├── columns: v:3!null w:4 755 │ ├── constraint: /3/4/1: [/5 - /5] 756 │ ├── limit: 1(rev) 757 │ ├── key: () 758 │ └── fd: ()-->(3,4) 759 └── projections 760 └── w:4 [as=max:5, outer=(4)] 761 762 # Add const_agg function, as well as min function. 763 opt expect=ReplaceMinWithLimit 764 SELECT v + 1, min(w), v FROM kuvw WHERE v = 5 AND w IS NOT NULL GROUP BY v 765 ---- 766 project 767 ├── columns: "?column?":6!null min:5!null v:3!null 768 ├── cardinality: [0 - 1] 769 ├── key: () 770 ├── fd: ()-->(3,5,6) 771 ├── project 772 │ ├── columns: min:5!null v:3!null 773 │ ├── cardinality: [0 - 1] 774 │ ├── key: () 775 │ ├── fd: ()-->(3,5) 776 │ ├── scan kuvw@vw 777 │ │ ├── columns: v:3!null w:4!null 778 │ │ ├── constraint: /3/4/1: (/5/NULL - /5] 779 │ │ ├── limit: 1 780 │ │ ├── key: () 781 │ │ └── fd: ()-->(3,4) 782 │ └── projections 783 │ └── w:4 [as=min:5, outer=(4)] 784 └── projections 785 └── v:3 + 1 [as="?column?":6, outer=(3)] 786 787 # Add const_agg function, as well as max function. 788 opt expect=ReplaceMaxWithLimit 789 SELECT v + 1, max(w), v FROM kuvw WHERE v = 5 GROUP BY v 790 ---- 791 project 792 ├── columns: "?column?":6!null max:5 v:3!null 793 ├── cardinality: [0 - 1] 794 ├── key: () 795 ├── fd: ()-->(3,5,6) 796 ├── project 797 │ ├── columns: max:5 v:3!null 798 │ ├── cardinality: [0 - 1] 799 │ ├── key: () 800 │ ├── fd: ()-->(3,5) 801 │ ├── scan kuvw@vw,rev 802 │ │ ├── columns: v:3!null w:4 803 │ │ ├── constraint: /3/4/1: [/5 - /5] 804 │ │ ├── limit: 1(rev) 805 │ │ ├── key: () 806 │ │ └── fd: ()-->(3,4) 807 │ └── projections 808 │ └── w:4 [as=max:5, outer=(4)] 809 └── projections 810 └── v:3 + 1 [as="?column?":6, outer=(3)] 811 812 # Use multiple grouping columns with min function. 813 opt expect=ReplaceMinWithLimit 814 SELECT min(k) FROM kuvw WHERE v = 5 AND w = 10 GROUP BY v, w 815 ---- 816 project 817 ├── columns: min:5!null 818 ├── cardinality: [0 - 1] 819 ├── key: () 820 ├── fd: ()-->(5) 821 ├── scan kuvw@vw 822 │ ├── columns: k:1!null v:3!null w:4!null 823 │ ├── constraint: /3/4/1: [/5/10 - /5/10] 824 │ ├── limit: 1 825 │ ├── key: () 826 │ └── fd: ()-->(1,3,4) 827 └── projections 828 └── k:1 [as=min:5, outer=(1)] 829 830 # Use multiple grouping columns with max function. 831 opt expect=ReplaceMaxWithLimit 832 SELECT max(k) FROM kuvw WHERE v = 5 AND w = 10 GROUP BY v, w 833 ---- 834 project 835 ├── columns: max:5!null 836 ├── cardinality: [0 - 1] 837 ├── key: () 838 ├── fd: ()-->(5) 839 ├── scan kuvw@vw,rev 840 │ ├── columns: k:1!null v:3!null w:4!null 841 │ ├── constraint: /3/4/1: [/5/10 - /5/10] 842 │ ├── limit: 1(rev) 843 │ ├── key: () 844 │ └── fd: ()-->(1,3,4) 845 └── projections 846 └── k:1 [as=max:5, outer=(1)] 847 848 # Use multiple grouping columns with min function, and project them. 849 opt expect=ReplaceMinWithLimit 850 SELECT v, min(k), w FROM kuvw WHERE v = 5 AND w = 10 GROUP BY v, w 851 ---- 852 project 853 ├── columns: v:3!null min:5!null w:4!null 854 ├── cardinality: [0 - 1] 855 ├── key: () 856 ├── fd: ()-->(3-5) 857 ├── scan kuvw@vw 858 │ ├── columns: k:1!null v:3!null w:4!null 859 │ ├── constraint: /3/4/1: [/5/10 - /5/10] 860 │ ├── limit: 1 861 │ ├── key: () 862 │ └── fd: ()-->(1,3,4) 863 └── projections 864 └── k:1 [as=min:5, outer=(1)] 865 866 # Use multiple grouping columns with max function, and project them. 867 opt expect=ReplaceMaxWithLimit 868 SELECT v, max(k), w FROM kuvw WHERE v = 5 AND w = 10 GROUP BY v, w 869 ---- 870 project 871 ├── columns: v:3!null max:5!null w:4!null 872 ├── cardinality: [0 - 1] 873 ├── key: () 874 ├── fd: ()-->(3-5) 875 ├── scan kuvw@vw,rev 876 │ ├── columns: k:1!null v:3!null w:4!null 877 │ ├── constraint: /3/4/1: [/5/10 - /5/10] 878 │ ├── limit: 1(rev) 879 │ ├── key: () 880 │ └── fd: ()-->(1,3,4) 881 └── projections 882 └── k:1 [as=max:5, outer=(1)] 883 884 # Multiple grouping columns, but different min column; use different index. 885 opt expect=ReplaceMinWithLimit 886 SELECT min(u) FROM kuvw WHERE v = 5 AND w = 10 AND u > 0 GROUP BY v, w 887 ---- 888 project 889 ├── columns: min:5!null 890 ├── cardinality: [0 - 1] 891 ├── key: () 892 ├── fd: ()-->(5) 893 ├── scan kuvw@wvu 894 │ ├── columns: u:2!null v:3!null w:4!null 895 │ ├── constraint: /4/3/2/1: [/10/5/1 - /10/5] 896 │ ├── limit: 1 897 │ ├── key: () 898 │ └── fd: ()-->(2-4) 899 └── projections 900 └── u:2 [as=min:5, outer=(2)] 901 902 # Multiple grouping columns, but different max column; use different index. 903 opt expect=ReplaceMaxWithLimit 904 SELECT max(u) FROM kuvw WHERE v = 5 AND w = 10 GROUP BY v, w 905 ---- 906 project 907 ├── columns: max:5 908 ├── cardinality: [0 - 1] 909 ├── key: () 910 ├── fd: ()-->(5) 911 ├── scan kuvw@wvu,rev 912 │ ├── columns: u:2 v:3!null w:4!null 913 │ ├── constraint: /4/3/2/1: [/10/5 - /10/5] 914 │ ├── limit: 1(rev) 915 │ ├── key: () 916 │ └── fd: ()-->(2-4) 917 └── projections 918 └── u:2 [as=max:5, outer=(2)] 919 920 # One of grouping columns is not constant, with min function. 921 opt expect-not=ReplaceMinWithLimit 922 SELECT min(k) FROM kuvw WHERE v = 5 GROUP BY v, w 923 ---- 924 project 925 ├── columns: min:5!null 926 └── group-by 927 ├── columns: w:4 min:5!null 928 ├── grouping columns: w:4 929 ├── internal-ordering: +4 opt(3) 930 ├── key: (4) 931 ├── fd: (4)-->(5) 932 ├── scan kuvw@vw 933 │ ├── columns: k:1!null v:3!null w:4 934 │ ├── constraint: /3/4/1: [/5 - /5] 935 │ ├── key: (1) 936 │ ├── fd: ()-->(3), (1)-->(4) 937 │ └── ordering: +4 opt(3) [actual: +4] 938 └── aggregations 939 └── min [as=min:5, outer=(1)] 940 └── k:1 941 942 # One of grouping columns is not constant, with max function. 943 opt expect-not=ReplaceMaxWithLimit 944 SELECT max(k) FROM kuvw WHERE v = 5 GROUP BY v, w 945 ---- 946 project 947 ├── columns: max:5!null 948 └── group-by 949 ├── columns: w:4 max:5!null 950 ├── grouping columns: w:4 951 ├── internal-ordering: +4 opt(3) 952 ├── key: (4) 953 ├── fd: (4)-->(5) 954 ├── scan kuvw@vw 955 │ ├── columns: k:1!null v:3!null w:4 956 │ ├── constraint: /3/4/1: [/5 - /5] 957 │ ├── key: (1) 958 │ ├── fd: ()-->(3), (1)-->(4) 959 │ └── ordering: +4 opt(3) [actual: +4] 960 └── aggregations 961 └── max [as=max:5, outer=(1)] 962 └── k:1 963 964 # We expect ReplaceMinWithLimit not to be preferred here. 965 # This is because we know nothing about the ordering of w 966 # on the index vw after a scan on vw with v>5. 967 opt expect-not=ReplaceMinWithLimit 968 SELECT min(w) FROM kuvw WHERE v > 5 AND w IS NOT NULL GROUP BY v 969 ---- 970 project 971 ├── columns: min:5!null 972 └── group-by 973 ├── columns: v:3!null min:5!null 974 ├── grouping columns: v:3!null 975 ├── internal-ordering: +3 976 ├── key: (3) 977 ├── fd: (3)-->(5) 978 ├── select 979 │ ├── columns: v:3!null w:4!null 980 │ ├── ordering: +3 981 │ ├── scan kuvw@vw 982 │ │ ├── columns: v:3!null w:4 983 │ │ ├── constraint: /3/4/1: (/6/NULL - ] 984 │ │ └── ordering: +3 985 │ └── filters 986 │ └── w:4 IS NOT NULL [outer=(4), constraints=(/4: (/NULL - ]; tight)] 987 └── aggregations 988 └── min [as=min:5, outer=(4)] 989 └── w:4 990 991 # We expect ReplaceMaxWithLimit not to be preferred here. 992 # This is because we know nothing about the ordering of w 993 # on the index vw after a scan on vw with v>5. 994 opt expect-not=ReplaceMaxWithLimit 995 SELECT max(w) FROM kuvw WHERE v > 5 GROUP BY v 996 ---- 997 project 998 ├── columns: max:5 999 └── group-by 1000 ├── columns: v:3!null max:5 1001 ├── grouping columns: v:3!null 1002 ├── internal-ordering: +3 1003 ├── key: (3) 1004 ├── fd: (3)-->(5) 1005 ├── scan kuvw@vw 1006 │ ├── columns: v:3!null w:4 1007 │ ├── constraint: /3/4/1: [/6 - ] 1008 │ └── ordering: +3 1009 └── aggregations 1010 └── max [as=max:5, outer=(4)] 1011 └── w:4 1012 1013 # ReplaceMinWithLimit does not apply on multiple aggregations 1014 # on different columns 1015 opt expect-not=ReplaceMinWithLimit 1016 SELECT min(w), min(k) FROM kuvw WHERE v = 5 AND w IS NOT NULL GROUP BY v 1017 ---- 1018 group-by 1019 ├── columns: min:5!null min:6!null 1020 ├── cardinality: [0 - 1] 1021 ├── key: () 1022 ├── fd: ()-->(5,6) 1023 ├── scan kuvw@vw 1024 │ ├── columns: k:1!null v:3!null w:4!null 1025 │ ├── constraint: /3/4/1: (/5/NULL - /5] 1026 │ ├── key: (1) 1027 │ └── fd: ()-->(3), (1)-->(4) 1028 └── aggregations 1029 ├── min [as=min:5, outer=(4)] 1030 │ └── w:4 1031 └── min [as=min:6, outer=(1)] 1032 └── k:1 1033 1034 # ReplaceMaxWithLimit does not apply on multiple aggregations 1035 # on different columns 1036 opt expect-not=ReplaceMaxWithLimit 1037 SELECT max(w), max(k) FROM kuvw WHERE v = 5 GROUP BY v 1038 ---- 1039 group-by 1040 ├── columns: max:5 max:6!null 1041 ├── cardinality: [0 - 1] 1042 ├── key: () 1043 ├── fd: ()-->(5,6) 1044 ├── scan kuvw@vw 1045 │ ├── columns: k:1!null v:3!null w:4 1046 │ ├── constraint: /3/4/1: [/5 - /5] 1047 │ ├── key: (1) 1048 │ └── fd: ()-->(3), (1)-->(4) 1049 └── aggregations 1050 ├── max [as=max:5, outer=(4)] 1051 │ └── w:4 1052 └── max [as=max:6, outer=(1)] 1053 └── k:1 1054 1055 # ReplaceMinWithLimit does not apply when other aggregates are present. 1056 opt expect-not=ReplaceMinWithLimit 1057 SELECT min(k), max(k) FROM kuvw WHERE w = 5 GROUP BY w 1058 ---- 1059 group-by 1060 ├── columns: min:5!null max:6!null 1061 ├── cardinality: [0 - 1] 1062 ├── key: () 1063 ├── fd: ()-->(5,6) 1064 ├── scan kuvw@wvu 1065 │ ├── columns: k:1!null w:4!null 1066 │ ├── constraint: /4/3/2/1: [/5 - /5] 1067 │ ├── key: (1) 1068 │ └── fd: ()-->(4) 1069 └── aggregations 1070 ├── min [as=min:5, outer=(1)] 1071 │ └── k:1 1072 └── max [as=max:6, outer=(1)] 1073 └── k:1 1074 1075 # ReplaceMaxWithLimit does not apply when other aggregates are present. 1076 opt expect-not=ReplaceMaxWithLimit 1077 SELECT max(w), count(w) FROM kuvw WHERE v = 5 GROUP BY v 1078 ---- 1079 group-by 1080 ├── columns: max:5 count:6!null 1081 ├── cardinality: [0 - 1] 1082 ├── key: () 1083 ├── fd: ()-->(5,6) 1084 ├── scan kuvw@vw 1085 │ ├── columns: v:3!null w:4 1086 │ ├── constraint: /3/4/1: [/5 - /5] 1087 │ └── fd: ()-->(3) 1088 └── aggregations 1089 ├── max [as=max:5, outer=(4)] 1090 │ └── w:4 1091 └── count [as=count:6, outer=(4)] 1092 └── w:4 1093 1094 # min/max functions are not symmetric because of NULL ordering (NULL values 1095 # always sort first, which interferes with MIN calculation). Ensure that min 1096 # function on nullable column does not trigger ReplaceMinWithLimit. 1097 opt expect-not=ReplaceMinWithLimit 1098 SELECT min(w) FROM kuvw WHERE v = 5 GROUP BY v 1099 ---- 1100 group-by 1101 ├── columns: min:5 1102 ├── cardinality: [0 - 1] 1103 ├── key: () 1104 ├── fd: ()-->(5) 1105 ├── scan kuvw@vw 1106 │ ├── columns: v:3!null w:4 1107 │ ├── constraint: /3/4/1: [/5 - /5] 1108 │ └── fd: ()-->(3) 1109 └── aggregations 1110 └── min [as=min:5, outer=(4)] 1111 └── w:4 1112 1113 # -------------------------------------------------- 1114 # GenerateStreamingGroupBy 1115 # -------------------------------------------------- 1116 1117 # All index orderings can be used. 1118 memo 1119 SELECT array_agg(w) FROM (SELECT * FROM kuvw ORDER BY w) GROUP BY u,v 1120 ---- 1121 memo (optimized, ~6KB, required=[presentation: array_agg:5]) 1122 ├── G1: (project G2 G3 array_agg) 1123 │ └── [presentation: array_agg:5] 1124 │ ├── best: (project G2 G3 array_agg) 1125 │ └── cost: 1120.05 1126 ├── G2: (group-by G4 G5 cols=(2,3),ordering=+4 opt(2,3)) (group-by G4 G5 cols=(2,3),ordering=+2,+3,+4) (group-by G4 G5 cols=(2,3),ordering=+4,+3,+2) (group-by G4 G5 cols=(2,3),ordering=+3,+4) 1127 │ └── [] 1128 │ ├── best: (group-by G4="[ordering: +2,+3,+4]" G5 cols=(2,3),ordering=+2,+3,+4) 1129 │ └── cost: 1110.04 1130 ├── G3: (projections) 1131 ├── G4: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1132 │ ├── [ordering: +2,+3,+4] 1133 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1134 │ │ └── cost: 1070.02 1135 │ ├── [ordering: +3,+4] 1136 │ │ ├── best: (scan kuvw@vw,cols=(2-4)) 1137 │ │ └── cost: 1070.02 1138 │ ├── [ordering: +4 opt(2,3)] 1139 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1140 │ │ └── cost: 1070.02 1141 │ ├── [ordering: +4,+3,+2] 1142 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1143 │ │ └── cost: 1070.02 1144 │ └── [] 1145 │ ├── best: (scan kuvw,cols=(2-4)) 1146 │ └── cost: 1070.02 1147 ├── G5: (aggregations G6) 1148 ├── G6: (array-agg G7) 1149 └── G7: (variable w) 1150 1151 # All index orderings can be used (note that +w is redundant with +w,+v+,u). 1152 memo 1153 SELECT sum(w) FROM kuvw GROUP BY u,v,w 1154 ---- 1155 memo (optimized, ~6KB, required=[presentation: sum:5]) 1156 ├── G1: (project G2 G3 sum) 1157 │ └── [presentation: sum:5] 1158 │ ├── best: (project G2 G3 sum) 1159 │ └── cost: 1130.05 1160 ├── G2: (group-by G4 G5 cols=(2-4)) (group-by G4 G5 cols=(2-4),ordering=+2,+3,+4) (group-by G4 G5 cols=(2-4),ordering=+4,+3,+2) (group-by G4 G5 cols=(2-4),ordering=+3,+4) 1161 │ └── [] 1162 │ ├── best: (group-by G4="[ordering: +2,+3,+4]" G5 cols=(2-4),ordering=+2,+3,+4) 1163 │ └── cost: 1120.04 1164 ├── G3: (projections) 1165 ├── G4: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1166 │ ├── [ordering: +2,+3,+4] 1167 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1168 │ │ └── cost: 1070.02 1169 │ ├── [ordering: +3,+4] 1170 │ │ ├── best: (scan kuvw@vw,cols=(2-4)) 1171 │ │ └── cost: 1070.02 1172 │ ├── [ordering: +4,+3,+2] 1173 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1174 │ │ └── cost: 1070.02 1175 │ └── [] 1176 │ ├── best: (scan kuvw,cols=(2-4)) 1177 │ └── cost: 1070.02 1178 ├── G5: (aggregations G6) 1179 ├── G6: (sum G7) 1180 └── G7: (variable w) 1181 1182 # Only index ordering +v,+w can be used (as +v). 1183 memo 1184 SELECT sum(w) FROM kuvw GROUP BY v 1185 ---- 1186 memo (optimized, ~5KB, required=[presentation: sum:5]) 1187 ├── G1: (project G2 G3 sum) 1188 │ └── [presentation: sum:5] 1189 │ ├── best: (project G2 G3 sum) 1190 │ └── cost: 1082.05 1191 ├── G2: (group-by G4 G5 cols=(3)) (group-by G4 G5 cols=(3),ordering=+3) 1192 │ └── [] 1193 │ ├── best: (group-by G4="[ordering: +3]" G5 cols=(3),ordering=+3) 1194 │ └── cost: 1081.04 1195 ├── G3: (projections) 1196 ├── G4: (scan kuvw,cols=(3,4)) (scan kuvw@uvw,cols=(3,4)) (scan kuvw@wvu,cols=(3,4)) (scan kuvw@vw,cols=(3,4)) (scan kuvw@w,cols=(3,4)) 1197 │ ├── [ordering: +3] 1198 │ │ ├── best: (scan kuvw@vw,cols=(3,4)) 1199 │ │ └── cost: 1060.02 1200 │ └── [] 1201 │ ├── best: (scan kuvw,cols=(3,4)) 1202 │ └── cost: 1060.02 1203 ├── G5: (aggregations G6) 1204 ├── G6: (sum G7) 1205 └── G7: (variable w) 1206 1207 # Only ordering +u,+v,+w can be used. 1208 memo 1209 SELECT array_agg(w) FROM (SELECT * FROM kuvw ORDER BY u,w) GROUP BY v 1210 ---- 1211 memo (optimized, ~5KB, required=[presentation: array_agg:5]) 1212 ├── G1: (project G2 G3 array_agg) 1213 │ └── [presentation: array_agg:5] 1214 │ ├── best: (project G2 G3 array_agg) 1215 │ └── cost: 1102.05 1216 ├── G2: (group-by G4 G5 cols=(3),ordering=+2,+4 opt(3)) (group-by G4 G5 cols=(3),ordering=+2,+3,+4) 1217 │ └── [] 1218 │ ├── best: (group-by G4="[ordering: +2,+4 opt(3)]" G5 cols=(3),ordering=+2,+4 opt(3)) 1219 │ └── cost: 1101.04 1220 ├── G3: (projections) 1221 ├── G4: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1222 │ ├── [ordering: +2,+3,+4] 1223 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1224 │ │ └── cost: 1070.02 1225 │ ├── [ordering: +2,+4 opt(3)] 1226 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1227 │ │ └── cost: 1070.02 1228 │ ├── [ordering: +2] 1229 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1230 │ │ └── cost: 1070.02 1231 │ └── [] 1232 │ ├── best: (scan kuvw,cols=(2-4)) 1233 │ └── cost: 1070.02 1234 ├── G5: (aggregations G6) 1235 ├── G6: (array-agg G7) 1236 └── G7: (variable w) 1237 1238 # Verify the orderings are simplified. 1239 memo 1240 SELECT array_agg(k) FROM (SELECT * FROM kuvw WHERE u=v ORDER BY u) GROUP BY w 1241 ---- 1242 memo (optimized, ~10KB, required=[presentation: array_agg:5]) 1243 ├── G1: (project G2 G3 array_agg) 1244 │ └── [presentation: array_agg:5] 1245 │ ├── best: (project G2 G3 array_agg) 1246 │ └── cost: 1079.63 1247 ├── G2: (group-by G4 G5 cols=(4),ordering=+(2|3) opt(4)) (group-by G4 G5 cols=(4),ordering=+(2|3)) (group-by G4 G5 cols=(4),ordering=+4,+(2|3)) (group-by G4 G5 cols=(4),ordering=+(2|3),+4) 1248 │ └── [] 1249 │ ├── best: (group-by G4="[ordering: +(2|3) opt(4)]" G5 cols=(4),ordering=+(2|3) opt(4)) 1250 │ └── cost: 1079.53 1251 ├── G3: (projections) 1252 ├── G4: (select G6 G7) (select G8 G7) (select G9 G7) 1253 │ ├── [ordering: +(2|3) opt(4)] 1254 │ │ ├── best: (select G8="[ordering: +2 opt(4)]" G7) 1255 │ │ └── cost: 1079.12 1256 │ ├── [ordering: +(2|3),+4] 1257 │ │ ├── best: (sort G4) 1258 │ │ └── cost: 1080.01 1259 │ ├── [ordering: +(2|3)] 1260 │ │ ├── best: (select G8="[ordering: +2]" G7) 1261 │ │ └── cost: 1079.12 1262 │ ├── [ordering: +4,+(2|3)] 1263 │ │ ├── best: (sort G4) 1264 │ │ └── cost: 1080.01 1265 │ └── [] 1266 │ ├── best: (select G8 G7) 1267 │ └── cost: 1079.12 1268 ├── G5: (aggregations G10) 1269 ├── G6: (scan kuvw) (scan kuvw@uvw) (scan kuvw@wvu) (scan kuvw@vw) (scan kuvw@w) 1270 │ ├── [ordering: +2 opt(4)] 1271 │ │ ├── best: (scan kuvw@uvw) 1272 │ │ └── cost: 1080.02 1273 │ ├── [ordering: +2,+4] 1274 │ │ ├── best: (sort G6="[ordering: +2]") 1275 │ │ └── cost: 1166.67 1276 │ ├── [ordering: +2] 1277 │ │ ├── best: (scan kuvw@uvw) 1278 │ │ └── cost: 1080.02 1279 │ ├── [ordering: +4,+2] 1280 │ │ ├── best: (sort G6="[ordering: +4]") 1281 │ │ └── cost: 1166.67 1282 │ ├── [ordering: +4] 1283 │ │ ├── best: (scan kuvw@wvu) 1284 │ │ └── cost: 1080.02 1285 │ └── [] 1286 │ ├── best: (scan kuvw) 1287 │ └── cost: 1080.02 1288 ├── G7: (filters G11) 1289 ├── G8: (scan kuvw@uvw,constrained) 1290 │ ├── [ordering: +2 opt(4)] 1291 │ │ ├── best: (scan kuvw@uvw,constrained) 1292 │ │ └── cost: 1069.21 1293 │ ├── [ordering: +2,+4] 1294 │ │ ├── best: (sort G8="[ordering: +2]") 1295 │ │ └── cost: 1154.71 1296 │ ├── [ordering: +2] 1297 │ │ ├── best: (scan kuvw@uvw,constrained) 1298 │ │ └── cost: 1069.21 1299 │ ├── [ordering: +4,+2] 1300 │ │ ├── best: (sort G8) 1301 │ │ └── cost: 1296.90 1302 │ ├── [ordering: +4] 1303 │ │ ├── best: (sort G8) 1304 │ │ └── cost: 1286.06 1305 │ └── [] 1306 │ ├── best: (scan kuvw@uvw,constrained) 1307 │ └── cost: 1069.21 1308 ├── G9: (scan kuvw@vw,constrained) 1309 │ ├── [ordering: +2 opt(4)] 1310 │ │ ├── best: (sort G9) 1311 │ │ └── cost: 1286.06 1312 │ ├── [ordering: +2,+4] 1313 │ │ ├── best: (sort G9) 1314 │ │ └── cost: 1296.90 1315 │ ├── [ordering: +2] 1316 │ │ ├── best: (sort G9) 1317 │ │ └── cost: 1286.06 1318 │ ├── [ordering: +4,+2] 1319 │ │ ├── best: (sort G9) 1320 │ │ └── cost: 1296.90 1321 │ ├── [ordering: +4] 1322 │ │ ├── best: (sort G9) 1323 │ │ └── cost: 1286.06 1324 │ └── [] 1325 │ ├── best: (scan kuvw@vw,constrained) 1326 │ └── cost: 1069.21 1327 ├── G10: (array-agg G12) 1328 ├── G11: (eq G13 G14) 1329 ├── G12: (variable k) 1330 ├── G13: (variable u) 1331 └── G14: (variable v) 1332 1333 memo 1334 SELECT sum(k) FROM (SELECT * FROM kuvw WHERE u=v) GROUP BY u,w 1335 ---- 1336 memo (optimized, ~10KB, required=[presentation: sum:5]) 1337 ├── G1: (project G2 G3 sum) 1338 │ └── [presentation: sum:5] 1339 │ ├── best: (project G2 G3 sum) 1340 │ └── cost: 1079.69 1341 ├── G2: (group-by G4 G5 cols=(2,4)) (group-by G4 G5 cols=(2,4),ordering=+(2|3)) (group-by G4 G5 cols=(2,4),ordering=+4) 1342 │ └── [] 1343 │ ├── best: (group-by G4="[ordering: +(2|3)]" G5 cols=(2,4),ordering=+(2|3)) 1344 │ └── cost: 1079.58 1345 ├── G3: (projections) 1346 ├── G4: (select G6 G7) (select G8 G7) (select G9 G7) 1347 │ ├── [ordering: +(2|3)] 1348 │ │ ├── best: (select G8="[ordering: +2]" G7) 1349 │ │ └── cost: 1079.12 1350 │ ├── [ordering: +4] 1351 │ │ ├── best: (sort G4) 1352 │ │ └── cost: 1079.97 1353 │ └── [] 1354 │ ├── best: (select G8 G7) 1355 │ └── cost: 1079.12 1356 ├── G5: (aggregations G10) 1357 ├── G6: (scan kuvw) (scan kuvw@uvw) (scan kuvw@wvu) (scan kuvw@vw) (scan kuvw@w) 1358 │ ├── [ordering: +2] 1359 │ │ ├── best: (scan kuvw@uvw) 1360 │ │ └── cost: 1080.02 1361 │ ├── [ordering: +4] 1362 │ │ ├── best: (scan kuvw@wvu) 1363 │ │ └── cost: 1080.02 1364 │ └── [] 1365 │ ├── best: (scan kuvw) 1366 │ └── cost: 1080.02 1367 ├── G7: (filters G11) 1368 ├── G8: (scan kuvw@uvw,constrained) 1369 │ ├── [ordering: +2] 1370 │ │ ├── best: (scan kuvw@uvw,constrained) 1371 │ │ └── cost: 1069.21 1372 │ ├── [ordering: +4] 1373 │ │ ├── best: (sort G8) 1374 │ │ └── cost: 1286.06 1375 │ └── [] 1376 │ ├── best: (scan kuvw@uvw,constrained) 1377 │ └── cost: 1069.21 1378 ├── G9: (scan kuvw@vw,constrained) 1379 │ ├── [ordering: +2] 1380 │ │ ├── best: (sort G9) 1381 │ │ └── cost: 1286.06 1382 │ ├── [ordering: +4] 1383 │ │ ├── best: (sort G9) 1384 │ │ └── cost: 1286.06 1385 │ └── [] 1386 │ ├── best: (scan kuvw@vw,constrained) 1387 │ └── cost: 1069.21 1388 ├── G10: (sum G12) 1389 ├── G11: (eq G13 G14) 1390 ├── G12: (variable k) 1391 ├── G13: (variable u) 1392 └── G14: (variable v) 1393 1394 # Ensure that we don't incorrectly use orderings that don't match the direction. 1395 memo 1396 SELECT array_agg(w) FROM (SELECT * FROM kuvw ORDER BY w DESC) GROUP BY u,v 1397 ---- 1398 memo (optimized, ~5KB, required=[presentation: array_agg:5]) 1399 ├── G1: (project G2 G3 array_agg) 1400 │ └── [presentation: array_agg:5] 1401 │ ├── best: (project G2 G3 array_agg) 1402 │ └── cost: 1229.71 1403 ├── G2: (group-by G4 G5 cols=(2,3),ordering=-4 opt(2,3)) 1404 │ └── [] 1405 │ ├── best: (group-by G4="[ordering: -4 opt(2,3)]" G5 cols=(2,3),ordering=-4 opt(2,3)) 1406 │ └── cost: 1219.70 1407 ├── G3: (projections) 1408 ├── G4: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1409 │ ├── [ordering: -4 opt(2,3)] 1410 │ │ ├── best: (scan kuvw@uvw,rev,cols=(2-4)) 1411 │ │ └── cost: 1169.68 1412 │ └── [] 1413 │ ├── best: (scan kuvw,cols=(2-4)) 1414 │ └── cost: 1070.02 1415 ├── G5: (aggregations G6) 1416 ├── G6: (array-agg G7) 1417 └── G7: (variable w) 1418 1419 1420 # All orderings can be used (note that +w is redundant with +w,+v,+u). 1421 memo 1422 SELECT DISTINCT u, v, w FROM kuvw 1423 ---- 1424 memo (optimized, ~5KB, required=[presentation: u:2,v:3,w:4]) 1425 ├── G1: (distinct-on G2 G3 cols=(2-4)) (distinct-on G2 G3 cols=(2-4),ordering=+2,+3,+4) (distinct-on G2 G3 cols=(2-4),ordering=+4,+3,+2) (distinct-on G2 G3 cols=(2-4),ordering=+3,+4) 1426 │ └── [presentation: u:2,v:3,w:4] 1427 │ ├── best: (distinct-on G2="[ordering: +2,+3,+4]" G3 cols=(2-4),ordering=+2,+3,+4) 1428 │ └── cost: 1110.04 1429 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1430 │ ├── [ordering: +2,+3,+4] 1431 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1432 │ │ └── cost: 1070.02 1433 │ ├── [ordering: +3,+4] 1434 │ │ ├── best: (scan kuvw@vw,cols=(2-4)) 1435 │ │ └── cost: 1070.02 1436 │ ├── [ordering: +4,+3,+2] 1437 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1438 │ │ └── cost: 1070.02 1439 │ └── [] 1440 │ ├── best: (scan kuvw,cols=(2-4)) 1441 │ └── cost: 1070.02 1442 └── G3: (aggregations) 1443 1444 # Orderings +u,+v and +v can be used. 1445 memo 1446 SELECT DISTINCT ON (u, v) u, v, w FROM kuvw 1447 ---- 1448 memo (optimized, ~5KB, required=[presentation: u:2,v:3,w:4]) 1449 ├── G1: (distinct-on G2 G3 cols=(2,3)) (distinct-on G2 G3 cols=(2,3),ordering=+2,+3) (distinct-on G2 G3 cols=(2,3),ordering=+3) 1450 │ └── [presentation: u:2,v:3,w:4] 1451 │ ├── best: (distinct-on G2="[ordering: +2,+3]" G3 cols=(2,3),ordering=+2,+3) 1452 │ └── cost: 1110.04 1453 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1454 │ ├── [ordering: +2,+3] 1455 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1456 │ │ └── cost: 1070.02 1457 │ ├── [ordering: +3] 1458 │ │ ├── best: (scan kuvw@vw,cols=(2-4)) 1459 │ │ └── cost: 1070.02 1460 │ └── [] 1461 │ ├── best: (scan kuvw,cols=(2-4)) 1462 │ └── cost: 1070.02 1463 ├── G3: (aggregations G4) 1464 ├── G4: (first-agg G5) 1465 └── G5: (variable w) 1466 1467 # Only ordering +u can be used. 1468 memo 1469 SELECT DISTINCT ON (u) u, v, w FROM kuvw 1470 ---- 1471 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4]) 1472 ├── G1: (distinct-on G2 G3 cols=(2)) (distinct-on G2 G3 cols=(2),ordering=+2) 1473 │ └── [presentation: u:2,v:3,w:4] 1474 │ ├── best: (distinct-on G2="[ordering: +2]" G3 cols=(2),ordering=+2) 1475 │ └── cost: 1101.04 1476 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1477 │ ├── [ordering: +2] 1478 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1479 │ │ └── cost: 1070.02 1480 │ └── [] 1481 │ ├── best: (scan kuvw,cols=(2-4)) 1482 │ └── cost: 1070.02 1483 ├── G3: (aggregations G4 G5) 1484 ├── G4: (first-agg G6) 1485 ├── G5: (first-agg G7) 1486 ├── G6: (variable v) 1487 └── G7: (variable w) 1488 1489 # Only ordering +v can be used. 1490 memo 1491 SELECT DISTINCT ON (v) u, v, w FROM kuvw 1492 ---- 1493 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4]) 1494 ├── G1: (distinct-on G2 G3 cols=(3)) (distinct-on G2 G3 cols=(3),ordering=+3) 1495 │ └── [presentation: u:2,v:3,w:4] 1496 │ ├── best: (distinct-on G2="[ordering: +3]" G3 cols=(3),ordering=+3) 1497 │ └── cost: 1101.04 1498 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1499 │ ├── [ordering: +3] 1500 │ │ ├── best: (scan kuvw@vw,cols=(2-4)) 1501 │ │ └── cost: 1070.02 1502 │ └── [] 1503 │ ├── best: (scan kuvw,cols=(2-4)) 1504 │ └── cost: 1070.02 1505 ├── G3: (aggregations G4 G5) 1506 ├── G4: (first-agg G6) 1507 ├── G5: (first-agg G7) 1508 ├── G6: (variable u) 1509 └── G7: (variable w) 1510 1511 # Only ordering +w can be used. 1512 memo 1513 SELECT DISTINCT ON (w) u, v, w FROM kuvw 1514 ---- 1515 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4]) 1516 ├── G1: (distinct-on G2 G3 cols=(4)) (distinct-on G2 G3 cols=(4),ordering=+4) 1517 │ └── [presentation: u:2,v:3,w:4] 1518 │ ├── best: (distinct-on G2="[ordering: +4]" G3 cols=(4),ordering=+4) 1519 │ └── cost: 1101.04 1520 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1521 │ ├── [ordering: +4] 1522 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1523 │ │ └── cost: 1070.02 1524 │ └── [] 1525 │ ├── best: (scan kuvw,cols=(2-4)) 1526 │ └── cost: 1070.02 1527 ├── G3: (aggregations G4 G5) 1528 ├── G4: (first-agg G6) 1529 ├── G5: (first-agg G7) 1530 ├── G6: (variable u) 1531 └── G7: (variable v) 1532 1533 # Only ordering +u can be used. 1534 memo 1535 SELECT DISTINCT ON (u) u, v, w FROM kuvw ORDER BY u, w 1536 ---- 1537 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4] [ordering: +2]) 1538 ├── G1: (distinct-on G2 G3 cols=(2),ordering=+4 opt(2)) (distinct-on G2 G3 cols=(2),ordering=+4) 1539 │ ├── [presentation: u:2,v:3,w:4] [ordering: +2] 1540 │ │ ├── best: (sort G1) 1541 │ │ └── cost: 1126.34 1542 │ └── [] 1543 │ ├── best: (distinct-on G2="[ordering: +4 opt(2)]" G3 cols=(2),ordering=+4 opt(2)) 1544 │ └── cost: 1111.04 1545 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1546 │ ├── [ordering: +2,+4] 1547 │ │ ├── best: (sort G2="[ordering: +2]") 1548 │ │ └── cost: 1156.67 1549 │ ├── [ordering: +2] 1550 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1551 │ │ └── cost: 1070.02 1552 │ ├── [ordering: +4 opt(2)] 1553 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1554 │ │ └── cost: 1070.02 1555 │ ├── [ordering: +4] 1556 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1557 │ │ └── cost: 1070.02 1558 │ └── [] 1559 │ ├── best: (scan kuvw,cols=(2-4)) 1560 │ └── cost: 1070.02 1561 ├── G3: (aggregations G4 G5) 1562 ├── G4: (first-agg G6) 1563 ├── G5: (first-agg G7) 1564 ├── G6: (variable v) 1565 └── G7: (variable w) 1566 1567 # Only ordering +u,+v,+w can be used. 1568 memo 1569 SELECT DISTINCT ON (u) u, v, w FROM kuvw ORDER BY u, v, w 1570 ---- 1571 memo (optimized, ~5KB, required=[presentation: u:2,v:3,w:4] [ordering: +2]) 1572 ├── G1: (distinct-on G2 G3 cols=(2),ordering=+3,+4 opt(2)) (distinct-on G2 G3 cols=(2),ordering=+2,+3,+4) (distinct-on G2 G3 cols=(2),ordering=+3,+4) 1573 │ ├── [presentation: u:2,v:3,w:4] [ordering: +2] 1574 │ │ ├── best: (distinct-on G2="[ordering: +2,+3,+4]" G3 cols=(2),ordering=+3,+4 opt(2)) 1575 │ │ └── cost: 1101.04 1576 │ └── [] 1577 │ ├── best: (distinct-on G2="[ordering: +2,+3,+4]" G3 cols=(2),ordering=+2,+3,+4) 1578 │ └── cost: 1101.04 1579 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1580 │ ├── [ordering: +2,+3,+4] 1581 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1582 │ │ └── cost: 1070.02 1583 │ ├── [ordering: +3,+4 opt(2)] 1584 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1585 │ │ └── cost: 1070.02 1586 │ ├── [ordering: +3,+4] 1587 │ │ ├── best: (scan kuvw@vw,cols=(2-4)) 1588 │ │ └── cost: 1070.02 1589 │ └── [] 1590 │ ├── best: (scan kuvw,cols=(2-4)) 1591 │ └── cost: 1070.02 1592 ├── G3: (aggregations G4 G5) 1593 ├── G4: (first-agg G6) 1594 ├── G5: (first-agg G7) 1595 ├── G6: (variable v) 1596 └── G7: (variable w) 1597 1598 # Ensure that we don't incorrectly use orderings that don't match the direction. 1599 memo 1600 SELECT DISTINCT ON (w, u) u, v, w FROM kuvw ORDER BY w, u, v DESC 1601 ---- 1602 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4] [ordering: +4,+2]) 1603 ├── G1: (distinct-on G2 G3 cols=(2,4),ordering=-3 opt(2,4)) 1604 │ ├── [presentation: u:2,v:3,w:4] [ordering: +4,+2] 1605 │ │ ├── best: (distinct-on G2="[ordering: +4,+2,-3]" G3 cols=(2,4),ordering=-3 opt(2,4)) 1606 │ │ └── cost: 1201.02 1607 │ └── [] 1608 │ ├── best: (distinct-on G2="[ordering: -3 opt(2,4)]" G3 cols=(2,4),ordering=-3 opt(2,4)) 1609 │ └── cost: 1219.70 1610 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1611 │ ├── [ordering: +4,+2,-3] 1612 │ │ ├── best: (sort G2="[ordering: +4]") 1613 │ │ └── cost: 1161.00 1614 │ ├── [ordering: +4] 1615 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1616 │ │ └── cost: 1070.02 1617 │ ├── [ordering: -3 opt(2,4)] 1618 │ │ ├── best: (scan kuvw@uvw,rev,cols=(2-4)) 1619 │ │ └── cost: 1169.68 1620 │ └── [] 1621 │ ├── best: (scan kuvw,cols=(2-4)) 1622 │ └── cost: 1070.02 1623 ├── G3: (aggregations G4) 1624 ├── G4: (first-agg G5) 1625 └── G5: (variable v) 1626 1627 memo 1628 SELECT DISTINCT ON (w) u, v, w FROM kuvw ORDER BY w, u DESC, v 1629 ---- 1630 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4] [ordering: +4]) 1631 ├── G1: (distinct-on G2 G3 cols=(4),ordering=-2,+3 opt(4)) 1632 │ ├── [presentation: u:2,v:3,w:4] [ordering: +4] 1633 │ │ ├── best: (distinct-on G2="[ordering: +4,-2,+3]" G3 cols=(4),ordering=-2,+3 opt(4)) 1634 │ │ └── cost: 1192.02 1635 │ └── [] 1636 │ ├── best: (distinct-on G2="[ordering: -2,+3 opt(4)]" G3 cols=(4),ordering=-2,+3 opt(4)) 1637 │ └── cost: 1341.33 1638 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1639 │ ├── [ordering: +4,-2,+3] 1640 │ │ ├── best: (sort G2="[ordering: +4]") 1641 │ │ └── cost: 1161.00 1642 │ ├── [ordering: +4] 1643 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1644 │ │ └── cost: 1070.02 1645 │ ├── [ordering: -2,+3 opt(4)] 1646 │ │ ├── best: (sort G2) 1647 │ │ └── cost: 1300.31 1648 │ └── [] 1649 │ ├── best: (scan kuvw,cols=(2-4)) 1650 │ └── cost: 1070.02 1651 ├── G3: (aggregations G4 G5) 1652 ├── G4: (first-agg G6) 1653 ├── G5: (first-agg G7) 1654 ├── G6: (variable u) 1655 └── G7: (variable v) 1656 1657 memo 1658 SELECT DISTINCT ON (w) u, v, w FROM kuvw ORDER BY w DESC, u DESC, v 1659 ---- 1660 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4] [ordering: -4]) 1661 ├── G1: (distinct-on G2 G3 cols=(4),ordering=-2,+3 opt(4)) 1662 │ ├── [presentation: u:2,v:3,w:4] [ordering: -4] 1663 │ │ ├── best: (distinct-on G2="[ordering: -4,-2,+3]" G3 cols=(4),ordering=-2,+3 opt(4)) 1664 │ │ └── cost: 1332.43 1665 │ └── [] 1666 │ ├── best: (distinct-on G2="[ordering: -2,+3 opt(4)]" G3 cols=(4),ordering=-2,+3 opt(4)) 1667 │ └── cost: 1341.33 1668 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1669 │ ├── [ordering: -2,+3 opt(4)] 1670 │ │ ├── best: (sort G2) 1671 │ │ └── cost: 1300.31 1672 │ ├── [ordering: -4,-2,+3] 1673 │ │ ├── best: (sort G2) 1674 │ │ └── cost: 1301.41 1675 │ └── [] 1676 │ ├── best: (scan kuvw,cols=(2-4)) 1677 │ └── cost: 1070.02 1678 ├── G3: (aggregations G4 G5) 1679 ├── G4: (first-agg G6) 1680 ├── G5: (first-agg G7) 1681 ├── G6: (variable u) 1682 └── G7: (variable v) 1683 1684 memo 1685 SELECT DISTINCT ON (w) u, v, w FROM kuvw ORDER BY w, u, v DESC 1686 ---- 1687 memo (optimized, ~4KB, required=[presentation: u:2,v:3,w:4] [ordering: +4]) 1688 ├── G1: (distinct-on G2 G3 cols=(4),ordering=+2,-3 opt(4)) 1689 │ ├── [presentation: u:2,v:3,w:4] [ordering: +4] 1690 │ │ ├── best: (distinct-on G2="[ordering: +4,+2,-3]" G3 cols=(4),ordering=+2,-3 opt(4)) 1691 │ │ └── cost: 1192.02 1692 │ └── [] 1693 │ ├── best: (distinct-on G2="[ordering: +2,-3 opt(4)]" G3 cols=(4),ordering=+2,-3 opt(4)) 1694 │ └── cost: 1197.69 1695 ├── G2: (scan kuvw,cols=(2-4)) (scan kuvw@uvw,cols=(2-4)) (scan kuvw@wvu,cols=(2-4)) (scan kuvw@vw,cols=(2-4)) (scan kuvw@w,cols=(2-4)) 1696 │ ├── [ordering: +2,-3 opt(4)] 1697 │ │ ├── best: (sort G2="[ordering: +2]") 1698 │ │ └── cost: 1156.67 1699 │ ├── [ordering: +2] 1700 │ │ ├── best: (scan kuvw@uvw,cols=(2-4)) 1701 │ │ └── cost: 1070.02 1702 │ ├── [ordering: +4,+2,-3] 1703 │ │ ├── best: (sort G2="[ordering: +4]") 1704 │ │ └── cost: 1161.00 1705 │ ├── [ordering: +4] 1706 │ │ ├── best: (scan kuvw@wvu,cols=(2-4)) 1707 │ │ └── cost: 1070.02 1708 │ └── [] 1709 │ ├── best: (scan kuvw,cols=(2-4)) 1710 │ └── cost: 1070.02 1711 ├── G3: (aggregations G4 G5) 1712 ├── G4: (first-agg G6) 1713 ├── G5: (first-agg G7) 1714 ├── G6: (variable u) 1715 └── G7: (variable v) 1716 1717 # Ensure that streaming ensure-distinct-on will be used. 1718 memo 1719 SELECT (SELECT w FROM kuvw WHERE v=1 AND x=u) FROM xyz ORDER BY x+1, x 1720 ---- 1721 memo (optimized, ~25KB, required=[presentation: w:8] [ordering: +9,+1]) 1722 ├── G1: (project G2 G3 x) 1723 │ ├── [presentation: w:8] [ordering: +9,+1] 1724 │ │ ├── best: (sort G1) 1725 │ │ └── cost: 1352.04 1726 │ └── [] 1727 │ ├── best: (project G2 G3 x) 1728 │ └── cost: 1121.74 1729 ├── G2: (ensure-distinct-on G4 G5 cols=(1)) (ensure-distinct-on G4 G5 cols=(1),ordering=+1) 1730 │ └── [] 1731 │ ├── best: (ensure-distinct-on G4="[ordering: +1]" G5 cols=(1),ordering=+1) 1732 │ └── cost: 1091.73 1733 ├── G3: (projections G6 G7) 1734 ├── G4: (left-join G8 G9 G10) (right-join G9 G8 G10) (merge-join G8 G9 G11 left-join,+1,+5) (lookup-join G12 G11 kuvw@uvw,keyCols=[1 10],outCols=(1,5-7)) (lookup-join G13 G10 kuvw@vw,keyCols=[11],outCols=(1,5-7)) (merge-join G9 G8 G11 right-join,+5,+1) 1735 │ ├── [ordering: +1] 1736 │ │ ├── best: (merge-join G8="[ordering: +1]" G9="[ordering: +5 opt(6)]" G11 left-join,+1,+5) 1737 │ │ └── cost: 1061.71 1738 │ └── [] 1739 │ ├── best: (merge-join G8="[ordering: +1]" G9="[ordering: +5 opt(6)]" G11 left-join,+1,+5) 1740 │ └── cost: 1061.71 1741 ├── G5: (aggregations G14) 1742 ├── G6: (variable kuvw.w) 1743 ├── G7: (plus G15 G16) 1744 ├── G8: (scan xyz,cols=(1)) (scan xyz@xy,cols=(1)) (scan xyz@zyx,cols=(1)) (scan xyz@yy,cols=(1)) 1745 │ ├── [ordering: +1] 1746 │ │ ├── best: (scan xyz@xy,cols=(1)) 1747 │ │ └── cost: 1030.02 1748 │ └── [] 1749 │ ├── best: (scan xyz@xy,cols=(1)) 1750 │ └── cost: 1030.02 1751 ├── G9: (select G17 G18) (scan kuvw@vw,cols=(5-7),constrained) 1752 │ ├── [ordering: +5 opt(6)] 1753 │ │ ├── best: (sort G9) 1754 │ │ └── cost: 11.58 1755 │ └── [] 1756 │ ├── best: (scan kuvw@vw,cols=(5-7),constrained) 1757 │ └── cost: 10.71 1758 ├── G10: (filters G19) 1759 ├── G11: (filters) 1760 ├── G12: (project G8 G20 x) 1761 │ ├── [ordering: +1] 1762 │ │ ├── best: (project G8="[ordering: +1]" G20 x) 1763 │ │ └── cost: 1050.03 1764 │ └── [] 1765 │ ├── best: (project G8 G20 x) 1766 │ └── cost: 1050.03 1767 ├── G13: (project G8 G20 x) 1768 │ ├── [ordering: +1] 1769 │ │ ├── best: (project G8="[ordering: +1]" G20 x) 1770 │ │ └── cost: 1050.03 1771 │ └── [] 1772 │ ├── best: (project G8 G20 x) 1773 │ └── cost: 1050.03 1774 ├── G14: (const-agg G6) 1775 ├── G15: (variable x) 1776 ├── G16: (const 1) 1777 ├── G17: (scan kuvw,cols=(5-7)) (scan kuvw@uvw,cols=(5-7)) (scan kuvw@wvu,cols=(5-7)) (scan kuvw@vw,cols=(5-7)) (scan kuvw@w,cols=(5-7)) 1778 │ ├── [ordering: +5 opt(6)] 1779 │ │ ├── best: (scan kuvw@uvw,cols=(5-7)) 1780 │ │ └── cost: 1070.02 1781 │ └── [] 1782 │ ├── best: (scan kuvw,cols=(5-7)) 1783 │ └── cost: 1070.02 1784 ├── G18: (filters G21) 1785 ├── G19: (eq G15 G22) 1786 ├── G20: (projections G16) 1787 ├── G21: (eq G23 G16) 1788 ├── G22: (variable u) 1789 └── G23: (variable v) 1790 1791 # Ensure that streaming upsert-distinct-on will be used. 1792 memo 1793 INSERT INTO xyz SELECT v, w, 1.0 FROM kuvw ON CONFLICT (x) DO NOTHING 1794 ---- 1795 memo (optimized, ~19KB, required=[]) 1796 ├── G1: (insert G2 G3 xyz) 1797 │ └── [] 1798 │ ├── best: (insert G2 G3 xyz) 1799 │ └── cost: 2150.50 1800 ├── G2: (upsert-distinct-on G4 G5 cols=(6)) (upsert-distinct-on G4 G5 cols=(6),ordering=+6 opt(8,9)) 1801 │ └── [] 1802 │ ├── best: (upsert-distinct-on G4="[ordering: +6 opt(8,9)]" G5 cols=(6),ordering=+6 opt(8,9)) 1803 │ └── cost: 2150.49 1804 ├── G3: (f-k-checks) 1805 ├── G4: (select G6 G7) 1806 │ ├── [ordering: +6 opt(8,9)] 1807 │ │ ├── best: (select G6="[ordering: +6 opt(8,9)]" G7) 1808 │ │ └── cost: 2150.07 1809 │ └── [] 1810 │ ├── best: (select G6 G7) 1811 │ └── cost: 2150.07 1812 ├── G5: (aggregations G8 G9) 1813 ├── G6: (left-join G10 G11 G12) (right-join G11 G10 G12) (merge-join G10 G11 G13 left-join,+6,+9) (lookup-join G10 G13 xyz,keyCols=[6],outCols=(6-9)) (lookup-join G10 G13 xyz@xy,keyCols=[6],outCols=(6-9)) (merge-join G11 G10 G13 right-join,+9,+6) 1814 │ ├── [ordering: +6 opt(8,9)] 1815 │ │ ├── best: (merge-join G10="[ordering: +6 opt(8)]" G11="[ordering: +9]" G13 left-join,+6,+9) 1816 │ │ └── cost: 2140.06 1817 │ └── [] 1818 │ ├── best: (merge-join G10="[ordering: +6 opt(8)]" G11="[ordering: +9]" G13 left-join,+6,+9) 1819 │ └── cost: 2140.06 1820 ├── G7: (filters G14) 1821 ├── G8: (first-agg G15) 1822 ├── G9: (first-agg G16) 1823 ├── G10: (project G17 G18 v w) 1824 │ ├── [ordering: +6 opt(8)] 1825 │ │ ├── best: (project G17="[ordering: +6]" G18 v w) 1826 │ │ └── cost: 1080.03 1827 │ └── [] 1828 │ ├── best: (project G17 G18 v w) 1829 │ └── cost: 1080.03 1830 ├── G11: (scan xyz,cols=(9)) (scan xyz@xy,cols=(9)) (scan xyz@zyx,cols=(9)) (scan xyz@yy,cols=(9)) 1831 │ ├── [ordering: +9] 1832 │ │ ├── best: (scan xyz@xy,cols=(9)) 1833 │ │ └── cost: 1030.02 1834 │ └── [] 1835 │ ├── best: (scan xyz@xy,cols=(9)) 1836 │ └── cost: 1030.02 1837 ├── G12: (filters G19) 1838 ├── G13: (filters) 1839 ├── G14: (is G20 G21) 1840 ├── G15: (variable w) 1841 ├── G16: (variable "?column?") 1842 ├── G17: (scan kuvw,cols=(6,7)) (scan kuvw@uvw,cols=(6,7)) (scan kuvw@wvu,cols=(6,7)) (scan kuvw@vw,cols=(6,7)) (scan kuvw@w,cols=(6,7)) 1843 │ ├── [ordering: +6] 1844 │ │ ├── best: (scan kuvw@vw,cols=(6,7)) 1845 │ │ └── cost: 1060.02 1846 │ └── [] 1847 │ ├── best: (scan kuvw,cols=(6,7)) 1848 │ └── cost: 1060.02 1849 ├── G18: (projections G22) 1850 ├── G19: (eq G23 G20) 1851 ├── G20: (variable x) 1852 ├── G21: (null) 1853 ├── G22: (const 1.0) 1854 └── G23: (variable v) 1855 1856 # Ensure that streaming ensure-upsert-distinct-on will be used. 1857 memo 1858 INSERT INTO xyz SELECT v, w, 1.0 FROM kuvw ON CONFLICT (x) DO UPDATE SET z=2.0 1859 ---- 1860 memo (optimized, ~19KB, required=[]) 1861 ├── G1: (upsert G2 G3 xyz) 1862 │ └── [] 1863 │ ├── best: (upsert G2 G3 xyz) 1864 │ └── cost: 2230.10 1865 ├── G2: (project G4 G5 v w ?column? x y z) 1866 │ └── [] 1867 │ ├── best: (project G4 G5 v w ?column? x y z) 1868 │ └── cost: 2230.09 1869 ├── G3: (f-k-checks) 1870 ├── G4: (left-join G6 G7 G8) (right-join G7 G6 G8) (lookup-join G6 G9 xyz,keyCols=[6],outCols=(6-11)) (lookup-join G10 G9 xyz,keyCols=[9],outCols=(6-11)) (merge-join G7 G6 G9 right-join,+9,+6) 1871 │ └── [] 1872 │ ├── best: (merge-join G7="[ordering: +9]" G6="[ordering: +6 opt(8)]" G9 right-join,+9,+6) 1873 │ └── cost: 2210.08 1874 ├── G5: (projections G11) 1875 ├── G6: (ensure-upsert-distinct-on G12 G13 cols=(6)) (ensure-upsert-distinct-on G12 G13 cols=(6),ordering=+6 opt(8)) 1876 │ ├── [ordering: +6 opt(8)] 1877 │ │ ├── best: (ensure-upsert-distinct-on G12="[ordering: +6 opt(8)]" G13 cols=(6)) 1878 │ │ └── cost: 1120.05 1879 │ └── [] 1880 │ ├── best: (ensure-upsert-distinct-on G12="[ordering: +6 opt(8)]" G13 cols=(6),ordering=+6 opt(8)) 1881 │ └── cost: 1120.05 1882 ├── G7: (scan xyz) (scan xyz@zyx) 1883 │ ├── [ordering: +9] 1884 │ │ ├── best: (scan xyz) 1885 │ │ └── cost: 1060.02 1886 │ └── [] 1887 │ ├── best: (scan xyz) 1888 │ └── cost: 1060.02 1889 ├── G8: (filters G14) 1890 ├── G9: (filters) 1891 ├── G10: (lookup-join G6 G9 xyz@xy,keyCols=[6],outCols=(6-10)) 1892 │ └── [] 1893 │ ├── best: (lookup-join G6 G9 xyz@xy,keyCols=[6],outCols=(6-10)) 1894 │ └── cost: 7160.06 1895 ├── G11: (case G15 G16 G17) 1896 ├── G12: (project G18 G19 v w) 1897 │ ├── [ordering: +6 opt(8)] 1898 │ │ ├── best: (project G18="[ordering: +6]" G19 v w) 1899 │ │ └── cost: 1080.03 1900 │ └── [] 1901 │ ├── best: (project G18 G19 v w) 1902 │ └── cost: 1080.03 1903 ├── G13: (aggregations G20 G21) 1904 ├── G14: (eq G22 G23) 1905 ├── G15: (true) 1906 ├── G16: (scalar-list G24) 1907 ├── G17: (const 2.0) 1908 ├── G18: (scan kuvw,cols=(6,7)) (scan kuvw@uvw,cols=(6,7)) (scan kuvw@wvu,cols=(6,7)) (scan kuvw@vw,cols=(6,7)) (scan kuvw@w,cols=(6,7)) 1909 │ ├── [ordering: +6] 1910 │ │ ├── best: (scan kuvw@vw,cols=(6,7)) 1911 │ │ └── cost: 1060.02 1912 │ └── [] 1913 │ ├── best: (scan kuvw,cols=(6,7)) 1914 │ └── cost: 1060.02 1915 ├── G19: (projections G25) 1916 ├── G20: (first-agg G26) 1917 ├── G21: (first-agg G27) 1918 ├── G22: (variable v) 1919 ├── G23: (variable x) 1920 ├── G24: (when G28 G27) 1921 ├── G25: (const 1.0) 1922 ├── G26: (variable w) 1923 ├── G27: (variable "?column?") 1924 ├── G28: (is G23 G29) 1925 └── G29: (null)