advanced_partition_matching_test.patch
text/x-patch
Filename: advanced_partition_matching_test.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| src/test/regress/expected/partition_join.out | 3389 | 407 |
| src/test/regress/sql/partition_join.sql | 476 | 26 |
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index a246d87..e9dd539 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -5,6 +5,9 @@
-- Enable partition-wise join, which by default is disabled.
SET enable_partition_wise_join to true;
--
+-- tests for range partitioned tables.
+--
+--
-- partitioned by a single column
--
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
@@ -16,15 +19,20 @@ CREATE INDEX iprt1_p1_a on prt1_p1(a);
CREATE INDEX iprt1_p2_a on prt1_p2(a);
CREATE INDEX iprt1_p3_a on prt1_p3(a);
ANALYZE prt1;
+-- prt2 have missing starting 0-50 range and missing ending 550-600
+-- range bounds
CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
-CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250);
+CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (50) TO (250);
CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500);
-CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600);
-INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 3) i;
+CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (550);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0+50, 599-50, 3) i;
CREATE INDEX iprt2_p1_b on prt2_p1(b);
CREATE INDEX iprt2_p2_b on prt2_p2(b);
CREATE INDEX iprt2_p3_b on prt2_p3(b);
ANALYZE prt2;
+-- Partition-wise-join is possible with some partition bounds overlap
+-- with each other completely and some partialy for inner,left,right,
+-- full, semi and anti joins
-- inner join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -56,10 +64,10 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c
-----+------+-----+------
- 0 | 0000 | 0 | 0000
- 150 | 0150 | 150 | 0150
- 300 | 0300 | 300 | 0300
- 450 | 0450 | 450 | 0450
+ 50 | 0050 | 50 | 0050
+ 200 | 0200 | 200 | 0200
+ 350 | 0350 | 350 | 0350
+ 500 | 0500 | 500 | 0500
(4 rows)
-- left outer join, with whole-row reference
@@ -94,25 +102,25 @@ SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
t1 | t2
--------------+--------------
- (0,0,0000) | (0,0,0000)
- (50,0,0050) |
+ (0,0,0000) |
+ (50,0,0050) | (0,50,0050)
(100,0,0100) |
- (150,0,0150) | (0,150,0150)
- (200,0,0200) |
+ (150,0,0150) |
+ (200,0,0200) | (0,200,0200)
(250,0,0250) |
- (300,0,0300) | (0,300,0300)
- (350,0,0350) |
+ (300,0,0300) |
+ (350,0,0350) | (0,350,0350)
(400,0,0400) |
- (450,0,0450) | (0,450,0450)
- (500,0,0500) |
+ (450,0,0450) |
+ (500,0,0500) | (0,500,0500)
(550,0,0550) |
(12 rows)
-- right outer join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Sort
Sort Key: t1.a, t2.b
-> Result
@@ -129,171 +137,150 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHE
-> Hash
-> Seq Scan on prt2_p2 t2_1
Filter: (a = 0)
- -> Nested Loop Left Join
- -> Seq Scan on prt2_p3 t2_2
- Filter: (a = 0)
- -> Index Scan using iprt1_p3_a on prt1_p3 t1_1
- Index Cond: (a = t2_2.b)
-(21 rows)
+ -> Hash Right Join
+ Hash Cond: (t1_1.a = t2_2.b)
+ -> Seq Scan on prt1_p3 t1_1
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+ Filter: (a = 0)
+(22 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
-----+------+-----+------
- 0 | 0000 | 0 | 0000
- 150 | 0150 | 150 | 0150
- 300 | 0300 | 300 | 0300
- 450 | 0450 | 450 | 0450
- | | 75 | 0075
- | | 225 | 0225
- | | 375 | 0375
- | | 525 | 0525
-(8 rows)
+ 50 | 0050 | 50 | 0050
+ 200 | 0200 | 200 | 0200
+ 350 | 0350 | 350 | 0350
+ 500 | 0500 | 500 | 0500
+ | | 125 | 0125
+ | | 275 | 0275
+ | | 425 | 0425
+(7 rows)
-- full outer join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Sort
Sort Key: prt1_p1.a, prt2_p1.b
- -> Append
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = prt2_p1.b)
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = prt2_p1.b)
+ -> Append
-> Seq Scan on prt1_p1
Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p1
- Filter: (a = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = prt2_p2.b)
+ -> Seq Scan on prt1_p3
+ Filter: (b = 0)
-> Seq Scan on prt1_p2
Filter: (b = 0)
- -> Hash
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1
+ Filter: (a = 0)
-> Seq Scan on prt2_p2
Filter: (a = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p3.a = prt2_p3.b)
- -> Seq Scan on prt1_p3
- Filter: (b = 0)
- -> Hash
-> Seq Scan on prt2_p3
Filter: (a = 0)
-(24 rows)
+(19 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
a | c | b | c
-----+------+-----+------
- 0 | 0000 | 0 | 0000
- 50 | 0050 | |
+ 0 | 0000 | |
+ 50 | 0050 | 50 | 0050
100 | 0100 | |
- 150 | 0150 | 150 | 0150
- 200 | 0200 | |
+ 150 | 0150 | |
+ 200 | 0200 | 200 | 0200
250 | 0250 | |
- 300 | 0300 | 300 | 0300
- 350 | 0350 | |
+ 300 | 0300 | |
+ 350 | 0350 | 350 | 0350
400 | 0400 | |
- 450 | 0450 | 450 | 0450
- 500 | 0500 | |
+ 450 | 0450 | |
+ 500 | 0500 | 500 | 0500
550 | 0550 | |
- | | 75 | 0075
- | | 225 | 0225
- | | 375 | 0375
- | | 525 | 0525
-(16 rows)
+ | | 125 | 0125
+ | | 275 | 0275
+ | | 425 | 0425
+(15 rows)
-- Cases with non-nullable expressions in subquery results;
-- make sure these go to null as expected
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Sort
Sort Key: prt1_p1.a, prt2_p1.b
- -> Append
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = prt2_p1.b)
- Filter: (((50) = prt1_p1.a) OR ((75) = prt2_p1.b))
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = prt2_p1.b)
+ Filter: (((50) = prt1_p1.a) OR ((75) = prt2_p1.b))
+ -> Append
-> Seq Scan on prt1_p1
Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p1
- Filter: (a = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = prt2_p2.b)
- Filter: (((50) = prt1_p2.a) OR ((75) = prt2_p2.b))
+ -> Seq Scan on prt1_p3
+ Filter: (b = 0)
-> Seq Scan on prt1_p2
Filter: (b = 0)
- -> Hash
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1
+ Filter: (a = 0)
-> Seq Scan on prt2_p2
Filter: (a = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p3.a = prt2_p3.b)
- Filter: (((50) = prt1_p3.a) OR ((75) = prt2_p3.b))
- -> Seq Scan on prt1_p3
- Filter: (b = 0)
- -> Hash
-> Seq Scan on prt2_p3
Filter: (a = 0)
-(27 rows)
+(20 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
a | c | b | c
----+------+----+------
- 50 | 0050 | |
- | | 75 | 0075
-(2 rows)
+ 50 | 0050 | 50 | 0050
+(1 row)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t1.phv, t2.b, t2.c, t2.phv FROM (SELECT 25 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 50 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Sort
Sort Key: prt1_p1.a, prt2_p1.b
- -> Result
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = prt2_p1.b)
-> Append
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = prt2_p1.b)
- -> Seq Scan on prt1_p1
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p1
- Filter: (a = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = prt2_p2.b)
- -> Seq Scan on prt1_p2
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p2
- Filter: (a = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p3.a = prt2_p3.b)
- -> Seq Scan on prt1_p3
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p3
- Filter: (a = 0)
-(25 rows)
+ -> Seq Scan on prt1_p1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p3
+ Filter: (a = 0)
+(19 rows)
SELECT t1.a, t1.c, t1.phv, t2.b, t2.c, t2.phv FROM (SELECT 25 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 50 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
a | c | phv | b | c | phv
-----+------+-----+-----+------+-----
- 0 | 0000 | 25 | 0 | 0000 | 50
- 50 | 0050 | 25 | | |
+ 0 | 0000 | 25 | | |
+ 50 | 0050 | 25 | 50 | 0050 | 50
100 | 0100 | 25 | | |
- 150 | 0150 | 25 | 150 | 0150 | 50
- 200 | 0200 | 25 | | |
+ 150 | 0150 | 25 | | |
+ 200 | 0200 | 25 | 200 | 0200 | 50
250 | 0250 | 25 | | |
- 300 | 0300 | 25 | 300 | 0300 | 50
- 350 | 0350 | 25 | | |
+ 300 | 0300 | 25 | | |
+ 350 | 0350 | 25 | 350 | 0350 | 50
400 | 0400 | 25 | | |
- 450 | 0450 | 25 | 450 | 0450 | 50
- 500 | 0500 | 25 | | |
+ 450 | 0450 | 25 | | |
+ 500 | 0500 | 25 | 500 | 0500 | 50
550 | 0550 | 25 | | |
- | | | 75 | 0075 | 50
- | | | 225 | 0225 | 50
- | | | 375 | 0375 | 50
- | | | 525 | 0525 | 50
-(16 rows)
+ | | | 125 | 0125 | 50
+ | | | 275 | 0275 | 50
+ | | | 425 | 0425 | 50
+(15 rows)
-- Join with pruned partitions from joining relations
EXPLAIN (COSTS OFF)
@@ -315,7 +302,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a <
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c
-----+------+-----+------
- 300 | 0300 | 300 | 0300
+ 350 | 0350 | 350 | 0350
(1 row)
EXPLAIN (COSTS OFF)
@@ -350,43 +337,32 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
150 | 0150 | |
200 | 0200 | |
250 | 0250 | |
- 300 | 0300 | 300 | 0300
- 350 | 0350 | |
+ 300 | 0300 | |
+ 350 | 0350 | 350 | 0350
400 | 0400 | |
(9 rows)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
- Sort Key: prt1_p1.a, b
- -> Append
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = b)
- Filter: ((prt1_p1.b = 0) OR (a = 0))
+ Sort Key: prt1_p1.a, prt2_p2.b
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = prt2_p2.b)
+ Filter: ((prt1_p1.b = 0) OR (prt2_p2.a = 0))
+ -> Append
-> Seq Scan on prt1_p1
Filter: (a < 450)
- -> Hash
- -> Result
- One-Time Filter: false
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = prt2_p2.b)
- Filter: ((prt1_p2.b = 0) OR (prt2_p2.a = 0))
-> Seq Scan on prt1_p2
Filter: (a < 450)
- -> Hash
+ -> Hash
+ -> Append
-> Seq Scan on prt2_p2
Filter: (b > 250)
- -> Hash Full Join
- Hash Cond: (prt2_p3.b = a)
- Filter: ((b = 0) OR (prt2_p3.a = 0))
- -> Seq Scan on prt2_p3
- Filter: (b > 250)
- -> Hash
- -> Result
- One-Time Filter: false
-(27 rows)
+ -> Seq Scan on prt2_p3
+ Filter: (b > 250)
+(16 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -397,12 +373,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
150 | 0150 | |
200 | 0200 | |
250 | 0250 | |
- 300 | 0300 | 300 | 0300
- 350 | 0350 | |
+ 300 | 0300 | |
+ 350 | 0350 | 350 | 0350
400 | 0400 | |
- | | 375 | 0375
- | | 450 | 0450
- | | 525 | 0525
+ | | 275 | 0275
+ | | 425 | 0425
+ | | 500 | 0500
(12 rows)
-- Semi-join
@@ -439,10 +415,46 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a;
a | b | c
-----+---+------
- 0 | 0 | 0000
- 150 | 0 | 0150
- 300 | 0 | 0300
- 450 | 0 | 0450
+ 50 | 0 | 0050
+ 200 | 0 | 0200
+ 350 | 0 | 0350
+ 500 | 0 | 0500
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT t1.* FROM prt2 t1 WHERE t1.b IN (SELECT t2.a FROM prt1 t2 WHERE t2.b = 0) AND t1.a = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Hash Semi Join
+ Hash Cond: (t1.b = t2.a)
+ -> Seq Scan on prt2_p1 t1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_p1 t2
+ Filter: (b = 0)
+ -> Hash Semi Join
+ Hash Cond: (t1_1.b = t2_2.a)
+ -> Seq Scan on prt2_p2 t1_1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_p2 t2_2
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: (t1_2.b = t2_1.a)
+ -> Seq Scan on prt2_p3 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt1_p3 t2_1
+ Filter: (b = 0)
+(21 rows)
+
+SELECT t1.* FROM prt2 t1 WHERE t1.b IN (SELECT t2.a FROM prt1 t2 WHERE t2.b = 0) AND t1.a = 0 ORDER BY t1.a;
+ a | b | c
+---+-----+------
+ 0 | 50 | 0050
+ 0 | 200 | 0200
+ 0 | 350 | 0350
+ 0 | 500 | 0500
(4 rows)
-- Anti-join with aggregates
@@ -472,7 +484,31 @@ SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS
SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b);
sum | avg | sum | avg
-------+----------------------+------+---------------------
- 60000 | 300.0000000000000000 | 2400 | 12.0000000000000000
+ 64584 | 299.0000000000000000 | 2584 | 11.9629629629629630
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt2 t1 WHERE NOT EXISTS (SELECT 1 FROM prt1 t2 WHERE t1.b = t2.a);
+ QUERY PLAN
+--------------------------------------------------
+ Aggregate
+ -> Hash Anti Join
+ Hash Cond: (t1.b = t2.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t1
+ -> Seq Scan on prt2_p2 t1_1
+ -> Seq Scan on prt2_p3 t1_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t2
+ -> Seq Scan on prt1_p3 t2_1
+ -> Seq Scan on prt1_p2 t2_2
+(12 rows)
+
+SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt2 t1 WHERE NOT EXISTS (SELECT 1 FROM prt1 t2 WHERE t1.b = t2.a);
+ sum | avg | sum | avg
+-----+---------------------+-------+----------------------
+ 992 | 11.9518072289156627 | 24817 | 299.0000000000000000
(1 row)
-- lateral reference
@@ -484,50 +520,46 @@ SELECT * FROM prt1 t1 LEFT JOIN LATERAL
--------------------------------------------------------------------------------
Sort
Sort Key: t1.a
- -> Result
+ -> Nested Loop Left Join
-> Append
- -> Nested Loop Left Join
- -> Seq Scan on prt1_p1 t1
- Filter: (b = 0)
- -> Nested Loop
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t3.b = t2.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p3 t3_2
+ -> Hash
+ -> Append
-> Index Only Scan using iprt1_p1_a on prt1_p1 t2
Index Cond: (a = t1.a)
- -> Index Scan using iprt2_p1_b on prt2_p1 t3
- Index Cond: (b = t2.a)
- -> Nested Loop Left Join
- -> Seq Scan on prt1_p2 t1_2
- Filter: (b = 0)
- -> Nested Loop
- -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_2
- Index Cond: (a = t1_2.a)
- -> Index Scan using iprt2_p2_b on prt2_p2 t3_1
- Index Cond: (b = t2_2.a)
- -> Nested Loop Left Join
- -> Seq Scan on prt1_p3 t1_1
- Filter: (b = 0)
- -> Nested Loop
-> Index Only Scan using iprt1_p3_a on prt1_p3 t2_1
- Index Cond: (a = t1_1.a)
- -> Index Scan using iprt2_p3_b on prt2_p3 t3_2
- Index Cond: (b = t2_1.a)
-(28 rows)
+ Index Cond: (a = t1.a)
+ -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_2
+ Index Cond: (a = t1.a)
+(24 rows)
SELECT * FROM prt1 t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a;
a | b | c | t2a | t3a | least
-----+---+------+-----+-----+-------
- 0 | 0 | 0000 | 0 | 0 | 0
- 50 | 0 | 0050 | | |
+ 0 | 0 | 0000 | | |
+ 50 | 0 | 0050 | 50 | 0 | 50
100 | 0 | 0100 | | |
- 150 | 0 | 0150 | 150 | 0 | 150
- 200 | 0 | 0200 | | |
+ 150 | 0 | 0150 | | |
+ 200 | 0 | 0200 | 200 | 0 | 200
250 | 0 | 0250 | | |
- 300 | 0 | 0300 | 300 | 0 | 300
- 350 | 0 | 0350 | | |
+ 300 | 0 | 0300 | | |
+ 350 | 0 | 0350 | 350 | 0 | 350
400 | 0 | 0400 | | |
- 450 | 0 | 0450 | 450 | 0 | 450
- 500 | 0 | 0500 | | |
+ 450 | 0 | 0450 | | |
+ 500 | 0 | 0500 | 500 | 0 | 500
550 | 0 | 0550 | | |
(12 rows)
@@ -570,17 +602,17 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
a | t2a | t2c
-----+-----+------
- 0 | 0 | 0000
- 50 | |
+ 0 | |
+ 50 | 50 | 0050
100 | |
- 150 | 150 | 0150
- 200 | |
+ 150 | |
+ 200 | 200 | 0200
250 | |
- 300 | 300 | 0300
- 350 | |
+ 300 | |
+ 350 | 350 | 0350
400 | |
- 450 | 450 | 0450
- 500 | |
+ 450 | |
+ 500 | 500 | 0500
550 | |
(12 rows)
@@ -641,55 +673,50 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 =
--
-- N-way join
--
+--TODO: remove below comments and enable partition_wise_join
+--one issue got fixed
+--Server crashed with below queries
+--setting partition-wise-join to off
+SET enable_partition_wise_join to false;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Sort
Sort Key: t1.a
- -> Result
+ -> Hash Join
+ Hash Cond: (((t3.a + t3.b) / 2) = t1.a)
-> Append
- -> Nested Loop
- Join Filter: (t1.a = ((t3.a + t3.b) / 2))
- -> Hash Join
- Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt1_e_p1 t3
+ -> Seq Scan on prt1_e_p2 t3_1
+ -> Seq Scan on prt1_e_p3 t3_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
-> Seq Scan on prt2_p1 t2
- -> Hash
- -> Seq Scan on prt1_p1 t1
- Filter: (b = 0)
- -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3
- Index Cond: (((a + b) / 2) = t2.b)
- -> Nested Loop
- Join Filter: (t1_2.a = ((t3_1.a + t3_1.b) / 2))
- -> Hash Join
- Hash Cond: (t2_1.b = t1_2.a)
-> Seq Scan on prt2_p2 t2_1
- -> Hash
- -> Seq Scan on prt1_p2 t1_2
- Filter: (b = 0)
- -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_1
- Index Cond: (((a + b) / 2) = t2_1.b)
- -> Nested Loop
- Join Filter: (t1_1.a = ((t3_2.a + t3_2.b) / 2))
- -> Hash Join
- Hash Cond: (t2_2.b = t1_1.a)
-> Seq Scan on prt2_p3 t2_2
- -> Hash
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
-> Seq Scan on prt1_p3 t1_1
Filter: (b = 0)
- -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_2
- Index Cond: (((a + b) / 2) = t2_2.b)
-(34 rows)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(23 rows)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c | ?column? | c
-----+------+-----+------+----------+---
- 0 | 0000 | 0 | 0000 | 0 | 0
- 150 | 0150 | 150 | 0150 | 300 | 0
- 300 | 0300 | 300 | 0300 | 600 | 0
- 450 | 0450 | 450 | 0450 | 900 | 0
+ 50 | 0050 | 50 | 0050 | 100 | 0
+ 200 | 0200 | 200 | 0200 | 400 | 0
+ 350 | 0350 | 350 | 0350 | 700 | 0
+ 500 | 0500 | 500 | 0500 | 1000 | 0
(4 rows)
+SET enable_partition_wise_join to true;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
QUERY PLAN
@@ -733,17 +760,17 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
-----+------+-----+------+----------+---
- 0 | 0000 | 0 | 0000 | 0 | 0
- 50 | 0050 | | | 100 | 0
+ 0 | 0000 | | | 0 | 0
+ 50 | 0050 | 50 | 0050 | 100 | 0
100 | 0100 | | | 200 | 0
- 150 | 0150 | 150 | 0150 | 300 | 0
- 200 | 0200 | | | 400 | 0
+ 150 | 0150 | | | 300 | 0
+ 200 | 0200 | 200 | 0200 | 400 | 0
250 | 0250 | | | 500 | 0
- 300 | 0300 | 300 | 0300 | 600 | 0
- 350 | 0350 | | | 700 | 0
+ 300 | 0300 | | | 600 | 0
+ 350 | 0350 | 350 | 0350 | 700 | 0
400 | 0400 | | | 800 | 0
- 450 | 0450 | 450 | 0450 | 900 | 0
- 500 | 0500 | | | 1000 | 0
+ 450 | 0450 | | | 900 | 0
+ 500 | 0500 | 500 | 0500 | 1000 | 0
550 | 0550 | | | 1100 | 0
(12 rows)
@@ -787,17 +814,17 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
-----+------+-----+------+----------+---
- 0 | 0000 | 0 | 0000 | 0 | 0
- 50 | 0050 | | | 100 | 0
+ 0 | 0000 | | | 0 | 0
+ 50 | 0050 | 50 | 0050 | 100 | 0
100 | 0100 | | | 200 | 0
- 150 | 0150 | 150 | 0150 | 300 | 0
- 200 | 0200 | | | 400 | 0
+ 150 | 0150 | | | 300 | 0
+ 200 | 0200 | 200 | 0200 | 400 | 0
250 | 0250 | | | 500 | 0
- 300 | 0300 | 300 | 0300 | 600 | 0
- 350 | 0350 | | | 700 | 0
+ 300 | 0300 | | | 600 | 0
+ 350 | 0350 | 350 | 0350 | 700 | 0
400 | 0400 | | | 800 | 0
- 450 | 0450 | 450 | 0450 | 900 | 0
- 500 | 0500 | | | 1000 | 0
+ 450 | 0450 | | | 900 | 0
+ 500 | 0500 | 500 | 0500 | 1000 | 0
550 | 0550 | | | 1100 | 0
(12 rows)
@@ -805,65 +832,51 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
-- make sure these go to null as expected
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Sort
Sort Key: prt1_p1.a, prt2_p1.b, ((prt1_e_p1.a + prt1_e_p1.b))
- -> Result
- -> Append
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = ((prt1_e_p1.a + prt1_e_p1.b) / 2))
- Filter: ((prt1_p1.a = (50)) OR (prt2_p1.b = (75)) OR (((prt1_e_p1.a + prt1_e_p1.b) / 2) = (50)))
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = prt2_p1.b)
- -> Seq Scan on prt1_p1
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p1
- Filter: (a = 0)
- -> Hash
- -> Seq Scan on prt1_e_p1
- Filter: (c = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = ((prt1_e_p2.a + prt1_e_p2.b) / 2))
- Filter: ((prt1_p2.a = (50)) OR (prt2_p2.b = (75)) OR (((prt1_e_p2.a + prt1_e_p2.b) / 2) = (50)))
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = prt2_p2.b)
- -> Seq Scan on prt1_p2
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p2
- Filter: (a = 0)
- -> Hash
- -> Seq Scan on prt1_e_p2
- Filter: (c = 0)
- -> Hash Full Join
- Hash Cond: (prt1_p3.a = ((prt1_e_p3.a + prt1_e_p3.b) / 2))
- Filter: ((prt1_p3.a = (50)) OR (prt2_p3.b = (75)) OR (((prt1_e_p3.a + prt1_e_p3.b) / 2) = (50)))
- -> Hash Full Join
- Hash Cond: (prt1_p3.a = prt2_p3.b)
- -> Seq Scan on prt1_p3
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p3
- Filter: (a = 0)
- -> Hash
- -> Seq Scan on prt1_e_p3
- Filter: (c = 0)
-(43 rows)
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = ((prt1_e_p1.a + prt1_e_p1.b) / 2))
+ Filter: ((prt1_p1.a = (50)) OR (prt2_p1.b = (75)) OR (((prt1_e_p1.a + prt1_e_p1.b) / 2) = (50)))
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = prt2_p1.b)
+ -> Append
+ -> Seq Scan on prt1_p1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p3
+ Filter: (a = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_e_p1
+ Filter: (c = 0)
+ -> Seq Scan on prt1_e_p2
+ Filter: (c = 0)
+ -> Seq Scan on prt1_e_p3
+ Filter: (c = 0)
+(30 rows)
SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b;
a | phv | b | phv | ?column? | phv
----+-----+----+-----+----------+-----
- 50 | 50 | | | 100 | 50
- | | 75 | 75 | |
-(2 rows)
+ 50 | 50 | 50 | 75 | 100 | 50
+(1 row)
-- Semi-join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a;
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -897,23 +910,24 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Join Filter: (t1_1.a = t1_5.b)
-> HashAggregate
Group Key: t1_5.b
- -> Nested Loop
- -> Seq Scan on prt2_p3 t1_5
- Filter: (a = 0)
- -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t2_2
- Index Cond: (((a + b) / 2) = t1_5.b)
+ -> Hash Join
+ Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_5.b)
+ -> Seq Scan on prt1_e_p3 t2_2
+ -> Hash
+ -> Seq Scan on prt2_p3 t1_5
+ Filter: (a = 0)
-> Index Scan using iprt1_p3_a on prt1_p3 t1_1
Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
Filter: (b = 0)
-(41 rows)
+(42 rows)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a;
a | b | c
-----+---+------
- 0 | 0 | 0000
- 150 | 0 | 0150
- 300 | 0 | 0300
- 450 | 0 | 0450
+ 50 | 0 | 0050
+ 200 | 0 | 0200
+ 350 | 0 | 0350
+ 500 | 0 | 0500
(4 rows)
EXPLAIN (COSTS OFF)
@@ -965,10 +979,10 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
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;
a | b | c
-----+---+------
- 0 | 0 | 0000
- 150 | 0 | 0150
- 300 | 0 | 0300
- 450 | 0 | 0450
+ 50 | 0 | 0050
+ 200 | 0 | 0200
+ 350 | 0 | 0350
+ 500 | 0 | 0500
(4 rows)
-- test merge joins
@@ -1030,10 +1044,10 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
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;
a | b | c
-----+---+------
- 0 | 0 | 0000
- 150 | 0 | 0150
- 300 | 0 | 0300
- 450 | 0 | 0450
+ 50 | 0 | 0050
+ 200 | 0 | 0200
+ 350 | 0 | 0350
+ 500 | 0 | 0500
(4 rows)
EXPLAIN (COSTS OFF)
@@ -1097,22 +1111,899 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
-----+------+-----+------+----------+---
- 0 | 0000 | 0 | 0000 | 0 | 0
- 50 | 0050 | | | 100 | 0
+ 0 | 0000 | | | 0 | 0
+ 50 | 0050 | 50 | 0050 | 100 | 0
100 | 0100 | | | 200 | 0
- 150 | 0150 | 150 | 0150 | 300 | 0
- 200 | 0200 | | | 400 | 0
+ 150 | 0150 | | | 300 | 0
+ 200 | 0200 | 200 | 0200 | 400 | 0
250 | 0250 | | | 500 | 0
- 300 | 0300 | 300 | 0300 | 600 | 0
- 350 | 0350 | | | 700 | 0
+ 300 | 0300 | | | 600 | 0
+ 350 | 0350 | 350 | 0350 | 700 | 0
400 | 0400 | | | 800 | 0
- 450 | 0450 | 450 | 0450 | 900 | 0
- 500 | 0500 | | | 1000 | 0
+ 450 | 0450 | | | 900 | 0
+ 500 | 0500 | 500 | 0500 | 1000 | 0
550 | 0550 | | | 1100 | 0
(12 rows)
RESET enable_hashjoin;
RESET enable_nestloop;
+-- Add an extra partition to prt2 , Partition-wise join is possible with
+-- partitions on inner side are allowed
+CREATE TABLE prt2_p4 PARTITION OF prt2 FOR VALUES FROM (600) TO (700);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(600, 699, 3) i;
+ANALYZE prt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt2_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2_1.b = t1_2.a)
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2_2.b = t1_1.a)
+ -> Seq Scan on prt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+(21 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+---+------
+ 50 | 0050 | 0 | 0050
+ 200 | 0200 | 0 | 0200
+ 350 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500
+(4 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Hash Right Join
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt2_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: (t2_1.b = t1_2.a)
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: (t2_2.b = t1_1.a)
+ -> Seq Scan on prt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+(21 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+---+------
+ 0 | 0000 | |
+ 50 | 0050 | 0 | 0050
+ 100 | 0100 | |
+ 150 | 0150 | |
+ 200 | 0200 | 0 | 0200
+ 250 | 0250 | |
+ 300 | 0300 | |
+ 350 | 0350 | 0 | 0350
+ 400 | 0400 | |
+ 450 | 0450 | |
+ 500 | 0500 | 0 | 0500
+ 550 | 0550 | |
+(12 rows)
+
+-- right join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t2.a;
+ QUERY PLAN
+--------------------------------------------
+ Hash Right Join
+ Hash Cond: (t1.a = t2.b)
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p3 t1_1
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p2 t2_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p3 t2_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p4 t2_3
+ Filter: (a = 0)
+(16 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+---+------
+ 50 | 0050 | 0 | 0050
+ 200 | 0200 | 0 | 0200
+ 350 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500
+ | | 0 | 0275
+ | | 0 | 0425
+ | | 0 | 0125
+ | | 0 | 0600
+ | | 0 | 0675
+(9 rows)
+
+-- full join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.a
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1.a = t2.b)
+ Join Filter: ((t1.b + t2.a) = 0)
+ -> Seq Scan on prt1_p1 t1
+ -> Hash
+ -> Seq Scan on prt2_p1 t2
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2_1.b)
+ Join Filter: ((t1_2.b + t2_1.a) = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: (t1_1.a = t2_2.b)
+ Join Filter: ((t1_1.b + t2_2.a) = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+(21 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+ a | c | a | c
+-----+------+---+------
+ 50 | 0050 | 0 | 0050
+ 200 | 0200 | 0 | 0200
+ 350 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500
+(4 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Append
+ -> Hash Semi Join
+ Hash Cond: (t1.a = t2.b)
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p1 t2
+ -> Hash Semi Join
+ Hash Cond: (t1_2.a = t2_1.b)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash Semi Join
+ Hash Cond: (t1_1.a = t2_2.b)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+(21 rows)
+
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 50 | 0 | 0050
+ 200 | 0 | 0200
+ 350 | 0 | 0350
+ 500 | 0 | 0500
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Sort
+ Sort Key: t1.b, t1.c
+ -> Append
+ -> Hash Semi Join
+ Hash Cond: (t1.b = t2.a)
+ -> Seq Scan on prt2_p1 t1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_p1 t2
+ -> Hash Semi Join
+ Hash Cond: (t1_1.b = t2_2.a)
+ -> Seq Scan on prt2_p2 t1_1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_p2 t2_2
+ -> Nested Loop Semi Join
+ -> Seq Scan on prt2_p3 t1_2
+ Filter: (a = 0)
+ -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_1
+ Index Cond: (a = t1_2.b)
+(20 rows)
+
+select t1.a, t1.b, t1.c from prt2 t1 where exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+---+-----+------
+ 0 | 50 | 0050
+ 0 | 200 | 0200
+ 0 | 350 | 0350
+ 0 | 500 | 0500
+(4 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where not exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Append
+ -> Hash Anti Join
+ Hash Cond: (t1.a = t2.b)
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p1 t2
+ -> Hash Anti Join
+ Hash Cond: (t1_2.a = t2_1.b)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash Anti Join
+ Hash Cond: (t1_1.a = t2_2.b)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+(21 rows)
+
+select t1.a, t1.b, t1.c from prt1 t1 where not exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 100 | 0 | 0100
+ 150 | 0 | 0150
+ 250 | 0 | 0250
+ 300 | 0 | 0300
+ 400 | 0 | 0400
+ 450 | 0 | 0450
+ 550 | 0 | 0550
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Sort
+ Sort Key: t1.b, t1.c
+ -> Append
+ -> Hash Anti Join
+ Hash Cond: (t1.b = t2.a)
+ -> Seq Scan on prt2_p1 t1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_p1 t2
+ -> Hash Anti Join
+ Hash Cond: (t1_1.b = t2_2.a)
+ -> Seq Scan on prt2_p2 t1_1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_p2 t2_2
+ -> Nested Loop Anti Join
+ -> Seq Scan on prt2_p3 t1_2
+ Filter: (a = 0)
+ -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_1
+ Index Cond: (a = t1_2.b)
+(20 rows)
+
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+---+-----+------
+ 0 | 125 | 0125
+ 0 | 275 | 0275
+ 0 | 425 | 0425
+(3 rows)
+
+--TODO: remove below comments and enable partition_wise_join
+--one issue got fixed
+--Getting wrong output with partition wise join
+--setting partition-wise-join to off to get correct output
+SET enable_partition_wise_join to false;
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+---+-----+------
+ 0 | 125 | 0125
+ 0 | 275 | 0275
+ 0 | 425 | 0425
+ 0 | 600 | 0600
+ 0 | 675 | 0675
+(5 rows)
+
+SET enable_partition_wise_join to true;
+-- N-Way joins
+--TODO: remove below comments and enable partition_wise_join
+--one issue got fixed
+--Server crashed with below queries
+--setting partition-wise-join to off
+SET enable_partition_wise_join to false;
+-- t1 join t2 qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c, t2.a, t2.c, t3.a, t3.c
+ -> Hash Right Join
+ Hash Cond: (t3.b = t2.b)
+ -> Append
+ -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p3 t3_2
+ -> Seq Scan on prt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(25 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ a | c | a | c | a | c
+-----+------+---+------+---+------
+ 50 | 0050 | 0 | 0050 | 0 | 0050
+ 200 | 0200 | 0 | 0200 | 0 | 0200
+ 350 | 0350 | 0 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500 | 0 | 0500
+(4 rows)
+
+-- t1 join t2 qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c, t2.a, t2.c, t3.a, t3.c
+ -> Hash Join
+ Hash Cond: (t3.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p3 t3_2
+ -> Seq Scan on prt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(25 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ a | c | a | c | a | c
+-----+------+---+------+---+------
+ 50 | 0050 | 0 | 0050 | 0 | 0050
+ 200 | 0200 | 0 | 0200 | 0 | 0200
+ 350 | 0350 | 0 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500 | 0 | 0500
+(4 rows)
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c, t2.a, t2.c, t3.a, t3.c
+ -> Hash Join
+ Hash Cond: (t3.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p3 t3_2
+ -> Seq Scan on prt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(25 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ a | c | a | c | a | c
+-----+------+---+------+---+------
+ 50 | 0050 | 0 | 0050 | 0 | 0050
+ 200 | 0200 | 0 | 0200 | 0 | 0200
+ 350 | 0350 | 0 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500 | 0 | 0500
+(4 rows)
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) FULL JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c, t2.a, t2.c, t3.a, t3.c
+ -> Hash Right Join
+ Hash Cond: (t3.b = t2.b)
+ -> Append
+ -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p3 t3_2
+ -> Seq Scan on prt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(25 rows)
+
+SET enable_partition_wise_join to true;
+-- Partition-wise join can not handle the case when one partition from one side
+-- matches with multiple partitions on the other side
+DROP TABLE prt2_p4;
+CREATE TABLE prt2_p4 PARTITION OF prt2 FOR VALUES FROM (550) TO (700);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(550, 699, 3) i;
+ANALYZE prt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(17 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Right Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(17 rows)
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t2.a;
+ QUERY PLAN
+--------------------------------------------
+ Hash Right Join
+ Hash Cond: (t1.a = t2.b)
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p3 t1_1
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p2 t2_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p3 t2_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p4 t2_3
+ Filter: (a = 0)
+(16 rows)
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.a
+ -> Hash Join
+ Hash Cond: (t1.a = t2.b)
+ Join Filter: ((t1.b + t2.a) = 0)
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p3 t1_1
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+(15 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Semi Join
+ Hash Cond: (t1.a = t2.b)
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+(17 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.b, t1.c
+ -> Hash Semi Join
+ Hash Cond: (t1.b = t2.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p2 t1_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p3 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p4 t1_3
+ Filter: (a = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t2
+ -> Seq Scan on prt1_p3 t2_1
+ -> Seq Scan on prt1_p2 t2_2
+(18 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where not exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Anti Join
+ Hash Cond: (t1.a = t2.b)
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+(17 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.b, t1.c
+ -> Hash Anti Join
+ Hash Cond: (t1.b = t2.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p2 t1_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p3 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_p4 t1_3
+ Filter: (a = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t2
+ -> Seq Scan on prt1_p3 t2_1
+ -> Seq Scan on prt1_p2 t2_2
+(18 rows)
+
+-- N-Way joins
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt1 t3 ON t2.b = t3.a WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c, t2.a, t2.c, t3.c
+ -> Hash Join
+ Hash Cond: (t3.a = t1.a)
+ -> Append
+ -> Seq Scan on prt1_p1 t3
+ -> Seq Scan on prt1_p3 t3_1
+ -> Seq Scan on prt1_p2 t3_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(24 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt1 t3 ON t2.b = t3.a WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ a | c | a | c | a | c
+-----+------+---+------+-----+------
+ 50 | 0050 | 0 | 0050 | 50 | 0050
+ 200 | 0200 | 0 | 0200 | 200 | 0200
+ 350 | 0350 | 0 | 0350 | 350 | 0350
+ 500 | 0500 | 0 | 0500 | 500 | 0500
+ 550 | 0550 | 0 | 0550 | 550 | 0550
+(5 rows)
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c, t2.a, t2.c, t3.a, t3.c
+ -> Hash Join
+ Hash Cond: (t3.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p3 t3_2
+ -> Seq Scan on prt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Append
+ -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+(25 rows)
+
+-- Partition-wise join with minvalue/maxvalue bounds
+DROP TABLE prt2_p1;
+DROP TABLE prt2_p3;
+DROP TABLE prt2_p4;
+CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (MINVALUE) TO (250);
+CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (MAXVALUE);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 250, 3) i;
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(500, 600, 3) i;
+ANALYZE prt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Hash Join
+ Hash Cond: (t2_1.b = t1.a)
+ -> Seq Scan on prt2_p1 t2_1
+ -> Hash
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2.b = t1_2.a)
+ -> Seq Scan on prt2_p2 t2
+ -> Hash
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2_2.b = t1_1.a)
+ -> Seq Scan on prt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+(21 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+---+------
+ 0 | 0000 | 0 | 0000
+ 150 | 0150 | 0 | 0150
+ 350 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500
+(4 rows)
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2_1.a
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1.a = t2_1.b)
+ Join Filter: ((t1.b + t2_1.a) = 0)
+ -> Seq Scan on prt1_p1 t1
+ -> Hash
+ -> Seq Scan on prt2_p1 t2_1
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2.b)
+ Join Filter: ((t1_2.b + t2.a) = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p2 t2
+ -> Hash Join
+ Hash Cond: (t1_1.a = t2_2.b)
+ Join Filter: ((t1_1.b + t2_2.a) = 0)
+ -> Seq Scan on prt1_p3 t1_1
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+(21 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+ a | c | a | c
+-----+------+---+------
+ 0 | 0000 | 0 | 0000
+ 150 | 0150 | 0 | 0150
+ 350 | 0350 | 0 | 0350
+ 500 | 0500 | 0 | 0500
+(4 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Append
+ -> Hash Semi Join
+ Hash Cond: (t1.a = t2_1.b)
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p1 t2_1
+ -> Hash Semi Join
+ Hash Cond: (t1_2.a = t2.b)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p2 t2
+ -> Hash Semi Join
+ Hash Cond: (t1_1.a = t2_2.b)
+ -> Seq Scan on prt1_p3 t1_1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+(21 rows)
+
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 150 | 0 | 0150
+ 350 | 0 | 0350
+ 500 | 0 | 0500
+(4 rows)
+
--
-- partitioned by multiple columns
--
@@ -1182,83 +2073,2174 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_m WHERE prt1_m.c = 0) t1
--
-- tests for list partitioned tables.
--
-CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
-CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0003', '0004', '0010');
-CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0001', '0005', '0002', '0009');
-CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007', '0008', '0011');
-INSERT INTO plt1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
-ANALYZE plt1;
-CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
-CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0000', '0003', '0004', '0010');
-CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0009');
-CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011');
-INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
-ANALYZE plt2;
+\set part_mod 17
+\set cond_mod 47
+\set num_rows 500
+CREATE TABLE plt1 (a int, b int, c varchar) PARTITION BY LIST(c);
+CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0001','0002','0003');
+CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0004','0005','0006');
+CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0008','0009');
+CREATE TABLE plt1_p4 PARTITION OF plt1 FOR VALUES IN ('0000','0010');
+INSERT INTO plt1 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod NOT IN (7, 11, 12, 13, 14, 15, 16);
+ANALYSE plt1;
+-- plt2 have missing starting 0001, additional 0007, missing ending 0010
+-- and additional 0011 and 0012 bounds
+CREATE TABLE plt2 (a int, b int, c varchar) PARTITION BY LIST(c);
+CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0002','0003');
+CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0004','0005','0006');
+CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0007','0008','0009');
+CREATE TABLE plt2_p4 PARTITION OF plt2 FOR VALUES IN ('0000','0011','0012');
+INSERT INTO plt2 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod NOT IN (1, 10, 13, 14, 15, 16);
+ANALYSE plt2;
+-- Partition-wise-join is possible with some partition bounds overlap
+-- with each other completely and some partialy for inner,left,right,
+-- full, semi and anti joins
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t1_3.b + t2_3.b) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + t2_1.b) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Hash Left Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Right Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Left Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Left Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 188 | 0001 | |
+ 282 | 0010 | |
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+(7 rows)
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Sort Key: t2_3.a
+ -> Result
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Filter: ((COALESCE(t1_3.b, 0) + t2_3.b) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Left Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Left Join
+ Hash Cond: ((t2_1.c)::text = (t1_1.c)::text)
+ Filter: ((COALESCE(t1_1.b, 0) + t2_1.b) = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash Right Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((COALESCE(t1_2.b, 0) + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(28 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+ | | 470 | 0011
+(6 rows)
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.a
+ -> Append
+ -> Hash Full Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Filter: ((COALESCE(t1_3.b, 0) + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Full Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Full Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Filter: ((COALESCE(t1_1.b, 0) + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Full Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((COALESCE(t1_2.b, 0) + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 188 | 0001 | |
+ 282 | 0010 | |
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+ | | 470 | 0011
+(8 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> HashAggregate
+ Group Key: (t2_3.c)::text
+ -> Seq Scan on plt2_p4 t2_3
+ -> Nested Loop
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Seq Scan on plt2_p1 t2
+ -> Materialize
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t2_2
+(29 rows)
+
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> HashAggregate
+ Group Key: (t2_3.c)::text
+ -> Seq Scan on plt1_p4 t2_3
+ -> Materialize
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p1 t2
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t2_1
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t2_2
+(26 rows)
+
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on plt2_p1 t2
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t2_2
+(24 rows)
+
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 188 | 0 | 0001
+ 282 | 0 | 0010
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Hash Anti Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on plt1_p4 t2_3
+ -> Nested Loop Anti Join
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p1 t2
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t2_1
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t2_2
+(24 rows)
+
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 470 | 0 | 0011
+(1 row)
+
+-- N-Way joins
+-- inner-inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1,plt2 t2,plt1 t3 WHERE t1.c = t2.c AND t2.c = t3.c AND t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a,t2.b;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.b
+ -> Result
+ -> Append
+ -> Merge Join
+ Merge Cond: ((t2_3.c)::text = (t1_3.c)::text)
+ Join Filter: ((t1_3.b + t2_3.b) = 0)
+ -> Merge Join
+ Merge Cond: ((t3_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t2_3.b + t3_3.b) = 0)
+ -> Sort
+ Sort Key: t3_3.c
+ -> Seq Scan on plt1_p4 t3_3
+ -> Sort
+ Sort Key: t2_3.c
+ -> Seq Scan on plt2_p4 t2_3
+ -> Sort
+ Sort Key: t1_3.c
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash Join
+ Hash Cond: ((t3.c)::text = (t1.c)::text)
+ Join Filter: ((t2.b + t3.b) = 0)
+ -> Seq Scan on plt1_p1 t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Join
+ Hash Cond: ((t3_1.c)::text = (t1_1.c)::text)
+ Join Filter: ((t2_1.b + t3_1.b) = 0)
+ -> Seq Scan on plt1_p2 t3_1
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + t2_1.b) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t3_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t2_2.b + t3_2.b) = 0)
+ -> Seq Scan on plt1_p3 t3_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(52 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1,plt2 t2,plt1 t3 WHERE t1.c = t2.c AND t2.c = t3.c AND t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a,t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- left-left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c LEFT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b, 0) = 0 AND t2.b + coalesce(t3.b, 0) = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.b
+ -> Result
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t3_3.c)::text = (t2_3.c)::text)
+ Filter: ((t2_3.b + COALESCE(t3_3.b, 0)) = 0)
+ -> Seq Scan on plt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Join Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt1_p1 t1
+ -> Hash
+ -> Hash Left Join
+ Hash Cond: ((t2.c)::text = (t3.c)::text)
+ Filter: ((t2.b + COALESCE(t3.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt2_p1 t3
+ -> Hash Right Join
+ Hash Cond: ((t3_1.c)::text = (t2_1.c)::text)
+ Filter: ((t2_1.b + COALESCE(t3_1.b, 0)) = 0)
+ -> Seq Scan on plt2_p2 t3_1
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Right Join
+ Hash Cond: ((t3_2.c)::text = (t2_2.c)::text)
+ Filter: ((t2_2.b + COALESCE(t3_2.b, 0)) = 0)
+ -> Seq Scan on plt2_p3 t3_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(48 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c LEFT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b, 0) = 0 AND t2.b + coalesce(t3.b, 0) = 0 ORDER BY t1.a, t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- left-right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c RIGHT JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t1.b, 0) + t3.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.b
+ -> Result
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t3_3.c)::text = (t1_3.c)::text)
+ Join Filter: ((COALESCE(t1_3.b, 0) + t3_3.b) = 0)
+ -> Seq Scan on plt1_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Join
+ Hash Cond: ((t3.c)::text = (t1.c)::text)
+ Join Filter: ((COALESCE(t1.b, 0) + t3.b) = 0)
+ -> Seq Scan on plt1_p1 t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Join
+ Hash Cond: ((t3_1.c)::text = (t1_1.c)::text)
+ Join Filter: ((COALESCE(t1_1.b, 0) + t3_1.b) = 0)
+ -> Seq Scan on plt1_p2 t3_1
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t2_2.c)::text = (t1_2.c)::text)
+ Join Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt2_p3 t2_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t3_2.c)::text)
+ Join Filter: ((COALESCE(t1_2.b, 0) + t3_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt1_p3 t3_2
+(48 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c RIGHT JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t1.b, 0) + t3.b = 0 ORDER BY t1.a, t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- right-full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c FULL JOIN plt1 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b,0) + t2.b = 0 AND coalesce(t2.b,0) + coalesce(t3.b,0) = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.b
+ -> Hash Right Join
+ Hash Cond: ((t3.c)::text = (t2_3.c)::text)
+ Filter: ((COALESCE(t2_3.b, 0) + COALESCE(t3.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t3
+ -> Seq Scan on plt1_p2 t3_1
+ -> Seq Scan on plt1_p3 t3_2
+ -> Seq Scan on plt1_p4 t3_3
+ -> Hash
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Filter: ((COALESCE(t1_3.b, 0) + t2_3.b) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Left Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Left Join
+ Hash Cond: ((t2_1.c)::text = (t1_1.c)::text)
+ Filter: ((COALESCE(t1_1.b, 0) + t2_1.b) = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash Right Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((COALESCE(t1_2.b, 0) + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(36 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c FULL JOIN plt1 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b,0) + t2.b = 0 AND coalesce(t2.b,0) + coalesce(t3.b,0) = 0 ORDER BY t1.a, t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+ | | 470 | 0011 | |
+(6 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+SELECT t1.* FROM plt1 t1 WHERE t1.c IN (SELECT t1.c FROM plt2 t1 WHERE t1.c IN (SELECT t3.c FROM plt1 t3 WHERE t3.b = 0)) AND t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Nested Loop
+ Join Filter: ((t1_3.c)::text = (t1_7.c)::text)
+ -> HashAggregate
+ Group Key: (t1_7.c)::text
+ -> Hash Join
+ Hash Cond: ((t1_7.c)::text = (t3_3.c)::text)
+ -> Seq Scan on plt2_p4 t1_7
+ -> Hash
+ -> HashAggregate
+ Group Key: (t3_3.c)::text
+ -> Seq Scan on plt1_p4 t3_3
+ Filter: (b = 0)
+ -> Materialize
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Nested Loop
+ Join Filter: ((t1.c)::text = (t1_4.c)::text)
+ -> HashAggregate
+ Group Key: (t1_4.c)::text
+ -> Hash Join
+ Hash Cond: ((t1_4.c)::text = (t3.c)::text)
+ -> Seq Scan on plt2_p1 t1_4
+ -> Hash
+ -> HashAggregate
+ Group Key: (t3.c)::text
+ -> Seq Scan on plt1_p1 t3
+ Filter: (b = 0)
+ -> Materialize
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Nested Loop
+ Join Filter: ((t1_1.c)::text = (t1_5.c)::text)
+ -> HashAggregate
+ Group Key: (t1_5.c)::text
+ -> Hash Join
+ Hash Cond: ((t1_5.c)::text = (t3_1.c)::text)
+ -> Seq Scan on plt2_p2 t1_5
+ -> Hash
+ -> HashAggregate
+ Group Key: (t3_1.c)::text
+ -> Seq Scan on plt1_p2 t3_1
+ Filter: (b = 0)
+ -> Materialize
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t1_6.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Hash Semi Join
+ Hash Cond: ((t1_6.c)::text = (t3_2.c)::text)
+ -> Seq Scan on plt2_p3 t1_6
+ -> Hash
+ -> Seq Scan on plt1_p3 t3_2
+ Filter: (b = 0)
+(58 rows)
+
+SELECT t1.* FROM plt1 t1 WHERE t1.c IN (SELECT t1.c FROM plt2 t1 WHERE t1.c IN (SELECT t3.c FROM plt1 t3 WHERE t3.b = 0)) AND t1.b = 0 ORDER BY t1.a;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
--
-- list partitioned by expression
--
CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c, 'A'));
-CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0000', '0003', '0004', '0010');
-CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0001', '0005', '0002', '0009');
-CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0006', '0007', '0008', '0011');
-INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
+CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0002', '0003');
+CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0004', '0005', '0006');
+CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0008', '0009');
+CREATE TABLE plt1_e_p4 PARTITION OF plt1_e FOR VALUES IN ('0000');
+INSERT INTO plt1_e SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod NOT IN (1, 7, 10, 11, 12, 13, 14, 15, 16);
ANALYZE plt1_e;
--- test partition matching with N-way join
+-- test partition matching with N-way join with expression
EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Sort
- Sort Key: t1.c, t3.c
+ Sort Key: t1_3.c, t3_3.c
-> HashAggregate
- Group Key: t1.c, t2.c, t3.c
+ Group Key: t1_3.c, t2_3.c, t3_3.c
-> Result
-> Append
-> Hash Join
- Hash Cond: (t1.c = t2.c)
- -> Seq Scan on plt1_p1 t1
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = ltrim(t3_3.c, 'A'::text))
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt1_e_p4 t3_3
-> Hash
- -> Hash Join
- Hash Cond: (t2.c = ltrim(t3.c, 'A'::text))
- -> Seq Scan on plt2_p1 t2
- -> Hash
- -> Seq Scan on plt1_e_p1 t3
+ -> Seq Scan on plt2_p4 t2_3
-> Hash Join
- Hash Cond: (t1_1.c = t2_1.c)
- -> Seq Scan on plt1_p2 t1_1
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = ltrim(t3.c, 'A'::text))
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_e_p1 t3
-> Hash
- -> Hash Join
- Hash Cond: (t2_1.c = ltrim(t3_1.c, 'A'::text))
- -> Seq Scan on plt2_p2 t2_1
- -> Hash
- -> Seq Scan on plt1_e_p2 t3_1
+ -> Seq Scan on plt1_p1 t1
-> Hash Join
- Hash Cond: (t1_2.c = t2_2.c)
- -> Seq Scan on plt1_p3 t1_2
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = ltrim(t3_1.c, 'A'::text))
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt1_e_p2 t3_1
-> Hash
- -> Hash Join
- Hash Cond: (t2_2.c = ltrim(t3_2.c, 'A'::text))
- -> Seq Scan on plt2_p3 t2_2
- -> Hash
- -> Seq Scan on plt1_e_p3 t3_2
-(33 rows)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = ltrim(t3_2.c, 'A'::text))
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt1_e_p3 t3_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(42 rows)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
- avg | avg | avg | c | c | c
-----------------------+----------------------+-----------------------+------+------+-------
- 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
- 74.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
- 124.0000000000000000 | 124.5000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
- 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
- 224.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
- 274.0000000000000000 | 274.5000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
- 324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
- 374.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
- 424.0000000000000000 | 424.5000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
- 474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
- 524.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
- 574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
-(12 rows)
+ avg | avg | avg | c | c | c
+----------------------+---------------------+----------------------+------+------+------
+ 246.5000000000000000 | 22.4666666666666667 | 268.9666666666666667 | 0000 | 0000 | 0000
+ 248.5000000000000000 | 21.3333333333333333 | 269.8333333333333333 | 0002 | 0002 | 0002
+ 249.5000000000000000 | 22.3333333333333333 | 271.8333333333333333 | 0003 | 0003 | 0003
+ 250.5000000000000000 | 23.3333333333333333 | 273.8333333333333333 | 0004 | 0004 | 0004
+ 251.5000000000000000 | 22.7666666666666667 | 274.2666666666666667 | 0005 | 0005 | 0005
+ 252.5000000000000000 | 22.2000000000000000 | 274.7000000000000000 | 0006 | 0006 | 0006
+ 246.0000000000000000 | 23.9655172413793103 | 269.9655172413793103 | 0008 | 0008 | 0008
+ 247.0000000000000000 | 23.3448275862068966 | 270.3448275862068966 | 0009 | 0009 | 0009
+(8 rows)
+
+-- Add an extra partition to plt2 , Partition-wise join is possible with
+-- partitions on inner side are allowed
+CREATE TABLE plt2_p5 PARTITION OF plt2 FOR VALUES IN ('0013','0014');
+INSERT INTO plt2 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (13, 14);
+ANALYZE plt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t1_3.b + t2_3.b) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + t2_1.b) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Hash Left Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Right Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Left Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Left Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 188 | 0001 | |
+ 282 | 0010 | |
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+(7 rows)
+
+-- right join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t2.a
+ -> Hash Right Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(17 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+ | | 47 | 0013
+ | | 470 | 0011
+ | | 235 | 0014
+(8 rows)
+
+-- full join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.a
+ -> Hash Full Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(17 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 188 | 0001 | |
+ 282 | 0010 | |
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+ | | 47 | 0013
+ | | 235 | 0014
+ | | 470 | 0011
+(10 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> HashAggregate
+ Group Key: (t2_3.c)::text
+ -> Seq Scan on plt2_p4 t2_3
+ -> Nested Loop
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Seq Scan on plt2_p1 t2
+ -> Materialize
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t2_2
+(29 rows)
+
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> HashAggregate
+ Group Key: (t2_3.c)::text
+ -> Seq Scan on plt1_p4 t2_3
+ -> Materialize
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p1 t2
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t2_1
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t2_2
+(26 rows)
+
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on plt2_p1 t2
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t2_2
+(24 rows)
+
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 188 | 0 | 0001
+ 282 | 0 | 0010
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p5 t1_4
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t2
+ -> Seq Scan on plt1_p2 t2_1
+ -> Seq Scan on plt1_p3 t2_2
+ -> Seq Scan on plt1_p4 t2_3
+(21 rows)
+
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 47 | 0 | 0013
+ 235 | 0 | 0014
+ 470 | 0 | 0011
+(3 rows)
+
+-- N-Way joins
+-- t1 join t2 qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) LEFT JOIN plt2 t3 ON t2.c = t3.c WHERE t1.b + t2.b = 0 AND t2.b + coalesce(t3.b,0) = 0 ORDER BY t1.a,t2.b;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.b
+ -> Result
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t3_3.c)::text = (t2_3.c)::text)
+ Filter: ((t2_3.b + COALESCE(t3_3.b, 0)) = 0)
+ -> Seq Scan on plt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t1_3.b + t2_3.b) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Seq Scan on plt1_p1 t1
+ -> Hash
+ -> Hash Left Join
+ Hash Cond: ((t2.c)::text = (t3.c)::text)
+ Filter: ((t2.b + COALESCE(t3.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt2_p1 t3
+ -> Hash Right Join
+ Hash Cond: ((t3_1.c)::text = (t2_1.c)::text)
+ Filter: ((t2_1.b + COALESCE(t3_1.b, 0)) = 0)
+ -> Seq Scan on plt2_p2 t3_1
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + t2_1.b) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Right Join
+ Hash Cond: ((t3_2.c)::text = (t2_2.c)::text)
+ Filter: ((t2_2.b + COALESCE(t3_2.b, 0)) = 0)
+ -> Seq Scan on plt2_p3 t3_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(48 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) LEFT JOIN plt2 t3 ON t2.c = t3.c WHERE t1.b + t2.b = 0 AND t2.b + coalesce(t3.b,0) = 0 ORDER BY t1.a,t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- t1 join t2 qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c) RIGHT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t2.b,0) + t3.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.b
+ -> Result
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t3_3.c)::text = (t1_3.c)::text)
+ Join Filter: ((COALESCE(t2_3.b, 0) + t3_3.b) = 0)
+ -> Seq Scan on plt2_p4 t3_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ Join Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Join
+ Hash Cond: ((t3.c)::text = (t1.c)::text)
+ Join Filter: ((COALESCE(t2.b, 0) + t3.b) = 0)
+ -> Seq Scan on plt2_p1 t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Join
+ Hash Cond: ((t3_1.c)::text = (t1_1.c)::text)
+ Join Filter: ((COALESCE(t2_1.b, 0) + t3_1.b) = 0)
+ -> Seq Scan on plt2_p2 t3_1
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t3_2.c)::text = (t1_2.c)::text)
+ Join Filter: ((COALESCE(t2_2.b, 0) + t3_2.b) = 0)
+ -> Seq Scan on plt2_p3 t3_2
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(48 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c) RIGHT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t2.b,0) + t3.b = 0 ORDER BY t1.a, t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt2 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b, 0) + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b
+ -> Hash Join
+ Hash Cond: ((t3.c)::text = (t2.c)::text)
+ Join Filter: ((t2.b + t3.b) = 0)
+ -> Append
+ -> Seq Scan on plt2_p1 t3
+ -> Seq Scan on plt2_p2 t3_1
+ -> Seq Scan on plt2_p3 t3_2
+ -> Seq Scan on plt2_p4 t3_3
+ -> Seq Scan on plt2_p5 t3_4
+ -> Hash
+ -> Hash Right Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt2 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b, 0) + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+ | | 470 | 0011 | 470 | 0011
+ | | 47 | 0013 | 47 | 0013
+ | | 235 | 0014 | 235 | 0014
+(8 rows)
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c) FULL JOIN plt1 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b, 0) + t2.b = 0 AND coalesce(t2.b,0) + coalesce(t3.b,0) = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b
+ -> Hash Right Join
+ Hash Cond: ((t3.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t2.b, 0) + COALESCE(t3.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t3
+ -> Seq Scan on plt1_p2 t3_1
+ -> Seq Scan on plt1_p3 t3_2
+ -> Seq Scan on plt1_p4 t3_3
+ -> Hash
+ -> Hash Right Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(26 rows)
+
+-- Partition-wise join can not handle the case when one partition from one side
+-- matches with multiple partitions on the other side
+DROP TABLE plt2_p5;
+CREATE TABLE plt2_p5 PARTITION OF plt2 FOR VALUES IN ('0001','0013','0014');
+INSERT INTO plt2 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (1, 13, 14);
+ANALYZE plt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(17 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Left Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(17 rows)
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t2.a
+ -> Hash Right Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(17 rows)
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.a
+ -> Hash Full Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(17 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Result
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(23 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p5 t1_4
+ Filter: (b = 0)
+ -> Hash
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Result
+ -> Append
+ -> Seq Scan on plt1_p1 t2
+ -> Seq Scan on plt1_p2 t2_1
+ -> Seq Scan on plt1_p3 t2_2
+ -> Seq Scan on plt1_p4 t2_3
+(24 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Nested Loop Anti Join
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Materialize
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(20 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p5 t1_4
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t2
+ -> Seq Scan on plt1_p2 t2_1
+ -> Seq Scan on plt1_p3 t2_2
+ -> Seq Scan on plt1_p4 t2_3
+(21 rows)
+
+-- N-Way joins
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t3.c)::text = (t2.c)::text)
+ Join Filter: ((t2.b + t3.b) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t3
+ -> Seq Scan on plt1_p2 t3_1
+ -> Seq Scan on plt1_p3 t3_2
+ -> Seq Scan on plt1_p4 t3_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(26 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+ a | c | a | c | a | c
+-----+------+-----+------+-----+------
+ 0 | 0000 | 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005 | 141 | 0005
+ 188 | 0001 | 188 | 0001 | 188 | 0001
+ 329 | 0006 | 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002 | 376 | 0002
+(6 rows)
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c) RIGHT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b, 0) = 0 AND coalesce(t2.b,0) + t3.b = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b
+ -> Hash Join
+ Hash Cond: ((t3.c)::text = (t1.c)::text)
+ Join Filter: ((COALESCE(t2.b, 0) + t3.b) = 0)
+ -> Append
+ -> Seq Scan on plt2_p1 t3
+ -> Seq Scan on plt2_p2 t3_1
+ -> Seq Scan on plt2_p3 t3_2
+ -> Seq Scan on plt2_p4 t3_3
+ -> Seq Scan on plt2_p5 t3_4
+ -> Hash
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ Join Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p3 t1_2
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Seq Scan on plt2_p5 t2_4
+(27 rows)
+
+-- partition have a NULL on one side, Partition-wise join is possible with
+-- NULL when NULL comparision is not strict i.e. NULL=NULL allowed
+-- in this case NULL will be treated as addition partition bounds.
+DROP TABLE plt2_p5;
+DROP TABLE plt2_p4;
+CREATE TABLE plt2_p4 PARTITION OF plt2 FOR VALUES IN ('0000',NULL,'0012');
+INSERT INTO plt2 SELECT i, i % :cond_mod, case when i % :part_mod = 11 then NULL else to_char(i % :part_mod, 'FM0000') end FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (0,11,12);
+ANALYZE plt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Hash Join
+ Hash Cond: ((t2_3.c)::text = (t1_3.c)::text)
+ Join Filter: ((t1_3.b + t2_3.b) = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Join Filter: ((t1_1.b + t2_1.b) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Join Filter: ((t1_2.b + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+(5 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t2_3.c)::text = (t1_3.c)::text)
+ Filter: ((t1_3.b + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash Right Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Left Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Filter: ((t1_1.b + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Left Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((t1_2.b + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 188 | 0001 | |
+ 282 | 0010 | |
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+(7 rows)
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+ QUERY PLAN
+------------------------------------------------------------------
+ Sort
+ Sort Key: t2_3.a
+ -> Result
+ -> Append
+ -> Hash Left Join
+ Hash Cond: ((t2_3.c)::text = (t1_3.c)::text)
+ Filter: ((COALESCE(t1_3.b, 0) + t2_3.b) = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash Left Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Left Join
+ Hash Cond: ((t2_1.c)::text = (t1_1.c)::text)
+ Filter: ((COALESCE(t1_1.b, 0) + t2_1.b) = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash Right Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((COALESCE(t1_2.b, 0) + t2_2.b) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(28 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t1.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+ | | 470 |
+(6 rows)
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t2_3.a
+ -> Append
+ -> Hash Full Join
+ Hash Cond: ((t2_3.c)::text = (t1_3.c)::text)
+ Filter: ((COALESCE(t1_3.b, 0) + COALESCE(t2_3.b, 0)) = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Seq Scan on plt1_p4 t1_3
+ -> Hash Full Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0)
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Full Join
+ Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
+ Filter: ((COALESCE(t1_1.b, 0) + COALESCE(t2_1.b, 0)) = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Full Join
+ Hash Cond: ((t1_2.c)::text = (t2_2.c)::text)
+ Filter: ((COALESCE(t1_2.b, 0) + COALESCE(t2_2.b, 0)) = 0)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(27 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 94 | 0009 | 94 | 0009
+ 141 | 0005 | 141 | 0005
+ 188 | 0001 | |
+ 282 | 0010 | |
+ 329 | 0006 | 329 | 0006
+ 376 | 0002 | 376 | 0002
+ | | 470 |
+(8 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> HashAggregate
+ Group Key: (t2_3.c)::text
+ -> Seq Scan on plt2_p4 t2_3
+ -> Materialize
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Nested Loop
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Seq Scan on plt2_p1 t2
+ -> Materialize
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t2_2
+(29 rows)
+
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> HashAggregate
+ Group Key: (t2_3.c)::text
+ -> Seq Scan on plt1_p4 t2_3
+ -> Materialize
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Nested Loop Semi Join
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p1 t2
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t2_1
+ -> Nested Loop Semi Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t2_2
+(26 rows)
+
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 94 | 0 | 0009
+ 141 | 0 | 0005
+ 329 | 0 | 0006
+ 376 | 0 | 0002
+(5 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt1_p4 t1_3
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on plt2_p1 t2
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t2_1
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt1_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t2_2
+(24 rows)
+
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+------
+ 188 | 0 | 0001
+ 282 | 0 | 0010
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1_3.a, t1_3.c
+ -> Append
+ -> Hash Anti Join
+ Hash Cond: ((t1_3.c)::text = (t2_3.c)::text)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> Seq Scan on plt1_p4 t2_3
+ -> Nested Loop Anti Join
+ Join Filter: ((t1.c)::text = (t2.c)::text)
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p1 t2
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t2_1
+ -> Nested Loop Anti Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t2_2
+(24 rows)
+
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ a | b | c
+-----+---+---
+ 470 | 0 |
+(1 row)
+
+-- partition have a NULL on both side with different partition bounds w.r.t other side
+-- NULL when NULL comparision is not strict i.e. NULL=NULL allowed
+-- Partition-wise join can not handle the case when one partition from one side
+-- matches with multiple partitions on the other side
+DROP TABLE plt1_p3;
+CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN (NULL,'0008','0009');
+INSERT INTO plt1 SELECT i, i % :cond_mod, case when i % :part_mod = 7 then NULL else to_char(i % :part_mod, 'FM0000') end FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (7,8,9);
+ANALYZE plt1;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Join Filter: ((t1.b + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p4 t1_2
+ -> Seq Scan on plt1_p3 t1_3
+(16 rows)
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Right Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p4 t1_2
+ -> Seq Scan on plt1_p3 t1_3
+(16 rows)
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t2.a
+ -> Hash Left Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + t2.b) = 0)
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p4 t1_2
+ -> Seq Scan on plt1_p3 t1_3
+(16 rows)
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.a
+ -> Hash Full Join
+ Hash Cond: ((t2.c)::text = (t1.c)::text)
+ Filter: ((COALESCE(t1.b, 0) + COALESCE(t2.b, 0)) = 0)
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ -> Seq Scan on plt1_p2 t1_1
+ -> Seq Scan on plt1_p4 t1_2
+ -> Seq Scan on plt1_p3 t1_3
+(16 rows)
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p4 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Result
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+(22 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> HashAggregate
+ Group Key: (t2.c)::text
+ -> Result
+ -> Append
+ -> Seq Scan on plt1_p1 t2
+ -> Seq Scan on plt1_p2 t2_1
+ -> Seq Scan on plt1_p4 t2_2
+ -> Seq Scan on plt1_p3 t2_3
+(22 rows)
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p4 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt1_p3 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on plt2_p1 t2
+ -> Seq Scan on plt2_p2 t2_1
+ -> Seq Scan on plt2_p3 t2_2
+ -> Seq Scan on plt2_p4 t2_3
+(19 rows)
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Hash Anti Join
+ Hash Cond: ((t1.c)::text = (t2.c)::text)
+ -> Append
+ -> Seq Scan on plt2_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on plt2_p4 t1_3
+ Filter: (b = 0)
+ -> Hash
+ -> Append
+ -> Seq Scan on plt1_p1 t2
+ -> Seq Scan on plt1_p2 t2_1
+ -> Seq Scan on plt1_p4 t2_2
+ -> Seq Scan on plt1_p3 t2_3
+(19 rows)
-- joins where one of the relations is proven empty
EXPLAIN (COSTS OFF)
@@ -1286,9 +4268,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
-> Hash Left Join
Hash Cond: (t2.b = a)
-> Append
- -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2
Filter: (a = 0)
- -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p1 t2_1
Filter: (a = 0)
-> Seq Scan on prt2_p3 t2_2
Filter: (a = 0)
@@ -1306,9 +4288,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
-> Hash Left Join
Hash Cond: (t2.b = a)
-> Append
- -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p2 t2
Filter: (a = 0)
- -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p1 t2_1
Filter: (a = 0)
-> Seq Scan on prt2_p3 t2_2
Filter: (a = 0)
@@ -1341,7 +4323,7 @@ CREATE TABLE prt2_l_p3_p2 PARTITION OF prt2_l_p3 FOR VALUES FROM (13) TO (25);
INSERT INTO prt2_l SELECT i % 25, i, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 3) i;
ANALYZE prt2_l;
-- inner join, qual covering only top-level partitions
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
-------------------------------------------------------------
@@ -1386,7 +4368,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1
(4 rows)
-- left join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
------------------------------------------------------------------------------------
@@ -1440,7 +4422,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b
(12 rows)
-- right join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
------------------------------------------------------------------------------------------
@@ -1491,7 +4473,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b
(8 rows)
-- full join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
@@ -1552,7 +4534,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1
(16 rows)
-- lateral partition-wise join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss
ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
@@ -1626,7 +4608,7 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
(12 rows)
-- join with one side empty
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c;
QUERY PLAN
-------------------------------------------------------------------------
@@ -1699,15 +4681,15 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt2 t3 WHERE t1.a = t2.a
-> Hash
-> Append
-> Hash Join
- Hash Cond: (t1.a = t3.b)
+ Hash Cond: (t1.a = t3_1.b)
-> Seq Scan on prt1_p1 t1
-> Hash
- -> Seq Scan on prt2_p1 t3
+ -> Seq Scan on prt2_p1 t3_1
-> Hash Join
- Hash Cond: (t1_2.a = t3_1.b)
+ Hash Cond: (t1_2.a = t3.b)
-> Seq Scan on prt1_p2 t1_2
-> Hash
- -> Seq Scan on prt2_p2 t3_1
+ -> Seq Scan on prt2_p2 t3
-> Hash Join
Hash Cond: (t1_1.a = t3_2.b)
-> Seq Scan on prt1_p3 t1_1
@@ -1719,21 +4701,20 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt2 t3 WHERE t1.a = t2.a
-- between partition keys
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON (t1.a < t2.b);
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------
Nested Loop Left Join
+ Join Filter: (t1.a < t2.b)
-> Append
-> Seq Scan on prt1_p1 t1
-> Seq Scan on prt1_p3 t1_1
-> Seq Scan on prt1_p2 t1_2
- -> Append
- -> Index Scan using iprt2_p1_b on prt2_p1 t2
- Index Cond: (t1.a < b)
- -> Index Scan using iprt2_p2_b on prt2_p2 t2_1
- Index Cond: (t1.a < b)
- -> Index Scan using iprt2_p3_b on prt2_p3 t2_2
- Index Cond: (t1.a < b)
-(12 rows)
+ -> Materialize
+ -> Append
+ -> Seq Scan on prt2_p2 t2
+ -> Seq Scan on prt2_p1 t2_1
+ -> Seq Scan on prt2_p3 t2_2
+(11 rows)
-- equi-join with join condition on partial keys does not qualify for
-- partition-wise join
@@ -1819,16 +4800,17 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
-> Seq Scan on prt2_n_p2 t2_1
-> Hash
-> Hash Join
- Hash Cond: (t3.c = (t1.c)::text)
+ Hash Cond: ((t3.c)::text = (t1.c)::text)
-> Append
-> Seq Scan on plt1_p1 t3
-> Seq Scan on plt1_p2 t3_1
- -> Seq Scan on plt1_p3 t3_2
+ -> Seq Scan on plt1_p4 t3_2
+ -> Seq Scan on plt1_p3 t3_3
-> Hash
-> Append
-> Seq Scan on prt1_n_p1 t1
-> Seq Scan on prt1_n_p2 t1_1
-(16 rows)
+(17 rows)
-- partition-wise join can not be applied for a join between list and range
-- partitioned table
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index cd54ea0..7aa53c0 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -7,6 +7,10 @@
SET enable_partition_wise_join to true;
--
+-- tests for range partitioned tables.
+--
+
+--
-- partitioned by a single column
--
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
@@ -19,16 +23,22 @@ CREATE INDEX iprt1_p2_a on prt1_p2(a);
CREATE INDEX iprt1_p3_a on prt1_p3(a);
ANALYZE prt1;
+-- prt2 have missing starting 0-50 range and missing ending 550-600
+-- range bounds
CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
-CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250);
+CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (50) TO (250);
CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500);
-CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600);
-INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 3) i;
+CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (550);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0+50, 599-50, 3) i;
CREATE INDEX iprt2_p1_b on prt2_p1(b);
CREATE INDEX iprt2_p2_b on prt2_p2(b);
CREATE INDEX iprt2_p3_b on prt2_p3(b);
ANALYZE prt2;
+-- Partition-wise-join is possible with some partition bounds overlap
+-- with each other completely and some partialy for inner,left,right,
+-- full, semi and anti joins
+
-- inner join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -77,11 +87,19 @@ EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a;
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a;
+EXPLAIN (COSTS OFF)
+SELECT t1.* FROM prt2 t1 WHERE t1.b IN (SELECT t2.a FROM prt1 t2 WHERE t2.b = 0) AND t1.a = 0 ORDER BY t1.a;
+SELECT t1.* FROM prt2 t1 WHERE t1.b IN (SELECT t2.a FROM prt1 t2 WHERE t2.b = 0) AND t1.a = 0 ORDER BY t1.a;
+
-- Anti-join with aggregates
EXPLAIN (COSTS OFF)
SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b);
SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b);
+EXPLAIN (COSTS OFF)
+SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt2 t1 WHERE NOT EXISTS (SELECT 1 FROM prt1 t2 WHERE t1.b = t2.a);
+SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt2 t1 WHERE NOT EXISTS (SELECT 1 FROM prt1 t2 WHERE t1.b = t2.a);
+
-- lateral reference
EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 LEFT JOIN LATERAL
@@ -126,9 +144,15 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 =
--
-- N-way join
--
+--TODO: remove below comments and enable partition_wise_join
+--one issue got fixed
+--Server crashed with below queries
+--setting partition-wise-join to off
+SET enable_partition_wise_join to false;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
+SET enable_partition_wise_join to true;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
@@ -168,6 +192,156 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
RESET enable_hashjoin;
RESET enable_nestloop;
+-- Add an extra partition to prt2 , Partition-wise join is possible with
+-- partitions on inner side are allowed
+CREATE TABLE prt2_p4 PARTITION OF prt2 FOR VALUES FROM (600) TO (700);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(600, 699, 3) i;
+ANALYZE prt2;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+
+-- right join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a;
+
+-- full join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from prt2 t1 where exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where not exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from prt1 t1 where not exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+--TODO: remove below comments and enable partition_wise_join
+--one issue got fixed
+--Getting wrong output with partition wise join
+--setting partition-wise-join to off to get correct output
+SET enable_partition_wise_join to false;
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+SET enable_partition_wise_join to true;
+
+-- N-Way joins
+--TODO: remove below comments and enable partition_wise_join
+--one issue got fixed
+--Server crashed with below queries
+--setting partition-wise-join to off
+SET enable_partition_wise_join to false;
+-- t1 join t2 qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+
+-- t1 join t2 qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) FULL JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+SET enable_partition_wise_join to true;
+
+-- Partition-wise join can not handle the case when one partition from one side
+-- matches with multiple partitions on the other side
+DROP TABLE prt2_p4;
+CREATE TABLE prt2_p4 PARTITION OF prt2 FOR VALUES FROM (550) TO (700);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(550, 699, 3) i;
+ANALYZE prt2;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t2.a;
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where not exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt2 t1 where not exists (select 1 from prt1 t2 WHERE t1.b = t2.a) and t1.a = 0 order by t1.a, t1.b, t1.c;
+
+-- N-Way joins
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt1 t3 ON t2.b = t3.a WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b) INNER JOIN prt1 t3 ON t2.b = t3.a WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt2 t3 ON t2.b = t3.b WHERE t1.b = 0 ORDER BY 1,2,3,4,5,6;
+
+-- Partition-wise join with minvalue/maxvalue bounds
+DROP TABLE prt2_p1;
+DROP TABLE prt2_p3;
+DROP TABLE prt2_p4;
+CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (MINVALUE) TO (250);
+CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (MAXVALUE);
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 250, 3) i;
+INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(500, 600, 3) i;
+ANALYZE prt2;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 INNER JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a;
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt1 t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b + t2.a = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from prt1 t1 where exists (select 1 from prt2 t2 WHERE t1.a = t2.b) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
--
-- partitioned by multiple columns
--
@@ -192,35 +366,311 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_m WHERE prt1_m.c = 0) t1
--
-- tests for list partitioned tables.
--
-CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
-CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0003', '0004', '0010');
-CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0001', '0005', '0002', '0009');
-CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007', '0008', '0011');
-INSERT INTO plt1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
-ANALYZE plt1;
+\set part_mod 17
+\set cond_mod 47
+\set num_rows 500
+
+CREATE TABLE plt1 (a int, b int, c varchar) PARTITION BY LIST(c);
+CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0001','0002','0003');
+CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0004','0005','0006');
+CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0008','0009');
+CREATE TABLE plt1_p4 PARTITION OF plt1 FOR VALUES IN ('0000','0010');
+INSERT INTO plt1 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod NOT IN (7, 11, 12, 13, 14, 15, 16);
+ANALYSE plt1;
+
+-- plt2 have missing starting 0001, additional 0007, missing ending 0010
+-- and additional 0011 and 0012 bounds
+CREATE TABLE plt2 (a int, b int, c varchar) PARTITION BY LIST(c);
+CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0002','0003');
+CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0004','0005','0006');
+CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0007','0008','0009');
+CREATE TABLE plt2_p4 PARTITION OF plt2 FOR VALUES IN ('0000','0011','0012');
+INSERT INTO plt2 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod NOT IN (1, 10, 13, 14, 15, 16);
+ANALYSE plt2;
+
+-- Partition-wise-join is possible with some partition bounds overlap
+-- with each other completely and some partialy for inner,left,right,
+-- full, semi and anti joins
-CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
-CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0000', '0003', '0004', '0010');
-CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0009');
-CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011');
-INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
-ANALYZE plt2;
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t1.a;
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- N-Way joins
+-- inner-inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1,plt2 t2,plt1 t3 WHERE t1.c = t2.c AND t2.c = t3.c AND t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a,t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1,plt2 t2,plt1 t3 WHERE t1.c = t2.c AND t2.c = t3.c AND t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a,t2.b;
+
+-- left-left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c LEFT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b, 0) = 0 AND t2.b + coalesce(t3.b, 0) = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c LEFT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b, 0) = 0 AND t2.b + coalesce(t3.b, 0) = 0 ORDER BY t1.a, t2.b;
+
+-- left-right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c RIGHT JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t1.b, 0) + t3.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c RIGHT JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t1.b, 0) + t3.b = 0 ORDER BY t1.a, t2.b;
+
+-- right-full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c FULL JOIN plt1 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b,0) + t2.b = 0 AND coalesce(t2.b,0) + coalesce(t3.b,0) = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c FULL JOIN plt1 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b,0) + t2.b = 0 AND coalesce(t2.b,0) + coalesce(t3.b,0) = 0 ORDER BY t1.a, t2.b;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+SELECT t1.* FROM plt1 t1 WHERE t1.c IN (SELECT t1.c FROM plt2 t1 WHERE t1.c IN (SELECT t3.c FROM plt1 t3 WHERE t3.b = 0)) AND t1.b = 0 ORDER BY t1.a;
+SELECT t1.* FROM plt1 t1 WHERE t1.c IN (SELECT t1.c FROM plt2 t1 WHERE t1.c IN (SELECT t3.c FROM plt1 t3 WHERE t3.b = 0)) AND t1.b = 0 ORDER BY t1.a;
--
-- list partitioned by expression
--
CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c, 'A'));
-CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0000', '0003', '0004', '0010');
-CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0001', '0005', '0002', '0009');
-CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0006', '0007', '0008', '0011');
-INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
+CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0002', '0003');
+CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0004', '0005', '0006');
+CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0008', '0009');
+CREATE TABLE plt1_e_p4 PARTITION OF plt1_e FOR VALUES IN ('0000');
+INSERT INTO plt1_e SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod NOT IN (1, 7, 10, 11, 12, 13, 14, 15, 16);
ANALYZE plt1_e;
--- test partition matching with N-way join
+-- test partition matching with N-way join with expression
EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+-- Add an extra partition to plt2 , Partition-wise join is possible with
+-- partitions on inner side are allowed
+CREATE TABLE plt2_p5 PARTITION OF plt2 FOR VALUES IN ('0013','0014');
+INSERT INTO plt2 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (13, 14);
+ANALYZE plt2;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+
+-- right join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t1.a;
+
+-- full join, partition-wise join can not handle extra partition on the outer
+-- side
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- N-Way joins
+-- t1 join t2 qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) LEFT JOIN plt2 t3 ON t2.c = t3.c WHERE t1.b + t2.b = 0 AND t2.b + coalesce(t3.b,0) = 0 ORDER BY t1.a,t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) LEFT JOIN plt2 t3 ON t2.c = t3.c WHERE t1.b + t2.b = 0 AND t2.b + coalesce(t3.b,0) = 0 ORDER BY t1.a,t2.b;
+
+-- t1 join t2 qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c) RIGHT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t2.b,0) + t3.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c) RIGHT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b,0) = 0 AND coalesce(t2.b,0) + t3.b = 0 ORDER BY t1.a, t2.b;
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt2 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b, 0) + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt2 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b, 0) + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c) FULL JOIN plt1 t3 ON (t2.c = t3.c) WHERE coalesce(t1.b, 0) + t2.b = 0 AND coalesce(t2.b,0) + coalesce(t3.b,0) = 0 ORDER BY t1.a, t2.b;
+
+-- Partition-wise join can not handle the case when one partition from one side
+-- matches with multiple partitions on the other side
+DROP TABLE plt2_p5;
+CREATE TABLE plt2_p5 PARTITION OF plt2 FOR VALUES IN ('0001','0013','0014');
+INSERT INTO plt2 SELECT i, i % :cond_mod, to_char(i % :part_mod, 'FM0000') FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (1, 13, 14);
+ANALYZE plt2;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- N-Way joins
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c) INNER JOIN plt1 t3 ON (t2.c = t3.c) WHERE t1.b + t2.b = 0 AND t2.b + t3.b = 0 ORDER BY t1.a, t2.b;
+
+-- t1 join t2 not qualify partition-wise-join and t2 join t3 not qualify partition-wise-join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c) RIGHT JOIN plt2 t3 ON (t2.c = t3.c) WHERE t1.b + coalesce(t2.b, 0) = 0 AND coalesce(t2.b,0) + t3.b = 0 ORDER BY t1.a, t2.b;
+
+-- partition have a NULL on one side, Partition-wise join is possible with
+-- NULL when NULL comparision is not strict i.e. NULL=NULL allowed
+-- in this case NULL will be treated as addition partition bounds.
+DROP TABLE plt2_p5;
+DROP TABLE plt2_p4;
+CREATE TABLE plt2_p4 PARTITION OF plt2 FOR VALUES IN ('0000',NULL,'0012');
+INSERT INTO plt2 SELECT i, i % :cond_mod, case when i % :part_mod = 11 then NULL else to_char(i % :part_mod, 'FM0000') end FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (0,11,12);
+ANALYZE plt2;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t1.a;
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- partition have a NULL on both side with different partition bounds w.r.t other side
+-- NULL when NULL comparision is not strict i.e. NULL=NULL allowed
+-- Partition-wise join can not handle the case when one partition from one side
+-- matches with multiple partitions on the other side
+DROP TABLE plt1_p3;
+CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN (NULL,'0008','0009');
+INSERT INTO plt1 SELECT i, i % :cond_mod, case when i % :part_mod = 7 then NULL else to_char(i % :part_mod, 'FM0000') end FROM generate_series(0, :num_rows) i WHERE i % :part_mod IN (7,8,9);
+ANALYZE plt1;
+
+-- inner join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + t2.b = 0 ORDER BY t1.a;
+
+-- left join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 LEFT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.b + coalesce(t2.b, 0) = 0 ORDER BY t1.a;
+
+-- right join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + t2.b = 0 ORDER BY t2.a;
+
+-- full join
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON t1.c = t2.c WHERE coalesce(t1.b, 0) + coalesce(t2.b, 0) = 0 ORDER BY t1.a, t2.a;
+
+-- semi join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+-- anti join
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt1 t1 where not exists (select 1 from plt2 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
+EXPLAIN (COSTS OFF)
+select t1.a, t1.b, t1.c from plt2 t1 where not exists (select 1 from plt1 t2 WHERE t1.c = t2.c) and t1.b = 0 order by t1.a, t1.b, t1.c;
+
-- joins where one of the relations is proven empty
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = 1 AND t1.a = 2;
@@ -260,27 +710,27 @@ INSERT INTO prt2_l SELECT i % 25, i, to_char(i % 4, 'FM0000') FROM generate_seri
ANALYZE prt2_l;
-- inner join, qual covering only top-level partitions
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
-- left join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
-- right join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
-- full join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b;
-- lateral partition-wise join
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss
ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
@@ -289,7 +739,7 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
-- join with one side empty
-EXPLAIN(COSTS OFF)
+EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c;
--