From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Optimze usage of immutable functions as relation
Date: Thu, 1 Aug 2019 20:54:10 +0300
Message-ID: <18cbbbf6-5c03-41d5-5bd4-30048c2e5155@postgrespro.ru> (raw)
In-Reply-To: <18924.1564165560@sss.pgh.pa.us>
References: <402356c32eeb93d4fed01f66d6c7fe2d@postgrespro.ru>
<20180906185109.661aaef0@asp437-ThinkPad-L380>
<153994199621.10161.17815885396545049364.pgcf@coridan.postgresql.org>
<CACdpekK1oDy7-_HnXOaREa_8HM2r-fsp8iv5e6p8aWOdGdK8Mg@mail.gmail.com>
<2906.1542395026@sss.pgh.pa.us>
<3f7fda2f-1cdc-8ad3-e3c9-c2436fb5d509@postgrespro.ru>
<CA+hUKGK1ATcNa678n=AG+DHQrFmWr-Lovmsq2AP-m5r=shW0Mg@mail.gmail.com>
<0dd4efb2-22a3-6fef-11f7-1b20dd71a509@postgrespro.ru>
<d31c5077-398f-dd26-ef9f-df11918b42f0@postgrespro.ru>
<18924.1564165560@sss.pgh.pa.us>
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
Attachments:
[text/x-patch] v8-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch (17.9K, ../18cbbbf6-5c03-41d5-5bd4-30048c2e5155@postgrespro.ru/2-v8-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch)
download | inline diff:diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 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.cindex 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.outindex 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.outindex 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.sqlindex 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.sqlindex 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lubennikova@postgrespro.ru, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: Optimze usage of immutable functions as relation
In-Reply-To: <18cbbbf6-5c03-41d5-5bd4-30048c2e5155@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox