26.07.2019 21:26, Tom Lane wrote:
I took a quick look at this and I have a couple of gripes ---
* The naming and documentation of transform_const_function_to_result
seem pretty off-point to me. ISTM the real goal of that function is to
pull up constant values from RTE_FUNCTION RTEs. Replacing the RTE
with an RTE_RESULT is just a side-effect that's needed so that we
don't generate a useless FunctionScan plan node. I'd probably call
it pull_up_constant_function or something like that.
* It would be useful for the commentary to point out that in principle we
could pull up any immutable (or, probably, even just stable) expression;
but we don't, (a) for fear of multiple evaluations of the result costing
us more than we can save, and (b) because a primary goal is to let the
constant participate in further const-folding, and of course that won't
happen for a non-Const.
Fixed
* The test cases are really pretty bogus, because int4(1) or int4(0) are
not function invocations at all. The parser thinks those are no-op type
casts, and so what comes out of parse analysis is already just a plain
Const. Thus, not one of these test cases is actually verifying that
const-folding of an immutable function has happened before we try to
pull up. While you could dodge the problem today by, say, writing
int8(0) which isn't a no-op cast, I'd recommend staying away from
typename() notation altogether. There's too much baggage there and too
little certainty that you wrote a function call not something else.
The existing test cases you changed, with int4(sin(1)) and so on,
are better examples of something that has to actively be folded to
a constant.
Thank you for pointing out on specific of int4() function,
I updated tests to use dummy plpgsql function.
I'm not sure if tests of various join types are redundant but I left them.
As far as I understand, the principal motivation of this patch was to
optimize
function scan joins that occur in FTS queries. For example:
select * from test_tsquery, to_tsquery('english', 'new') q where
txtsample @@ q;
So I also added another test to tsearch.sql to illustrate difference
between optimized and not optimized plans.
--
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..e6e8fef 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 pull_up_constant_function(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,20 @@ 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);
+
+ pull_up_constant_function(root, jtnode, rte,
+ lowest_nulling_outer_join);
+
+ return jtnode;
+ }
+
/* Otherwise, do nothing at this node. */
}
else if (IsA(jtnode, FromExpr))
@@ -1784,6 +1801,93 @@ is_simple_union_all_recurse(Node *setOp, Query *setOpQuery, List *colTypes)
}
/*
+ * pull_up_constant_function
+ * Pull up an immutable function that was evaluated to a constant
+ *
+ * jtnode is a RangeTblRef that has been identified as a FUNCTION RTE
+ * by pull_up_subqueries.
+ *
+ * If this RTE is on a nullable side of an outer join, insert
+ * PlaceHolderVars around our Consts so that they go to null when needed.
+ *
+ */
+static void
+pull_up_constant_function(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;
+
+ /*
+ * In principle we could pull up any immutable expression, but we don't.
+ * Firsly to avoid multiple evaluations of an expensive parameter at
+ * runtime. And secondly because a primary goal is to let the constant
+ * participate in further const-folding,and of course that won't happen
+ * for a non-Const.
+ */
+ 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, 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->eref = makeAlias("*RESULT*", NIL);
+ 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..596ada6 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2963,6 +2963,40 @@ where nt3.id = 1 and ss2.b3;
(1 row)
--
+-- test inlining of immutable functions with PlaceHolderVars
+--
+CREATE OR REPLACE FUNCTION f_immutable_int4(i integer) RETURNS integer AS $$
+ BEGIN
+ RETURN i;
+ END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+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
+ f_immutable_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)
+
+DROP FUNCTION f_immutable_int4;
+--
-- test case where a PlaceHolderVar is propagated into a subquery
--
explain (costs off)
@@ -3060,11 +3094,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 +3109,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 +3125,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 +3133,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 +3277,123 @@ where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
(1 row)
--
+-- test inlining of immutable functions
+--
+CREATE OR REPLACE FUNCTION f_immutable_int4(i integer) RETURNS integer AS $$
+ BEGIN
+ RETURN i;
+ END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+-- check optimization of function scan join --
+explain (costs off)
+select unique1 from tenk1, (select * from f_immutable_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 f_immutable_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 f_immutable_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, f_immutable_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 f_immutable_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 f_immutable_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 f_immutable_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 f_immutable_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 f_immutable_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)
+
+-- check const-folding --
+explain (costs off)
+select unique1 from tenk1, f_immutable_int4(1) x where x = 42;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+
+-- test ability to push immutable const functions through outer join clauses
+explain (costs off)
+ select * from tenk1 a full join tenk1 b using(unique2) where unique2 = f_immutable_int4(42);
+ QUERY PLAN
+-------------------------------------------------
+ Merge Full Join
+ Merge Cond: (a.unique2 = b.unique2)
+ -> Index Scan using tenk1_unique2 on tenk1 a
+ Index Cond: (unique2 = 42)
+ -> Index Scan using tenk1_unique2 on tenk1 b
+ Index Cond: (unique2 = 42)
+(6 rows)
+
+DROP FUNCTION f_immutable_int4;
+--
-- 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/expected/tsearch.out b/src/test/regress/expected/tsearch.out
index 6f61acc..6eab8c4 100644
--- a/src/test/regress/expected/tsearch.out
+++ b/src/test/regress/expected/tsearch.out
@@ -1259,6 +1259,28 @@ S. T. Coleridge (1772-1834)
--Rewrite sub system
CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);
\set ECHO none
+-- Test inlining of immutable constant functions --
+-- to_tsquery(text) is not immutable, so it won't be inlined
+-- to_tsquery(regconfig, text) is an immutable function.
+-- That allows to get rid of using function scan and join at all.
+explain (costs off)
+select * from test_tsquery, to_tsquery('new') q where txtsample @@ q;
+ QUERY PLAN
+------------------------------------------------
+ Nested Loop
+ Join Filter: (test_tsquery.txtsample @@ q.q)
+ -> Function Scan on to_tsquery q
+ -> Seq Scan on test_tsquery
+(4 rows)
+
+explain (costs off)
+select * from test_tsquery, to_tsquery('english', 'new') q where txtsample @@ q;
+ QUERY PLAN
+---------------------------------------------
+ Seq Scan on test_tsquery
+ Filter: (txtsample @@ '''new'''::tsquery)
+(2 rows)
+
ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;
UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);
ALTER TABLE test_tsquery ADD COLUMN sample tsquery;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index bf6d5c3..9f1b025 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -884,6 +884,30 @@ from nt3 as nt3
where nt3.id = 1 and ss2.b3;
--
+-- test inlining of immutable functions with PlaceHolderVars
+--
+CREATE OR REPLACE FUNCTION f_immutable_int4(i integer) RETURNS integer AS $$
+ BEGIN
+ RETURN i;
+ END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+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
+ f_immutable_int4(0) i4
+ on i4 = nt2.nt1_id
+ ) as ss2
+ on ss2.id = nt3.nt2_id
+where nt3.id = 1 and ss2.b3;
+
+DROP FUNCTION f_immutable_int4;
+
+--
-- test case where a PlaceHolderVar is propagated into a subquery
--
@@ -914,18 +938,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 +1043,53 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
--
+-- test inlining of immutable functions
+--
+CREATE OR REPLACE FUNCTION f_immutable_int4(i integer) RETURNS integer AS $$
+ BEGIN
+ RETURN i;
+ END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+-- check optimization of function scan join --
+explain (costs off)
+select unique1 from tenk1, (select * from f_immutable_int4(1) x) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1 join (select * from f_immutable_int4(1) x) x on x = unique1;
+
+explain (costs off)
+select unique1, x.* from tenk1, (select *, random() from f_immutable_int4(1) x) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1, f_immutable_int4(1) x where x = unique1;
+
+explain (costs off)
+select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1;
+
+explain (costs off)
+select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x;
+
+explain (costs off)
+select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x;
+
+explain (costs off)
+select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x;
+
+explain (costs off)
+select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x;
+
+-- check const-folding --
+explain (costs off)
+select unique1 from tenk1, f_immutable_int4(1) x where x = 42;
+
+-- test ability to push immutable const functions through outer join clauses
+explain (costs off)
+ select * from tenk1 a full join tenk1 b using(unique2) where unique2 = f_immutable_int4(42);
+
+DROP FUNCTION f_immutable_int4;
+
+--
-- 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/tsearch.sql b/src/test/regress/sql/tsearch.sql
index 637bfb3..ad606bb 100644
--- a/src/test/regress/sql/tsearch.sql
+++ b/src/test/regress/sql/tsearch.sql
@@ -414,6 +414,15 @@ Moscow moskva | moscow
\.
\set ECHO all
+-- Test inlining of immutable constant functions --
+-- to_tsquery(text) is not immutable, so it won't be inlined
+-- to_tsquery(regconfig, text) is an immutable function.
+-- That allows to get rid of using function scan and join at all.
+explain (costs off)
+select * from test_tsquery, to_tsquery('new') q where txtsample @@ q;
+explain (costs off)
+select * from test_tsquery, to_tsquery('english', 'new') q where txtsample @@ q;
+
ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;
UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);
ALTER TABLE test_tsquery ADD COLUMN sample tsquery;
