diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/subselect.out /tmp/cirrus-ci-build/src/bin/pg_upgrade/tmp_check/results/subselect.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/subselect.out 2025-03-20 06:04:50.568927793 +0000
+++ /tmp/cirrus-ci-build/src/bin/pg_upgrade/tmp_check/results/subselect.out 2025-03-20 06:11:19.682262404 +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 -U3 /tmp/cirrus-ci-build/src/test/regress/expected/join.out /tmp/cirrus-ci-build/src/bin/pg_upgrade/tmp_check/results/join.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/join.out 2025-03-20 06:04:50.517264038 +0000
+++ /tmp/cirrus-ci-build/src/bin/pg_upgrade/tmp_check/results/join.out 2025-03-20 06:11:25.042319920 +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