On 11/16/18 22:03, Tom Lane wrote:
A possible fix for this is to do eval_const_expressions() on
function RTE expressions at this stage (and then not need to
do it later), and then pull up only when we find that the
RTE expression has been reduced to a single Const.
Attached is a patch that does this, and transforms RTE_FUCTION that was
reduced to a single Const into an RTE_RESULT.
Not sure it does everything correctly, but some basic cases work. In
particular, I don't understand whether it needs any handling of "append
relations".
--
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
>From 4921a58f38659580f76f9684e56c0546d46526bc Mon Sep 17 00:00:00 2001
From: Alexander Kuzmenkov <a.kuzmen...@postgrespro.ru>
Date: Wed, 20 Mar 2019 19:56:20 +0300
Subject: [PATCH v5] Simplify immutable RTE_FUNCTION to RTE_RESULT.
---
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
