From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Optimze usage of immutable functions as relation
Date: Tue, 23 Jul 2019 14:36:23 +0300
Message-ID: <0dd4efb2-22a3-6fef-11f7-1b20dd71a509@postgrespro.ru> (raw)
In-Reply-To: <CA+hUKGK1ATcNa678n=AG+DHQrFmWr-Lovmsq2AP-m5r=shW0Mg@mail.gmail.com>
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>
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.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
[text/x-patch] v6-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch (12.1K, ../0dd4efb2-22a3-6fef-11f7-1b20dd71a509@postgrespro.ru/2-v6-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch)
download | inline diff:
commit 959dab6ef869821e757f93a82de40b6bf883ad71
Author: Anastasia <a.lubennikova@postgrespro.ru>
Date: Tue Jul 23 14:22:18 2019 +0300
[PATCH v6] Simplify immutable RTE_FUNCTION to RTE_RESULT
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..fca78b7 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,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 07e631d..bbe9533 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 bf6d5c3..90f3fdb 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)
--
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, pgsql-hackers@lists.postgresql.org
Subject: Re: Optimze usage of immutable functions as relation
In-Reply-To: <0dd4efb2-22a3-6fef-11f7-1b20dd71a509@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