On Mon, Dec 4, 2017 at 7:34 AM, Ashutosh Bapat
<[email protected]> 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.
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27ab852..f83166b 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
--
@@ -1683,27 +1831,27 @@ CREATE TABLE prt3_n_p3 PARTITION OF prt3_n FOR VALUES IN ('0003', '0005', '0009'
INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
ANALYZE prt3_n;
CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
-CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
+CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (250);
CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
-INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
+INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i WHERE i < 250 AND i > 300;
ANALYZE prt4_n;
-- partition-wise join can not be applied if the partition ranges differ
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+--------------------------------------------
Hash Join
- Hash Cond: (t1.a = t2.a)
+ Hash Cond: (t2.a = t1.a)
-> Append
- -> Seq Scan on prt1_p1 t1
- -> Seq Scan on prt1_p2 t1_1
- -> Seq Scan on prt1_p3 t1_2
+ -> Seq Scan on prt4_n_p1 t2
+ -> Seq Scan on prt4_n_p2 t2_1
+ -> Seq Scan on prt4_n_p3 t2_2
-> Hash
-> Append
- -> Seq Scan on prt4_n_p1 t2
- -> Seq Scan on prt4_n_p2 t2_1
- -> Seq Scan on prt4_n_p3 t2_2
+ -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p2 t1_1
+ -> Seq Scan on prt1_p3 t1_2
(11 rows)
EXPLAIN (COSTS OFF)
@@ -1868,3 +2016,24 @@ 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 different partition have
+-- default partition
+ALTER TABLE prt4_n DETACH PARTITION prt4_n_p2;
+ALTER TABLE prt4_n ATTACH PARTITION prt4_n_p2 DEFAULT;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
+ QUERY PLAN
+--------------------------------------------
+ Hash Join
+ Hash Cond: (t2.a = t1.a)
+ -> Append
+ -> Seq Scan on prt4_n_p1 t2
+ -> Seq Scan on prt4_n_p3 t2_1
+ -> Seq Scan on prt4_n_p2 t2_2
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p2 t1_1
+ -> Seq Scan on prt1_p3 t1_2
+(11 rows)
+
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 6efdf3c..cb39485 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
--
@@ -342,10 +373,10 @@ INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0,
ANALYZE prt3_n;
CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
-CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
+CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (250);
CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
-INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
+INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i WHERE i < 250 AND i > 300;
ANALYZE prt4_n;
-- partition-wise join can not be applied if the partition ranges differ
@@ -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 different partition have
+-- default partition
+ALTER TABLE prt4_n DETACH PARTITION prt4_n_p2;
+ALTER TABLE prt4_n ATTACH PARTITION prt4_n_p2 DEFAULT;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
