Subject: [PATCH v5] Simplify immutable RTE_FUNCTION to RTE_RESULT.

From: Alexander Kuzmenkov <[email-masked]>
Date: Wed, 20 Mar 2019 19:56:20 +0300

---
 src/backend/optimizer/plan/planner.c      |   6 +-
 src/backend/optimizer/prep/prepjointree.c |  85 ++++++++++++++++++++++
 src/test/regress/expected/join.out        | 113 +++++++++++++++++++++++++++---
 src/test/regress/sql/join.sql             |  44 ++++++++++--
 4 files changed, 234 insertions(+), 14 deletions(-)

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index e408e77..fe2f426 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1050,7 +1050,8 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
 		expr = flatten_join_alias_vars(root->parse, expr);
 
 	/*
-	 * Simplify constant expressions.
+	 * Simplify constant expressions. For function RTEs, this was already done
+	 * by pullup_simple_subqueries.
 	 *
 	 * Note: an essential effect of this is to convert named-argument function
 	 * calls to positional notation and insert the current actual values of
@@ -1064,7 +1065,8 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
 	 * careful to maintain AND/OR flatness --- that is, do not generate a tree
 	 * with AND directly under AND, nor OR directly under OR.
 	 */
-	expr = eval_const_expressions(root, expr);
+	if (kind != EXPRKIND_RTFUNC && kind != EXPRKIND_RTFUNC_LATERAL)
+		expr = eval_const_expressions(root, expr);
 
 	/*
 	 * If it's a qual or havingQual, canonicalize it.
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index aebe162..313c2bb 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -79,6 +79,9 @@ static Node *pull_up_simple_union_all(PlannerInfo *root, Node *jtnode,
 static void pull_up_union_leaf_queries(Node *setOp, PlannerInfo *root,
 						   int parentRTindex, Query *setOpQuery,
 						   int childRToffset);
+static void transform_const_function_to_result(PlannerInfo *root, Node *jtnode,
+									 RangeTblEntry *rte,
+									 JoinExpr *lowest_nulling_outer_join);
 static void make_setop_translation_list(Query *query, Index newvarno,
 							List **translated_vars);
 static bool is_simple_subquery(Query *subquery, RangeTblEntry *rte,
@@ -754,6 +757,18 @@ pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
 			is_simple_values(root, rte))
 			return pull_up_simple_values(root, jtnode, rte);
 
+		/*
+		 * Or is it an immutable function that evaluated to a single Const?
+		 */
+		if (rte->rtekind == RTE_FUNCTION)
+		{
+			rte->functions = (List *) 
+				eval_const_expressions(root, (Node *) rte->functions);
+			transform_const_function_to_result(root, jtnode, rte,
+											   lowest_nulling_outer_join);
+			return jtnode;
+		}
+
 		/* Otherwise, do nothing at this node. */
 	}
 	else if (IsA(jtnode, FromExpr))
@@ -1783,6 +1798,76 @@ is_simple_union_all_recurse(Node *setOp, Query *setOpQuery, List *colTypes)
 }
 
 /*
+ * If the function of this RTE_FUNCTION entry evaluated to a single Const
+ * after eval_const_expressions, transform it to RTE_RESULT.
+ */
+static void
+transform_const_function_to_result(PlannerInfo *root, Node *jtnode,
+								   RangeTblEntry *rte,
+								   JoinExpr *lowest_nulling_outer_join)
+{
+	ListCell *lc;
+	pullup_replace_vars_context rvcontext;
+	RangeTblFunction *rtf = (RangeTblFunction *) linitial(rte->functions);
+	Query *parse = root->parse;
+
+	if (!IsA(rtf->funcexpr, Const))
+		return;
+
+	if (rte->funcordinality)
+		return;
+
+	if (list_length(rte->functions) != 1)
+		return;
+
+	rvcontext.targetlist = list_make1(makeTargetEntry((Expr *) rtf->funcexpr,
+		1 /* resno */, NULL /* resname */, false /* resjunk */));
+	rvcontext.root = root;
+	rvcontext.target_rte = rte;
+
+	/*
+	 * Since this function was reduced to Const, it can't really have lateral
+	 * references, even if it's marked as LATERAL. This means we don't need
+	 * to fill relids.
+	 */
+	rvcontext.relids = NULL;
+
+	rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
+	rvcontext.varno = ((RangeTblRef *) jtnode)->rtindex;
+
+	/*
+	 * If this RTE is on a nullable side of an outer join, we have to insert
+	 * PHVs around our Consts so that they go to null when needed.
+	 */
+	rvcontext.need_phvs = lowest_nulling_outer_join != NULL;
+
+	rvcontext.wrap_non_vars = false;
+	rvcontext.rv_cache = palloc0((list_length(rvcontext.targetlist) + 1)
+								 * sizeof(Node *));
+
+	parse->targetList = (List *)
+			pullup_replace_vars((Node *) parse->targetList, &rvcontext);
+	parse->returningList = (List *)
+			pullup_replace_vars((Node *) parse->returningList, &rvcontext);
+	replace_vars_in_jointree((Node *) parse->jointree, &rvcontext, NULL);
+
+	foreach(lc, parse->rtable)
+	{
+		RangeTblEntry *otherrte = (RangeTblEntry *) lfirst(lc);
+
+		if (otherrte->rtekind == RTE_JOIN)
+			otherrte->joinaliasvars = (List *)
+				pullup_replace_vars((Node *) otherrte->joinaliasvars,
+									&rvcontext);
+	}
+
+	rte->rtekind = RTE_RESULT;
+	rte->functions = NIL;
+
+	return;
+}
+
+/*
  * is_safe_append_member
  *	  Check a subquery that is a leaf of a UNION ALL appendrel to see if it's
  *	  safe to pull up.
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 88fcd52..200bdde 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3060,11 +3060,14 @@ select * from int4_tbl a full join int4_tbl b on false;
 --
 -- test for ability to use a cartesian join when necessary
 --
+create temp table q1 as select 1 q1;
+create temp table q2 as select 0 q2;
+analyze q1;
+analyze q2;
 explain (costs off)
 select * from
   tenk1 join int4_tbl on f1 = twothousand,
-  int4(sin(1)) q1,
-  int4(sin(0)) q2
+  q1, q2
 where q1 = thousand or q2 = thousand;
                                QUERY PLAN                               
 ------------------------------------------------------------------------
@@ -3072,8 +3075,8 @@ where q1 = thousand or q2 = thousand;
    Hash Cond: (tenk1.twothousand = int4_tbl.f1)
    ->  Nested Loop
          ->  Nested Loop
-               ->  Function Scan on q1
-               ->  Function Scan on q2
+               ->  Seq Scan on q1
+               ->  Seq Scan on q2
          ->  Bitmap Heap Scan on tenk1
                Recheck Cond: ((q1.q1 = thousand) OR (q2.q2 = thousand))
                ->  BitmapOr
@@ -3088,8 +3091,7 @@ where q1 = thousand or q2 = thousand;
 explain (costs off)
 select * from
   tenk1 join int4_tbl on f1 = twothousand,
-  int4(sin(1)) q1,
-  int4(sin(0)) q2
+  q1, q2
 where thousand = (q1 + q2);
                           QUERY PLAN                          
 --------------------------------------------------------------
@@ -3097,8 +3099,8 @@ where thousand = (q1 + q2);
    Hash Cond: (tenk1.twothousand = int4_tbl.f1)
    ->  Nested Loop
          ->  Nested Loop
-               ->  Function Scan on q1
-               ->  Function Scan on q2
+               ->  Seq Scan on q1
+               ->  Seq Scan on q2
          ->  Bitmap Heap Scan on tenk1
                Recheck Cond: (thousand = (q1.q1 + q2.q2))
                ->  Bitmap Index Scan on tenk1_thous_tenthous
@@ -3241,6 +3243,101 @@ where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
 (1 row)
 
 --
+-- test inlining of immutable functions
+--
+explain (costs off)
+select unique1 from tenk1, (select int4(1) x) x where x = unique1;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+   Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1 from tenk1, (select * from int4(1) x) x where x = unique1;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+   Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1 from tenk1 join (select * from int4(1) x) x on x = unique1;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+   Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1, x.* from tenk1, (select *, random() from int4(1) x) x where x = unique1;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Nested Loop
+   ->  Result
+   ->  Index Only Scan using tenk1_unique1 on tenk1
+         Index Cond: (unique1 = (1))
+(4 rows)
+
+explain (costs off)
+select unique1 from tenk1, int4(1) x where x = unique1;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+   Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1 from tenk1, lateral int4(1) x where x = unique1;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+   Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1, x from tenk1 join int4(1) x on unique1 = x;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using tenk1_unique1 on tenk1
+   Index Cond: (unique1 = 1)
+(2 rows)
+
+explain (costs off)
+select unique1, x from tenk1 left join int4(1) x on unique1 = x;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Merge Left Join
+   Merge Cond: (tenk1.unique1 = (1))
+   ->  Index Only Scan using tenk1_unique1 on tenk1
+   ->  Sort
+         Sort Key: (1)
+         ->  Result
+(6 rows)
+
+explain (costs off)
+select unique1, x from tenk1 right join int4(1) x on unique1 = x;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Nested Loop Left Join
+   ->  Result
+   ->  Index Only Scan using tenk1_unique1 on tenk1
+         Index Cond: (unique1 = 1)
+(4 rows)
+
+explain (costs off)
+select unique1, x from tenk1 full join int4(1) x on unique1 = x;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Merge Full Join
+   Merge Cond: (tenk1.unique1 = (1))
+   ->  Index Only Scan using tenk1_unique1 on tenk1
+   ->  Sort
+         Sort Key: (1)
+         ->  Result
+(6 rows)
+
+--
 -- test extraction of restriction OR clauses from join OR clause
 -- (we used to only do this for indexable clauses)
 --
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index c247509..b05be86 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -914,18 +914,21 @@ select * from int4_tbl a full join int4_tbl b on false;
 -- test for ability to use a cartesian join when necessary
 --
 
+create temp table q1 as select 1 q1;
+create temp table q2 as select 0 q2;
+analyze q1;
+analyze q2;
+
 explain (costs off)
 select * from
   tenk1 join int4_tbl on f1 = twothousand,
-  int4(sin(1)) q1,
-  int4(sin(0)) q2
+  q1, q2
 where q1 = thousand or q2 = thousand;
 
 explain (costs off)
 select * from
   tenk1 join int4_tbl on f1 = twothousand,
-  int4(sin(1)) q1,
-  int4(sin(0)) q2
+  q1, q2
 where thousand = (q1 + q2);
 
 --
@@ -1016,6 +1019,39 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
 where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
 
 --
+-- test inlining of immutable functions
+--
+explain (costs off)
+select unique1 from tenk1, (select int4(1) x) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1, (select * from int4(1) x) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1 join (select * from int4(1) x) x on x = unique1;
+
+explain (costs off)
+select unique1, x.* from tenk1, (select *, random() from int4(1) x) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1, int4(1) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1, lateral int4(1) x where x = unique1;
+
+explain (costs off)
+select unique1, x from tenk1 join int4(1) x on unique1 = x;
+
+explain (costs off)
+select unique1, x from tenk1 left join int4(1) x on unique1 = x;
+
+explain (costs off)
+select unique1, x from tenk1 right join int4(1) x on unique1 = x;
+
+explain (costs off)
+select unique1, x from tenk1 full join int4(1) x on unique1 = x;
+
+--
 -- test extraction of restriction OR clauses from join OR clause
 -- (we used to only do this for indexable clauses)
 --
-- 
2.7.4

--------------6CF956F72F1A58485BF937FB--