23.07.2019 14:36, Anastasia Lubennikova :
08.07.2019 4:18, Thomas Munro:
The July Commitfest is here. Could we please have a rebase of this
patch?
Updated patch is in attachments.
I've only resolved one small cosmetic merge conflict.
Later this week I'm going to send a more thoughtful review.
Well, I looked through the patch and didn't find any issues, so I'll
mark this ready for committer.
Last implementation differs from the original one and is based on
suggestion from this message:
https://www.postgresql.org/message-id/2906.1542395026%40sss.pgh.pa.us
It does eval_const_expressions() earlier and pulls up only Const functions.
I also added a few more tests and comments.
New version is in attachments.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 36fefd9..acc3776 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1071,7 +1071,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
@@ -1085,7 +1086,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 4fbc03f..f96b290 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))
@@ -1784,6 +1799,78 @@ 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;
+
+ /* Fail if the RTE has ORDINALITY - we don't implement that here. */
+ if (rte->funcordinality)
+ return;
+
+ /* Fail if RTE isn't a single, simple Const expr */
+ 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 07e631d..20cdac6 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2963,6 +2963,34 @@ where nt3.id = 1 and ss2.b3;
(1 row)
--
+-- test inlining of immutable functions with PlaceHolderVars
+--
+explain (costs off)
+select nt3.id
+from nt3 as nt3
+ left join
+ (select nt2.*, (nt2.b1 or i4=42) AS b3
+ from nt2 as nt2
+ left join
+ int4(0) i4
+ on i4 = nt2.nt1_id
+ ) as ss2
+ on ss2.id = nt3.nt2_id
+where nt3.id = 1 and ss2.b3;
+ QUERY PLAN
+----------------------------------------------
+ Nested Loop Left Join
+ Filter: ((nt2.b1 OR ((0) = 42)))
+ -> Index Scan using nt3_pkey on nt3
+ Index Cond: (id = 1)
+ -> Nested Loop Left Join
+ Join Filter: ((0) = nt2.nt1_id)
+ -> Index Scan using nt2_pkey on nt2
+ Index Cond: (id = nt3.nt2_id)
+ -> Result
+(9 rows)
+
+--
-- test case where a PlaceHolderVar is propagated into a subquery
--
explain (costs off)
@@ -3060,11 +3088,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 +3103,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 +3119,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 +3127,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 +3271,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 bf6d5c3..0d4100e 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -884,6 +884,22 @@ from nt3 as nt3
where nt3.id = 1 and ss2.b3;
--
+-- test inlining of immutable functions with PlaceHolderVars
+--
+explain (costs off)
+select nt3.id
+from nt3 as nt3
+ left join
+ (select nt2.*, (nt2.b1 or i4=42) AS b3
+ from nt2 as nt2
+ left join
+ int4(0) i4
+ on i4 = nt2.nt1_id
+ ) as ss2
+ on ss2.id = nt3.nt2_id
+where nt3.id = 1 and ss2.b3;
+
+--
-- test case where a PlaceHolderVar is propagated into a subquery
--
@@ -914,18 +930,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 +1035,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)
--
