forked frompostgres/postgres
- Notifications
You must be signed in to change notification settings - Fork6
Commitbd10ec5
committed
Detect redundant GROUP BY columns using UNIQUE indexes
d4c3a15 added support that when the GROUP BY contained all of thecolumns belonging to a relation's PRIMARY KEY, all other columnsbelonging to that relation would be removed from the GROUP BY clause.That's possible because all other columns are functionally dependent onthe PRIMARY KEY and those columns alone ensure the groups are distinct.Here we expand on that optimization and allow it to work for any uniqueindexes on the table rather than just the PRIMARY KEY index. Thisnormally requires that all columns in the index are defined with NOT NULL,however, we can relax that requirement when the index is defined withNULLS NOT DISTINCT.When there are multiple suitable indexes to allow columns to be removed,we prefer the index with the least number of columns as this allows usto remove the highest number of GROUP BY columns. One day, we may want torevisit that decision as it may make more sense to use the narrower set ofcolumns in terms of the width of the data types and stored/queried data.This also adjusts the code to make use of RelOptInfo.indexlist ratherthan looking up the catalog tables.In passing, add another short-circuit path to allow bailing out earlierin cases where it's certainly not possible to remove redundant GROUP BYcolumns. This early exit is now cheaper to do than when this code wasoriginally written as00b4146 made it cheaper to check for emptyBitmapsets.Patch originally by Zhang Mingli and later worked on by jian he, but afterI (David) worked on it, there was very little of the original left.Author: Zhang Mingli, jian he, David RowleyReviewed-by: jian he, Andrei LepikhovDiscussion:https://postgr.es/m/327990c8-b9b2-4b0c-bffb-462249f82de0%40Spark1 parentd8f3351 commitbd10ec5
File tree
5 files changed
+200
-22
lines changed- src
- backend/optimizer
- plan
- util
- include/nodes
- test/regress
- expected
- sql
5 files changed
+200
-22
lines changedLines changed: 98 additions & 22 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
400 | 400 |
| |
401 | 401 |
| |
402 | 402 |
| |
403 |
| - | |
404 |
| - | |
| 403 | + | |
| 404 | + | |
| 405 | + | |
405 | 406 |
| |
406 | 407 |
| |
407 |
| - | |
408 |
| - | |
409 |
| - | |
410 |
| - | |
411 |
| - | |
412 |
| - | |
413 |
| - | |
| 408 | + | |
| 409 | + | |
414 | 410 |
| |
415 | 411 |
| |
416 | 412 |
| |
417 | 413 |
| |
418 | 414 |
| |
419 | 415 |
| |
420 | 416 |
| |
| 417 | + | |
421 | 418 |
| |
422 | 419 |
| |
423 | 420 |
| |
| |||
457 | 454 |
| |
458 | 455 |
| |
459 | 456 |
| |
| 457 | + | |
| 458 | + | |
| 459 | + | |
| 460 | + | |
| 461 | + | |
| 462 | + | |
| 463 | + | |
460 | 464 |
| |
461 | 465 |
| |
462 | 466 |
| |
463 | 467 |
| |
| 468 | + | |
| 469 | + | |
| 470 | + | |
| 471 | + | |
| 472 | + | |
| 473 | + | |
| 474 | + | |
464 | 475 |
| |
465 | 476 |
| |
466 | 477 |
| |
| |||
472 | 483 |
| |
473 | 484 |
| |
474 | 485 |
| |
| 486 | + | |
475 | 487 |
| |
476 |
| - | |
477 |
| - | |
| 488 | + | |
| 489 | + | |
478 | 490 |
| |
479 | 491 |
| |
480 | 492 |
| |
| |||
495 | 507 |
| |
496 | 508 |
| |
497 | 509 |
| |
498 |
| - | |
499 |
| - | |
500 |
| - | |
501 |
| - | |
502 |
| - | |
503 |
| - | |
504 |
| - | |
| 510 | + | |
505 | 511 |
| |
506 | 512 |
| |
507 |
| - | |
508 |
| - | |
| 513 | + | |
| 514 | + | |
| 515 | + | |
509 | 516 |
| |
510 |
| - | |
| 517 | + | |
| 518 | + | |
| 519 | + | |
| 520 | + | |
| 521 | + | |
| 522 | + | |
| 523 | + | |
| 524 | + | |
| 525 | + | |
| 526 | + | |
| 527 | + | |
| 528 | + | |
| 529 | + | |
| 530 | + | |
| 531 | + | |
| 532 | + | |
| 533 | + | |
| 534 | + | |
| 535 | + | |
| 536 | + | |
| 537 | + | |
| 538 | + | |
| 539 | + | |
| 540 | + | |
| 541 | + | |
| 542 | + | |
| 543 | + | |
| 544 | + | |
| 545 | + | |
| 546 | + | |
| 547 | + | |
| 548 | + | |
| 549 | + | |
| 550 | + | |
| 551 | + | |
| 552 | + | |
| 553 | + | |
| 554 | + | |
| 555 | + | |
| 556 | + | |
| 557 | + | |
| 558 | + | |
| 559 | + | |
| 560 | + | |
| 561 | + | |
| 562 | + | |
| 563 | + | |
| 564 | + | |
| 565 | + | |
| 566 | + | |
| 567 | + | |
| 568 | + | |
| 569 | + | |
| 570 | + | |
| 571 | + | |
| 572 | + | |
| 573 | + | |
| 574 | + | |
| 575 | + | |
| 576 | + | |
| 577 | + | |
| 578 | + | |
| 579 | + | |
| 580 | + | |
| 581 | + | |
| 582 | + | |
| 583 | + | |
| 584 | + | |
| 585 | + | |
| 586 | + | |
511 | 587 |
| |
512 | 588 |
| |
513 | 589 |
| |
| |||
518 | 594 |
| |
519 | 595 |
| |
520 | 596 |
| |
521 |
| - | |
| 597 | + | |
522 | 598 |
| |
523 | 599 |
| |
524 | 600 |
| |
|
Lines changed: 1 addition & 0 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
457 | 457 |
| |
458 | 458 |
| |
459 | 459 |
| |
| 460 | + | |
460 | 461 |
| |
461 | 462 |
| |
462 | 463 |
| |
|
Lines changed: 2 additions & 0 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
1187 | 1187 |
| |
1188 | 1188 |
| |
1189 | 1189 |
| |
| 1190 | + | |
| 1191 | + | |
1190 | 1192 |
| |
1191 | 1193 |
| |
1192 | 1194 |
| |
|
Lines changed: 67 additions & 0 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
1448 | 1448 |
| |
1449 | 1449 |
| |
1450 | 1450 |
| |
| 1451 | + | |
| 1452 | + | |
| 1453 | + | |
| 1454 | + | |
| 1455 | + | |
| 1456 | + | |
| 1457 | + | |
| 1458 | + | |
| 1459 | + | |
| 1460 | + | |
| 1461 | + | |
| 1462 | + | |
| 1463 | + | |
| 1464 | + | |
| 1465 | + | |
| 1466 | + | |
| 1467 | + | |
| 1468 | + | |
| 1469 | + | |
| 1470 | + | |
| 1471 | + | |
| 1472 | + | |
| 1473 | + | |
| 1474 | + | |
| 1475 | + | |
| 1476 | + | |
| 1477 | + | |
| 1478 | + | |
| 1479 | + | |
| 1480 | + | |
| 1481 | + | |
| 1482 | + | |
| 1483 | + | |
| 1484 | + | |
| 1485 | + | |
| 1486 | + | |
| 1487 | + | |
| 1488 | + | |
| 1489 | + | |
| 1490 | + | |
| 1491 | + | |
| 1492 | + | |
| 1493 | + | |
| 1494 | + | |
| 1495 | + | |
| 1496 | + | |
| 1497 | + | |
| 1498 | + | |
| 1499 | + | |
| 1500 | + | |
| 1501 | + | |
| 1502 | + | |
| 1503 | + | |
| 1504 | + | |
| 1505 | + | |
| 1506 | + | |
| 1507 | + | |
| 1508 | + | |
| 1509 | + | |
| 1510 | + | |
| 1511 | + | |
| 1512 | + | |
| 1513 | + | |
| 1514 | + | |
| 1515 | + | |
| 1516 | + | |
| 1517 | + | |
1451 | 1518 |
| |
1452 | 1519 |
| |
1453 | 1520 |
| |
|
Lines changed: 32 additions & 0 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
507 | 507 |
| |
508 | 508 |
| |
509 | 509 |
| |
| 510 | + | |
| 511 | + | |
| 512 | + | |
| 513 | + | |
| 514 | + | |
| 515 | + | |
| 516 | + | |
| 517 | + | |
| 518 | + | |
| 519 | + | |
| 520 | + | |
| 521 | + | |
| 522 | + | |
| 523 | + | |
| 524 | + | |
| 525 | + | |
| 526 | + | |
| 527 | + | |
| 528 | + | |
| 529 | + | |
| 530 | + | |
| 531 | + | |
| 532 | + | |
| 533 | + | |
| 534 | + | |
| 535 | + | |
| 536 | + | |
| 537 | + | |
| 538 | + | |
| 539 | + | |
| 540 | + | |
| 541 | + | |
510 | 542 |
| |
511 | 543 |
| |
512 | 544 |
| |
|
0 commit comments
Comments
(0)