aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/optimizer/path/allpaths.c14
-rw-r--r--src/backend/optimizer/path/joinrels.c87
-rw-r--r--src/backend/optimizer/plan/planner.c17
-rw-r--r--src/test/regress/expected/partition_aggregate.out98
-rw-r--r--src/test/regress/expected/partition_join.out91
-rw-r--r--src/test/regress/sql/partition_aggregate.sql10
-rw-r--r--src/test/regress/sql/partition_join.sql3
7 files changed, 157 insertions, 163 deletions
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index eef58b5dce8..56ccde977cc 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1166,11 +1166,11 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
* for partitioned child rels.
*
* Note: here we abuse the consider_partitionwise_join flag by setting
- * it *even* for child rels that are not partitioned. In that case,
- * we set it to tell try_partitionwise_join() that it doesn't need to
- * generate their targetlists and EC entries as they have already been
- * generated here, as opposed to the dummy child rels for which the
- * flag is left set to false so that it will generate them.
+ * it for child rels that are not themselves partitioned. We do so to
+ * tell try_partitionwise_join() that the child rel is sufficiently
+ * valid to be used as a per-partition input, even if it later gets
+ * proven to be dummy. (It's not usable until we've set up the
+ * reltarget and EC entries, which we just did.)
*/
if (rel->consider_partitionwise_join)
childrel->consider_partitionwise_join = true;
@@ -3551,7 +3551,9 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
{
RelOptInfo *child_rel = part_rels[cnt_parts];
- Assert(child_rel != NULL);
+ /* If it's been pruned entirely, it's certainly dummy. */
+ if (child_rel == NULL)
+ continue;
/* Add partitionwise join paths for partitioned child-joins. */
generate_partitionwise_join_paths(root, child_rel);
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index d3c25766b5b..e2c20ded901 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -43,8 +43,6 @@ static void try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1,
RelOptInfo *rel2, RelOptInfo *joinrel,
SpecialJoinInfo *parent_sjinfo,
List *parent_restrictlist);
-static void update_child_rel_info(PlannerInfo *root,
- RelOptInfo *rel, RelOptInfo *childrel);
static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op);
@@ -1401,6 +1399,10 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
{
RelOptInfo *child_rel1 = rel1->part_rels[cnt_parts];
RelOptInfo *child_rel2 = rel2->part_rels[cnt_parts];
+ bool rel1_empty = (child_rel1 == NULL ||
+ IS_DUMMY_REL(child_rel1));
+ bool rel2_empty = (child_rel2 == NULL ||
+ IS_DUMMY_REL(child_rel2));
SpecialJoinInfo *child_sjinfo;
List *child_restrictlist;
RelOptInfo *child_joinrel;
@@ -1409,24 +1411,69 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
int nappinfos;
/*
- * If a child table has consider_partitionwise_join=false, it means
+ * Check for cases where we can prove that this segment of the join
+ * returns no rows, due to one or both inputs being empty (including
+ * inputs that have been pruned away entirely). If so just ignore it.
+ * These rules are equivalent to populate_joinrel_with_paths's rules
+ * for dummy input relations.
+ */
+ switch (parent_sjinfo->jointype)
+ {
+ case JOIN_INNER:
+ case JOIN_SEMI:
+ if (rel1_empty || rel2_empty)
+ continue; /* ignore this join segment */
+ break;
+ case JOIN_LEFT:
+ case JOIN_ANTI:
+ if (rel1_empty)
+ continue; /* ignore this join segment */
+ break;
+ case JOIN_FULL:
+ if (rel1_empty && rel2_empty)
+ continue; /* ignore this join segment */
+ break;
+ default:
+ /* other values not expected here */
+ elog(ERROR, "unrecognized join type: %d",
+ (int) parent_sjinfo->jointype);
+ break;
+ }
+
+ /*
+ * If a child has been pruned entirely then we can't generate paths
+ * for it, so we have to reject partitionwise joining unless we were
+ * able to eliminate this partition above.
+ */
+ if (child_rel1 == NULL || child_rel2 == NULL)
+ {
+ /*
+ * Mark the joinrel as unpartitioned so that later functions treat
+ * it correctly.
+ */
+ joinrel->nparts = 0;
+ return;
+ }
+
+ /*
+ * If a leaf relation has consider_partitionwise_join=false, it means
* that it's a dummy relation for which we skipped setting up tlist
- * expressions and adding EC members in set_append_rel_size(), so do
- * that now for use later.
+ * expressions and adding EC members in set_append_rel_size(), so
+ * again we have to fail here.
*/
if (rel1_is_simple && !child_rel1->consider_partitionwise_join)
{
Assert(child_rel1->reloptkind == RELOPT_OTHER_MEMBER_REL);
Assert(IS_DUMMY_REL(child_rel1));
- update_child_rel_info(root, rel1, child_rel1);
- child_rel1->consider_partitionwise_join = true;
+ joinrel->nparts = 0;
+ return;
}
if (rel2_is_simple && !child_rel2->consider_partitionwise_join)
{
Assert(child_rel2->reloptkind == RELOPT_OTHER_MEMBER_REL);
Assert(IS_DUMMY_REL(child_rel2));
- update_child_rel_info(root, rel2, child_rel2);
- child_rel2->consider_partitionwise_join = true;
+ joinrel->nparts = 0;
+ return;
}
/* We should never try to join two overlapping sets of rels. */
@@ -1471,28 +1518,6 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
}
/*
- * Set up tlist expressions for the childrel, and add EC members referencing
- * the childrel.
- */
-static void
-update_child_rel_info(PlannerInfo *root,
- RelOptInfo *rel, RelOptInfo *childrel)
-{
- AppendRelInfo *appinfo = root->append_rel_array[childrel->relid];
-
- /* Make child tlist expressions */
- childrel->reltarget->exprs = (List *)
- adjust_appendrel_attrs(root,
- (Node *) rel->reltarget->exprs,
- 1, &appinfo);
-
- /* Make child entries in the EquivalenceClass as well */
- if (rel->has_eclass_joins || has_useful_pathkeys(root, rel))
- add_child_rel_equivalences(root, appinfo, rel, childrel);
- childrel->has_eclass_joins = rel->has_eclass_joins;
-}
-
-/*
* Returns true if there exists an equi-join condition for each pair of
* partition keys from given relations being joined.
*/
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 03589da0134..60edaa8b0a3 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -6963,6 +6963,10 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
List *child_scanjoin_targets = NIL;
ListCell *lc;
+ /* Pruned or dummy children can be ignored. */
+ if (child_rel == NULL || IS_DUMMY_REL(child_rel))
+ continue;
+
/* Translate scan/join targets for this child. */
appinfos = find_appinfos_by_relids(root, child_rel->relids,
&nappinfos);
@@ -7063,8 +7067,9 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
RelOptInfo *child_grouped_rel;
RelOptInfo *child_partially_grouped_rel;
- /* Input child rel must have a path */
- Assert(child_input_rel->pathlist != NIL);
+ /* Pruned or dummy children can be ignored. */
+ if (child_input_rel == NULL || IS_DUMMY_REL(child_input_rel))
+ continue;
/*
* Copy the given "extra" structure as is and then override the
@@ -7106,14 +7111,6 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
extra->target_parallel_safe,
child_extra.havingQual);
- /* Ignore empty children. They contribute nothing. */
- if (IS_DUMMY_REL(child_input_rel))
- {
- mark_dummy_rel(child_grouped_rel);
-
- continue;
- }
-
/* Create grouping paths for this child relation. */
create_ordinary_grouping_paths(root, child_input_rel,
child_grouped_rel,
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index 6bc106831ee..e1549cbb5c6 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -716,37 +716,33 @@ SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y G
| 500
(16 rows)
--- LEFT JOIN, with dummy relation on right side,
+-- LEFT JOIN, with dummy relation on right side, ideally
-- should produce full partitionwise aggregation plan as GROUP BY is on
--- non-nullable columns
+-- non-nullable columns.
+-- But right now we are unable to do partitionwise join in this case.
EXPLAIN (COSTS OFF)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Sort
- Sort Key: pagg_tab1_p1.x, y
- -> Append
- -> HashAggregate
- Group Key: pagg_tab1_p1.x, y
- -> Hash Left Join
- Hash Cond: (pagg_tab1_p1.x = y)
- Filter: ((pagg_tab1_p1.x > 5) OR (y < 20))
+ Sort Key: pagg_tab1_p1.x, pagg_tab2_p2.y
+ -> HashAggregate
+ Group Key: pagg_tab1_p1.x, pagg_tab2_p2.y
+ -> Hash Left Join
+ Hash Cond: (pagg_tab1_p1.x = pagg_tab2_p2.y)
+ Filter: ((pagg_tab1_p1.x > 5) OR (pagg_tab2_p2.y < 20))
+ -> Append
-> Seq Scan on pagg_tab1_p1
Filter: (x < 20)
- -> Hash
- -> Result
- One-Time Filter: false
- -> HashAggregate
- Group Key: pagg_tab1_p2.x, pagg_tab2_p2.y
- -> Hash Left Join
- Hash Cond: (pagg_tab1_p2.x = pagg_tab2_p2.y)
- Filter: ((pagg_tab1_p2.x > 5) OR (pagg_tab2_p2.y < 20))
-> Seq Scan on pagg_tab1_p2
Filter: (x < 20)
- -> Hash
+ -> Hash
+ -> Append
-> Seq Scan on pagg_tab2_p2
Filter: (y > 10)
-(23 rows)
+ -> Seq Scan on pagg_tab2_p3
+ Filter: (y > 10)
+(18 rows)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
x | y | count
@@ -760,49 +756,33 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
18 | 18 | 100
(7 rows)
--- FULL JOIN, with dummy relations on both sides,
+-- FULL JOIN, with dummy relations on both sides, ideally
-- should produce partial partitionwise aggregation plan as GROUP BY is on
--- nullable columns
+-- nullable columns.
+-- But right now we are unable to do partitionwise join in this case.
EXPLAIN (COSTS OFF)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
- QUERY PLAN
------------------------------------------------------------------------------------
- Finalize GroupAggregate
- Group Key: pagg_tab1_p1.x, y
- -> Sort
- Sort Key: pagg_tab1_p1.x, y
- -> Append
- -> Partial HashAggregate
- Group Key: pagg_tab1_p1.x, y
- -> Hash Full Join
- Hash Cond: (pagg_tab1_p1.x = y)
- Filter: ((pagg_tab1_p1.x > 5) OR (y < 20))
- -> Seq Scan on pagg_tab1_p1
- Filter: (x < 20)
- -> Hash
- -> Result
- One-Time Filter: false
- -> Partial HashAggregate
- Group Key: pagg_tab1_p2.x, pagg_tab2_p2.y
- -> Hash Full Join
- Hash Cond: (pagg_tab1_p2.x = pagg_tab2_p2.y)
- Filter: ((pagg_tab1_p2.x > 5) OR (pagg_tab2_p2.y < 20))
- -> Seq Scan on pagg_tab1_p2
- Filter: (x < 20)
- -> Hash
- -> Seq Scan on pagg_tab2_p2
- Filter: (y > 10)
- -> Partial HashAggregate
- Group Key: x, pagg_tab2_p3.y
- -> Hash Full Join
- Hash Cond: (pagg_tab2_p3.y = x)
- Filter: ((x > 5) OR (pagg_tab2_p3.y < 20))
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Sort
+ Sort Key: pagg_tab1_p1.x, pagg_tab2_p2.y
+ -> HashAggregate
+ Group Key: pagg_tab1_p1.x, pagg_tab2_p2.y
+ -> Hash Full Join
+ Hash Cond: (pagg_tab1_p1.x = pagg_tab2_p2.y)
+ Filter: ((pagg_tab1_p1.x > 5) OR (pagg_tab2_p2.y < 20))
+ -> Append
+ -> Seq Scan on pagg_tab1_p1
+ Filter: (x < 20)
+ -> Seq Scan on pagg_tab1_p2
+ Filter: (x < 20)
+ -> Hash
+ -> Append
+ -> Seq Scan on pagg_tab2_p2
+ Filter: (y > 10)
-> Seq Scan on pagg_tab2_p3
Filter: (y > 10)
- -> Hash
- -> Result
- One-Time Filter: false
-(35 rows)
+(18 rows)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
x | y | count
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index a1fe29fc3a9..078b5fd2400 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -206,28 +206,27 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a <
300 | 0300 | 300 | 0300
(1 row)
+-- Currently we can't do partitioned join if nullable-side partitions are pruned
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
-----------------------------------------------------------
Sort
- Sort Key: prt1_p1.a, b
- -> Append
- -> Hash Left Join
- Hash Cond: (prt1_p1.a = b)
- -> Seq Scan on prt1_p1
- Filter: ((a < 450) AND (b = 0))
- -> Hash
- -> Result
- One-Time Filter: false
- -> Hash Right Join
- Hash Cond: (prt2_p2.b = prt1_p2.a)
+ Sort Key: prt1_p1.a, prt2_p2.b
+ -> Hash Right Join
+ Hash Cond: (prt2_p2.b = prt1_p1.a)
+ -> Append
-> Seq Scan on prt2_p2
Filter: (b > 250)
- -> Hash
+ -> Seq Scan on prt2_p3
+ Filter: (b > 250)
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1
+ Filter: ((a < 450) AND (b = 0))
-> Seq Scan on prt1_p2
Filter: ((a < 450) AND (b = 0))
-(17 rows)
+(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -243,38 +242,28 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
400 | 0400 | |
(9 rows)
+-- Currently we can't do partitioned join if nullable-side partitions are pruned
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
@@ -994,34 +983,30 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
(12 rows)
-- MergeAppend on nullable column
+-- This should generate a partitionwise join, but currently fails to
EXPLAIN (COSTS OFF)
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
-----------------------------------------------------------
Sort
- Sort Key: prt1_p1.a, b
- -> Append
- -> Merge Left Join
- Merge Cond: (prt1_p1.a = b)
- -> Sort
- Sort Key: prt1_p1.a
+ Sort Key: prt1_p1.a, prt2_p2.b
+ -> Merge Left Join
+ Merge Cond: (prt1_p1.a = prt2_p2.b)
+ -> Sort
+ Sort Key: prt1_p1.a
+ -> Append
-> Seq Scan on prt1_p1
Filter: ((a < 450) AND (b = 0))
- -> Sort
- Sort Key: b
- -> Result
- One-Time Filter: false
- -> Merge Left Join
- Merge Cond: (prt1_p2.a = prt2_p2.b)
- -> Sort
- Sort Key: prt1_p2.a
-> Seq Scan on prt1_p2
Filter: ((a < 450) AND (b = 0))
- -> Sort
- Sort Key: prt2_p2.b
+ -> Sort
+ Sort Key: prt2_p2.b
+ -> Append
-> Seq Scan on prt2_p2
Filter: (b > 250)
-(23 rows)
+ -> Seq Scan on prt2_p3
+ Filter: (b > 250)
+(18 rows)
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
a | b
diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql
index c387d64db3a..dcd6edbad28 100644
--- a/src/test/regress/sql/partition_aggregate.sql
+++ b/src/test/regress/sql/partition_aggregate.sql
@@ -149,16 +149,18 @@ EXPLAIN (COSTS OFF)
SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST;
SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST;
--- LEFT JOIN, with dummy relation on right side,
+-- LEFT JOIN, with dummy relation on right side, ideally
-- should produce full partitionwise aggregation plan as GROUP BY is on
--- non-nullable columns
+-- non-nullable columns.
+-- But right now we are unable to do partitionwise join in this case.
EXPLAIN (COSTS OFF)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
--- FULL JOIN, with dummy relations on both sides,
+-- FULL JOIN, with dummy relations on both sides, ideally
-- should produce partial partitionwise aggregation plan as GROUP BY is on
--- nullable columns
+-- nullable columns.
+-- But right now we are unable to do partitionwise join in this case.
EXPLAIN (COSTS OFF)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index a74117111b3..a59ecfd10f9 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -54,10 +54,12 @@ 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 < 450 AND t2.b > 250 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.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
+-- Currently we can't do partitioned join if nullable-side partitions are pruned
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
+-- Currently we can't do partitioned join if nullable-side partitions are pruned
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;
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;
@@ -156,6 +158,7 @@ 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;
-- MergeAppend on nullable column
+-- This should generate a partitionwise join, but currently fails to
EXPLAIN (COSTS OFF)
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-20 16:34:30 +0000