summaryrefslogtreecommitdiff
path: root/src/test/regress/expected/partition_join.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/partition_join.out')
-rw-r--r--src/test/regress/expected/partition_join.out98
1 files changed, 32 insertions, 66 deletions
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 713828be335..17d27ef3d46 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -63,18 +63,21 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
(4 rows)
-- inner join with partially-redundant join clauses
+-- (avoid a mergejoin, because the planner thinks that an non-partitionwise
+-- merge join is the cheapest plan, and we want to test a partitionwise join)
+BEGIN;
+SET LOCAL enable_mergejoin = false;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Merge Join
- Merge Cond: (t1_1.a = t2_1.a)
- -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
- -> Sort
- Sort Key: t2_1.b
+ -> Hash Join
+ Hash Cond: (t1_1.a = t2_1.a)
+ -> Seq Scan on prt1_p1 t1_1
+ -> Hash
-> Seq Scan on prt2_p1 t2_1
Filter: (a = b)
-> Hash Join
@@ -89,7 +92,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a =
-> Hash
-> Seq Scan on prt2_p3 t2_3
Filter: (a = b)
-(22 rows)
+(21 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
a | c | b | c
@@ -101,6 +104,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a =
24 | 0024 | 24 | 0024
(5 rows)
+COMMIT;
-- left outer join, 3-way
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM prt1 t1
@@ -1244,11 +1248,12 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
450 | 0 | 0450
(4 rows)
--- test merge joins
+-- test merge joins, slightly modifying the query to ensure that we still
+-- get a fully partitionwise join
SET enable_hashjoin TO off;
SET enable_nestloop TO off;
EXPLAIN (COSTS OFF)
-SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
+SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) ORDER BY t1.a;
QUERY PLAN
------------------------------------------------------------------
Merge Append
@@ -1258,7 +1263,6 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
-> Sort
Sort Key: t1_3.a
-> Seq Scan on prt1_p1 t1_3
- Filter: (b = 0)
-> Merge Semi Join
Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
-> Sort
@@ -1273,7 +1277,6 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
-> Sort
Sort Key: t1_4.a
-> Seq Scan on prt1_p2 t1_4
- Filter: (b = 0)
-> Merge Semi Join
Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
-> Sort
@@ -1288,7 +1291,6 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
-> Sort
Sort Key: t1_5.a
-> Seq Scan on prt1_p3 t1_5
- Filter: (b = 0)
-> Merge Semi Join
Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
-> Sort
@@ -1298,9 +1300,9 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
Sort Key: (((t1_11.a + t1_11.b) / 2))
-> Seq Scan on prt1_e_p3 t1_11
Filter: (c = 0)
-(47 rows)
+(44 rows)
-SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
+SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) ORDER BY t1.a;
a | b | c
-----+---+------
0 | 0 | 0000
@@ -4922,27 +4924,27 @@ ANALYZE plt3_adv;
-- merged partition when re-called with plt1_adv_p1 for the second list value
-- '0001' of that partition
EXPLAIN (COSTS OFF)
-SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c, t1.a, t2.a, t3.a
-> Append
-> Hash Full Join
- Hash Cond: (t1_1.c = t3_1.c)
+ Hash Cond: ((t1_1.a = t3_1.a) AND (t1_1.c = t3_1.c))
Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
-> Hash Left Join
- Hash Cond: (t1_1.c = t2_1.c)
+ Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
-> Seq Scan on plt1_adv_p1 t1_1
-> Hash
-> Seq Scan on plt2_adv_p1 t2_1
-> Hash
-> Seq Scan on plt3_adv_p1 t3_1
-> Hash Full Join
- Hash Cond: (t1_2.c = t3_2.c)
+ Hash Cond: ((t1_2.a = t3_2.a) AND (t1_2.c = t3_2.c))
Filter: (((COALESCE(t1_2.a, 0) % 5) <> 3) AND ((COALESCE(t1_2.a, 0) % 5) <> 4))
-> Hash Left Join
- Hash Cond: (t1_2.c = t2_2.c)
+ Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
-> Seq Scan on plt1_adv_p2 t1_2
-> Hash
-> Seq Scan on plt2_adv_p2 t2_2
@@ -4950,7 +4952,7 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t
-> Seq Scan on plt3_adv_p2 t3_2
(23 rows)
-SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
a | c | a | c | a | c
----+------+----+------+----+------
0 | 0000 | | | |
@@ -4959,56 +4961,16 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t
15 | 0000 | | | |
20 | 0000 | | | |
1 | 0001 | | | 1 | 0001
- 1 | 0001 | | | 6 | 0001
- 1 | 0001 | | | 11 | 0001
- 1 | 0001 | | | 16 | 0001
- 1 | 0001 | | | 21 | 0001
- 6 | 0001 | | | 1 | 0001
6 | 0001 | | | 6 | 0001
- 6 | 0001 | | | 11 | 0001
- 6 | 0001 | | | 16 | 0001
- 6 | 0001 | | | 21 | 0001
- 11 | 0001 | | | 1 | 0001
- 11 | 0001 | | | 6 | 0001
11 | 0001 | | | 11 | 0001
- 11 | 0001 | | | 16 | 0001
- 11 | 0001 | | | 21 | 0001
- 16 | 0001 | | | 1 | 0001
- 16 | 0001 | | | 6 | 0001
- 16 | 0001 | | | 11 | 0001
16 | 0001 | | | 16 | 0001
- 16 | 0001 | | | 21 | 0001
- 21 | 0001 | | | 1 | 0001
- 21 | 0001 | | | 6 | 0001
- 21 | 0001 | | | 11 | 0001
- 21 | 0001 | | | 16 | 0001
21 | 0001 | | | 21 | 0001
2 | 0002 | 2 | 0002 | |
- 2 | 0002 | 7 | 0002 | |
- 2 | 0002 | 12 | 0002 | |
- 2 | 0002 | 17 | 0002 | |
- 2 | 0002 | 22 | 0002 | |
- 7 | 0002 | 2 | 0002 | |
7 | 0002 | 7 | 0002 | |
- 7 | 0002 | 12 | 0002 | |
- 7 | 0002 | 17 | 0002 | |
- 7 | 0002 | 22 | 0002 | |
- 12 | 0002 | 2 | 0002 | |
- 12 | 0002 | 7 | 0002 | |
12 | 0002 | 12 | 0002 | |
- 12 | 0002 | 17 | 0002 | |
- 12 | 0002 | 22 | 0002 | |
- 17 | 0002 | 2 | 0002 | |
- 17 | 0002 | 7 | 0002 | |
- 17 | 0002 | 12 | 0002 | |
17 | 0002 | 17 | 0002 | |
- 17 | 0002 | 22 | 0002 | |
- 22 | 0002 | 2 | 0002 | |
- 22 | 0002 | 7 | 0002 | |
- 22 | 0002 | 12 | 0002 | |
- 22 | 0002 | 17 | 0002 | |
22 | 0002 | 22 | 0002 | |
-(55 rows)
+(15 rows)
DROP TABLE plt1_adv;
DROP TABLE plt2_adv;
@@ -5233,8 +5195,11 @@ CREATE TABLE fract_t1 PARTITION OF fract_t FOR VALUES FROM ('1000') TO ('2000');
INSERT INTO fract_t (id) (SELECT generate_series(0, 1999));
ANALYZE fract_t;
-- verify plan; nested index only scans
+-- (avoid merge joins, because the costs of partitionwise and non-partitionwise
+-- merge joins tend to be almost equal, and we want this test to be stable)
SET max_parallel_workers_per_gather = 0;
SET enable_partitionwise_join = on;
+SET enable_mergejoin = off;
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
QUERY PLAN
@@ -5242,14 +5207,14 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id AS
Limit
-> Merge Append
Sort Key: x.id
- -> Merge Left Join
- Merge Cond: (x_1.id = y_1.id)
+ -> Nested Loop Left Join
-> Index Only Scan using fract_t0_pkey on fract_t0 x_1
-> Index Only Scan using fract_t0_pkey on fract_t0 y_1
- -> Merge Left Join
- Merge Cond: (x_2.id = y_2.id)
+ Index Cond: (id = x_1.id)
+ -> Nested Loop Left Join
-> Index Only Scan using fract_t1_pkey on fract_t1 x_2
-> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ Index Cond: (id = x_2.id)
(11 rows)
EXPLAIN (COSTS OFF)
@@ -5366,6 +5331,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
-> Seq Scan on pht1_p3 p2_3
(17 rows)
+RESET enable_mergejoin;
SET max_parallel_workers_per_gather = 1;
SET debug_parallel_query = on;
-- Partial paths should also be smart enough to employ limits
generated by cgit v1.2.3 (git 2.46.0) at 2025-12-06 05:48:47 +0000