Hi,
I noticed a crash in partition-wise involving dummy partitioned
tables. Here's simple testcase
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM
generate_series(0, 599) i WHERE i % 2 = 0;
ANALYZE prt1;
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_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) i WHERE i % 3 = 0;
ANALYZE prt2;
SET enable_partition_wise_join TO true;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND
a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b =
t3.a;
t1 is an empty partitioned relation, with partition scheme matching
that of t2. Thus build_joinrel_partition_info() deems t1 RIGHT JOIN t2
as partitioned and sets part_scheme, nparts and other partition
properties except part_rels. Later in try_partition_wise_join(), the
function bails out since t1 is dummy because of following code
/*
* set_rel_pathlist() may not create paths in children of an empty
* partitioned table and so we can not add paths to child-joins. So, deem
* such a join as unpartitioned. When a partitioned relation is deemed
* empty because all its children are empty, dummy path will be set in
* each of the children. In such a case we could still consider the join
* as partitioned, but it might not help much.
*/
if (IS_DUMMY_REL(rel1) || IS_DUMMY_REL(rel2))
return;
So, part_rels is never set for relation t1 LEFT JOIN t2. When
build_joinrel_partition_info() processes (t1 LEFT JOIN t2, t3), it
expects part_rels to be set for (t1 LEFT JOIN t2) since it's deemed to
be partitioned and following assertion fails
Assert(REL_HAS_ALL_PART_PROPS(outer_rel) &&
REL_HAS_ALL_PART_PROPS(inner_rel));
When I wrote this code, I thought that some join order of an N-way
join involving a dummy relation would have both the joining relations
partitioned with part_rels set i.e. child-join created. But that was a
wrong assumption. Any two-way join involving a dummy relation can not
have child-joins and hence can not be deemed as partitioned. For a 3
way join involving dummy relation, every two-way join involving that
dummy relation won't have child-joins and hence the 3 way join can not
have child-join. Similarly we can use induction to prove that any
N-way join involving a dummy relation will not have child-joins and
hence won't be partitioned. We can detect this case during
build_joinrel_partition_info(). One of the joining relations presented
to that function will involve the dummy relation and would have been
deemed as unpartitioned when it was processed. We don't need any dummy
relation handling in try_partition_wise_join().
Here's patch taking that approach.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 5bff90e..6e842f9 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3425,20 +3425,8 @@ generate_partition_wise_join_paths(PlannerInfo *root, RelOptInfo *rel)
if (!IS_JOIN_REL(rel))
return;
- /*
- * If we've already proven this join is empty, we needn't consider any
- * more paths for it.
- */
- if (IS_DUMMY_REL(rel))
- return;
-
- /*
- * We've nothing to do if the relation is not partitioned. An outer join
- * relation which had an empty inner relation in every pair will have the
- * rest of the partitioning properties set except the child-join
- * RelOptInfos. See try_partition_wise_join() for more details.
- */
- if (rel->nparts <= 0 || rel->part_rels == NULL)
+ /* We've nothing to do if the relation is not partitioned. */
+ if (!IS_PARTITIONED_REL(rel))
return;
/* Guard against stack overflow due to overly deep partition hierarchy. */
@@ -3452,6 +3440,8 @@ generate_partition_wise_join_paths(PlannerInfo *root, RelOptInfo *rel)
{
RelOptInfo *child_rel = part_rels[cnt_parts];
+ Assert(child_rel != NULL);
+
/* Add partition-wise join paths for partitioned child-joins. */
generate_partition_wise_join_paths(root, child_rel);
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index a35d068..f74afdb 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1319,17 +1319,6 @@ try_partition_wise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
return;
/*
- * set_rel_pathlist() may not create paths in children of an empty
- * partitioned table and so we can not add paths to child-joins. So, deem
- * such a join as unpartitioned. When a partitioned relation is deemed
- * empty because all its children are empty, dummy path will be set in
- * each of the children. In such a case we could still consider the join
- * as partitioned, but it might not help much.
- */
- if (IS_DUMMY_REL(rel1) || IS_DUMMY_REL(rel2))
- return;
-
- /*
* Since this join relation is partitioned, all the base relations
* participating in this join must be partitioned and so are all the
* intermediate join relations.
@@ -1360,11 +1349,6 @@ try_partition_wise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
nparts = joinrel->nparts;
- /* Allocate space to hold child-joins RelOptInfos, if not already done. */
- if (!joinrel->part_rels)
- joinrel->part_rels =
- (RelOptInfo **) palloc0(sizeof(RelOptInfo *) * nparts);
-
/*
* Create child-join relations for this partitioned join, if those don't
* exist. Add paths to child-joins for a pair of child relations
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index ac5a7c9..5c36832 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -1662,11 +1662,14 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
*/
joinrel->part_scheme = part_scheme;
joinrel->boundinfo = outer_rel->boundinfo;
- joinrel->nparts = outer_rel->nparts;
partnatts = joinrel->part_scheme->partnatts;
joinrel->partexprs = (List **) palloc0(sizeof(List *) * partnatts);
joinrel->nullable_partexprs =
(List **) palloc0(sizeof(List *) * partnatts);
+ joinrel->nparts = outer_rel->nparts;
+ joinrel->part_rels =
+ (RelOptInfo **) palloc0(sizeof(RelOptInfo *) * joinrel->nparts);
+
/*
* Construct partition keys for the join.
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 6bf68f3..e082d05 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -668,11 +668,24 @@ typedef struct RelOptInfo
*
* A join between two partitioned relations with same partitioning scheme
* without any matching partitions will not have any partition in it but will
- * have partition scheme set. So a relation is deemed to be partitioned if it
- * has a partitioning scheme, bounds and positive number of partitions.
+ * have partition scheme set.
+ *
+ * set_rel_pathlist() may not create paths in children of an empty partitioned
+ * table. Hence we can not add paths to child-joins produced as a result of
+ * joining an empty partitioned table with another similarly partitioned table.
+ * So, deem such a join as unpartitioned. As a result any N-way join involving
+ * an empty partitioned table will be deemed as unpartitioned and will not have
+ * child-RelOptInfos created. NOTE: When a partitioned relation is deemed empty
+ * because all its children are empty, dummy path will be set in each of the
+ * children. In such a case we could still consider the join as partitioned,
+ * but it might not help much.
+ *
+ * So a relation is deemed to be partitioned if it has a partitioning scheme,
+ * bounds, positive number of partitions and child RelOptInfos.
*/
#define IS_PARTITIONED_REL(rel) \
- ((rel)->part_scheme && (rel)->boundinfo && (rel)->nparts > 0)
+ ((rel)->part_scheme && (rel)->boundinfo && (rel)->nparts > 0 && \
+ (rel)->part_rels && !(IS_DUMMY_REL(rel)))
/*
* Convenience macro to make sure that a partitioned relation has all the
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27ab852..333f938 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1217,24 +1217,31 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
(2 rows)
EXPLAIN (COSTS OFF)
-SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------
- Sort
- Sort Key: a, t2.b
- -> Hash Left Join
- Hash Cond: (t2.b = a)
- -> 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)
- -> Hash
- -> Result
- One-Time Filter: false
-(14 rows)
+SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a;
+ QUERY PLAN
+--------------------------------------------------
+ Hash Left Join
+ Hash Cond: (t2.b = a)
+ -> Append
+ -> Hash Join
+ Hash Cond: (t3.a = t2.b)
+ -> Seq Scan on prt1_p1 t3
+ -> Hash
+ -> Seq Scan on prt2_p1 t2
+ -> Hash Join
+ Hash Cond: (t3_1.a = t2_1.b)
+ -> Seq Scan on prt1_p2 t3_1
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash Join
+ Hash Cond: (t3_2.a = t2_2.b)
+ -> Seq Scan on prt1_p3 t3_2
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+ -> Hash
+ -> Result
+ One-Time Filter: false
+(21 rows)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 6efdf3c..55c5615 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -224,7 +224,7 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 LEFT JOIN prt2 t2 ON t1.a = t2.b;
EXPLAIN (COSTS OFF)
-SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
