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; --