identity_projection_v3_20130212.patch

*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 936,945 **** create_unique_plan(PlannerInfo *root, UniquePath *best_path)
  	if (newitems || best_path->umethod == UNIQUE_PATH_SORT)
  	{
  		/*
! 		 * If the top plan node can't do projections, we need to add a Result
! 		 * node to help it along.
  		 */
! 		if (!is_projection_capable_plan(subplan))
  			subplan = (Plan *) make_result(root, newtlist, NULL, subplan);
  		else
  			subplan->targetlist = newtlist;
--- 936,947 ----
  	if (newitems || best_path->umethod == UNIQUE_PATH_SORT)
  	{
  		/*
! 		 * If the top plan node can't do projections and target list of top
! 		 * plan node is not equivalent to target list expected from plan, we
! 		 * need to add a Result node to help it along.
  		 */
! 		if (!is_projection_capable_plan(subplan) &&
! 			!equivalent_tlists(newtlist, subplan->targetlist))
  			subplan = (Plan *) make_result(root, newtlist, NULL, subplan);
  		else
  			subplan->targetlist = newtlist;
***************
*** 3953,3959 **** prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
  				elog(ERROR, "could not find pathkey item to sort");
  
  			/*
! 			 * Do we need to insert a Result node?
  			 */
  			if (!adjust_tlist_in_place &&
  				!is_projection_capable_plan(lefttree))
--- 3955,3964 ----
  				elog(ERROR, "could not find pathkey item to sort");
  
  			/*
! 			 * Do we need to insert a Result node? Here we can use
! 			 * equivalent_tlists to avoid result node for cases where the
! 			 * projection is identity, but for that we need to make sure
! 			 * sortexpr is a Var.
  			 */
  			if (!adjust_tlist_in_place &&
  				!is_projection_capable_plan(lefttree))
***************
*** 4776,4778 **** is_projection_capable_plan(Plan *plan)
--- 4781,4827 ----
  	}
  	return true;
  }
+ 
+ 
+ /*
+  * equivalent_tlists
+  *	  returns whether two traget lists are equivalent
+  *
+  * We consider two target lists equivalent if both have
+  * only Var entries and resjunk of each target entry is same.
+  *
+  * This function is used to decide whether to create a result node.
+  * We need to ensure that each entry is Var as below node will not be
+  * projection capable, so it will not be able to compute expressions.
+  */
+ bool
+ equivalent_tlists(List *tlist1, List *tlist2)
+ {
+ 	ListCell   *lp,
+ 			   *lc;
+ 
+ 	if (list_length(tlist1) != list_length(tlist2))
+ 		return false;			/* tlists not same length */
+ 
+ 	forboth(lp, tlist1, lc, tlist2)
+ 	{
+ 		TargetEntry *tle1 = (TargetEntry *) lfirst(lp);
+ 		TargetEntry *tle2 = (TargetEntry *) lfirst(lc);
+ 
+ 		if (tle1->resjunk != tle1->resjunk)
+ 			return false;		/* tlist doesn't match junk status */
+ 
+ 		if (tle1->expr && IsA(tle1->expr, Var) &&
+ 			tle2->expr && IsA(tle2->expr, Var))
+ 		{
+ 			Var		   *var1 = (Var *) tle1->expr;
+ 			Var		   *var2 = (Var *) tle2->expr;
+ 
+ 			if (var1->varattno != var2->varattno)
+ 				return false;	/* different order */
+ 		}
+ 		else
+ 			return false;
+ 	}
+ 	return true;
+ }
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 1377,1386 **** grouping_planner(PlannerInfo *root, double tuple_fraction)
  			{
  				/*
  				 * If the top-level plan node is one that cannot do expression
! 				 * evaluation, we must insert a Result node to project the
! 				 * desired tlist.
  				 */
! 				if (!is_projection_capable_plan(result_plan))
  				{
  					result_plan = (Plan *) make_result(root,
  													   sub_tlist,
--- 1377,1388 ----
  			{
  				/*
  				 * If the top-level plan node is one that cannot do expression
! 				 * evaluation and target list of top-level plan node is not
! 				 * equivalent to target list expected from this level, we must
! 				 * insert a Result node to project the desired tlist.
  				 */
! 				if (!is_projection_capable_plan(result_plan) &&
! 					!equivalent_tlists(sub_tlist, result_plan->targetlist))
  				{
  					result_plan = (Plan *) make_result(root,
  													   sub_tlist,
***************
*** 1537,1559 **** grouping_planner(PlannerInfo *root, double tuple_fraction)
  			ListCell   *l;
  
  			/*
- 			 * If the top-level plan node is one that cannot do expression
- 			 * evaluation, we must insert a Result node to project the desired
- 			 * tlist.  (In some cases this might not really be required, but
- 			 * it's not worth trying to avoid it.)  Note that on second and
- 			 * subsequent passes through the following loop, the top-level
- 			 * node will be a WindowAgg which we know can project; so we only
- 			 * need to check once.
- 			 */
- 			if (!is_projection_capable_plan(result_plan))
- 			{
- 				result_plan = (Plan *) make_result(root,
- 												   NIL,
- 												   NULL,
- 												   result_plan);
- 			}
- 
- 			/*
  			 * The "base" targetlist for all steps of the windowing process is
  			 * a flat tlist of all Vars and Aggs needed in the result.  (In
  			 * some cases we wouldn't need to propagate all of these all the
--- 1539,1544 ----
***************
*** 1570,1575 **** grouping_planner(PlannerInfo *root, double tuple_fraction)
--- 1555,1579 ----
  													  activeWindows);
  
  			/*
+ 			 * If the top-level plan node is one that cannot do expression
+ 			 * evaluation and target list of top-level plan node is not
+ 			 * equivalent to generated windows_tlist, we must insert a Result
+ 			 * node to project the desiredtlist.  (In some cases this might
+ 			 * not really be required, but it's not worth trying to avoid it.)
+ 			 * Note that on second and subsequent passes through the following
+ 			 * loop, the top-level node will be a WindowAgg which we know can
+ 			 * project; so we only need to check once.
+ 			 */
+ 			if (!is_projection_capable_plan(result_plan) &&
+ 				!equivalent_tlists(window_tlist, result_plan->targetlist))
+ 			{
+ 				result_plan = (Plan *) make_result(root,
+ 												   NIL,
+ 												   NULL,
+ 												   result_plan);
+ 			}
+ 
+ 			/*
  			 * The copyObject steps here are needed to ensure that each plan
  			 * node has a separately modifiable tlist.  (XXX wouldn't a
  			 * shallow list copy do for that?)
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 83,88 **** extern ModifyTable *make_modifytable(CmdType operation, bool canSetTag,
--- 83,89 ----
  				 List *resultRelations, List *subplans, List *returningLists,
  				 List *rowMarks, int epqParam);
  extern bool is_projection_capable_plan(Plan *plan);
+ extern bool equivalent_tlists(List *tlist1, List *tlist2);
  
  /*
   * prototypes for plan/initsplan.c
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 385,445 **** create table nv_child_2011 () inherits (nv_parent);
  alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
  alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
  explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
!                                    QUERY PLAN                                    
! ---------------------------------------------------------------------------------
!  Result
!    ->  Append
!          ->  Seq Scan on nv_parent
!                Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!          ->  Seq Scan on nv_child_2010
!                Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!          ->  Seq Scan on nv_child_2011
!                Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
! (8 rows)
  
  create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
  explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
!                                    QUERY PLAN                                    
! ---------------------------------------------------------------------------------
!  Result
!    ->  Append
!          ->  Seq Scan on nv_parent
!                Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!          ->  Seq Scan on nv_child_2010
!                Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!          ->  Seq Scan on nv_child_2011
!                Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
! (8 rows)
  
  explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
!                                    QUERY PLAN                                    
! ---------------------------------------------------------------------------------
!  Result
!    ->  Append
!          ->  Seq Scan on nv_parent
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!          ->  Seq Scan on nv_child_2010
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!          ->  Seq Scan on nv_child_2011
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!          ->  Seq Scan on nv_child_2009
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
! (10 rows)
  
  -- after validation, the constraint should be used
  alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
  explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
!                                    QUERY PLAN                                    
! ---------------------------------------------------------------------------------
!  Result
!    ->  Append
!          ->  Seq Scan on nv_parent
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!          ->  Seq Scan on nv_child_2010
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!          ->  Seq Scan on nv_child_2009
!                Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
! (8 rows)
  
  -- Foreign key adding test with mixed types
  -- Note: these tables are TEMP to avoid name conflicts when this test
--- 385,441 ----
  alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
  alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
  explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
!                                 QUERY PLAN                                 
! ---------------------------------------------------------------------------
!  Append
!    ->  Seq Scan on nv_parent
!          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!    ->  Seq Scan on nv_child_2010
!          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!    ->  Seq Scan on nv_child_2011
!          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
! (7 rows)
  
  create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
  explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
!                                 QUERY PLAN                                 
! ---------------------------------------------------------------------------
!  Append
!    ->  Seq Scan on nv_parent
!          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!    ->  Seq Scan on nv_child_2010
!          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
!    ->  Seq Scan on nv_child_2011
!          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
! (7 rows)
  
  explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
!                                 QUERY PLAN                                 
! ---------------------------------------------------------------------------
!  Append
!    ->  Seq Scan on nv_parent
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!    ->  Seq Scan on nv_child_2010
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!    ->  Seq Scan on nv_child_2011
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!    ->  Seq Scan on nv_child_2009
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
! (9 rows)
  
  -- after validation, the constraint should be used
  alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
  explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
!                                 QUERY PLAN                                 
! ---------------------------------------------------------------------------
!  Append
!    ->  Seq Scan on nv_parent
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!    ->  Seq Scan on nv_child_2010
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
!    ->  Seq Scan on nv_child_2009
!          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
! (7 rows)
  
  -- Foreign key adding test with mixed types
  -- Note: these tables are TEMP to avoid name conflicts when this test
*** a/src/test/regress/expected/inherit.out
--- b/src/test/regress/expected/inherit.out
***************
*** 1258,1305 **** SELECT thousand, tenthous FROM tenk1
  UNION ALL
  SELECT thousand, thousand FROM tenk1
  ORDER BY thousand, tenthous;
!                                   QUERY PLAN                                   
! -------------------------------------------------------------------------------
!  Result
!    ->  Merge Append
!          Sort Key: tenk1.thousand, tenk1.tenthous
!          ->  Index Only Scan using tenk1_thous_tenthous on tenk1
!          ->  Sort
!                Sort Key: tenk1_1.thousand, tenk1_1.thousand
!                ->  Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
! (7 rows)
  
  explain (costs off)
  SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
  UNION ALL
  SELECT 42, 42, hundred FROM tenk1
  ORDER BY thousand, tenthous;
!                                QUERY PLAN                               
! ------------------------------------------------------------------------
!  Result
!    ->  Merge Append
!          Sort Key: tenk1.thousand, tenk1.tenthous
!          ->  Index Only Scan using tenk1_thous_tenthous on tenk1
!          ->  Sort
!                Sort Key: (42), (42)
!                ->  Index Only Scan using tenk1_hundred on tenk1 tenk1_1
! (7 rows)
  
  explain (costs off)
  SELECT thousand, tenthous FROM tenk1
  UNION ALL
  SELECT thousand, random()::integer FROM tenk1
  ORDER BY thousand, tenthous;
!                                   QUERY PLAN                                   
! -------------------------------------------------------------------------------
!  Result
!    ->  Merge Append
!          Sort Key: tenk1.thousand, tenk1.tenthous
!          ->  Index Only Scan using tenk1_thous_tenthous on tenk1
!          ->  Sort
!                Sort Key: tenk1_1.thousand, ((random())::integer)
!                ->  Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
! (7 rows)
  
  -- Check min/max aggregate optimization
  explain (costs off)
--- 1258,1302 ----
  UNION ALL
  SELECT thousand, thousand FROM tenk1
  ORDER BY thousand, tenthous;
!                                QUERY PLAN                                
! -------------------------------------------------------------------------
!  Merge Append
!    Sort Key: tenk1.thousand, tenk1.tenthous
!    ->  Index Only Scan using tenk1_thous_tenthous on tenk1
!    ->  Sort
!          Sort Key: tenk1_1.thousand, tenk1_1.thousand
!          ->  Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
! (6 rows)
  
  explain (costs off)
  SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
  UNION ALL
  SELECT 42, 42, hundred FROM tenk1
  ORDER BY thousand, tenthous;
!                             QUERY PLAN                            
! ------------------------------------------------------------------
!  Merge Append
!    Sort Key: tenk1.thousand, tenk1.tenthous
!    ->  Index Only Scan using tenk1_thous_tenthous on tenk1
!    ->  Sort
!          Sort Key: (42), (42)
!          ->  Index Only Scan using tenk1_hundred on tenk1 tenk1_1
! (6 rows)
  
  explain (costs off)
  SELECT thousand, tenthous FROM tenk1
  UNION ALL
  SELECT thousand, random()::integer FROM tenk1
  ORDER BY thousand, tenthous;
!                                QUERY PLAN                                
! -------------------------------------------------------------------------
!  Merge Append
!    Sort Key: tenk1.thousand, tenk1.tenthous
!    ->  Index Only Scan using tenk1_thous_tenthous on tenk1
!    ->  Sort
!          Sort Key: tenk1_1.thousand, ((random())::integer)
!          ->  Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
! (6 rows)
  
  -- Check min/max aggregate optimization
  explain (costs off)
***************
*** 1345,1360 **** SELECT x, y FROM
     UNION ALL
     SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
  ORDER BY x, y;
!                             QUERY PLAN                             
! -------------------------------------------------------------------
!  Result
!    ->  Merge Append
!          Sort Key: a.thousand, a.tenthous
!          ->  Index Only Scan using tenk1_thous_tenthous on tenk1 a
!          ->  Sort
!                Sort Key: b.unique2, b.unique2
!                ->  Index Only Scan using tenk1_unique2 on tenk1 b
! (7 rows)
  
  reset enable_seqscan;
  reset enable_indexscan;
--- 1342,1356 ----
     UNION ALL
     SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
  ORDER BY x, y;
!                          QUERY PLAN                          
! -------------------------------------------------------------
!  Merge Append
!    Sort Key: a.thousand, a.tenthous
!    ->  Index Only Scan using tenk1_thous_tenthous on tenk1 a
!    ->  Sort
!          Sort Key: b.unique2, b.unique2
!          ->  Index Only Scan using tenk1_unique2 on tenk1 b
! (6 rows)
  
  reset enable_seqscan;
  reset enable_indexscan;
*** a/src/test/regress/expected/union.out
--- b/src/test/regress/expected/union.out
***************
*** 474,488 **** explain (costs off)
    UNION ALL
    SELECT * FROM t2) t
   WHERE ab = 'ab';
!                     QUERY PLAN                     
! ---------------------------------------------------
!  Result
!    ->  Append
!          ->  Index Scan using t1_ab_idx on t1
!                Index Cond: ((a || b) = 'ab'::text)
!          ->  Index Only Scan using t2_pkey on t2
!                Index Cond: (ab = 'ab'::text)
! (6 rows)
  
  explain (costs off)
   SELECT * FROM
--- 474,487 ----
    UNION ALL
    SELECT * FROM t2) t
   WHERE ab = 'ab';
!                  QUERY PLAN                  
! ---------------------------------------------
!  Append
!    ->  Index Scan using t1_ab_idx on t1
!          Index Cond: ((a || b) = 'ab'::text)
!    ->  Index Only Scan using t2_pkey on t2
!          Index Cond: (ab = 'ab'::text)
! (5 rows)
  
  explain (costs off)
   SELECT * FROM
***************
*** 510,519 **** explain (costs off)
     UNION ALL
     SELECT 2 AS t, * FROM tenk1 b) c
   WHERE t = 2;
!            QUERY PLAN            
! ---------------------------------
!  Result
!    ->  Append
!          ->  Seq Scan on tenk1 b
! (3 rows)
  
--- 509,517 ----
     UNION ALL
     SELECT 2 AS t, * FROM tenk1 b) c
   WHERE t = 2;
!         QUERY PLAN         
! ---------------------------
!  Append
!    ->  Seq Scan on tenk1 b
! (2 rows)