diff --strip-trailing-cr -U3 C:/cirrus/src/test/regress/expected/subselect.out C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/subselect.out --- C:/cirrus/src/test/regress/expected/subselect.out 2025-03-20 06:04:54.387946900 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/subselect.out 2025-03-20 06:09:59.611498100 +0000 @@ -822,16 +822,17 @@ FROM tb JOIN tc ON ta.id = tb.id); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Semi Join (actual rows=2 loops=1) - -> Seq Scan on ta (actual rows=2 loops=1) - -> Nested Loop (actual rows=1 loops=2) - -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Semi Join (actual rows=2.00 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) + -> Nested Loop (actual rows=1.00 loops=2) + -> Index Only Scan using tb_pkey on tb (actual rows=1.00 loops=2) Index Cond: (id = ta.id) Heap Fetches: 2 - -> Seq Scan on tc (actual rows=1 loops=2) -(7 rows) + Index Searches: 2 + -> Seq Scan on tc (actual rows=1.00 loops=2) +(8 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 1 @@ -840,16 +841,17 @@ FROM tb JOIN tc ON ta.id = tb.id); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Anti Join (actual rows=0 loops=1) - -> Seq Scan on ta (actual rows=2 loops=1) - -> Nested Loop (actual rows=1 loops=2) - -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Anti Join (actual rows=0.00 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) + -> Nested Loop (actual rows=1.00 loops=2) + -> Index Only Scan using tb_pkey on tb (actual rows=1.00 loops=2) Index Cond: (id = ta.id) Heap Fetches: 2 - -> Seq Scan on tc (actual rows=1 loops=2) -(7 rows) + Index Searches: 2 + -> Seq Scan on tc (actual rows=1.00 loops=2) +(8 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 1 @@ -858,16 +860,17 @@ FROM tb JOIN tc ON ta.id = tc.id); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Semi Join (actual rows=2 loops=1) - -> Seq Scan on ta (actual rows=2 loops=1) - -> Nested Loop (actual rows=1 loops=2) - -> Index Only Scan using tc_pkey on tc (actual rows=1 loops=2) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Semi Join (actual rows=2.00 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) + -> Nested Loop (actual rows=1.00 loops=2) + -> Index Only Scan using tc_pkey on tc (actual rows=1.00 loops=2) Index Cond: (id = ta.id) Heap Fetches: 2 - -> Seq Scan on tb (actual rows=1 loops=2) -(7 rows) + Index Searches: 2 + -> Seq Scan on tb (actual rows=1.00 loops=2) +(8 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 1 @@ -876,16 +879,17 @@ FROM tb JOIN tc ON ta.id = tb.id); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Anti Join (actual rows=0 loops=1) - -> Seq Scan on ta (actual rows=2 loops=1) - -> Nested Loop (actual rows=1 loops=2) - -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Anti Join (actual rows=0.00 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) + -> Nested Loop (actual rows=1.00 loops=2) + -> Index Only Scan using tb_pkey on tb (actual rows=1.00 loops=2) Index Cond: (id = ta.id) Heap Fetches: 2 - -> Seq Scan on tc (actual rows=1 loops=2) -(7 rows) + Index Searches: 2 + -> Seq Scan on tc (actual rows=1.00 loops=2) +(8 rows) -- Join compound expression EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) @@ -898,17 +902,17 @@ ta.id = tb.id); QUERY PLAN ------------------------------------------------------------- - Hash Right Semi Join (actual rows=2 loops=1) + Hash Right Semi Join (actual rows=2.00 loops=1) Hash Cond: (tc.id = ta.id) - -> Hash Join (actual rows=2 loops=1) + -> Hash Join (actual rows=2.00 loops=1) Hash Cond: (tb.id = tc.id) - -> Seq Scan on tb (actual rows=4 loops=1) - -> Hash (actual rows=2 loops=1) + -> Seq Scan on tb (actual rows=4.00 loops=1) + -> Hash (actual rows=2.00 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 33kB - -> Seq Scan on tc (actual rows=2 loops=1) - -> Hash (actual rows=2 loops=1) + -> Seq Scan on tc (actual rows=2.00 loops=1) + -> Hash (actual rows=2.00 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 33kB - -> Seq Scan on ta (actual rows=2 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) (11 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) @@ -919,18 +923,20 @@ JOIN tc ON ta.id = tc.id and ta.id = tb.id); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Anti Join (actual rows=0 loops=1) - -> Seq Scan on ta (actual rows=2 loops=1) - -> Nested Loop (actual rows=1 loops=2) - -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=2) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Anti Join (actual rows=0.00 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) + -> Nested Loop (actual rows=1.00 loops=2) + -> Index Only Scan using tb_pkey on tb (actual rows=1.00 loops=2) Index Cond: (id = ta.id) Heap Fetches: 2 - -> Index Only Scan using tc_pkey on tc (actual rows=1 loops=2) + Index Searches: 2 + -> Index Only Scan using tc_pkey on tc (actual rows=1.00 loops=2) Index Cond: (id = ta.id) Heap Fetches: 2 -(9 rows) + Index Searches: 2 +(11 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 1 @@ -942,17 +948,17 @@ ta.id = tb.id); QUERY PLAN ------------------------------------------------------------- - Hash Right Semi Join (actual rows=2 loops=1) + Hash Right Semi Join (actual rows=2.00 loops=1) Hash Cond: (tc.id = ta.id) - -> Hash Join (actual rows=2 loops=1) + -> Hash Join (actual rows=2.00 loops=1) Hash Cond: (tb.id = tc.id) - -> Seq Scan on tb (actual rows=4 loops=1) - -> Hash (actual rows=2 loops=1) + -> Seq Scan on tb (actual rows=4.00 loops=1) + -> Hash (actual rows=2.00 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 33kB - -> Seq Scan on tc (actual rows=2 loops=1) - -> Hash (actual rows=2 loops=1) + -> Seq Scan on tc (actual rows=2.00 loops=1) + -> Hash (actual rows=2.00 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 33kB - -> Seq Scan on ta (actual rows=2 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) (11 rows) -- Compound expression with const type @@ -964,18 +970,20 @@ JOIN tc ON ta.id = tc.id and ta.id = 1); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Semi Join (actual rows=1 loops=1) - -> Index Only Scan using ta_pkey on ta (actual rows=1 loops=1) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Semi Join (actual rows=1.00 loops=1) + -> Index Only Scan using ta_pkey on ta (actual rows=1.00 loops=1) Index Cond: (id = 1) Heap Fetches: 1 - -> Nested Loop (actual rows=1 loops=1) - -> Index Only Scan using tc_pkey on tc (actual rows=1 loops=1) + Index Searches: 1 + -> Nested Loop (actual rows=1.00 loops=1) + -> Index Only Scan using tc_pkey on tc (actual rows=1.00 loops=1) Index Cond: (id = 1) Heap Fetches: 1 - -> Seq Scan on tb (actual rows=1 loops=1) -(9 rows) + Index Searches: 1 + -> Seq Scan on tb (actual rows=1.00 loops=1) +(11 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 1 @@ -985,20 +993,20 @@ JOIN tc ON ta.id = tc.id and ta.id = 1); - QUERY PLAN ----------------------------------------------------------- - Hash Right Anti Join (actual rows=1 loops=1) + QUERY PLAN +------------------------------------------------------------- + Hash Right Anti Join (actual rows=1.00 loops=1) Hash Cond: (tc.id = ta.id) Join Filter: (ta.id = 1) Rows Removed by Join Filter: 4 - -> Nested Loop (actual rows=8 loops=1) - -> Seq Scan on tb (actual rows=4 loops=1) - -> Materialize (actual rows=2 loops=4) + -> Nested Loop (actual rows=8.00 loops=1) + -> Seq Scan on tb (actual rows=4.00 loops=1) + -> Materialize (actual rows=2.00 loops=4) Storage: Memory Maximum Storage: 17kB - -> Seq Scan on tc (actual rows=2 loops=1) - -> Hash (actual rows=2 loops=1) + -> Seq Scan on tc (actual rows=2.00 loops=1) + -> Hash (actual rows=2.00 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 33kB - -> Seq Scan on ta (actual rows=2 loops=1) + -> Seq Scan on ta (actual rows=2.00 loops=1) (12 rows) EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) @@ -1009,18 +1017,20 @@ JOIN tc ON tb.id = 1 and ta.id = 1); - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop Semi Join (actual rows=1 loops=1) - -> Index Only Scan using ta_pkey on ta (actual rows=1 loops=1) + QUERY PLAN +---------------------------------------------------------------------------- + Nested Loop Semi Join (actual rows=1.00 loops=1) + -> Index Only Scan using ta_pkey on ta (actual rows=1.00 loops=1) Index Cond: (id = 1) Heap Fetches: 1 - -> Nested Loop (actual rows=1 loops=1) - -> Index Only Scan using tb_pkey on tb (actual rows=1 loops=1) + Index Searches: 1 + -> Nested Loop (actual rows=1.00 loops=1) + -> Index Only Scan using tb_pkey on tb (actual rows=1.00 loops=1) Index Cond: (id = 1) Heap Fetches: 1 - -> Seq Scan on tc (actual rows=1 loops=1) -(9 rows) + Index Searches: 1 + -> Seq Scan on tc (actual rows=1.00 loops=1) +(11 rows) -- Disabled pull up because it is applcapable for INNER JOIN connection EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) @@ -1030,18 +1040,18 @@ FROM tb RIGHT JOIN tc ON ta.id = tc.id); - QUERY PLAN ------------------------------------------------------------- - Seq Scan on ta (actual rows=2 loops=1) + QUERY PLAN +--------------------------------------------------------------- + Seq Scan on ta (actual rows=2.00 loops=1) Filter: EXISTS(SubPlan 1) SubPlan 1 - -> Nested Loop Left Join (actual rows=1 loops=2) + -> Nested Loop Left Join (actual rows=1.00 loops=2) Join Filter: (ta.id = tc.id) Rows Removed by Join Filter: 2 - -> Seq Scan on tc (actual rows=1 loops=2) - -> Materialize (actual rows=2 loops=2) + -> Seq Scan on tc (actual rows=1.00 loops=2) + -> Materialize (actual rows=2.50 loops=2) Storage: Memory Maximum Storage: 17kB - -> Seq Scan on tb (actual rows=4 loops=1) + -> Seq Scan on tb (actual rows=4.00 loops=1) (10 rows) -- 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 2025-03-20 06:04:54.303909100 +0000 +++ C:/cirrus/build/testrun/recovery/027_stream_regress/data/results/join.out 2025-03-20 06:10:03.284326300 +0000 @@ -7420,7 +7420,7 @@ QUERY PLAN ------------------------------------------------------------------------------------- Nested Loop - Join Filter: (sj_t3.id = sj_t1.id) + Join Filter: (sj_t2.id = sj_t1.id) -> Nested Loop Join Filter: (sj_t2.id = sj_t3.id) -> Nested Loop Semi Join