On Tue, Dec 5, 2017 at 11:04 AM, Rajkumar Raghuwanshi
<rajkumar.raghuwan...@enterprisedb.com> wrote:
> On Mon, Dec 4, 2017 at 7:34 AM, Ashutosh Bapat
> <ashutosh.ba...@enterprisedb.com> wrote:
>> I agree, the patch looks longer than expected. I think, it's important
>> to have some testcases to test partition-wise join with default
>> partitions. I think we need at least one test for range default
>> partitions, one test for list partitioning, one for multi-level
>> partitioning and one negative testcase with default partition missing
>> from one side of join.
>>
>> May be we could reduce the number of SQL commands and queries in the
>> patch by adding default partition to every table that participates in
>> partition-wise join (leave the tables participating in negative tests
>> aside.). But that's going to increase the size of EXPLAIN outputs and
>> query results. The negative test may simply drop the default partition
>> from one of the tables.
>>
>> For every table being tested, the patch adds two ALTER TABLE commands,
>> one for detaching an existing partition and then attach the same as
>> default partition. Alternative to that is just add a new default
>> partition without detaching and existing partition. But then the
>> default partition needs to populated with some data, which requires 1
>> INSERT statement at least. That doesn't reduce the size of patch, but
>> increases the output of query and EXPLAIN plan.
>>
>> May be in case of multi-level partitioning test, we don't need to add
>> DEFAULT in every partitioned relation; adding to one of them would be
>> enough. May be add it to the parent, but that too can be avoided. That
>> would reduce the size of patch a bit.
>
> Thanks Ashutosh for suggestions.
>
> I have reduced test cases as suggested. Attaching updated patch.
>
Sorry Attached wrong patch.
attaching correct patch now.
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27ab852..a60ba7f 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1337,6 +1337,96 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, ph
574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
(12 rows)
+-- test default partition behavior for range
+ALTER TABLE prt1 DETACH PARTITION prt1_p3;
+ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
+ALTER TABLE prt2 DETACH PARTITION prt2_p3;
+ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
+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;
+ 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_1.a)
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on prt1_p2 t1_1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on prt1_p3 t1_2
+ Filter: (b = 0)
+(21 rows)
+
+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
+(4 rows)
+
+-- test default partition behavior for list
+ALTER TABLE plt1 DETACH PARTITION plt1_p3;
+ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
+ALTER TABLE plt2 DETACH PARTITION plt2_p3;
+ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
+EXPLAIN (COSTS OFF)
+SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.c, t2.c
+ -> HashAggregate
+ Group Key: t1.c, t2.c
+ -> Result
+ -> Append
+ -> Hash Right Join
+ Hash Cond: (t1.c = t2.c)
+ -> Seq Scan on plt1_p1 t1
+ -> Hash
+ -> Seq Scan on plt2_p1 t2
+ -> Hash Right Join
+ Hash Cond: (t1_1.c = t2_1.c)
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash Right Join
+ Hash Cond: (t1_2.c = t2_2.c)
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(21 rows)
+
+SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+ avg | avg | c | c
+----------------------+----------------------+------+------
+ 24.0000000000000000 | 24.0000000000000000 | 0000 | 0000
+ 74.0000000000000000 | 75.0000000000000000 | 0001 | 0001
+ 124.0000000000000000 | 124.5000000000000000 | 0002 | 0002
+ 174.0000000000000000 | 174.0000000000000000 | 0003 | 0003
+ 224.0000000000000000 | 225.0000000000000000 | 0004 | 0004
+ 274.0000000000000000 | 274.5000000000000000 | 0005 | 0005
+ 324.0000000000000000 | 324.0000000000000000 | 0006 | 0006
+ 374.0000000000000000 | 375.0000000000000000 | 0007 | 0007
+ 424.0000000000000000 | 424.5000000000000000 | 0008 | 0008
+ 474.0000000000000000 | 474.0000000000000000 | 0009 | 0009
+ 524.0000000000000000 | 525.0000000000000000 | 0010 | 0010
+ 574.0000000000000000 | 574.5000000000000000 | 0011 | 0011
+(12 rows)
+
--
-- multiple levels of partitioning
--
@@ -1663,6 +1753,64 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2)
One-Time Filter: false
(11 rows)
+-- test default partition behavior for multiple levels of partitoning
+ALTER TABLE prt1_l DETACH PARTITION prt1_l_p2;
+ALTER TABLE prt1_l ATTACH PARTITION prt1_l_p2 DEFAULT;
+ALTER TABLE prt2_l DETACH PARTITION prt2_l_p2;
+ALTER TABLE prt2_l ATTACH PARTITION prt2_l_p2 DEFAULT;
+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
+------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t2.b = t1.a) AND ((t2.c)::text = (t1.c)::text))
+ -> Seq Scan on prt2_l_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_l_p1 t1
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: ((t2_1.b = t1_1.a) AND ((t2_1.c)::text = (t1_1.c)::text))
+ -> Append
+ -> Seq Scan on prt2_l_p3_p1 t2_1
+ -> Seq Scan on prt2_l_p3_p2 t2_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_l_p3_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: ((t2_3.b = t1_2.a) AND ((t2_3.c)::text = (t1_2.c)::text))
+ -> Seq Scan on prt2_l_p2_p1 t2_3
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p1 t1_2
+ Filter: (b = 0)
+ -> Hash Right Join
+ Hash Cond: ((t2_4.b = t1_3.a) AND ((t2_4.c)::text = (t1_3.c)::text))
+ -> Seq Scan on prt2_l_p2_p2 t2_4
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p2 t1_3
+ Filter: (b = 0)
+(30 rows)
+
+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;
+ a | c | b | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 50 | 0002 | |
+ 100 | 0000 | |
+ 150 | 0002 | 150 | 0002
+ 200 | 0000 | |
+ 250 | 0002 | |
+ 300 | 0000 | 300 | 0000
+ 350 | 0002 | |
+ 400 | 0000 | |
+ 450 | 0002 | 450 | 0002
+ 500 | 0000 | |
+ 550 | 0002 | |
+(12 rows)
+
--
-- negative testcases
--
@@ -1868,3 +2016,29 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
-> Seq Scan on prt1_n_p2 t1_1
(10 rows)
+-- partition-wise join can not be applied if the only one of joining table have
+-- default partition
+ALTER TABLE prt2 DETACH PARTITION prt2_p3;
+ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600);
+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;
+ 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
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_2
+ Filter: (b = 0)
+(16 rows)
+
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 6efdf3c..5b96012 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -261,6 +261,26 @@ EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_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 pht1 t1, pht2 t2, pht1_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;
+-- test default partition behavior for range
+ALTER TABLE prt1 DETACH PARTITION prt1_p3;
+ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
+ALTER TABLE prt2 DETACH PARTITION prt2_p3;
+ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
+
+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;
+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;
+
+-- test default partition behavior for list
+ALTER TABLE plt1 DETACH PARTITION plt1_p3;
+ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
+ALTER TABLE plt2 DETACH PARTITION plt2_p3;
+ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
+
+EXPLAIN (COSTS OFF)
+SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+
--
-- multiple levels of partitioning
--
@@ -319,6 +339,17 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
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;
+-- test default partition behavior for multiple levels of partitoning
+ALTER TABLE prt1_l DETACH PARTITION prt1_l_p2;
+ALTER TABLE prt1_l ATTACH PARTITION prt1_l_p2 DEFAULT;
+
+ALTER TABLE prt2_l DETACH PARTITION prt2_l_p2;
+ALTER TABLE prt2_l ATTACH PARTITION prt2_l_p2 DEFAULT;
+
+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;
+
--
-- negative testcases
--
@@ -384,3 +415,10 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
-- partitioned table
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
+
+-- partition-wise join can not be applied if the only one of joining table have
+-- default partition
+ALTER TABLE prt2 DETACH PARTITION prt2_p3;
+ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600);
+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;
