From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Aleksandr Parfenov <asp437@gmail.com>
Cc: a.bykov@postgrespro.ru, pgsql-hackers@lists.postgresql.org, Antonin Houska <ah@cybertec.at>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Subject: Re: Optimze usage of immutable functions as relation
Date: Wed, 20 Mar 2019 19:58:21 +0300
Message-ID: <3f7fda2f-1cdc-8ad3-e3c9-c2436fb5d509@postgrespro.ru> (raw)
In-Reply-To: <2906.1542395026@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>
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
Attachments:
[text/x-patch] v5-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch (12.4K, ../3f7fda2f-1cdc-8ad3-e3c9-c2436fb5d509@postgrespro.ru/2-v5-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch)
download | inline diff:
From 4921a58f38659580f76f9684e56c0546d46526bc Mon Sep 17 00:00:00 2001
From: Alexander Kuzmenkov <a.kuzmenkov@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.cindex 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.cindex 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.outindex 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.sqlindex 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
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.kuzmenkov@postgrespro.ru, tgl@sss.pgh.pa.us, asp437@gmail.com, ah@cybertec.at, horiguchi.kyotaro@lab.ntt.co.jp
Subject: Re: Optimze usage of immutable functions as relation
In-Reply-To: <3f7fda2f-1cdc-8ad3-e3c9-c2436fb5d509@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