Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hpt5t-00042y-5n for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Jul 2019 11:36:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hpt5q-0003p5-Nw for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Jul 2019 11:36:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hpt5q-0003iB-1o for pgsql-hackers@lists.postgresql.org; Tue, 23 Jul 2019 11:36:30 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1hpt5l-0000FG-PY for pgsql-hackers@lists.postgresql.org; Tue, 23 Jul 2019 11:36:28 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 7125B21C2446 for ; Tue, 23 Jul 2019 14:36:23 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.74] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 48AA521C238E for ; Tue, 23 Jul 2019 14:36:23 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1563881783; bh=9ZIymEYMKnCcZMVExQK6cTXGoyISxbxgK3U/NO1XHA8=; h=Subject:To:References:From:Date:In-Reply-To; b=mRUyk4k4/dYcSp1NzqVwJlIVHUKc/y4IF5kD+6juvumPABRs5eXptyRzS55adto0U bIgAbw9GA6lHhgILOG1rrgXwtR0uvwGecME+vNNLlFpe886EuKEaZeaDIPAuiVCIpw r1CIzObBMOe918nGQzZ11l/BJhJiyosl8B+Dfacw= Subject: Re: Optimze usage of immutable functions as relation To: pgsql-hackers@lists.postgresql.org References: <402356c32eeb93d4fed01f66d6c7fe2d@postgrespro.ru> <20180906185109.661aaef0@asp437-ThinkPad-L380> <153994199621.10161.17815885396545049364.pgcf@coridan.postgresql.org> <2906.1542395026@sss.pgh.pa.us> <3f7fda2f-1cdc-8ad3-e3c9-c2436fb5d509@postgrespro.ru> From: Anastasia Lubennikova Message-ID: <0dd4efb2-22a3-6fef-11f7-1b20dd71a509@postgrespro.ru> Date: Tue, 23 Jul 2019 14:36:23 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------3368043C4D69445C500B9818" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------3368043C4D69445C500B9818 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------3368043C4D69445C500B9818 Content-Type: text/x-patch; name="v6-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="v6-0001-Simplify-immutable-RTE_FUNCTION-to-RTE_RESULT.patch" commit 959dab6ef869821e757f93a82de40b6bf883ad71 Author: Anastasia 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.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..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.out index 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.sql index 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) -- --------------3368043C4D69445C500B9818--