(2018/08/03 22:28), Etsuro Fujita wrote:
(2018/08/03 22:18), Etsuro Fujita wrote:
Here is a patch for refusing to generate PWJ paths when WRVs are
involved:

* We no longer need to handle WRVs, so I've simplified
build_joinrel_tlist() and setrefs.c to what they were before
partition-wise join went in, as in the previous patch.

* attr_needed data for each child is used for building child-joins'
tlists, but I think we can build those by applying
adjust_appendrel_attrs to the parent's tlists, without attr_needed. So,
I've also removed that as in the previous patch.

One thing to add: as for the latter, we don't need the changes to
placeholder.c either, so I've also simplified that to what they were
before partition-wise join went in.

*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 3960,3965 **** create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
--- 3960,3967 ----
     */
    if (extra->patype != PARTITIONWISE_AGGREGATE_NONE &&
        input_rel->part_scheme && input_rel->part_rels &&
+       (input_rel->reloptkind == RELOPT_BASEREL ||
+        input_rel->consider_partitionwise_join) &&
        !IS_DUMMY_REL(input_rel))
    {
        /*
***************
*** 6913,6919 **** apply_scanjoin_target_to_paths(PlannerInfo *root,
* projection-capable, that might save a separate Result node, and it also
     * is important for partitionwise aggregate.
     */
!   if (rel->part_scheme && rel->part_rels)
    {
        int         partition_idx;
        List       *live_children = NIL;
--- 6915,6923 ----
* projection-capable, that might save a separate Result node, and it also
     * is important for partitionwise aggregate.
     */
!   if (rel->part_scheme && rel->part_rels &&
!       (rel->reloptkind == RELOPT_BASEREL ||
!        rel->consider_partitionwise_join))
    {
        int         partition_idx;
        List       *live_children = NIL;

In the above I used the test whether the relation's reloptkind is RELOPT_BASEREL or not, but I noticed that I had overlooked the case of a multi-level partitioned table. So I fixed that and added regression test cases for that. I also revised comments a bit. Attached is an updated version of the patch.

Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 8337,8344 **** ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
  ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
  INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250)
  	SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
  CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
  	SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
  ANALYZE fprt2;
--- 8337,8345 ----
  ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
  INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int)
  	SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
+ ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250);
  CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
  	SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
  ANALYZE fprt2;
***************
*** 8389,8416 **** SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10)
   8 |   | 
  (5 rows)
  
! -- with whole-row reference
  EXPLAIN (COSTS OFF)
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
!                                    QUERY PLAN                                    
! ---------------------------------------------------------------------------------
   Sort
     Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2)
!    ->  Append
!          ->  Foreign Scan
!                Relations: (public.ftprt1_p1 t1) INNER JOIN (public.ftprt2_p1 t2)
!          ->  Foreign Scan
!                Relations: (public.ftprt1_p2 t1) INNER JOIN (public.ftprt2_p2 t2)
! (7 rows)
  
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
!        t1       |       t2       
  ----------------+----------------
   (0,0,0000)     | (0,0,0000)
   (150,150,0003) | (150,150,0003)
   (250,250,0005) | (250,250,0005)
   (400,400,0008) | (400,400,0008)
! (4 rows)
  
  -- join with lateral reference
  EXPLAIN (COSTS OFF)
--- 8390,8431 ----
   8 |   | 
  (5 rows)
  
! -- with whole-row reference; partitionwise join does not apply
  EXPLAIN (COSTS OFF)
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
!                        QUERY PLAN                       
! --------------------------------------------------------
   Sort
     Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2)
!    ->  Hash Full Join
!          Hash Cond: (t1.a = t2.b)
!          ->  Append
!                ->  Foreign Scan on ftprt1_p1 t1
!                ->  Foreign Scan on ftprt1_p2 t1_1
!          ->  Hash
!                ->  Append
!                      ->  Foreign Scan on ftprt2_p1 t2
!                      ->  Foreign Scan on ftprt2_p2 t2_1
! (11 rows)
  
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
!        wr       |       wr       
  ----------------+----------------
   (0,0,0000)     | (0,0,0000)
+  (50,50,0001)   | 
+  (100,100,0002) | 
   (150,150,0003) | (150,150,0003)
+  (200,200,0004) | 
   (250,250,0005) | (250,250,0005)
+  (300,300,0006) | 
+  (350,350,0007) | 
   (400,400,0008) | (400,400,0008)
!  (450,450,0009) | 
!                 | (75,75,0001)
!                 | (225,225,0004)
!                 | (325,325,0006)
!                 | (475,475,0009)
! (14 rows)
  
  -- join with lateral reference
  EXPLAIN (COSTS OFF)
***************
*** 8474,8479 **** SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE
--- 8489,8522 ----
       |        | 475 | t2_phv
  (14 rows)
  
+ -- test FOR UPDATE; partitionwise join does not apply
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+                           QUERY PLAN                          
+ --------------------------------------------------------------
+  LockRows
+    ->  Sort
+          Sort Key: t1.a
+          ->  Hash Join
+                Hash Cond: (t2.b = t1.a)
+                ->  Append
+                      ->  Foreign Scan on ftprt2_p1 t2
+                      ->  Foreign Scan on ftprt2_p2 t2_1
+                ->  Hash
+                      ->  Append
+                            ->  Foreign Scan on ftprt1_p1 t1
+                            ->  Foreign Scan on ftprt1_p2 t1_1
+ (12 rows)
+ 
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+   a  |  b  
+ -----+-----
+    0 |   0
+  150 | 150
+  250 | 250
+  400 | 400
+ (4 rows)
+ 
  RESET enable_partitionwise_join;
  -- ===================================================================
  -- test partitionwise aggregates
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 2263,2270 **** ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
  ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
  INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250)
  	SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
  CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
  	SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
  ANALYZE fprt2;
--- 2263,2271 ----
  ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
  INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
  INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int)
  	SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
+ ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250);
  CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
  	SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
  ANALYZE fprt2;
***************
*** 2281,2290 **** EXPLAIN (COSTS OFF)
  SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
  SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
  
! -- with whole-row reference
  EXPLAIN (COSTS OFF)
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
  
  -- join with lateral reference
  EXPLAIN (COSTS OFF)
--- 2282,2291 ----
  SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
  SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
  
! -- with whole-row reference; partitionwise join does not apply
  EXPLAIN (COSTS OFF)
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
  
  -- join with lateral reference
  EXPLAIN (COSTS OFF)
***************
*** 2296,2301 **** EXPLAIN (COSTS OFF)
--- 2297,2307 ----
  SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
  SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
  
+ -- test FOR UPDATE; partitionwise join does not apply
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+ 
  RESET enable_partitionwise_join;
  
  
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2359,2364 **** _outRelOptInfo(StringInfo str, const RelOptInfo *node)
--- 2359,2365 ----
  	WRITE_UINT_FIELD(baserestrict_min_security);
  	WRITE_NODE_FIELD(joininfo);
  	WRITE_BOOL_FIELD(has_eclass_joins);
+ 	WRITE_BOOL_FIELD(consider_partitionwise_join);
  	WRITE_BITMAPSET_FIELD(top_parent_relids);
  	WRITE_NODE_FIELD(partitioned_child_rels);
  }
*** a/src/backend/optimizer/path/allpaths.c
--- b/src/backend/optimizer/path/allpaths.c
***************
*** 910,915 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
--- 910,926 ----
  	}
  
  	/*
+ 	 * If this is a partitioned baserel, set the consider_partitionwise_join
+ 	 * flag; currently, we only consider partitionwise joins with the baserel
+ 	 * if its targetlist doesn't contain a whole-row Var.
+ 	 */
+ 	if (enable_partitionwise_join &&
+ 		rel->reloptkind == RELOPT_BASEREL &&
+ 		rte->relkind == RELKIND_PARTITIONED_TABLE &&
+ 		rel->attr_needed[InvalidAttrNumber - rel->min_attr] == NULL)
+ 		rel->consider_partitionwise_join = true;
+ 
+ 	/*
  	 * Initialize to compute size estimates for whole append relation.
  	 *
  	 * We handle width estimates by weighting the widths of different child
***************
*** 956,1009 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
  		childrel = find_base_rel(root, childRTindex);
  		Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
  
- 		if (rel->part_scheme)
- 		{
- 			AttrNumber	attno;
- 
- 			/*
- 			 * We need attr_needed data for building targetlist of a join
- 			 * relation representing join between matching partitions for
- 			 * partitionwise join. A given attribute of a child will be needed
- 			 * in the same highest joinrel where the corresponding attribute
- 			 * of parent is needed. Hence it suffices to use the same Relids
- 			 * set for parent and child.
- 			 */
- 			for (attno = rel->min_attr; attno <= rel->max_attr; attno++)
- 			{
- 				int			index = attno - rel->min_attr;
- 				Relids		attr_needed = rel->attr_needed[index];
- 
- 				/* System attributes do not need translation. */
- 				if (attno <= 0)
- 				{
- 					Assert(rel->min_attr == childrel->min_attr);
- 					childrel->attr_needed[index] = attr_needed;
- 				}
- 				else
- 				{
- 					Var		   *var = list_nth_node(Var,
- 													appinfo->translated_vars,
- 													attno - 1);
- 					int			child_index;
- 
- 					/*
- 					 * Ignore any column dropped from the parent.
- 					 * Corresponding Var won't have any translation. It won't
- 					 * have attr_needed information, since it can not be
- 					 * referenced in the query.
- 					 */
- 					if (var == NULL)
- 					{
- 						Assert(attr_needed == NULL);
- 						continue;
- 					}
- 
- 					child_index = var->varattno - childrel->min_attr;
- 					childrel->attr_needed[child_index] = attr_needed;
- 				}
- 			}
- 		}
- 
  		/*
  		 * Copy/Modify targetlist. Even if this child is deemed empty, we need
  		 * its targetlist in case it falls on nullable side in a child-join
--- 967,972 ----
***************
*** 1181,1186 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
--- 1144,1165 ----
  								   1, &appinfo);
  
  		/*
+ 		 * Note: we could compute appropriate attr_needed data for the child's
+ 		 * variables, by transforming the parent's attr_needed through the
+ 		 * translated_vars mapping.  However, currently there's no need
+ 		 * because attr_needed is only examined for base relations not
+ 		 * otherrels.  So we just leave the child's attr_needed empty.
+ 		 */
+ 
+ 		/*
+ 		 * If we consider partitionwise joins with the parent rel, do the same
+ 		 * for partitioned child rels.
+ 		 */
+ 		if (rel->consider_partitionwise_join &&
+ 			childRTE->relkind == RELKIND_PARTITIONED_TABLE)
+ 			childrel->consider_partitionwise_join = true;
+ 
+ 		/*
  		 * If parallelism is allowable for this query in general, see whether
  		 * it's allowable for this childrel in particular.  But if we've
  		 * already decided the appendrel is not parallel-safe as a whole,
***************
*** 3539,3546 **** generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
  	if (!IS_JOIN_REL(rel))
  		return;
  
! 	/* 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. */
--- 3518,3528 ----
  	if (!IS_JOIN_REL(rel))
  		return;
  
! 	/*
! 	 * We've nothing to do if the relation is not partitioned, or
! 	 * partitionwise joins are not allowed for the join relation.
! 	 */
! 	if (!IS_PARTITIONED_REL(rel) || !rel->consider_partitionwise_join)
  		return;
  
  	/* Guard against stack overflow due to overly deep partition hierarchy. */
*** a/src/backend/optimizer/path/joinrels.c
--- b/src/backend/optimizer/path/joinrels.c
***************
*** 1318,1325 **** try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
  	/* Guard against stack overflow due to overly deep partition hierarchy. */
  	check_stack_depth();
  
! 	/* Nothing to do, if the join relation is not partitioned. */
! 	if (!IS_PARTITIONED_REL(joinrel))
  		return;
  
  	/*
--- 1318,1328 ----
  	/* Guard against stack overflow due to overly deep partition hierarchy. */
  	check_stack_depth();
  
! 	/*
! 	 * Nothing to do, if the join relation is not partitioned, or
! 	 * partitionwise joins are not allowed for the join relation.
! 	 */
! 	if (!IS_PARTITIONED_REL(joinrel) || !joinrel->consider_partitionwise_join)
  		return;
  
  	/*
***************
*** 1351,1356 **** try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
--- 1354,1365 ----
  								  joinrel->part_scheme->parttypbyval,
  								  joinrel->boundinfo, rel2->boundinfo));
  
+ 	/*
+ 	 * Partitionwise joins should have been allowed for the joining relations.
+ 	 */
+ 	Assert(rel1->consider_partitionwise_join &&
+ 		   rel2->consider_partitionwise_join);
+ 
  	nparts = joinrel->nparts;
  
  	/*
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 3955,3965 **** create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
  	 * If this is the topmost grouping relation or if the parent relation is
  	 * doing some form of partitionwise aggregation, then we may be able to do
  	 * it at this level also.  However, if the input relation is not
! 	 * partitioned, partitionwise aggregate is impossible, and if it is dummy,
  	 * partitionwise aggregate is pointless.
  	 */
  	if (extra->patype != PARTITIONWISE_AGGREGATE_NONE &&
  		input_rel->part_scheme && input_rel->part_rels &&
  		!IS_DUMMY_REL(input_rel))
  	{
  		/*
--- 3955,3967 ----
  	 * If this is the topmost grouping relation or if the parent relation is
  	 * doing some form of partitionwise aggregation, then we may be able to do
  	 * it at this level also.  However, if the input relation is not
! 	 * partitioned or if we are not allowed to generate Append paths for it,
! 	 * partitionwise aggregate is impossible, and if it is dummy,
  	 * partitionwise aggregate is pointless.
  	 */
  	if (extra->patype != PARTITIONWISE_AGGREGATE_NONE &&
  		input_rel->part_scheme && input_rel->part_rels &&
+ 		(IS_SIMPLE_REL(input_rel) || input_rel->consider_partitionwise_join) &&
  		!IS_DUMMY_REL(input_rel))
  	{
  		/*
***************
*** 6908,6919 **** apply_scanjoin_target_to_paths(PlannerInfo *root,
  							  scanjoin_targets_contain_srfs);
  
  	/*
! 	 * If the relation is partitioned, recursively apply the same changes to
! 	 * all partitions and generate new Append paths.  Since Append is not
! 	 * projection-capable, that might save a separate Result node, and it also
! 	 * is important for partitionwise aggregate.
  	 */
! 	if (rel->part_scheme && rel->part_rels)
  	{
  		int			partition_idx;
  		List	   *live_children = NIL;
--- 6910,6923 ----
  							  scanjoin_targets_contain_srfs);
  
  	/*
! 	 * If the relation is partitioned and we are allowed to generate Append
! 	 * paths for it, recursively apply the same changes to all partitions and
! 	 * generate new Append paths.  Since Append is not projection-capable,
! 	 * that might save a separate Result node, and it also is important for
! 	 * partitionwise aggregate.
  	 */
! 	if (rel->part_scheme && rel->part_rels &&
! 		(IS_SIMPLE_REL(rel) || rel->consider_partitionwise_join))
  	{
  		int			partition_idx;
  		List	   *live_children = NIL;
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
***************
*** 41,49 **** typedef struct
  	int			num_vars;		/* number of plain Var tlist entries */
  	bool		has_ph_vars;	/* are there PlaceHolderVar entries? */
  	bool		has_non_vars;	/* are there other entries? */
- 	bool		has_conv_whole_rows;	/* are there ConvertRowtypeExpr
- 										 * entries encapsulating a whole-row
- 										 * Var? */
  	tlist_vinfo vars[FLEXIBLE_ARRAY_MEMBER];	/* has num_vars entries */
  } indexed_tlist;
  
--- 41,46 ----
***************
*** 143,149 **** static List *set_returning_clause_references(PlannerInfo *root,
  								int rtoffset);
  static bool extract_query_dependencies_walker(Node *node,
  								  PlannerInfo *context);
- static bool is_converted_whole_row_reference(Node *node);
  
  /*****************************************************************************
   *
--- 140,145 ----
***************
*** 1997,2003 **** build_tlist_index(List *tlist)
  	itlist->tlist = tlist;
  	itlist->has_ph_vars = false;
  	itlist->has_non_vars = false;
- 	itlist->has_conv_whole_rows = false;
  
  	/* Find the Vars and fill in the index array */
  	vinfo = itlist->vars;
--- 1993,1998 ----
***************
*** 2016,2023 **** build_tlist_index(List *tlist)
  		}
  		else if (tle->expr && IsA(tle->expr, PlaceHolderVar))
  			itlist->has_ph_vars = true;
- 		else if (is_converted_whole_row_reference((Node *) tle->expr))
- 			itlist->has_conv_whole_rows = true;
  		else
  			itlist->has_non_vars = true;
  	}
--- 2011,2016 ----
***************
*** 2033,2042 **** build_tlist_index(List *tlist)
   * This is like build_tlist_index, but we only index tlist entries that
   * are Vars belonging to some rel other than the one specified.  We will set
   * has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars
!  * (so nothing other than Vars and PlaceHolderVars can be matched). In case of
!  * DML, where this function will be used, returning lists from child relations
!  * will be appended similar to a simple append relation. That does not require
!  * fixing ConvertRowtypeExpr references. So, those are not considered here.
   */
  static indexed_tlist *
  build_tlist_index_other_vars(List *tlist, Index ignore_rel)
--- 2026,2032 ----
   * This is like build_tlist_index, but we only index tlist entries that
   * are Vars belonging to some rel other than the one specified.  We will set
   * has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars
!  * (so nothing other than Vars and PlaceHolderVars can be matched).
   */
  static indexed_tlist *
  build_tlist_index_other_vars(List *tlist, Index ignore_rel)
***************
*** 2053,2059 **** build_tlist_index_other_vars(List *tlist, Index ignore_rel)
  	itlist->tlist = tlist;
  	itlist->has_ph_vars = false;
  	itlist->has_non_vars = false;
- 	itlist->has_conv_whole_rows = false;
  
  	/* Find the desired Vars and fill in the index array */
  	vinfo = itlist->vars;
--- 2043,2048 ----
***************
*** 2256,2262 **** static Node *
  fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
  {
  	Var		   *newvar;
- 	bool		converted_whole_row;
  
  	if (node == NULL)
  		return NULL;
--- 2245,2250 ----
***************
*** 2326,2337 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
  	}
  	if (IsA(node, Param))
  		return fix_param_node(context->root, (Param *) node);
- 
  	/* Try matching more complex expressions too, if tlists have any */
! 	converted_whole_row = is_converted_whole_row_reference(node);
! 	if (context->outer_itlist &&
! 		(context->outer_itlist->has_non_vars ||
! 		 (context->outer_itlist->has_conv_whole_rows && converted_whole_row)))
  	{
  		newvar = search_indexed_tlist_for_non_var((Expr *) node,
  												  context->outer_itlist,
--- 2314,2321 ----
  	}
  	if (IsA(node, Param))
  		return fix_param_node(context->root, (Param *) node);
  	/* Try matching more complex expressions too, if tlists have any */
! 	if (context->outer_itlist && context->outer_itlist->has_non_vars)
  	{
  		newvar = search_indexed_tlist_for_non_var((Expr *) node,
  												  context->outer_itlist,
***************
*** 2339,2347 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
  		if (newvar)
  			return (Node *) newvar;
  	}
! 	if (context->inner_itlist &&
! 		(context->inner_itlist->has_non_vars ||
! 		 (context->inner_itlist->has_conv_whole_rows && converted_whole_row)))
  	{
  		newvar = search_indexed_tlist_for_non_var((Expr *) node,
  												  context->inner_itlist,
--- 2323,2329 ----
  		if (newvar)
  			return (Node *) newvar;
  	}
! 	if (context->inner_itlist && context->inner_itlist->has_non_vars)
  	{
  		newvar = search_indexed_tlist_for_non_var((Expr *) node,
  												  context->inner_itlist,
***************
*** 2461,2469 **** fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
  		/* If no match, just fall through to process it normally */
  	}
  	/* Try matching more complex expressions too, if tlist has any */
! 	if (context->subplan_itlist->has_non_vars ||
! 		(context->subplan_itlist->has_conv_whole_rows &&
! 		 is_converted_whole_row_reference(node)))
  	{
  		newvar = search_indexed_tlist_for_non_var((Expr *) node,
  												  context->subplan_itlist,
--- 2443,2449 ----
  		/* If no match, just fall through to process it normally */
  	}
  	/* Try matching more complex expressions too, if tlist has any */
! 	if (context->subplan_itlist->has_non_vars)
  	{
  		newvar = search_indexed_tlist_for_non_var((Expr *) node,
  												  context->subplan_itlist,
***************
*** 2670,2702 **** extract_query_dependencies_walker(Node *node, PlannerInfo *context)
  	return expression_tree_walker(node, extract_query_dependencies_walker,
  								  (void *) context);
  }
- 
- /*
-  * is_converted_whole_row_reference
-  *		If the given node is a ConvertRowtypeExpr encapsulating a whole-row
-  *		reference as implicit cast, return true. Otherwise return false.
-  */
- static bool
- is_converted_whole_row_reference(Node *node)
- {
- 	ConvertRowtypeExpr *convexpr;
- 
- 	if (!node || !IsA(node, ConvertRowtypeExpr))
- 		return false;
- 
- 	/* Traverse nested ConvertRowtypeExpr's. */
- 	convexpr = castNode(ConvertRowtypeExpr, node);
- 	while (convexpr->convertformat == COERCE_IMPLICIT_CAST &&
- 		   IsA(convexpr->arg, ConvertRowtypeExpr))
- 		convexpr = castNode(ConvertRowtypeExpr, convexpr->arg);
- 
- 	if (IsA(convexpr->arg, Var))
- 	{
- 		Var		   *var = castNode(Var, convexpr->arg);
- 
- 		if (var->varattno == 0)
- 			return true;
- 	}
- 
- 	return false;
- }
--- 2650,2652 ----
*** a/src/backend/optimizer/util/placeholder.c
--- b/src/backend/optimizer/util/placeholder.c
***************
*** 20,26 ****
  #include "optimizer/pathnode.h"
  #include "optimizer/placeholder.h"
  #include "optimizer/planmain.h"
- #include "optimizer/prep.h"
  #include "optimizer/var.h"
  #include "utils/lsyscache.h"
  
--- 20,25 ----
***************
*** 415,424 **** add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel,
  	Relids		relids = joinrel->relids;
  	ListCell   *lc;
  
- 	/* This function is called only on the parent relations. */
- 	Assert(!IS_OTHER_REL(joinrel) && !IS_OTHER_REL(outer_rel) &&
- 		   !IS_OTHER_REL(inner_rel));
- 
  	foreach(lc, root->placeholder_list)
  	{
  		PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc);
--- 414,419 ----
***************
*** 464,519 **** add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel,
  		}
  	}
  }
- 
- /*
-  * add_placeholders_to_child_joinrel
-  *		Translate the PHVs in parent's targetlist and add them to the child's
-  *		targetlist. Also adjust the cost
-  */
- void
- add_placeholders_to_child_joinrel(PlannerInfo *root, RelOptInfo *childrel,
- 								  RelOptInfo *parentrel)
- {
- 	ListCell   *lc;
- 	AppendRelInfo **appinfos;
- 	int			nappinfos;
- 
- 	Assert(IS_JOIN_REL(childrel) && IS_JOIN_REL(parentrel));
- 	Assert(IS_OTHER_REL(childrel));
- 
- 	/* Nothing to do if no PHVs. */
- 	if (root->placeholder_list == NIL)
- 		return;
- 
- 	appinfos = find_appinfos_by_relids(root, childrel->relids, &nappinfos);
- 	foreach(lc, parentrel->reltarget->exprs)
- 	{
- 		PlaceHolderVar *phv = lfirst(lc);
- 
- 		if (IsA(phv, PlaceHolderVar))
- 		{
- 			/*
- 			 * In case the placeholder Var refers to any of the parent
- 			 * relations, translate it to refer to the corresponding child.
- 			 */
- 			if (bms_overlap(phv->phrels, parentrel->relids) &&
- 				childrel->reloptkind == RELOPT_OTHER_JOINREL)
- 			{
- 				phv = (PlaceHolderVar *) adjust_appendrel_attrs(root,
- 																(Node *) phv,
- 																nappinfos,
- 																appinfos);
- 			}
- 
- 			childrel->reltarget->exprs = lappend(childrel->reltarget->exprs,
- 												 phv);
- 		}
- 	}
- 
- 	/* Adjust the cost and width of child targetlist. */
- 	childrel->reltarget->cost.startup = parentrel->reltarget->cost.startup;
- 	childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple;
- 	childrel->reltarget->width = parentrel->reltarget->width;
- 
- 	pfree(appinfos);
- }
--- 459,461 ----
*** a/src/backend/optimizer/util/relnode.c
--- b/src/backend/optimizer/util/relnode.c
***************
*** 57,62 **** static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
--- 57,67 ----
  static void build_joinrel_partition_info(RelOptInfo *joinrel,
  							 RelOptInfo *outer_rel, RelOptInfo *inner_rel,
  							 List *restrictlist, JoinType jointype);
+ static void build_child_join_reltarget(PlannerInfo *root,
+ 						   RelOptInfo *parentrel,
+ 						   RelOptInfo *childrel,
+ 						   int nappinfos,
+ 						   AppendRelInfo **appinfos);
  
  
  /*
***************
*** 188,193 **** build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
--- 193,199 ----
  	rel->baserestrict_min_security = UINT_MAX;
  	rel->joininfo = NIL;
  	rel->has_eclass_joins = false;
+ 	rel->consider_partitionwise_join = false; /* might get changed later */
  	rel->part_scheme = NULL;
  	rel->nparts = 0;
  	rel->boundinfo = NULL;
***************
*** 602,607 **** build_join_rel(PlannerInfo *root,
--- 608,614 ----
  	joinrel->baserestrict_min_security = UINT_MAX;
  	joinrel->joininfo = NIL;
  	joinrel->has_eclass_joins = false;
+ 	joinrel->consider_partitionwise_join = false; /* might get changed later */
  	joinrel->top_parent_relids = NULL;
  	joinrel->part_scheme = NULL;
  	joinrel->nparts = 0;
***************
*** 661,666 **** build_join_rel(PlannerInfo *root,
--- 668,682 ----
  								 sjinfo->jointype);
  
  	/*
+ 	 * Set the consider_partitionwise_join flag if this joinrel is partitioned
+ 	 * and partitionwise joins are allowed for the outer and inner rels.
+ 	 */
+ 	if (IS_PARTITIONED_REL(joinrel) &&
+ 		outer_rel->consider_partitionwise_join &&
+ 		inner_rel->consider_partitionwise_join)
+ 		joinrel->consider_partitionwise_join = true;
+ 
+ 	/*
  	 * Set estimates of the joinrel's size.
  	 */
  	set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
***************
*** 732,737 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
--- 748,756 ----
  	/* Only joins between "other" relations land here. */
  	Assert(IS_OTHER_REL(outer_rel) && IS_OTHER_REL(inner_rel));
  
+ 	/* Partitionwise joins should have been allowed for the parent joinrel. */
+ 	Assert(parent_joinrel->consider_partitionwise_join);
+ 
  	joinrel->reloptkind = RELOPT_OTHER_JOINREL;
  	joinrel->relids = bms_union(outer_rel->relids, inner_rel->relids);
  	joinrel->rows = 0;
***************
*** 773,778 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
--- 792,798 ----
  	joinrel->baserestrictcost.per_tuple = 0;
  	joinrel->joininfo = NIL;
  	joinrel->has_eclass_joins = false;
+ 	joinrel->consider_partitionwise_join = false; /* might get changed later */
  	joinrel->top_parent_relids = NULL;
  	joinrel->part_scheme = NULL;
  	joinrel->nparts = 0;
***************
*** 789,802 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
  	/* Compute information relevant to foreign relations. */
  	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
  
! 	/* Build targetlist */
! 	build_joinrel_tlist(root, joinrel, outer_rel);
! 	build_joinrel_tlist(root, joinrel, inner_rel);
! 	/* Add placeholder variables. */
! 	add_placeholders_to_child_joinrel(root, joinrel, parent_joinrel);
  
  	/* Construct joininfo list. */
- 	appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos);
  	joinrel->joininfo = (List *) adjust_appendrel_attrs(root,
  														(Node *) parent_joinrel->joininfo,
  														nappinfos,
--- 809,821 ----
  	/* Compute information relevant to foreign relations. */
  	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
  
! 	appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos);
! 
! 	/* Set up reltarget struct */
! 	build_child_join_reltarget(root, parent_joinrel, joinrel,
! 							   nappinfos, appinfos);
  
  	/* Construct joininfo list. */
  	joinrel->joininfo = (List *) adjust_appendrel_attrs(root,
  														(Node *) parent_joinrel->joininfo,
  														nappinfos,
***************
*** 823,832 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
  	build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
  								 jointype);
  
  	/* Child joinrel is parallel safe if parent is parallel safe. */
  	joinrel->consider_parallel = parent_joinrel->consider_parallel;
  
- 
  	/* Set estimates of the child-joinrel's size. */
  	set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
  							   sjinfo, restrictlist);
--- 842,854 ----
  	build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
  								 jointype);
  
+ 	/* If so, consider partitionwise joins for that join. */
+ 	if (IS_PARTITIONED_REL(joinrel))
+ 		joinrel->consider_partitionwise_join = true;
+ 
  	/* Child joinrel is parallel safe if parent is parallel safe. */
  	joinrel->consider_parallel = parent_joinrel->consider_parallel;
  
  	/* Set estimates of the child-joinrel's size. */
  	set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
  							   sjinfo, restrictlist);
***************
*** 895,909 **** static void
  build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
  					RelOptInfo *input_rel)
  {
! 	Relids		relids;
  	ListCell   *vars;
  
- 	/* attrs_needed refers to parent relids and not those of a child. */
- 	if (joinrel->top_parent_relids)
- 		relids = joinrel->top_parent_relids;
- 	else
- 		relids = joinrel->relids;
- 
  	foreach(vars, input_rel->reltarget->exprs)
  	{
  		Var		   *var = (Var *) lfirst(vars);
--- 917,925 ----
  build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
  					RelOptInfo *input_rel)
  {
! 	Relids		relids = joinrel->relids;
  	ListCell   *vars;
  
  	foreach(vars, input_rel->reltarget->exprs)
  	{
  		Var		   *var = (Var *) lfirst(vars);
***************
*** 919,972 **** build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
  
  		/*
  		 * Otherwise, anything in a baserel or joinrel targetlist ought to be
! 		 * a Var. Children of a partitioned table may have ConvertRowtypeExpr
! 		 * translating whole-row Var of a child to that of the parent.
! 		 * Children of an inherited table or subquery child rels can not
! 		 * directly participate in a join, so other kinds of nodes here.
  		 */
! 		if (IsA(var, Var))
! 		{
! 			baserel = find_base_rel(root, var->varno);
! 			ndx = var->varattno - baserel->min_attr;
! 		}
! 		else if (IsA(var, ConvertRowtypeExpr))
! 		{
! 			ConvertRowtypeExpr *child_expr = (ConvertRowtypeExpr *) var;
! 			Var		   *childvar = (Var *) child_expr->arg;
! 
! 			/*
! 			 * Child's whole-row references are converted to look like those
! 			 * of parent using ConvertRowtypeExpr. There can be as many
! 			 * ConvertRowtypeExpr decorations as the depth of partition tree.
! 			 * The argument to the deepest ConvertRowtypeExpr is expected to
! 			 * be a whole-row reference of the child.
! 			 */
! 			while (IsA(childvar, ConvertRowtypeExpr))
! 			{
! 				child_expr = (ConvertRowtypeExpr *) childvar;
! 				childvar = (Var *) child_expr->arg;
! 			}
! 			Assert(IsA(childvar, Var) &&childvar->varattno == 0);
! 
! 			baserel = find_base_rel(root, childvar->varno);
! 			ndx = 0 - baserel->min_attr;
! 		}
! 		else
  			elog(ERROR, "unexpected node type in rel targetlist: %d",
  				 (int) nodeTag(var));
  
  
! 		/* Is the target expression still needed above this joinrel? */
  		if (bms_nonempty_difference(baserel->attr_needed[ndx], relids))
  		{
  			/* Yup, add it to the output */
  			joinrel->reltarget->exprs = lappend(joinrel->reltarget->exprs, var);
! 
! 			/*
! 			 * Vars have cost zero, so no need to adjust reltarget->cost. Even
! 			 * if it's a ConvertRowtypeExpr, it will be computed only for the
! 			 * base relation, costing nothing for a join.
! 			 */
  			joinrel->reltarget->width += baserel->attr_widths[ndx];
  		}
  	}
--- 935,957 ----
  
  		/*
  		 * Otherwise, anything in a baserel or joinrel targetlist ought to be
! 		 * a Var.  (More general cases can only appear in appendrel child
! 		 * rels, which will never be seen here.)
  		 */
! 		if (!IsA(var, Var))
  			elog(ERROR, "unexpected node type in rel targetlist: %d",
  				 (int) nodeTag(var));
  
+ 		/* Get the Var's original base rel */
+ 		baserel = find_base_rel(root, var->varno);
  
! 		/* Is it still needed above this joinrel? */
! 		ndx = var->varattno - baserel->min_attr;
  		if (bms_nonempty_difference(baserel->attr_needed[ndx], relids))
  		{
  			/* Yup, add it to the output */
  			joinrel->reltarget->exprs = lappend(joinrel->reltarget->exprs, var);
! 			/* Vars have cost zero, so no need to adjust reltarget->cost */
  			joinrel->reltarget->width += baserel->attr_widths[ndx];
  		}
  	}
***************
*** 1768,1770 **** build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
--- 1753,1778 ----
  		joinrel->nullable_partexprs[cnt] = nullable_partexpr;
  	}
  }
+ 
+ /*
+  * build_child_join_reltarget
+  *	  Set up a child-join relation's reltarget from a parent-join relation.
+  */
+ static void
+ build_child_join_reltarget(PlannerInfo *root,
+ 						   RelOptInfo *parentrel,
+ 						   RelOptInfo *childrel,
+ 						   int nappinfos,
+ 						   AppendRelInfo **appinfos)
+ {
+ 	/* Build the targetlist */
+ 	childrel->reltarget->exprs = (List *)
+ 		adjust_appendrel_attrs(root,
+ 							   (Node *) parentrel->reltarget->exprs,
+ 							   nappinfos, appinfos);
+ 
+ 	/* Set the cost and width fields */
+ 	childrel->reltarget->cost.startup = parentrel->reltarget->cost.startup;
+ 	childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple;
+ 	childrel->reltarget->width = parentrel->reltarget->width;
+ }
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 687,694 **** typedef struct RelOptInfo
  								 * involving this rel */
  	bool		has_eclass_joins;	/* T means joininfo is incomplete */
  
! 	/* used by "other" relations */
! 	Relids		top_parent_relids;	/* Relids of topmost parents */
  
  	/* used for partitioned relations */
  	PartitionScheme part_scheme;	/* Partitioning scheme. */
--- 687,698 ----
  								 * involving this rel */
  	bool		has_eclass_joins;	/* T means joininfo is incomplete */
  
! 	/* used by partitionwise joins: */
! 	bool		consider_partitionwise_join;	/* consider partitionwise
! 												 * join paths? (if
! 												 * partitioned rel) */
! 	Relids		top_parent_relids;	/* Relids of topmost parents (if "other"
! 									 * rel) */
  
  	/* used for partitioned relations */
  	PartitionScheme part_scheme;	/* Partitioning scheme. */
*** a/src/test/regress/expected/partition_aggregate.out
--- b/src/test/regress/expected/partition_aggregate.out
***************
*** 449,454 **** SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2
--- 449,486 ----
   24 |  900 |   100
  (5 rows)
  
+ -- Check with whole-row reference; partitionwise aggregation does not apply
+ EXPLAIN (COSTS OFF)
+ SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
+                          QUERY PLAN                          
+ -------------------------------------------------------------
+  Sort
+    Sort Key: t1.x, (sum(t1.y)), (count(((t1.*)::pagg_tab1)))
+    ->  HashAggregate
+          Group Key: t1.x
+          ->  Hash Join
+                Hash Cond: (t1.x = t2.y)
+                ->  Append
+                      ->  Seq Scan on pagg_tab1_p1 t1
+                      ->  Seq Scan on pagg_tab1_p2 t1_1
+                      ->  Seq Scan on pagg_tab1_p3 t1_2
+                ->  Hash
+                      ->  Append
+                            ->  Seq Scan on pagg_tab2_p1 t2
+                            ->  Seq Scan on pagg_tab2_p2 t2_1
+                            ->  Seq Scan on pagg_tab2_p3 t2_2
+ (15 rows)
+ 
+ SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
+  x  | sum  | count 
+ ----+------+-------
+   0 |  500 |   100
+   6 | 1100 |   100
+  12 |  700 |   100
+  18 | 1300 |   100
+  24 |  900 |   100
+ (5 rows)
+ 
  -- GROUP BY having other matching key
  EXPLAIN (COSTS OFF)
  SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t2.y ORDER BY 1, 2, 3;
***************
*** 1062,1067 **** SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER B
--- 1094,1151 ----
   22 | 2000 |  1000
  (9 rows)
  
+ -- Check with whole-row reference
+ EXPLAIN (COSTS OFF)
+ SELECT a, sum(b), count(pagg_tab_ml) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
+                                             QUERY PLAN                                             
+ ---------------------------------------------------------------------------------------------------
+  Sort
+    Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(((pagg_tab_ml_p1.*)::pagg_tab_ml)))
+    ->  Append
+          ->  HashAggregate
+                Group Key: pagg_tab_ml_p1.a
+                Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric)
+                ->  Seq Scan on pagg_tab_ml_p1
+          ->  Finalize GroupAggregate
+                Group Key: pagg_tab_ml_p2_s1.a
+                Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric)
+                ->  Sort
+                      Sort Key: pagg_tab_ml_p2_s1.a
+                      ->  Append
+                            ->  Partial HashAggregate
+                                  Group Key: pagg_tab_ml_p2_s1.a
+                                  ->  Seq Scan on pagg_tab_ml_p2_s1
+                            ->  Partial HashAggregate
+                                  Group Key: pagg_tab_ml_p2_s2.a
+                                  ->  Seq Scan on pagg_tab_ml_p2_s2
+          ->  Finalize GroupAggregate
+                Group Key: pagg_tab_ml_p3_s1.a
+                Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric)
+                ->  Sort
+                      Sort Key: pagg_tab_ml_p3_s1.a
+                      ->  Append
+                            ->  Partial HashAggregate
+                                  Group Key: pagg_tab_ml_p3_s1.a
+                                  ->  Seq Scan on pagg_tab_ml_p3_s1
+                            ->  Partial HashAggregate
+                                  Group Key: pagg_tab_ml_p3_s2.a
+                                  ->  Seq Scan on pagg_tab_ml_p3_s2
+ (31 rows)
+ 
+ SELECT a, sum(b), count(pagg_tab_ml) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
+  a  | sum  | count 
+ ----+------+-------
+   0 |    0 |  1000
+   1 | 1000 |  1000
+   2 | 2000 |  1000
+  10 |    0 |  1000
+  11 | 1000 |  1000
+  12 | 2000 |  1000
+  20 |    0 |  1000
+  21 | 1000 |  1000
+  22 | 2000 |  1000
+ (9 rows)
+ 
  -- Partial aggregation at all levels as GROUP BY clause does not match with
  -- PARTITION KEY
  EXPLAIN (COSTS OFF)
*** a/src/test/regress/expected/partition_join.out
--- b/src/test/regress/expected/partition_join.out
***************
*** 62,94 **** SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
   450 | 0450 | 450 | 0450
  (4 rows)
  
! -- left outer join, with whole-row reference
  EXPLAIN (COSTS OFF)
  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;
                      QUERY PLAN                    
  --------------------------------------------------
   Sort
     Sort Key: t1.a, t2.b
!    ->  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_1.a)
-                ->  Seq Scan on prt2_p2 t2_1
-                ->  Hash
                       ->  Seq Scan on prt1_p2 t1_1
                             Filter: (b = 0)
-          ->  Hash Right 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, 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      
--- 62,89 ----
   450 | 0450 | 450 | 0450
  (4 rows)
  
! -- left outer join, with whole-row reference; partitionwise join does not apply
  EXPLAIN (COSTS OFF)
  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;
                      QUERY PLAN                    
  --------------------------------------------------
   Sort
     Sort Key: t1.a, t2.b
!    ->  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
!          ->  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)
  
  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      
***************
*** 1042,1047 **** SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
--- 1037,1076 ----
   400 |    
  (9 rows)
  
+ -- merge join when expression with whole-row reference needs to be sorted;
+ -- partitionwise join does not apply
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+                                        QUERY PLAN                                        
+ -----------------------------------------------------------------------------------------
+  Merge Join
+    Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text)))
+    ->  Sort
+          Sort Key: t1.a, ((((t1.*)::prt1))::text)
+          ->  Result
+                ->  Append
+                      ->  Seq Scan on prt1_p1 t1
+                      ->  Seq Scan on prt1_p2 t1_1
+                      ->  Seq Scan on prt1_p3 t1_2
+    ->  Sort
+          Sort Key: t2.b, ((((t2.*)::prt2))::text)
+          ->  Result
+                ->  Append
+                      ->  Seq Scan on prt2_p1 t2
+                      ->  Seq Scan on prt2_p2 t2_1
+                      ->  Seq Scan on prt2_p3 t2_2
+ (16 rows)
+ 
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+  a  | b  
+ ----+----
+   0 |  0
+   6 |  6
+  12 | 12
+  18 | 18
+  24 | 24
+ (5 rows)
+ 
  RESET enable_hashjoin;
  RESET enable_nestloop;
  --
***************
*** 1722,1727 **** SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2)
--- 1751,1792 ----
                 One-Time Filter: false
  (11 rows)
  
+ -- Check that apply_scanjoin_target_to_paths() works for case with
+ -- whole-row Var referencing the multi-level partitioned table
+ EXPLAIN (COSTS OFF)
+ SELECT t1.tableoid::regclass, t1, t1.a, t1.c FROM prt1_l t1 WHERE t1.b = 0 ORDER BY t1.a;
+                 QUERY PLAN                 
+ -------------------------------------------
+  Sort
+    Sort Key: t1.a
+    ->  Append
+          ->  Seq Scan on prt1_l_p1 t1
+                Filter: (b = 0)
+          ->  Seq Scan on prt1_l_p2_p1 t1_1
+                Filter: (b = 0)
+          ->  Seq Scan on prt1_l_p2_p2 t1_2
+                Filter: (b = 0)
+          ->  Seq Scan on prt1_l_p3_p1 t1_3
+                Filter: (b = 0)
+ (11 rows)
+ 
+ SELECT t1.tableoid::regclass, t1, t1.a, t1.c FROM prt1_l t1 WHERE t1.b = 0 ORDER BY t1.a;
+    tableoid   |      t1      |  a  |  c   
+ --------------+--------------+-----+------
+  prt1_l_p1    | (0,0,0000)   |   0 | 0000
+  prt1_l_p1    | (50,0,0002)  |  50 | 0002
+  prt1_l_p1    | (100,0,0000) | 100 | 0000
+  prt1_l_p1    | (150,0,0002) | 150 | 0002
+  prt1_l_p1    | (200,0,0000) | 200 | 0000
+  prt1_l_p2_p2 | (250,0,0002) | 250 | 0002
+  prt1_l_p2_p1 | (300,0,0000) | 300 | 0000
+  prt1_l_p2_p2 | (350,0,0002) | 350 | 0002
+  prt1_l_p2_p1 | (400,0,0000) | 400 | 0000
+  prt1_l_p2_p2 | (450,0,0002) | 450 | 0002
+  prt1_l_p3_p1 | (500,0,0000) | 500 | 0000
+  prt1_l_p3_p1 | (550,0,0002) | 550 | 0002
+ (12 rows)
+ 
  --
  -- negative testcases
  --
*** a/src/test/regress/sql/partition_aggregate.sql
--- b/src/test/regress/sql/partition_aggregate.sql
***************
*** 111,116 **** EXPLAIN (COSTS OFF)
--- 111,121 ----
  SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
  SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
  
+ -- Check with whole-row reference; partitionwise aggregation does not apply
+ EXPLAIN (COSTS OFF)
+ SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
+ SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
+ 
  -- GROUP BY having other matching key
  EXPLAIN (COSTS OFF)
  SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t2.y ORDER BY 1, 2, 3;
***************
*** 230,235 **** EXPLAIN (COSTS OFF)
--- 235,245 ----
  SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
  SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
  
+ -- Check with whole-row reference
+ EXPLAIN (COSTS OFF)
+ SELECT a, sum(b), count(pagg_tab_ml) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
+ SELECT a, sum(b), count(pagg_tab_ml) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3;
+ 
  -- Partial aggregation at all levels as GROUP BY clause does not match with
  -- PARTITION KEY
  EXPLAIN (COSTS OFF)
*** a/src/test/regress/sql/partition_join.sql
--- b/src/test/regress/sql/partition_join.sql
***************
*** 34,40 **** 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;
  
! -- left outer join, with whole-row reference
  EXPLAIN (COSTS OFF)
  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;
  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;
--- 34,40 ----
  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;
  
! -- left outer join, with whole-row reference; partitionwise join does not apply
  EXPLAIN (COSTS OFF)
  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;
  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;
***************
*** 160,165 **** EXPLAIN (COSTS OFF)
--- 160,171 ----
  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;
  
+ -- merge join when expression with whole-row reference needs to be sorted;
+ -- partitionwise join does not apply
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+ 
  RESET enable_hashjoin;
  RESET enable_nestloop;
  
***************
*** 340,345 **** SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
--- 346,357 ----
  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;
  
+ -- Check that apply_scanjoin_target_to_paths() works for case with
+ -- whole-row Var referencing the multi-level partitioned table
+ EXPLAIN (COSTS OFF)
+ SELECT t1.tableoid::regclass, t1, t1.a, t1.c FROM prt1_l t1 WHERE t1.b = 0 ORDER BY t1.a;
+ SELECT t1.tableoid::regclass, t1, t1.a, t1.c FROM prt1_l t1 WHERE t1.b = 0 ORDER BY t1.a;
+ 
  --
  -- negative testcases
  --

Reply via email to