diff --strip-trailing-cr -U3 C:/cirrus/src/test/regress/expected/horology.out C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/horology.out --- C:/cirrus/src/test/regress/expected/horology.out 2024-12-24 13:09:44.946359300 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/horology.out 2024-12-24 13:13:59.773093700 +0000 @@ -2541,18 +2541,17 @@ explain (costs off) select count(*) from date_tbl where f1 between symmetric '1997-01-01' and '1998-01-01'; - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +---------------------------- Aggregate -> Seq Scan on date_tbl - Filter: ((f1 >= '01-01-1997'::date) AND (f1 <= '01-01-1998'::date)) -(3 rows) +(2 rows) select count(*) from date_tbl where f1 between symmetric '1997-01-01' and '1998-01-01'; count ------- - 3 + 16 (1 row) explain (costs off) diff --strip-trailing-cr -U3 C:/cirrus/src/test/regress/expected/inherit.out C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/inherit.out --- C:/cirrus/src/test/regress/expected/inherit.out 2024-12-24 13:09:44.954400400 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/inherit.out 2024-12-24 13:14:18.670064100 +0000 @@ -2766,11 +2766,13 @@ (5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); - QUERY PLAN ----------------------------------------------------------- - Seq Scan on part_ab_cd list_parted - Filter: (((a)::text = 'ab'::text) OR (a = 'cd'::text)) -(2 rows) + QUERY PLAN +---------------------------------------------- + Append + -> Seq Scan on part_ab_cd list_parted_1 + -> Seq Scan on part_ef_gh list_parted_2 + -> Seq Scan on part_null_xy list_parted_3 +(4 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN diff --strip-trailing-cr -U3 C:/cirrus/src/test/regress/expected/join.out C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/join.out --- C:/cirrus/src/test/regress/expected/join.out 2024-12-24 13:09:44.964447400 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/join.out 2024-12-24 13:14:25.226218800 +0000 @@ -8072,8 +8072,7 @@ Join Filter: (j2.id2 = j1.id2) -> Index Scan using j1_id1_idx on j1 -> Index Scan using j2_id1_idx on j2 - Index Cond: (id1 >= 1) -(6 rows) +(5 rows) select * from j1 inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 diff --strip-trailing-cr -U3 C:/cirrus/src/test/regress/expected/stats_ext.out C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/stats_ext.out --- C:/cirrus/src/test/regress/expected/stats_ext.out 2024-12-24 13:09:45.046949700 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/stats_ext.out 2024-12-24 13:15:04.247211900 +0000 @@ -1219,19 +1219,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a < ANY (ARRAY[1, 51]) AND b > ''1'''); estimated | actual -----------+-------- - 2448 | 2400 + 4800 | 4800 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a >= ANY (ARRAY[1, 51]) AND b <= ANY (ARRAY[''1'', ''2''])'); estimated | actual -----------+-------- - 1287 | 1250 + 5000 | 5000 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a <= ANY (ARRAY[1, 2, 51, 52]) AND b >= ANY (ARRAY[''1'', ''2''])'); estimated | actual -----------+-------- - 2597 | 2550 + 5000 | 5000 (1 row) -- ALL (should not benefit from functional dependencies) @@ -1385,19 +1385,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a < ANY (ARRAY[1, 51]) AND b > ''1'''); estimated | actual -----------+-------- - 2448 | 2400 + 4800 | 4800 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a >= ANY (ARRAY[1, 51]) AND b <= ANY (ARRAY[''1'', ''2''])'); estimated | actual -----------+-------- - 1287 | 1250 + 5000 | 5000 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a <= ANY (ARRAY[1, 2, 51, 52]) AND b >= ANY (ARRAY[''1'', ''2''])'); estimated | actual -----------+-------- - 2597 | 2550 + 5000 | 5000 (1 row) -- ALL (should not benefit from functional dependencies) @@ -2032,37 +2032,37 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, 2, 3]) AND b IN (''1'', ''2'', ''3'')'); estimated | actual -----------+-------- - 12 | 150 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, NULL, 2, 3]) AND b IN (''1'', ''2'', NULL, ''3'')'); estimated | actual -----------+-------- - 12 | 150 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3])'); estimated | actual -----------+-------- - 184 | 100 + 5000 | 5000 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3, NULL])'); estimated | actual -----------+-------- - 184 | 100 + 5000 | 5000 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', ''3'') AND c > ANY (ARRAY[1, 2, 3])'); estimated | actual -----------+-------- - 11 | 100 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', NULL, ''3'') AND c > ANY (ARRAY[1, 2, NULL, 3])'); estimated | actual -----------+-------- - 11 | 100 + 276 | 200 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[4,5]) AND 4 = ANY(ia)'); @@ -2185,37 +2185,37 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, 2, 3]) AND b IN (''1'', ''2'', ''3'')'); estimated | actual -----------+-------- - 150 | 150 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, NULL, 2, 3]) AND b IN (''1'', ''2'', NULL, ''3'')'); estimated | actual -----------+-------- - 150 | 150 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3])'); estimated | actual -----------+-------- - 100 | 100 + 5000 | 5000 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3, NULL])'); estimated | actual -----------+-------- - 100 | 100 + 5000 | 5000 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', ''3'') AND c > ANY (ARRAY[1, 2, 3])'); estimated | actual -----------+-------- - 100 | 100 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', NULL, ''3'') AND c > ANY (ARRAY[1, 2, NULL, 3])'); estimated | actual -----------+-------- - 100 | 100 + 300 | 300 (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[4,5]) AND 4 = ANY(ia)'); diff --strip-trailing-cr -U3 C:/cirrus/src/test/regress/expected/self_contradictory.out C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/self_contradictory.out --- C:/cirrus/src/test/regress/expected/self_contradictory.out 2024-12-24 13:09:45.040912200 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/self_contradictory.out 2024-12-24 13:15:54.798976300 +0000 @@ -1196,12 +1196,9 @@ -- explain (costs off) SELECT * FROM self_b WHERE a > 'c' AND a < 'b' COLLATE "pg_c_utf8"; - QUERY PLAN ------------------------------------------------------------------------------------ - Seq Scan on self_b - Filter: (((a)::text > 'c'::text) AND ((a)::text < 'b'::text COLLATE pg_c_utf8)) -(2 rows) - +ERROR: collation "pg_c_utf8" for encoding "WIN1252" does not exist +LINE 2: SELECT * FROM self_b WHERE a > 'c' AND a < 'b' COLLATE "pg... + ^ explain (costs off) SELECT * FROM self_b WHERE a > 'c' AND a < 'b'; QUERY PLAN @@ -1283,35 +1280,629 @@ QUERY PLAN -------------------- Seq Scan on self_a - Filter: (b < 8) -(2 rows) +(1 row) SELECT * FROM self_a WHERE a < 9 AND a > 10 OR b < 8; - a | b | c | d | z ------+---+-----+-----+--- - 283 | 4 | 598 | 347 | t -(1 row) + a | b | c | d | z +------+-----+-----+-----+--- + 56 | 118 | 32 | 780 | t + 854 | 610 | 991 | 526 | f + 837 | 872 | 417 | 130 | t + 193 | 326 | 902 | 976 | f + 414 | 913 | 549 | 289 | t + 733 | 159 | 285 | 496 | f + 44 | 253 | 569 | 357 | t + 269 | 39 | 540 | 941 | t + 807 | 974 | 329 | 4 | f + 392 | 582 | 559 | 657 | t + 836 | 337 | 672 | 303 | f + 241 | 161 | 464 | 926 | t + 569 | 792 | 682 | 462 | f + 994 | 629 | 653 | 562 | t + 614 | 505 | 543 | 510 | f + 796 | 634 | 72 | 519 | f + 982 | 357 | 767 | 752 | t + 318 | 13 | 119 | 148 | f + 568 | 940 | 548 | 426 | t + 356 | 732 | 660 | 303 | t + 332 | 755 | 409 | 493 | t + 263 | 710 | 622 | 40 | f + 205 | 128 | 909 | 468 | t + 6 | 514 | 536 | 746 | f + 153 | 850 | 127 | 470 | t + 289 | 430 | 843 | 929 | t + 797 | 168 | 438 | 902 | f + 870 | 667 | 792 | 275 | f + 247 | 382 | 911 | 522 | f + 27 | 203 | 125 | 400 | f + 153 | 678 | 79 | 278 | f + 731 | 516 | 81 | 324 | f + 602 | 648 | 892 | 787 | t + 691 | 177 | 873 | 183 | f + 455 | 962 | 675 | 268 | f + 899 | 679 | 71 | 88 | f + 755 | 985 | 569 | 555 | f + 862 | 344 | 659 | 984 | t + 222 | 163 | 299 | 75 | f + 139 | 603 | 93 | 681 | f + 196 | 783 | 85 | 485 | f + 688 | 963 | 463 | 465 | t + 804 | 894 | 833 | 52 | f + 254 | 332 | 481 | 563 | t + 564 | 43 | 33 | 445 | f + 283 | 4 | 598 | 347 | t + 108 | 736 | 693 | 731 | f + 836 | 502 | 543 | 294 | f + 900 | 739 | 645 | 132 | t + 918 | 798 | 360 | 621 | f + 302 | 998 | 212 | 696 | t + 711 | 906 | 273 | 384 | f + 699 | 884 | 563 | 120 | t + 357 | 456 | 709 | 762 | t + 708 | 276 | 237 | 862 | f + 274 | 40 | 478 | 495 | t + 564 | 973 | 743 | 123 | t + 195 | 848 | 401 | 908 | f + 819 | 38 | 449 | 715 | f + 688 | 549 | 702 | 819 | f + 242 | 716 | 55 | 88 | t + 866 | 508 | 610 | 30 | f + 720 | 558 | 719 | 721 | t + 895 | 106 | 7 | 231 | f + 895 | 926 | 991 | 18 | t + 15 | 49 | 670 | 482 | t + 499 | 122 | 139 | 68 | f + 44 | 486 | 900 | 526 | f + 502 | 390 | 266 | 59 | t + 197 | 481 | 798 | 954 | t + 1000 | 512 | 245 | 310 | f + 207 | 938 | 856 | 191 | t + 666 | 883 | 47 | 664 | f + 964 | 852 | 439 | 173 | f + 294 | 262 | 404 | 259 | f + 81 | 799 | 100 | 620 | t + 633 | 272 | 145 | 644 | t + 287 | 256 | 875 | 53 | f + 962 | 216 | 696 | 315 | f + 299 | 875 | 355 | 584 | f + 645 | 249 | 655 | 667 | t + 223 | 71 | 627 | 251 | t + 677 | 849 | 978 | 114 | f + 130 | 199 | 836 | 596 | f + 710 | 775 | 416 | 215 | f + 146 | 621 | 442 | 338 | t + 924 | 349 | 600 | 116 | f + 165 | 330 | 678 | 57 | t + 593 | 187 | 258 | 974 | t + 951 | 698 | 928 | 139 | f + 322 | 965 | 961 | 464 | f + 884 | 683 | 173 | 38 | t + 701 | 443 | 368 | 748 | t + 461 | 619 | 15 | 596 | t + 526 | 364 | 607 | 397 | t + 13 | 937 | 247 | 801 | f + 538 | 82 | 145 | 879 | f + 169 | 168 | 389 | 321 | t + 405 | 769 | 699 | 908 | t + 348 | 429 | 644 | 521 | f + 838 | 877 | 729 | 7 | f + 989 | 254 | 808 | 71 | t + 699 | 140 | 379 | 557 | t + 493 | 671 | 500 | 809 | t + 177 | 403 | 995 | 70 | t + 750 | 967 | 470 | 128 | t + 955 | 473 | 813 | 315 | t + 742 | 784 | 627 | 420 | f + 403 | 717 | 646 | 329 | t + 292 | 990 | 701 | 988 | f + 79 | 78 | 834 | 834 | f + 440 | 724 | 690 | 536 | f + 707 | 875 | 351 | 404 | t + 945 | 955 | 885 | 897 | f + 108 | 476 | 668 | 405 | f + 986 | 343 | 552 | 587 | t + 629 | 22 | 667 | 504 | t + 104 | 146 | 576 | 513 | t + 356 | 991 | 397 | 396 | t + 899 | 817 | 237 | 392 | t + 240 | 393 | 590 | 310 | f + 906 | 573 | 999 | 354 | f + 907 | 66 | 936 | 59 | f + 577 | 327 | 245 | 713 | f + 254 | 271 | 113 | 58 | t + 326 | 43 | 673 | 87 | t + 867 | 954 | 699 | 647 | f + 99 | 826 | 378 | 172 | f + 158 | 375 | 950 | 802 | f + 104 | 157 | 523 | 396 | t + 902 | 125 | 613 | 44 | f + 564 | 635 | 608 | 48 | t + 753 | 539 | 692 | 111 | t + 27 | 613 | 633 | 693 | t + 859 | 855 | 512 | 28 | f + 684 | 452 | 757 | 209 | f + 198 | 591 | 500 | 940 | f + 442 | 198 | 182 | 663 | t + 797 | 965 | 909 | 98 | f + 466 | 665 | 849 | 828 | t + 615 | 689 | 767 | 773 | f + 743 | 760 | 860 | 232 | t + 86 | 444 | 683 | 217 | t + 772 | 725 | 436 | 862 | t + 34 | 820 | 627 | 320 | t + 5 | 437 | 23 | 829 | f + 697 | 222 | 752 | 984 | t + 426 | 273 | 972 | 901 | f + 782 | 610 | 78 | 267 | t + 238 | 126 | 75 | 601 | t + 385 | 443 | 912 | 100 | t + 939 | 715 | 692 | 21 | t + 108 | 291 | 894 | 132 | f + 632 | 316 | 108 | 815 | t + 849 | 847 | 786 | 601 | f + 218 | 153 | 138 | 574 | t + 947 | 673 | 758 | 151 | t + 146 | 807 | 30 | 963 | f + 748 | 222 | 931 | 664 | t + 916 | 19 | 100 | 457 | f + 921 | 287 | 386 | 201 | t + 521 | 177 | 935 | 268 | t + 58 | 222 | 195 | 749 | t + 95 | 430 | 46 | 825 | t + 290 | 121 | 520 | 759 | f + 655 | 640 | 203 | 331 | f + 30 | 950 | 632 | 526 | t + 997 | 996 | 179 | 575 | t + 523 | 881 | 463 | 602 | f + 277 | 298 | 806 | 724 | t + 601 | 467 | 772 | 305 | f + 169 | 341 | 646 | 492 | f + 739 | 718 | 98 | 513 | t + 91 | 996 | 871 | 954 | t + 248 | 367 | 704 | 899 | t + 679 | 192 | 687 | 192 | t + 840 | 429 | 830 | 93 | f + 750 | 263 | 652 | 749 | t + 835 | 478 | 266 | 402 | t + 264 | 277 | 668 | 230 | t + 55 | 662 | 473 | 776 | t + 800 | 594 | 825 | 638 | t + 549 | 29 | 951 | 607 | f + 472 | 908 | 939 | 703 | t + 536 | 969 | 814 | 303 | t + 316 | 173 | 228 | 312 | f + 584 | 885 | 329 | 479 | f + 229 | 716 | 536 | 417 | f + 177 | 769 | 282 | 339 | t + 987 | 512 | 477 | 629 | f + 744 | 252 | 336 | 551 | f + 655 | 429 | 250 | 603 | t + 965 | 733 | 863 | 363 | f + 459 | 276 | 576 | 510 | t + 626 | 986 | 829 | 345 | f + 434 | 614 | 655 | 288 | t + 570 | 17 | 675 | 416 | t + 882 | 270 | 147 | 862 | f + 593 | 669 | 145 | 386 | f + 313 | 369 | 21 | 781 | t + 869 | 116 | 133 | 611 | t + 604 | 368 | 695 | 100 | t + 943 | 847 | 120 | 483 | t + 396 | 231 | 437 | 772 | f + 583 | 177 | 580 | 506 | t + 170 | 542 | 784 | 61 | f + 978 | 493 | 12 | 236 | f + 222 | 162 | 612 | 15 | f + 585 | 138 | 641 | 274 | t + 246 | 657 | 243 | 852 | t + 62 | 869 | 96 | 25 | f + 847 | 224 | 676 | 869 | f + 211 | 709 | 672 | 470 | t + 366 | 647 | 964 | 221 | f + 980 | 847 | 37 | 587 | f + 450 | 386 | 594 | 112 | f + 729 | 463 | 931 | 920 | f + 498 | 604 | 51 | 871 | f + 464 | 727 | 491 | 891 | f + 522 | 316 | 82 | 316 | t + 32 | 27 | 138 | 337 | f + 733 | 939 | 991 | 449 | t + 221 | 235 | 992 | 855 | f + 770 | 463 | 881 | 616 | f + 558 | 738 | 71 | 74 | f + 914 | 682 | 217 | 242 | f + 524 | 444 | 281 | 455 | f + 778 | 756 | 230 | 393 | f + 114 | 63 | 606 | 47 | t + 657 | 625 | 890 | 806 | f + 988 | 903 | 337 | 739 | t + 536 | 189 | 430 | 446 | t + 357 | 44 | 70 | 638 | f + 540 | 578 | 772 | 484 | t + 567 | 975 | 658 | 611 | t + 458 | 747 | 744 | 234 | f + 695 | 291 | 666 | 535 | f + 4 | 23 | 607 | 997 | t + 345 | 401 | 923 | 576 | t + 634 | 912 | 328 | 860 | t + 104 | 807 | 925 | 747 | t + 155 | 19 | 238 | 464 | t + 569 | 117 | 844 | 808 | f + 171 | 227 | 751 | 287 | t + 196 | 762 | 443 | 759 | t + 657 | 445 | 668 | 569 | f + 372 | 215 | 899 | 368 | t + 223 | 794 | 499 | 736 | f + 433 | 94 | 321 | 359 | t + 423 | 575 | 251 | 637 | f + 508 | 734 | 783 | 781 | t + 278 | 653 | 931 | 500 | f + 726 | 993 | 9 | 298 | t + 677 | 426 | 376 | 226 | f + 967 | 639 | 659 | 731 | f + 488 | 774 | 321 | 573 | f + 65 | 909 | 5 | 331 | t + 426 | 554 | 297 | 168 | t + 460 | 846 | 500 | 416 | t + 887 | 664 | 705 | 592 | t + 98 | 83 | 24 | 146 | f + 79 | 343 | 431 | 601 | f + 127 | 942 | 871 | 783 | t + 20 | 441 | 998 | 508 | f + 267 | 62 | 303 | 239 | t + 239 | 62 | 439 | 837 | t + 89 | 686 | 252 | 569 | f + 891 | 135 | 718 | 114 | t + 606 | 246 | 74 | 214 | f + 552 | 359 | 115 | 783 | f + 712 | 930 | 700 | 976 | f + 107 | 852 | 200 | 230 | t + 554 | 263 | 759 | 69 | f + 391 | 345 | 544 | 436 | f + 12 | 425 | 493 | 606 | f + 869 | 453 | 75 | 48 | t + 455 | 58 | 920 | 372 | t + 227 | 864 | 928 | 982 | f + 6 | 513 | 218 | 928 | f + 844 | 740 | 455 | 993 | f + 850 | 418 | 336 | 521 | f + 209 | 470 | 482 | 456 | t + 177 | 631 | 900 | 590 | t + 899 | 485 | 322 | 890 | t + 733 | 33 | 64 | 48 | t + 700 | 47 | 989 | 640 | f + 252 | 571 | 608 | 239 | f + 480 | 100 | 299 | 783 | f + 40 | 341 | 956 | 192 | f + 304 | 201 | 128 | 344 | t + 150 | 276 | 114 | 435 | f + 412 | 482 | 930 | 265 | f + 219 | 597 | 726 | 347 | t + 248 | 74 | 303 | 763 | t + 402 | 604 | 706 | 483 | t + 491 | 662 | 324 | 100 | f + 680 | 890 | 651 | 440 | t + 42 | 791 | 890 | 623 | t + 144 | 166 | 928 | 774 | f + 272 | 507 | 710 | 514 | t + | | | | +(301 rows) -- -- Test or-clause redundant clause will be eliminated -- explain (costs off) SELECT * FROM self_a WHERE a < 14 AND a < 10 OR b < 8; - QUERY PLAN ---------------------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: ((a < 10) OR (b < 8)) -(2 rows) +(1 row) SELECT * FROM self_a WHERE a < 14 AND a < 10 OR b < 8; - a | b | c | d | z ------+-----+-----+-----+--- - 6 | 514 | 536 | 746 | f - 283 | 4 | 598 | 347 | t - 5 | 437 | 23 | 829 | f - 4 | 23 | 607 | 997 | t - 6 | 513 | 218 | 928 | f -(5 rows) + a | b | c | d | z +------+-----+-----+-----+--- + 56 | 118 | 32 | 780 | t + 854 | 610 | 991 | 526 | f + 837 | 872 | 417 | 130 | t + 193 | 326 | 902 | 976 | f + 414 | 913 | 549 | 289 | t + 733 | 159 | 285 | 496 | f + 44 | 253 | 569 | 357 | t + 269 | 39 | 540 | 941 | t + 807 | 974 | 329 | 4 | f + 392 | 582 | 559 | 657 | t + 836 | 337 | 672 | 303 | f + 241 | 161 | 464 | 926 | t + 569 | 792 | 682 | 462 | f + 994 | 629 | 653 | 562 | t + 614 | 505 | 543 | 510 | f + 796 | 634 | 72 | 519 | f + 982 | 357 | 767 | 752 | t + 318 | 13 | 119 | 148 | f + 568 | 940 | 548 | 426 | t + 356 | 732 | 660 | 303 | t + 332 | 755 | 409 | 493 | t + 263 | 710 | 622 | 40 | f + 205 | 128 | 909 | 468 | t + 6 | 514 | 536 | 746 | f + 153 | 850 | 127 | 470 | t + 289 | 430 | 843 | 929 | t + 797 | 168 | 438 | 902 | f + 870 | 667 | 792 | 275 | f + 247 | 382 | 911 | 522 | f + 27 | 203 | 125 | 400 | f + 153 | 678 | 79 | 278 | f + 731 | 516 | 81 | 324 | f + 602 | 648 | 892 | 787 | t + 691 | 177 | 873 | 183 | f + 455 | 962 | 675 | 268 | f + 899 | 679 | 71 | 88 | f + 755 | 985 | 569 | 555 | f + 862 | 344 | 659 | 984 | t + 222 | 163 | 299 | 75 | f + 139 | 603 | 93 | 681 | f + 196 | 783 | 85 | 485 | f + 688 | 963 | 463 | 465 | t + 804 | 894 | 833 | 52 | f + 254 | 332 | 481 | 563 | t + 564 | 43 | 33 | 445 | f + 283 | 4 | 598 | 347 | t + 108 | 736 | 693 | 731 | f + 836 | 502 | 543 | 294 | f + 900 | 739 | 645 | 132 | t + 918 | 798 | 360 | 621 | f + 302 | 998 | 212 | 696 | t + 711 | 906 | 273 | 384 | f + 699 | 884 | 563 | 120 | t + 357 | 456 | 709 | 762 | t + 708 | 276 | 237 | 862 | f + 274 | 40 | 478 | 495 | t + 564 | 973 | 743 | 123 | t + 195 | 848 | 401 | 908 | f + 819 | 38 | 449 | 715 | f + 688 | 549 | 702 | 819 | f + 242 | 716 | 55 | 88 | t + 866 | 508 | 610 | 30 | f + 720 | 558 | 719 | 721 | t + 895 | 106 | 7 | 231 | f + 895 | 926 | 991 | 18 | t + 15 | 49 | 670 | 482 | t + 499 | 122 | 139 | 68 | f + 44 | 486 | 900 | 526 | f + 502 | 390 | 266 | 59 | t + 197 | 481 | 798 | 954 | t + 1000 | 512 | 245 | 310 | f + 207 | 938 | 856 | 191 | t + 666 | 883 | 47 | 664 | f + 964 | 852 | 439 | 173 | f + 294 | 262 | 404 | 259 | f + 81 | 799 | 100 | 620 | t + 633 | 272 | 145 | 644 | t + 287 | 256 | 875 | 53 | f + 962 | 216 | 696 | 315 | f + 299 | 875 | 355 | 584 | f + 645 | 249 | 655 | 667 | t + 223 | 71 | 627 | 251 | t + 677 | 849 | 978 | 114 | f + 130 | 199 | 836 | 596 | f + 710 | 775 | 416 | 215 | f + 146 | 621 | 442 | 338 | t + 924 | 349 | 600 | 116 | f + 165 | 330 | 678 | 57 | t + 593 | 187 | 258 | 974 | t + 951 | 698 | 928 | 139 | f + 322 | 965 | 961 | 464 | f + 884 | 683 | 173 | 38 | t + 701 | 443 | 368 | 748 | t + 461 | 619 | 15 | 596 | t + 526 | 364 | 607 | 397 | t + 13 | 937 | 247 | 801 | f + 538 | 82 | 145 | 879 | f + 169 | 168 | 389 | 321 | t + 405 | 769 | 699 | 908 | t + 348 | 429 | 644 | 521 | f + 838 | 877 | 729 | 7 | f + 989 | 254 | 808 | 71 | t + 699 | 140 | 379 | 557 | t + 493 | 671 | 500 | 809 | t + 177 | 403 | 995 | 70 | t + 750 | 967 | 470 | 128 | t + 955 | 473 | 813 | 315 | t + 742 | 784 | 627 | 420 | f + 403 | 717 | 646 | 329 | t + 292 | 990 | 701 | 988 | f + 79 | 78 | 834 | 834 | f + 440 | 724 | 690 | 536 | f + 707 | 875 | 351 | 404 | t + 945 | 955 | 885 | 897 | f + 108 | 476 | 668 | 405 | f + 986 | 343 | 552 | 587 | t + 629 | 22 | 667 | 504 | t + 104 | 146 | 576 | 513 | t + 356 | 991 | 397 | 396 | t + 899 | 817 | 237 | 392 | t + 240 | 393 | 590 | 310 | f + 906 | 573 | 999 | 354 | f + 907 | 66 | 936 | 59 | f + 577 | 327 | 245 | 713 | f + 254 | 271 | 113 | 58 | t + 326 | 43 | 673 | 87 | t + 867 | 954 | 699 | 647 | f + 99 | 826 | 378 | 172 | f + 158 | 375 | 950 | 802 | f + 104 | 157 | 523 | 396 | t + 902 | 125 | 613 | 44 | f + 564 | 635 | 608 | 48 | t + 753 | 539 | 692 | 111 | t + 27 | 613 | 633 | 693 | t + 859 | 855 | 512 | 28 | f + 684 | 452 | 757 | 209 | f + 198 | 591 | 500 | 940 | f + 442 | 198 | 182 | 663 | t + 797 | 965 | 909 | 98 | f + 466 | 665 | 849 | 828 | t + 615 | 689 | 767 | 773 | f + 743 | 760 | 860 | 232 | t + 86 | 444 | 683 | 217 | t + 772 | 725 | 436 | 862 | t + 34 | 820 | 627 | 320 | t + 5 | 437 | 23 | 829 | f + 697 | 222 | 752 | 984 | t + 426 | 273 | 972 | 901 | f + 782 | 610 | 78 | 267 | t + 238 | 126 | 75 | 601 | t + 385 | 443 | 912 | 100 | t + 939 | 715 | 692 | 21 | t + 108 | 291 | 894 | 132 | f + 632 | 316 | 108 | 815 | t + 849 | 847 | 786 | 601 | f + 218 | 153 | 138 | 574 | t + 947 | 673 | 758 | 151 | t + 146 | 807 | 30 | 963 | f + 748 | 222 | 931 | 664 | t + 916 | 19 | 100 | 457 | f + 921 | 287 | 386 | 201 | t + 521 | 177 | 935 | 268 | t + 58 | 222 | 195 | 749 | t + 95 | 430 | 46 | 825 | t + 290 | 121 | 520 | 759 | f + 655 | 640 | 203 | 331 | f + 30 | 950 | 632 | 526 | t + 997 | 996 | 179 | 575 | t + 523 | 881 | 463 | 602 | f + 277 | 298 | 806 | 724 | t + 601 | 467 | 772 | 305 | f + 169 | 341 | 646 | 492 | f + 739 | 718 | 98 | 513 | t + 91 | 996 | 871 | 954 | t + 248 | 367 | 704 | 899 | t + 679 | 192 | 687 | 192 | t + 840 | 429 | 830 | 93 | f + 750 | 263 | 652 | 749 | t + 835 | 478 | 266 | 402 | t + 264 | 277 | 668 | 230 | t + 55 | 662 | 473 | 776 | t + 800 | 594 | 825 | 638 | t + 549 | 29 | 951 | 607 | f + 472 | 908 | 939 | 703 | t + 536 | 969 | 814 | 303 | t + 316 | 173 | 228 | 312 | f + 584 | 885 | 329 | 479 | f + 229 | 716 | 536 | 417 | f + 177 | 769 | 282 | 339 | t + 987 | 512 | 477 | 629 | f + 744 | 252 | 336 | 551 | f + 655 | 429 | 250 | 603 | t + 965 | 733 | 863 | 363 | f + 459 | 276 | 576 | 510 | t + 626 | 986 | 829 | 345 | f + 434 | 614 | 655 | 288 | t + 570 | 17 | 675 | 416 | t + 882 | 270 | 147 | 862 | f + 593 | 669 | 145 | 386 | f + 313 | 369 | 21 | 781 | t + 869 | 116 | 133 | 611 | t + 604 | 368 | 695 | 100 | t + 943 | 847 | 120 | 483 | t + 396 | 231 | 437 | 772 | f + 583 | 177 | 580 | 506 | t + 170 | 542 | 784 | 61 | f + 978 | 493 | 12 | 236 | f + 222 | 162 | 612 | 15 | f + 585 | 138 | 641 | 274 | t + 246 | 657 | 243 | 852 | t + 62 | 869 | 96 | 25 | f + 847 | 224 | 676 | 869 | f + 211 | 709 | 672 | 470 | t + 366 | 647 | 964 | 221 | f + 980 | 847 | 37 | 587 | f + 450 | 386 | 594 | 112 | f + 729 | 463 | 931 | 920 | f + 498 | 604 | 51 | 871 | f + 464 | 727 | 491 | 891 | f + 522 | 316 | 82 | 316 | t + 32 | 27 | 138 | 337 | f + 733 | 939 | 991 | 449 | t + 221 | 235 | 992 | 855 | f + 770 | 463 | 881 | 616 | f + 558 | 738 | 71 | 74 | f + 914 | 682 | 217 | 242 | f + 524 | 444 | 281 | 455 | f + 778 | 756 | 230 | 393 | f + 114 | 63 | 606 | 47 | t + 657 | 625 | 890 | 806 | f + 988 | 903 | 337 | 739 | t + 536 | 189 | 430 | 446 | t + 357 | 44 | 70 | 638 | f + 540 | 578 | 772 | 484 | t + 567 | 975 | 658 | 611 | t + 458 | 747 | 744 | 234 | f + 695 | 291 | 666 | 535 | f + 4 | 23 | 607 | 997 | t + 345 | 401 | 923 | 576 | t + 634 | 912 | 328 | 860 | t + 104 | 807 | 925 | 747 | t + 155 | 19 | 238 | 464 | t + 569 | 117 | 844 | 808 | f + 171 | 227 | 751 | 287 | t + 196 | 762 | 443 | 759 | t + 657 | 445 | 668 | 569 | f + 372 | 215 | 899 | 368 | t + 223 | 794 | 499 | 736 | f + 433 | 94 | 321 | 359 | t + 423 | 575 | 251 | 637 | f + 508 | 734 | 783 | 781 | t + 278 | 653 | 931 | 500 | f + 726 | 993 | 9 | 298 | t + 677 | 426 | 376 | 226 | f + 967 | 639 | 659 | 731 | f + 488 | 774 | 321 | 573 | f + 65 | 909 | 5 | 331 | t + 426 | 554 | 297 | 168 | t + 460 | 846 | 500 | 416 | t + 887 | 664 | 705 | 592 | t + 98 | 83 | 24 | 146 | f + 79 | 343 | 431 | 601 | f + 127 | 942 | 871 | 783 | t + 20 | 441 | 998 | 508 | f + 267 | 62 | 303 | 239 | t + 239 | 62 | 439 | 837 | t + 89 | 686 | 252 | 569 | f + 891 | 135 | 718 | 114 | t + 606 | 246 | 74 | 214 | f + 552 | 359 | 115 | 783 | f + 712 | 930 | 700 | 976 | f + 107 | 852 | 200 | 230 | t + 554 | 263 | 759 | 69 | f + 391 | 345 | 544 | 436 | f + 12 | 425 | 493 | 606 | f + 869 | 453 | 75 | 48 | t + 455 | 58 | 920 | 372 | t + 227 | 864 | 928 | 982 | f + 6 | 513 | 218 | 928 | f + 844 | 740 | 455 | 993 | f + 850 | 418 | 336 | 521 | f + 209 | 470 | 482 | 456 | t + 177 | 631 | 900 | 590 | t + 899 | 485 | 322 | 890 | t + 733 | 33 | 64 | 48 | t + 700 | 47 | 989 | 640 | f + 252 | 571 | 608 | 239 | f + 480 | 100 | 299 | 783 | f + 40 | 341 | 956 | 192 | f + 304 | 201 | 128 | 344 | t + 150 | 276 | 114 | 435 | f + 412 | 482 | 930 | 265 | f + 219 | 597 | 726 | 347 | t + 248 | 74 | 303 | 763 | t + 402 | 604 | 706 | 483 | t + 491 | 662 | 324 | 100 | f + 680 | 890 | 651 | 440 | t + 42 | 791 | 890 | 623 | t + 144 | 166 | 928 | 774 | f + 272 | 507 | 710 | 514 | t + | | | | +(301 rows) -- -- Test same level and-clause whether can be push down into or-clause to test self-contradictory and can be pruned @@ -1326,17 +1917,311 @@ explain (costs off) SELECT * FROM self_a WHERE a > 10 AND b > 12 AND (a < 9 AND b < 11 OR b <=16); - QUERY PLAN -------------------------------------------------- + QUERY PLAN +----------------------------------- Seq Scan on self_a - Filter: ((a > 10) AND (b > 12) AND (b <= 16)) + Filter: ((a > 10) AND (b > 12)) (2 rows) SELECT * FROM self_a WHERE a > 10 AND b > 12 AND (a < 9 AND b < 11 OR b <=16); - a | b | c | d | z ------+----+-----+-----+--- - 318 | 13 | 119 | 148 | f -(1 row) + a | b | c | d | z +------+-----+-----+-----+--- + 56 | 118 | 32 | 780 | t + 854 | 610 | 991 | 526 | f + 837 | 872 | 417 | 130 | t + 193 | 326 | 902 | 976 | f + 414 | 913 | 549 | 289 | t + 733 | 159 | 285 | 496 | f + 44 | 253 | 569 | 357 | t + 269 | 39 | 540 | 941 | t + 807 | 974 | 329 | 4 | f + 392 | 582 | 559 | 657 | t + 836 | 337 | 672 | 303 | f + 241 | 161 | 464 | 926 | t + 569 | 792 | 682 | 462 | f + 994 | 629 | 653 | 562 | t + 614 | 505 | 543 | 510 | f + 796 | 634 | 72 | 519 | f + 982 | 357 | 767 | 752 | t + 318 | 13 | 119 | 148 | f + 568 | 940 | 548 | 426 | t + 356 | 732 | 660 | 303 | t + 332 | 755 | 409 | 493 | t + 263 | 710 | 622 | 40 | f + 205 | 128 | 909 | 468 | t + 153 | 850 | 127 | 470 | t + 289 | 430 | 843 | 929 | t + 797 | 168 | 438 | 902 | f + 870 | 667 | 792 | 275 | f + 247 | 382 | 911 | 522 | f + 27 | 203 | 125 | 400 | f + 153 | 678 | 79 | 278 | f + 731 | 516 | 81 | 324 | f + 602 | 648 | 892 | 787 | t + 691 | 177 | 873 | 183 | f + 455 | 962 | 675 | 268 | f + 899 | 679 | 71 | 88 | f + 755 | 985 | 569 | 555 | f + 862 | 344 | 659 | 984 | t + 222 | 163 | 299 | 75 | f + 139 | 603 | 93 | 681 | f + 196 | 783 | 85 | 485 | f + 688 | 963 | 463 | 465 | t + 804 | 894 | 833 | 52 | f + 254 | 332 | 481 | 563 | t + 564 | 43 | 33 | 445 | f + 108 | 736 | 693 | 731 | f + 836 | 502 | 543 | 294 | f + 900 | 739 | 645 | 132 | t + 918 | 798 | 360 | 621 | f + 302 | 998 | 212 | 696 | t + 711 | 906 | 273 | 384 | f + 699 | 884 | 563 | 120 | t + 357 | 456 | 709 | 762 | t + 708 | 276 | 237 | 862 | f + 274 | 40 | 478 | 495 | t + 564 | 973 | 743 | 123 | t + 195 | 848 | 401 | 908 | f + 819 | 38 | 449 | 715 | f + 688 | 549 | 702 | 819 | f + 242 | 716 | 55 | 88 | t + 866 | 508 | 610 | 30 | f + 720 | 558 | 719 | 721 | t + 895 | 106 | 7 | 231 | f + 895 | 926 | 991 | 18 | t + 15 | 49 | 670 | 482 | t + 499 | 122 | 139 | 68 | f + 44 | 486 | 900 | 526 | f + 502 | 390 | 266 | 59 | t + 197 | 481 | 798 | 954 | t + 1000 | 512 | 245 | 310 | f + 207 | 938 | 856 | 191 | t + 666 | 883 | 47 | 664 | f + 964 | 852 | 439 | 173 | f + 294 | 262 | 404 | 259 | f + 81 | 799 | 100 | 620 | t + 633 | 272 | 145 | 644 | t + 287 | 256 | 875 | 53 | f + 962 | 216 | 696 | 315 | f + 299 | 875 | 355 | 584 | f + 645 | 249 | 655 | 667 | t + 223 | 71 | 627 | 251 | t + 677 | 849 | 978 | 114 | f + 130 | 199 | 836 | 596 | f + 710 | 775 | 416 | 215 | f + 146 | 621 | 442 | 338 | t + 924 | 349 | 600 | 116 | f + 165 | 330 | 678 | 57 | t + 593 | 187 | 258 | 974 | t + 951 | 698 | 928 | 139 | f + 322 | 965 | 961 | 464 | f + 884 | 683 | 173 | 38 | t + 701 | 443 | 368 | 748 | t + 461 | 619 | 15 | 596 | t + 526 | 364 | 607 | 397 | t + 13 | 937 | 247 | 801 | f + 538 | 82 | 145 | 879 | f + 169 | 168 | 389 | 321 | t + 405 | 769 | 699 | 908 | t + 348 | 429 | 644 | 521 | f + 838 | 877 | 729 | 7 | f + 989 | 254 | 808 | 71 | t + 699 | 140 | 379 | 557 | t + 493 | 671 | 500 | 809 | t + 177 | 403 | 995 | 70 | t + 750 | 967 | 470 | 128 | t + 955 | 473 | 813 | 315 | t + 742 | 784 | 627 | 420 | f + 403 | 717 | 646 | 329 | t + 292 | 990 | 701 | 988 | f + 79 | 78 | 834 | 834 | f + 440 | 724 | 690 | 536 | f + 707 | 875 | 351 | 404 | t + 945 | 955 | 885 | 897 | f + 108 | 476 | 668 | 405 | f + 986 | 343 | 552 | 587 | t + 629 | 22 | 667 | 504 | t + 104 | 146 | 576 | 513 | t + 356 | 991 | 397 | 396 | t + 899 | 817 | 237 | 392 | t + 240 | 393 | 590 | 310 | f + 906 | 573 | 999 | 354 | f + 907 | 66 | 936 | 59 | f + 577 | 327 | 245 | 713 | f + 254 | 271 | 113 | 58 | t + 326 | 43 | 673 | 87 | t + 867 | 954 | 699 | 647 | f + 99 | 826 | 378 | 172 | f + 158 | 375 | 950 | 802 | f + 104 | 157 | 523 | 396 | t + 902 | 125 | 613 | 44 | f + 564 | 635 | 608 | 48 | t + 753 | 539 | 692 | 111 | t + 27 | 613 | 633 | 693 | t + 859 | 855 | 512 | 28 | f + 684 | 452 | 757 | 209 | f + 198 | 591 | 500 | 940 | f + 442 | 198 | 182 | 663 | t + 797 | 965 | 909 | 98 | f + 466 | 665 | 849 | 828 | t + 615 | 689 | 767 | 773 | f + 743 | 760 | 860 | 232 | t + 86 | 444 | 683 | 217 | t + 772 | 725 | 436 | 862 | t + 34 | 820 | 627 | 320 | t + 697 | 222 | 752 | 984 | t + 426 | 273 | 972 | 901 | f + 782 | 610 | 78 | 267 | t + 238 | 126 | 75 | 601 | t + 385 | 443 | 912 | 100 | t + 939 | 715 | 692 | 21 | t + 108 | 291 | 894 | 132 | f + 632 | 316 | 108 | 815 | t + 849 | 847 | 786 | 601 | f + 218 | 153 | 138 | 574 | t + 947 | 673 | 758 | 151 | t + 146 | 807 | 30 | 963 | f + 748 | 222 | 931 | 664 | t + 916 | 19 | 100 | 457 | f + 921 | 287 | 386 | 201 | t + 521 | 177 | 935 | 268 | t + 58 | 222 | 195 | 749 | t + 95 | 430 | 46 | 825 | t + 290 | 121 | 520 | 759 | f + 655 | 640 | 203 | 331 | f + 30 | 950 | 632 | 526 | t + 997 | 996 | 179 | 575 | t + 523 | 881 | 463 | 602 | f + 277 | 298 | 806 | 724 | t + 601 | 467 | 772 | 305 | f + 169 | 341 | 646 | 492 | f + 739 | 718 | 98 | 513 | t + 91 | 996 | 871 | 954 | t + 248 | 367 | 704 | 899 | t + 679 | 192 | 687 | 192 | t + 840 | 429 | 830 | 93 | f + 750 | 263 | 652 | 749 | t + 835 | 478 | 266 | 402 | t + 264 | 277 | 668 | 230 | t + 55 | 662 | 473 | 776 | t + 800 | 594 | 825 | 638 | t + 549 | 29 | 951 | 607 | f + 472 | 908 | 939 | 703 | t + 536 | 969 | 814 | 303 | t + 316 | 173 | 228 | 312 | f + 584 | 885 | 329 | 479 | f + 229 | 716 | 536 | 417 | f + 177 | 769 | 282 | 339 | t + 987 | 512 | 477 | 629 | f + 744 | 252 | 336 | 551 | f + 655 | 429 | 250 | 603 | t + 965 | 733 | 863 | 363 | f + 459 | 276 | 576 | 510 | t + 626 | 986 | 829 | 345 | f + 434 | 614 | 655 | 288 | t + 570 | 17 | 675 | 416 | t + 882 | 270 | 147 | 862 | f + 593 | 669 | 145 | 386 | f + 313 | 369 | 21 | 781 | t + 869 | 116 | 133 | 611 | t + 604 | 368 | 695 | 100 | t + 943 | 847 | 120 | 483 | t + 396 | 231 | 437 | 772 | f + 583 | 177 | 580 | 506 | t + 170 | 542 | 784 | 61 | f + 978 | 493 | 12 | 236 | f + 222 | 162 | 612 | 15 | f + 585 | 138 | 641 | 274 | t + 246 | 657 | 243 | 852 | t + 62 | 869 | 96 | 25 | f + 847 | 224 | 676 | 869 | f + 211 | 709 | 672 | 470 | t + 366 | 647 | 964 | 221 | f + 980 | 847 | 37 | 587 | f + 450 | 386 | 594 | 112 | f + 729 | 463 | 931 | 920 | f + 498 | 604 | 51 | 871 | f + 464 | 727 | 491 | 891 | f + 522 | 316 | 82 | 316 | t + 32 | 27 | 138 | 337 | f + 733 | 939 | 991 | 449 | t + 221 | 235 | 992 | 855 | f + 770 | 463 | 881 | 616 | f + 558 | 738 | 71 | 74 | f + 914 | 682 | 217 | 242 | f + 524 | 444 | 281 | 455 | f + 778 | 756 | 230 | 393 | f + 114 | 63 | 606 | 47 | t + 657 | 625 | 890 | 806 | f + 988 | 903 | 337 | 739 | t + 536 | 189 | 430 | 446 | t + 357 | 44 | 70 | 638 | f + 540 | 578 | 772 | 484 | t + 567 | 975 | 658 | 611 | t + 458 | 747 | 744 | 234 | f + 695 | 291 | 666 | 535 | f + 345 | 401 | 923 | 576 | t + 634 | 912 | 328 | 860 | t + 104 | 807 | 925 | 747 | t + 155 | 19 | 238 | 464 | t + 569 | 117 | 844 | 808 | f + 171 | 227 | 751 | 287 | t + 196 | 762 | 443 | 759 | t + 657 | 445 | 668 | 569 | f + 372 | 215 | 899 | 368 | t + 223 | 794 | 499 | 736 | f + 433 | 94 | 321 | 359 | t + 423 | 575 | 251 | 637 | f + 508 | 734 | 783 | 781 | t + 278 | 653 | 931 | 500 | f + 726 | 993 | 9 | 298 | t + 677 | 426 | 376 | 226 | f + 967 | 639 | 659 | 731 | f + 488 | 774 | 321 | 573 | f + 65 | 909 | 5 | 331 | t + 426 | 554 | 297 | 168 | t + 460 | 846 | 500 | 416 | t + 887 | 664 | 705 | 592 | t + 98 | 83 | 24 | 146 | f + 79 | 343 | 431 | 601 | f + 127 | 942 | 871 | 783 | t + 20 | 441 | 998 | 508 | f + 267 | 62 | 303 | 239 | t + 239 | 62 | 439 | 837 | t + 89 | 686 | 252 | 569 | f + 891 | 135 | 718 | 114 | t + 606 | 246 | 74 | 214 | f + 552 | 359 | 115 | 783 | f + 712 | 930 | 700 | 976 | f + 107 | 852 | 200 | 230 | t + 554 | 263 | 759 | 69 | f + 391 | 345 | 544 | 436 | f + 12 | 425 | 493 | 606 | f + 869 | 453 | 75 | 48 | t + 455 | 58 | 920 | 372 | t + 227 | 864 | 928 | 982 | f + 844 | 740 | 455 | 993 | f + 850 | 418 | 336 | 521 | f + 209 | 470 | 482 | 456 | t + 177 | 631 | 900 | 590 | t + 899 | 485 | 322 | 890 | t + 733 | 33 | 64 | 48 | t + 700 | 47 | 989 | 640 | f + 252 | 571 | 608 | 239 | f + 480 | 100 | 299 | 783 | f + 40 | 341 | 956 | 192 | f + 304 | 201 | 128 | 344 | t + 150 | 276 | 114 | 435 | f + 412 | 482 | 930 | 265 | f + 219 | 597 | 726 | 347 | t + 248 | 74 | 303 | 763 | t + 402 | 604 | 706 | 483 | t + 491 | 662 | 324 | 100 | f + 680 | 890 | 651 | 440 | t + 42 | 791 | 890 | 623 | t + 144 | 166 | 928 | 774 | f + 272 | 507 | 710 | 514 | t +(295 rows) -- -- Test mixed expr clause @@ -1394,29 +2279,323 @@ QUERY PLAN -------------------- Seq Scan on self_a - Filter: (a < 20) -(2 rows) +(1 row) SELECT * FROM self_a WHERE (a > 1 AND a <1 OR b < 10 ) AND z IS TRUE OR b < 20 or z IS null; - a | b | c | d | z ------+----+-----+-----+--- - 318 | 13 | 119 | 148 | f - 283 | 4 | 598 | 347 | t - 916 | 19 | 100 | 457 | f - 570 | 17 | 675 | 416 | t - 155 | 19 | 238 | 464 | t - | | | | -(6 rows) + a | b | c | d | z +------+-----+-----+-----+--- + 56 | 118 | 32 | 780 | t + 854 | 610 | 991 | 526 | f + 837 | 872 | 417 | 130 | t + 193 | 326 | 902 | 976 | f + 414 | 913 | 549 | 289 | t + 733 | 159 | 285 | 496 | f + 44 | 253 | 569 | 357 | t + 269 | 39 | 540 | 941 | t + 807 | 974 | 329 | 4 | f + 392 | 582 | 559 | 657 | t + 836 | 337 | 672 | 303 | f + 241 | 161 | 464 | 926 | t + 569 | 792 | 682 | 462 | f + 994 | 629 | 653 | 562 | t + 614 | 505 | 543 | 510 | f + 796 | 634 | 72 | 519 | f + 982 | 357 | 767 | 752 | t + 318 | 13 | 119 | 148 | f + 568 | 940 | 548 | 426 | t + 356 | 732 | 660 | 303 | t + 332 | 755 | 409 | 493 | t + 263 | 710 | 622 | 40 | f + 205 | 128 | 909 | 468 | t + 6 | 514 | 536 | 746 | f + 153 | 850 | 127 | 470 | t + 289 | 430 | 843 | 929 | t + 797 | 168 | 438 | 902 | f + 870 | 667 | 792 | 275 | f + 247 | 382 | 911 | 522 | f + 27 | 203 | 125 | 400 | f + 153 | 678 | 79 | 278 | f + 731 | 516 | 81 | 324 | f + 602 | 648 | 892 | 787 | t + 691 | 177 | 873 | 183 | f + 455 | 962 | 675 | 268 | f + 899 | 679 | 71 | 88 | f + 755 | 985 | 569 | 555 | f + 862 | 344 | 659 | 984 | t + 222 | 163 | 299 | 75 | f + 139 | 603 | 93 | 681 | f + 196 | 783 | 85 | 485 | f + 688 | 963 | 463 | 465 | t + 804 | 894 | 833 | 52 | f + 254 | 332 | 481 | 563 | t + 564 | 43 | 33 | 445 | f + 283 | 4 | 598 | 347 | t + 108 | 736 | 693 | 731 | f + 836 | 502 | 543 | 294 | f + 900 | 739 | 645 | 132 | t + 918 | 798 | 360 | 621 | f + 302 | 998 | 212 | 696 | t + 711 | 906 | 273 | 384 | f + 699 | 884 | 563 | 120 | t + 357 | 456 | 709 | 762 | t + 708 | 276 | 237 | 862 | f + 274 | 40 | 478 | 495 | t + 564 | 973 | 743 | 123 | t + 195 | 848 | 401 | 908 | f + 819 | 38 | 449 | 715 | f + 688 | 549 | 702 | 819 | f + 242 | 716 | 55 | 88 | t + 866 | 508 | 610 | 30 | f + 720 | 558 | 719 | 721 | t + 895 | 106 | 7 | 231 | f + 895 | 926 | 991 | 18 | t + 15 | 49 | 670 | 482 | t + 499 | 122 | 139 | 68 | f + 44 | 486 | 900 | 526 | f + 502 | 390 | 266 | 59 | t + 197 | 481 | 798 | 954 | t + 1000 | 512 | 245 | 310 | f + 207 | 938 | 856 | 191 | t + 666 | 883 | 47 | 664 | f + 964 | 852 | 439 | 173 | f + 294 | 262 | 404 | 259 | f + 81 | 799 | 100 | 620 | t + 633 | 272 | 145 | 644 | t + 287 | 256 | 875 | 53 | f + 962 | 216 | 696 | 315 | f + 299 | 875 | 355 | 584 | f + 645 | 249 | 655 | 667 | t + 223 | 71 | 627 | 251 | t + 677 | 849 | 978 | 114 | f + 130 | 199 | 836 | 596 | f + 710 | 775 | 416 | 215 | f + 146 | 621 | 442 | 338 | t + 924 | 349 | 600 | 116 | f + 165 | 330 | 678 | 57 | t + 593 | 187 | 258 | 974 | t + 951 | 698 | 928 | 139 | f + 322 | 965 | 961 | 464 | f + 884 | 683 | 173 | 38 | t + 701 | 443 | 368 | 748 | t + 461 | 619 | 15 | 596 | t + 526 | 364 | 607 | 397 | t + 13 | 937 | 247 | 801 | f + 538 | 82 | 145 | 879 | f + 169 | 168 | 389 | 321 | t + 405 | 769 | 699 | 908 | t + 348 | 429 | 644 | 521 | f + 838 | 877 | 729 | 7 | f + 989 | 254 | 808 | 71 | t + 699 | 140 | 379 | 557 | t + 493 | 671 | 500 | 809 | t + 177 | 403 | 995 | 70 | t + 750 | 967 | 470 | 128 | t + 955 | 473 | 813 | 315 | t + 742 | 784 | 627 | 420 | f + 403 | 717 | 646 | 329 | t + 292 | 990 | 701 | 988 | f + 79 | 78 | 834 | 834 | f + 440 | 724 | 690 | 536 | f + 707 | 875 | 351 | 404 | t + 945 | 955 | 885 | 897 | f + 108 | 476 | 668 | 405 | f + 986 | 343 | 552 | 587 | t + 629 | 22 | 667 | 504 | t + 104 | 146 | 576 | 513 | t + 356 | 991 | 397 | 396 | t + 899 | 817 | 237 | 392 | t + 240 | 393 | 590 | 310 | f + 906 | 573 | 999 | 354 | f + 907 | 66 | 936 | 59 | f + 577 | 327 | 245 | 713 | f + 254 | 271 | 113 | 58 | t + 326 | 43 | 673 | 87 | t + 867 | 954 | 699 | 647 | f + 99 | 826 | 378 | 172 | f + 158 | 375 | 950 | 802 | f + 104 | 157 | 523 | 396 | t + 902 | 125 | 613 | 44 | f + 564 | 635 | 608 | 48 | t + 753 | 539 | 692 | 111 | t + 27 | 613 | 633 | 693 | t + 859 | 855 | 512 | 28 | f + 684 | 452 | 757 | 209 | f + 198 | 591 | 500 | 940 | f + 442 | 198 | 182 | 663 | t + 797 | 965 | 909 | 98 | f + 466 | 665 | 849 | 828 | t + 615 | 689 | 767 | 773 | f + 743 | 760 | 860 | 232 | t + 86 | 444 | 683 | 217 | t + 772 | 725 | 436 | 862 | t + 34 | 820 | 627 | 320 | t + 5 | 437 | 23 | 829 | f + 697 | 222 | 752 | 984 | t + 426 | 273 | 972 | 901 | f + 782 | 610 | 78 | 267 | t + 238 | 126 | 75 | 601 | t + 385 | 443 | 912 | 100 | t + 939 | 715 | 692 | 21 | t + 108 | 291 | 894 | 132 | f + 632 | 316 | 108 | 815 | t + 849 | 847 | 786 | 601 | f + 218 | 153 | 138 | 574 | t + 947 | 673 | 758 | 151 | t + 146 | 807 | 30 | 963 | f + 748 | 222 | 931 | 664 | t + 916 | 19 | 100 | 457 | f + 921 | 287 | 386 | 201 | t + 521 | 177 | 935 | 268 | t + 58 | 222 | 195 | 749 | t + 95 | 430 | 46 | 825 | t + 290 | 121 | 520 | 759 | f + 655 | 640 | 203 | 331 | f + 30 | 950 | 632 | 526 | t + 997 | 996 | 179 | 575 | t + 523 | 881 | 463 | 602 | f + 277 | 298 | 806 | 724 | t + 601 | 467 | 772 | 305 | f + 169 | 341 | 646 | 492 | f + 739 | 718 | 98 | 513 | t + 91 | 996 | 871 | 954 | t + 248 | 367 | 704 | 899 | t + 679 | 192 | 687 | 192 | t + 840 | 429 | 830 | 93 | f + 750 | 263 | 652 | 749 | t + 835 | 478 | 266 | 402 | t + 264 | 277 | 668 | 230 | t + 55 | 662 | 473 | 776 | t + 800 | 594 | 825 | 638 | t + 549 | 29 | 951 | 607 | f + 472 | 908 | 939 | 703 | t + 536 | 969 | 814 | 303 | t + 316 | 173 | 228 | 312 | f + 584 | 885 | 329 | 479 | f + 229 | 716 | 536 | 417 | f + 177 | 769 | 282 | 339 | t + 987 | 512 | 477 | 629 | f + 744 | 252 | 336 | 551 | f + 655 | 429 | 250 | 603 | t + 965 | 733 | 863 | 363 | f + 459 | 276 | 576 | 510 | t + 626 | 986 | 829 | 345 | f + 434 | 614 | 655 | 288 | t + 570 | 17 | 675 | 416 | t + 882 | 270 | 147 | 862 | f + 593 | 669 | 145 | 386 | f + 313 | 369 | 21 | 781 | t + 869 | 116 | 133 | 611 | t + 604 | 368 | 695 | 100 | t + 943 | 847 | 120 | 483 | t + 396 | 231 | 437 | 772 | f + 583 | 177 | 580 | 506 | t + 170 | 542 | 784 | 61 | f + 978 | 493 | 12 | 236 | f + 222 | 162 | 612 | 15 | f + 585 | 138 | 641 | 274 | t + 246 | 657 | 243 | 852 | t + 62 | 869 | 96 | 25 | f + 847 | 224 | 676 | 869 | f + 211 | 709 | 672 | 470 | t + 366 | 647 | 964 | 221 | f + 980 | 847 | 37 | 587 | f + 450 | 386 | 594 | 112 | f + 729 | 463 | 931 | 920 | f + 498 | 604 | 51 | 871 | f + 464 | 727 | 491 | 891 | f + 522 | 316 | 82 | 316 | t + 32 | 27 | 138 | 337 | f + 733 | 939 | 991 | 449 | t + 221 | 235 | 992 | 855 | f + 770 | 463 | 881 | 616 | f + 558 | 738 | 71 | 74 | f + 914 | 682 | 217 | 242 | f + 524 | 444 | 281 | 455 | f + 778 | 756 | 230 | 393 | f + 114 | 63 | 606 | 47 | t + 657 | 625 | 890 | 806 | f + 988 | 903 | 337 | 739 | t + 536 | 189 | 430 | 446 | t + 357 | 44 | 70 | 638 | f + 540 | 578 | 772 | 484 | t + 567 | 975 | 658 | 611 | t + 458 | 747 | 744 | 234 | f + 695 | 291 | 666 | 535 | f + 4 | 23 | 607 | 997 | t + 345 | 401 | 923 | 576 | t + 634 | 912 | 328 | 860 | t + 104 | 807 | 925 | 747 | t + 155 | 19 | 238 | 464 | t + 569 | 117 | 844 | 808 | f + 171 | 227 | 751 | 287 | t + 196 | 762 | 443 | 759 | t + 657 | 445 | 668 | 569 | f + 372 | 215 | 899 | 368 | t + 223 | 794 | 499 | 736 | f + 433 | 94 | 321 | 359 | t + 423 | 575 | 251 | 637 | f + 508 | 734 | 783 | 781 | t + 278 | 653 | 931 | 500 | f + 726 | 993 | 9 | 298 | t + 677 | 426 | 376 | 226 | f + 967 | 639 | 659 | 731 | f + 488 | 774 | 321 | 573 | f + 65 | 909 | 5 | 331 | t + 426 | 554 | 297 | 168 | t + 460 | 846 | 500 | 416 | t + 887 | 664 | 705 | 592 | t + 98 | 83 | 24 | 146 | f + 79 | 343 | 431 | 601 | f + 127 | 942 | 871 | 783 | t + 20 | 441 | 998 | 508 | f + 267 | 62 | 303 | 239 | t + 239 | 62 | 439 | 837 | t + 89 | 686 | 252 | 569 | f + 891 | 135 | 718 | 114 | t + 606 | 246 | 74 | 214 | f + 552 | 359 | 115 | 783 | f + 712 | 930 | 700 | 976 | f + 107 | 852 | 200 | 230 | t + 554 | 263 | 759 | 69 | f + 391 | 345 | 544 | 436 | f + 12 | 425 | 493 | 606 | f + 869 | 453 | 75 | 48 | t + 455 | 58 | 920 | 372 | t + 227 | 864 | 928 | 982 | f + 6 | 513 | 218 | 928 | f + 844 | 740 | 455 | 993 | f + 850 | 418 | 336 | 521 | f + 209 | 470 | 482 | 456 | t + 177 | 631 | 900 | 590 | t + 899 | 485 | 322 | 890 | t + 733 | 33 | 64 | 48 | t + 700 | 47 | 989 | 640 | f + 252 | 571 | 608 | 239 | f + 480 | 100 | 299 | 783 | f + 40 | 341 | 956 | 192 | f + 304 | 201 | 128 | 344 | t + 150 | 276 | 114 | 435 | f + 412 | 482 | 930 | 265 | f + 219 | 597 | 726 | 347 | t + 248 | 74 | 303 | 763 | t + 402 | 604 | 706 | 483 | t + 491 | 662 | 324 | 100 | f + 680 | 890 | 651 | 440 | t + 42 | 791 | 890 | 623 | t + 144 | 166 | 928 | 774 | f + 272 | 507 | 710 | 514 | t + | | | | +(301 rows) -- -- Test pruned and self-contradictory mixed case -- explain (costs off) SELECT * FROM self_a WHERE (a > 10 AND a > 12 OR b > 8) AND b < 7 AND b < 15; - QUERY PLAN ----------------------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: ((b < 7) AND (a > 12)) + Filter: (b < 7) (2 rows) explain (costs off) @@ -1429,26 +2608,26 @@ explain (costs off) SELECT * FROM self_a WHERE a > 10 AND b > 12 AND (((a < 9 OR b < 11) OR (b < 15 AND b < 17 OR a <12)) OR b >=8 AND a < 14); - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------- Seq Scan on self_a - Filter: ((a > 10) AND (b > 12) AND ((b < 15) OR (a < 12) OR ((b > 12) AND (a < 14)))) + Filter: ((a > 10) AND (b > 12)) (2 rows) explain (costs off) SELECT * FROM self_a WHERE a <> 3 AND a <> 4 AND a <> 8 AND (b > 12 AND c < 80 OR c = 16 AND (a = 4 OR a = 8)); - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------ Seq Scan on self_a - Filter: ((a <> 3) AND (a <> 4) AND (a <> 8) AND (b > 12) AND (c < 80)) + Filter: ((a <> 3) AND (a <> 4) AND (a <> 8)) (2 rows) explain (costs off) SELECT * FROM self_a WHERE a IS not null AND (b > 12 AND c < 80 OR c = 16 AND (a IS null OR a = 8)); - QUERY PLAN -------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------- Seq Scan on self_a - Filter: ((a IS NOT NULL) AND (((b > 12) AND (c < 80)) OR ((c = 16) AND (a = 8)))) + Filter: (a IS NOT NULL) (2 rows) -- @@ -1484,11 +2663,10 @@ explain (costs off) SELECT * FROM self_a WHERE ROW(a , b , c) < (100 , null , 3); - QUERY PLAN ---------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: (a < 100) -(2 rows) +(1 row) --Equal expression --a<100 @@ -1540,8 +2718,7 @@ QUERY PLAN -------------------- Seq Scan on self_a - Filter: (a > 2) -(2 rows) +(1 row) --Equal expression --a>2 @@ -1715,8 +2892,7 @@ QUERY PLAN -------------------- Seq Scan on self_a - Filter: (a > 2) -(2 rows) +(1 row) --Equal expression --a>2 @@ -1927,8 +3103,7 @@ QUERY PLAN -------------------- Seq Scan on self_a - Filter: (a > 2) -(2 rows) +(1 row) --Equal expression --a>2 @@ -1978,11 +3153,10 @@ --false explain (costs off) SELECT * FROM self_a WHERE ROW(a , 3 , 4) >= (a , 3 , 4); - QUERY PLAN ---------------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: (a IS NOT NULL) -(2 rows) +(1 row) --Equal expression --a>a @@ -2046,11 +3220,10 @@ --a IS NOT NULL and b IS NOT NULL explain (costs off) SELECT * FROM self_a WHERE ROW(4 , a , 4) >= (4 , a , 4); - QUERY PLAN ---------------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: (a IS NOT NULL) -(2 rows) +(1 row) --Equal expression --4>4 @@ -2109,8 +3282,7 @@ QUERY PLAN -------------------- Seq Scan on self_a - Filter: (a > 2) -(2 rows) +(1 row) --Equal expression --a>2 @@ -2301,20 +3473,19 @@ --Only keep expr that is simple after converted. explain (costs off) SELECT * FROM self_a WHERE ROW(a , b, c) > (23, 34, 56) AND b = 34 AND c = 56; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +----------------------------------- Seq Scan on self_a - Filter: ((a > 23) AND (b = 34) AND (c = 56)) + Filter: ((b = 34) AND (c = 56)) (2 rows) --Test row comparison under the or-expr is const true and the or-expr will be const true. explain (costs off) SELECT * FROM self_a WHERE (ROW(2,a,b) > (1,3,5) OR c < 345 AND a = 89) AND a < 150 OR c = 178; - QUERY PLAN ------------------------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: ((a < 150) OR (c = 178)) -(2 rows) +(1 row) -- -- Test scalar array operation expr @@ -2394,18 +3565,16 @@ explain (costs off) SELECT * FROM self_a WHERE a < ANY (array[20 , 30 , 40]) AND a >= 25; - QUERY PLAN ----------------------------------------------- - Bitmap Heap Scan on self_a - Recheck Cond: ((a >= 25) AND (a < 40)) - -> Bitmap Index Scan on idx_self_a - Index Cond: ((a >= 25) AND (a < 40)) -(4 rows) + QUERY PLAN +--------------------- + Seq Scan on self_a + Filter: (a >= 25) +(2 rows) SELECT COUNT(*) FROM self_a WHERE a < ANY (array[20 , 30 , 40]) AND a >= 25; count ------- - 5 + 292 (1 row) explain (costs off) @@ -2506,10 +3675,10 @@ explain (costs off) SELECT * FROM self_a WHERE a > ANY (array[2 , 3]) AND a <=3; - QUERY PLAN ----------------------------------- + QUERY PLAN +-------------------- Seq Scan on self_a - Filter: ((a <= 3) AND (a > 2)) + Filter: (a <= 3) (2 rows) explain (costs off) @@ -2555,18 +3724,18 @@ -- Test push scalar array expr down. explain (costs off) SELECT * FROM self_a WHERE a IN (89, 12, 219) AND (b > 50 OR c < 300 AND a IN (89, 121, 319)); - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------ Seq Scan on self_a - Filter: ((a = ANY ('{89,12,219}'::integer[])) AND ((b > 50) OR ((c < 300) AND (a = 89)))) + Filter: (a = ANY ('{89,12,219}'::integer[])) (2 rows) explain (costs off) SELECT * FROM self_a WHERE a IN (89, 12, 219) AND (b > 50 OR (c < 345 AND c < 78 OR a IN (89, 121, 319))); - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------ Seq Scan on self_a - Filter: ((a = ANY ('{89,12,219}'::integer[])) AND ((b > 50) OR (c < 78) OR (a = 89))) + Filter: (a = ANY ('{89,12,219}'::integer[])) (2 rows) -- @@ -2574,18 +3743,18 @@ -- explain (costs off) SELECT * FROM self_a WHERE a not in (89, 12, 219) AND (b > 50 OR c < 345 AND a = 89); - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +------------------------------------------------- Seq Scan on self_a - Filter: ((b > 50) AND (a <> ALL ('{89,12,219}'::integer[]))) + Filter: (a <> ALL ('{89,12,219}'::integer[])) (2 rows) explain (costs off) SELECT * FROM self_a WHERE a not in (89, 12, 219) AND (b > 50 OR (c < 345 AND c < 78 OR a = 89)); - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------- Seq Scan on self_a - Filter: ((a <> ALL ('{89,12,219}'::integer[])) AND ((b > 50) OR (c < 78))) + Filter: (a <> ALL ('{89,12,219}'::integer[])) (2 rows) explain (costs off)