- Notifications
You must be signed in to change notification settings - Fork5
Commitf9aefcb
committed
Support using index-only scans with partial indexes in more cases.
Previously, the planner would reject an index-only scan if any restrictionclause for its table used a column not available from the index, evenif that restriction clause would later be dropped from the plan entirelybecause it's implied by the index's predicate. This is a fairly commonsituation for partial indexes because predicates using columns not includedin the index are often the most useful kind of predicate, and we have toduplicate (or at least imply) the predicate in the WHERE clause in orderto get the index to be considered at all. So index-only scans wereessentially unavailable with such partial indexes.To fix, we have to do detection of implied-by-predicate clauses muchearlier in the planner. This patch puts it in check_index_predicates(nee check_partial_indexes), meaning it gets done for every partial index,whereas we previously only considered this issue at createplan time,so that the work was only done for an index actually selected for use.That could result in a noticeable planning slowdown for queries againsttables with many partial indexes. However, testing suggested that thereisn't really a significant cost, especially not with reasonable numbersof partial indexes. We do get a small additional benefit, which is thatcost_index is more accurate since it correctly discounts the evaluationcost of clauses that will be removed. We can also avoid considering suchclauses as potential indexquals, which saves useless matching cycles inthe case where the predicate columns aren't in the index, and preventsgenerating bogus plans that double-count the clause's selectivity whenthe columns are in the index.Tomas Vondra and Kyotaro Horiguchi, reviewed by Kevin Grittner andKonstantin Knizhnik, and whacked around a little by me1 parent3501f71 commitf9aefcb
File tree
11 files changed
+351
-92
lines changed- src
- backend
- nodes
- optimizer
- path
- plan
- util
- include
- nodes
- optimizer
- test/regress
- expected
- sql
11 files changed
+351
-92
lines changedLines changed: 1 addition & 0 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
2129 | 2129 |
| |
2130 | 2130 |
| |
2131 | 2131 |
| |
| 2132 | + | |
2132 | 2133 |
| |
2133 | 2134 |
| |
2134 | 2135 |
| |
|
Lines changed: 2 additions & 2 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
474 | 474 |
| |
475 | 475 |
| |
476 | 476 |
| |
477 |
| - | |
| 477 | + | |
478 | 478 |
| |
479 | 479 |
| |
480 | 480 |
| |
| |||
716 | 716 |
| |
717 | 717 |
| |
718 | 718 |
| |
719 |
| - | |
| 719 | + | |
720 | 720 |
| |
721 | 721 |
| |
722 | 722 |
| |
|
Lines changed: 13 additions & 10 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
433 | 433 |
| |
434 | 434 |
| |
435 | 435 |
| |
436 |
| - | |
| 436 | + | |
| 437 | + | |
| 438 | + | |
| 439 | + | |
437 | 440 |
| |
438 | 441 |
| |
439 | 442 |
| |
440 | 443 |
| |
441 | 444 |
| |
442 | 445 |
| |
443 |
| - | |
444 |
| - | |
| 446 | + | |
| 447 | + | |
445 | 448 |
| |
446 | 449 |
| |
447 | 450 |
| |
448 | 451 |
| |
449 | 452 |
| |
450 | 453 |
| |
451 | 454 |
| |
452 |
| - | |
| 455 | + | |
453 | 456 |
| |
454 | 457 |
| |
455 | 458 |
| |
| |||
631 | 634 |
| |
632 | 635 |
| |
633 | 636 |
| |
634 |
| - | |
635 |
| - | |
636 |
| - | |
637 |
| - | |
638 |
| - | |
| 637 | + | |
| 638 | + | |
| 639 | + | |
| 640 | + | |
| 641 | + | |
639 | 642 |
| |
640 | 643 |
| |
641 | 644 |
| |
| |||
654 | 657 |
| |
655 | 658 |
| |
656 | 659 |
| |
657 |
| - | |
| 660 | + | |
658 | 661 |
| |
659 | 662 |
| |
660 | 663 |
| |
|
Lines changed: 86 additions & 37 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
30 | 30 |
| |
31 | 31 |
| |
32 | 32 |
| |
| 33 | + | |
33 | 34 |
| |
34 | 35 |
| |
35 | 36 |
| |
| |||
216 | 217 |
| |
217 | 218 |
| |
218 | 219 |
| |
219 |
| - | |
| 220 | + | |
220 | 221 |
| |
221 | 222 |
| |
222 | 223 |
| |
| |||
1800 | 1801 |
| |
1801 | 1802 |
| |
1802 | 1803 |
| |
1803 |
| - | |
1804 |
| - | |
1805 |
| - | |
1806 |
| - | |
1807 |
| - | |
1808 |
| - | |
1809 |
| - | |
1810 |
| - | |
1811 | 1804 |
| |
1812 | 1805 |
| |
1813 | 1806 |
| |
1814 |
| - | |
1815 |
| - | |
1816 |
| - | |
| 1807 | + | |
| 1808 | + | |
| 1809 | + | |
1817 | 1810 |
| |
1818 | 1811 |
| |
1819 | 1812 |
| |
1820 |
| - | |
1821 |
| - | |
| 1813 | + | |
| 1814 | + | |
| 1815 | + | |
| 1816 | + | |
| 1817 | + | |
| 1818 | + | |
| 1819 | + | |
| 1820 | + | |
| 1821 | + | |
| 1822 | + | |
| 1823 | + | |
| 1824 | + | |
1822 | 1825 |
| |
1823 | 1826 |
| |
1824 | 1827 |
| |
| |||
2023 | 2026 |
| |
2024 | 2027 |
| |
2025 | 2028 |
| |
2026 |
| - | |
| 2029 | + | |
| 2030 | + | |
2027 | 2031 |
| |
2028 | 2032 |
| |
2029 | 2033 |
| |
| |||
2664 | 2668 |
| |
2665 | 2669 |
| |
2666 | 2670 |
| |
2667 |
| - | |
2668 |
| - | |
2669 |
| - | |
| 2671 | + | |
| 2672 | + | |
| 2673 | + | |
| 2674 | + | |
| 2675 | + | |
| 2676 | + | |
2670 | 2677 |
| |
2671 |
| - | |
2672 |
| - | |
2673 |
| - | |
| 2678 | + | |
| 2679 | + | |
| 2680 | + | |
| 2681 | + | |
| 2682 | + | |
| 2683 | + | |
| 2684 | + | |
| 2685 | + | |
| 2686 | + | |
| 2687 | + | |
| 2688 | + | |
| 2689 | + | |
2674 | 2690 |
| |
2675 | 2691 |
| |
2676 |
| - | |
| 2692 | + | |
2677 | 2693 |
| |
2678 | 2694 |
| |
2679 | 2695 |
| |
| 2696 | + | |
2680 | 2697 |
| |
2681 | 2698 |
| |
2682 | 2699 |
| |
2683 | 2700 |
| |
2684 |
| - | |
2685 |
| - | |
| 2701 | + | |
| 2702 | + | |
| 2703 | + | |
2686 | 2704 |
| |
2687 | 2705 |
| |
2688 | 2706 |
| |
2689 | 2707 |
| |
2690 | 2708 |
| |
2691 | 2709 |
| |
2692 |
| - | |
2693 |
| - | |
2694 |
| - | |
2695 |
| - | |
2696 |
| - | |
2697 |
| - | |
2698 |
| - | |
2699 |
| - | |
| 2710 | + | |
| 2711 | + | |
| 2712 | + | |
2700 | 2713 |
| |
2701 | 2714 |
| |
2702 | 2715 |
| |
| |||
2743 | 2756 |
| |
2744 | 2757 |
| |
2745 | 2758 |
| |
2746 |
| - | |
| 2759 | + | |
| 2760 | + | |
| 2761 | + | |
| 2762 | + | |
| 2763 | + | |
| 2764 | + | |
| 2765 | + | |
| 2766 | + | |
| 2767 | + | |
| 2768 | + | |
| 2769 | + | |
| 2770 | + | |
| 2771 | + | |
| 2772 | + | |
| 2773 | + | |
| 2774 | + | |
| 2775 | + | |
| 2776 | + | |
| 2777 | + | |
| 2778 | + | |
| 2779 | + | |
2747 | 2780 |
| |
2748 | 2781 |
| |
2749 | 2782 |
| |
| 2783 | + | |
2750 | 2784 |
| |
2751 | 2785 |
| |
2752 |
| - | |
| 2786 | + | |
2753 | 2787 |
| |
2754 |
| - | |
2755 |
| - | |
| 2788 | + | |
| 2789 | + | |
2756 | 2790 |
| |
2757 |
| - | |
| 2791 | + | |
| 2792 | + | |
| 2793 | + | |
| 2794 | + | |
| 2795 | + | |
| 2796 | + | |
| 2797 | + | |
| 2798 | + | |
| 2799 | + | |
| 2800 | + | |
| 2801 | + | |
| 2802 | + | |
| 2803 | + | |
| 2804 | + | |
| 2805 | + | |
| 2806 | + | |
2758 | 2807 |
| |
2759 | 2808 |
| |
2760 | 2809 |
| |
|
Lines changed: 30 additions & 34 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
35 | 35 |
| |
36 | 36 |
| |
37 | 37 |
| |
38 |
| - | |
39 | 38 |
| |
40 | 39 |
| |
41 | 40 |
| |
| |||
494 | 493 |
| |
495 | 494 |
| |
496 | 495 |
| |
| 496 | + | |
| 497 | + | |
| 498 | + | |
| 499 | + | |
| 500 | + | |
| 501 | + | |
| 502 | + | |
497 | 503 |
| |
498 |
| - | |
| 504 | + | |
| 505 | + | |
| 506 | + | |
| 507 | + | |
| 508 | + | |
| 509 | + | |
| 510 | + | |
| 511 | + | |
| 512 | + | |
| 513 | + | |
| 514 | + | |
499 | 515 |
| |
500 | 516 |
| |
501 | 517 |
| |
| |||
2385 | 2401 |
| |
2386 | 2402 |
| |
2387 | 2403 |
| |
2388 |
| - | |
2389 |
| - | |
2390 |
| - | |
2391 |
| - | |
2392 |
| - | |
2393 | 2404 |
| |
2394 | 2405 |
| |
2395 | 2406 |
| |
| |||
2405 | 2416 |
| |
2406 | 2417 |
| |
2407 | 2418 |
| |
2408 |
| - | |
2409 |
| - | |
2410 |
| - | |
2411 |
| - | |
2412 |
| - | |
2413 |
| - | |
2414 |
| - | |
2415 |
| - | |
2416 |
| - | |
2417 |
| - | |
2418 |
| - | |
2419 |
| - | |
2420 |
| - | |
2421 |
| - | |
2422 |
| - | |
| 2419 | + | |
| 2420 | + | |
| 2421 | + | |
2423 | 2422 |
| |
2424 | 2423 |
| |
2425 | 2424 |
| |
| |||
2556 | 2555 |
| |
2557 | 2556 |
| |
2558 | 2557 |
| |
2559 |
| - | |
2560 |
| - | |
2561 |
| - | |
2562 |
| - | |
2563 |
| - | |
| 2558 | + | |
| 2559 | + | |
| 2560 | + | |
| 2561 | + | |
| 2562 | + | |
| 2563 | + | |
2564 | 2564 |
| |
2565 | 2565 |
| |
2566 | 2566 |
| |
| |||
2575 | 2575 |
| |
2576 | 2576 |
| |
2577 | 2577 |
| |
2578 |
| - | |
2579 |
| - | |
2580 |
| - | |
2581 |
| - | |
2582 |
| - | |
2583 |
| - | |
2584 |
| - | |
| 2578 | + | |
| 2579 | + | |
| 2580 | + | |
2585 | 2581 |
| |
2586 | 2582 |
| |
2587 | 2583 |
| |
|
Lines changed: 2 additions & 1 deletion
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
339 | 339 |
| |
340 | 340 |
| |
341 | 341 |
| |
342 |
| - | |
| 342 | + | |
| 343 | + | |
343 | 344 |
| |
344 | 345 |
| |
345 | 346 |
| |
|
0 commit comments
Comments
(0)