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 1gq0Xj-0004qv-FZ for pgsql-hackers@arkaria.postgresql.org; Sat, 02 Feb 2019 19:01:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gq0Xg-0006tK-9V for pgsql-hackers@arkaria.postgresql.org; Sat, 02 Feb 2019 19:01:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gq0Xf-0006r4-W3 for pgsql-hackers@lists.postgresql.org; Sat, 02 Feb 2019 19:01:28 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gq0Xa-00053p-Dl for pgsql-hackers@postgresql.org; Sat, 02 Feb 2019 19:01:27 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id x12J11YL004592; Sat, 2 Feb 2019 14:01:02 -0500 From: Tom Lane To: Michael Paquier cc: Peter Eisentraut , Robert Haas , Andreas Karlsson , Andres Freund , Thomas Munro , Andrew Gierth , David Fetter , Pg Hackers Subject: Re: Early WIP/PoC for inlining CTEs In-reply-to: <17787.1548791564@sss.pgh.pa.us> References: <20190111190403.77ye7m5mxypxsf4l@alap3.anarazel.de> <00818be9-2f63-f584-efcd-d97ee0662905@proxel.se> <5715d93e-6ebc-be17-c2c3-56a873100a38@proxel.se> <29576.1548084476@sss.pgh.pa.us> <12408.1548707732@sss.pgh.pa.us> <32184.1548713132@sss.pgh.pa.us> <20190129045158.GD3121@paquier.xyz> <17787.1548791564@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 29 Jan 2019 14:52:44 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <4422.1549133964.0@sss.pgh.pa.us> Date: Sat, 02 Feb 2019 14:01:01 -0500 Message-ID: <4591.1549134061@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4422.1549133964.1@sss.pgh.pa.us> I wrote: > I propose that we implement and document this as > WITH ctename AS [ MATERIALIZE { ON | OFF } ] ( query ) > which is maybe a bit clunky but not awful, and it would leave room > to generalize it to "AS [ optionname optionvalue [ , ... ] ]" if we > ever need to. Looking at the precedent of e.g. EXPLAIN, we could > probably allow just "MATERIALIZE" as well, with the boolean value > defaulting to true. In hopes of moving things along, here's a version of the patch that does it like that. This demonstrates that, in fact, we can accept "keyword [value] [, ...]" style options without any parens and there's no syntax conflict. We'd have to work a bit harder on the actual code in gram.y if we wanted to handle multiple options, but the Bison productions will work. There's nothing particularly stopping us from accepting "materialized" with a D in this syntax, instead of or in addition to "materialize"; though I hesitate to mention it for fear of another round of bikeshedding. After further reflection I really don't like Andrew's suggestion that we not document the rule that multiply-referenced CTEs won't be inlined by default. That would be giving up the principle that WITH calculations are not done multiple times by default, and I draw the line at that. It's an often-useful behavior as well as one that's been documented from day one, so I do not accept the argument that we might someday override it on the basis of nothing but planner cost estimates. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="inlining-ctes-v11.patch"; charset="us-ascii" Content-ID: <4422.1549133964.2@sss.pgh.pa.us> Content-Description: inlining-ctes-v11.patch Content-Transfer-Encoding: quoted-printable diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_= stat_statements/pg_stat_statements.c index ea2e4bc..9905593 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2927,6 +2927,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node) = /* we store the string name because RTE_CTE RTEs need it */ APP_JUMB_STRING(cte->ctename); + APP_JUMB(cte->ctematerialized); JumbleQuery(jstate, castNode(Query, cte->ctequery)); } break; diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/post= gres_fdw/expected/postgres_fdw.out index b3894d0..226ba56 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1888,7 +1888,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c= 1 =3D t2.c1) ORDER BY t1.c3, t = -- join in CTE EXPLAIN (VERBOSE, COSTS OFF) -WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN= ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1= OFFSET 100 LIMIT 10; +WITH t (c1_1, c1_3, c2_1) AS MATERIALIZE ON (SELECT t1.c1, t1.c3, t2.c1 F= ROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDE= R BY c1_3, c1_1 OFFSET 100 LIMIT 10; QUERY PLAN = = -------------------------------------------------------------------------= ------------------------------------------------------------ Limit @@ -1905,7 +1905,7 @@ WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t= 2.c1 FROM ft1 t1 JOIN ft2 t2 Output: t.c1_1, t.c2_1, t.c1_3 (12 rows) = -WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN= ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1= OFFSET 100 LIMIT 10; +WITH t (c1_1, c1_3, c2_1) AS MATERIALIZE ON (SELECT t1.c1, t1.c3, t2.c1 F= ROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDE= R BY c1_3, c1_1 OFFSET 100 LIMIT 10; c1_1 | c2_1 = ------+------ 101 | 101 diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_= fdw/sql/postgres_fdw.sql index f438165..6399b8b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -493,8 +493,8 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = =3D t2.c1) ORDER BY t1.c3, t SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =3D t2.c1) ORDER BY= t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE; -- join in CTE EXPLAIN (VERBOSE, COSTS OFF) -WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN= ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1= OFFSET 100 LIMIT 10; -WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN= ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1= OFFSET 100 LIMIT 10; +WITH t (c1_1, c1_3, c2_1) AS MATERIALIZE ON (SELECT t1.c1, t1.c3, t2.c1 F= ROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDE= R BY c1_3, c1_1 OFFSET 100 LIMIT 10; +WITH t (c1_1, c1_3, c2_1) AS MATERIALIZE ON (SELECT t1.c1, t1.c3, t2.c1 F= ROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =3D t2.c1)) SELECT c1_1, c2_1 FROM t ORDE= R BY c1_3, c1_1 OFFSET 100 LIMIT 10; -- ctid with whole-row reference EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 =3D t2.c1= ) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 88bc189..0cd4056 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -2199,16 +2199,19 @@ SELECT n FROM t LIMIT 100; = - A useful property of WITH queries is that they are = evaluated - only once per execution of the parent query, even if they are referred= to - more than once by the parent query or sibling WITH = queries. + A useful property of WITH queries is that they are + normally evaluated only once per execution of the parent query, even i= f + they are referred to more than once by the parent query or + sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work.= Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. - However, the other side of this coin is that the optimizer is less abl= e to - push restrictions from the parent query down into a WITH query - than an ordinary subquery. The WITH query will gen= erally be + However, the other side of this coin is that the optimizer is not able= to + push restrictions from the parent query down into a multiply-reference= d + WITH query, since that might affect all uses of the + WITH query's output when it should affect only one. + The multiply-referenced WITH query will be evaluated as written, without suppression of rows that the parent quer= y might discard afterwards. (But, as mentioned above, evaluation might = stop early if the reference(s) to the query demand only a limited number of @@ -2216,6 +2219,76 @@ SELECT n FROM t LIMIT 100; = + However, if a WITH query is non-recursive and + side-effect-free (that is, it is a SELECT containin= g + no volatile functions) then it can be folded into the parent query, + allowing joint optimization of the two query levels. By default, this + happens if the parent query references the WITH que= ry + just once, but not if it references the WITH query + more than once. + You can override the decision by specifying MATERIALIZE + ON to force separate calculation of the WITH + query, or by specifying MATERIALIZE OFF to force it= to + be merged into the parent query. + The latter choice risks duplicate computation of + the WITH query, but it can still give a net savings= if + each usage of the WITH query needs only a small par= t + of the WITH query's full output. + + + + A simple example of these rules is + +WITH w AS ( + SELECT * FROM big_table +) +SELECT * FROM w WHERE key =3D 123; + + This WITH query will be folded, producing the same + execution plan as + +SELECT * FROM big_table WHERE key =3D 123; + + In particular, if there's an index on key, + it will probably be used to fetch just the rows having key =3D + 123. On the other hand, in + +WITH w AS ( + SELECT * FROM big_table +) +SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key =3D w2.ref +WHERE w2.key =3D 123; + + the WITH query will be materialized, producing a + temporary copy of big_table that is then + joined with itself — without benefit of any index. This query + will be executed much more efficiently if written as + +WITH w AS MATERIALIZE OFF ( + SELECT * FROM big_table +) +SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key =3D w2.ref +WHERE w2.key =3D 123; + + so that the parent query's restrictions can be applied directly + to scans of big_table. + + + + An example where MATERIALIZE OFF could be + undesirable is + +WITH w AS ( + SELECT key, very_expensive_function(val) as f FROM some_table +) +SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f =3D w2.f; + + Here, materialization of the WITH query ensures + that very_expensive_function is evaluated only + once per table row, not twice. + + + The examples above only show WITH being used with SELECT, but it can be attached in the same way to INSERT, UPDATE, or DELE= TE. diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 4db8142..6d29cf5 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -72,7 +72,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressionand with_query is:= = - with_query_name [ ( column_name [, ...] ) ] AS ( = select | values | ins= ert | update = | delete ) + with_query_name [ ( column_name [, ...] ) ] AS [ = MATERIALIZE [ ON | OFF ] ] ( select | values | insert | update | delete ) = TABLE [ ONLY ] table_name = [ * ] @@ -93,7 +93,8 @@ TABLE [ ONLY ] table_na= me [ * ] These effectively serve as temporary tables that can be referenced in the FROM list. A WITH que= ry that is referenced more than once in FROM is - computed only once. + computed only once, + unless specified otherwise with MATERIALIZE OFF. (See below.= ) @@ -273,8 +274,17 @@ TABLE [ ONLY ] table= _name [ * ] = + The primary query and the WITH queries are all + (notionally) executed at the same time. This implies that the effect= s of + a data-modifying statement in WITH cannot be seen = from + other parts of the query, other than by reading its RETURNIN= G + output. If two such data-modifying statements attempt to modify the = same + row, the results are unspecified. + + + A key property of WITH queries is that they - are evaluated only once per execution of the primary query, + are normally evaluated only once per execution of the primary query, even if the primary query refers to them more than once. In particular, data-modifying statements are guaranteed to be executed once and only once, regardless of whether the primary query @@ -282,12 +292,35 @@ TABLE [ ONLY ] tabl= e_name [ * ] = - The primary query and the WITH queries are all - (notionally) executed at the same time. This implies that the effect= s of - a data-modifying statement in WITH cannot be seen = from - other parts of the query, other than by reading its RETURNIN= G - output. If two such data-modifying statements attempt to modify the = same - row, the results are unspecified. + However, a WITH query can be marked with + MATERIALIZE OFF to remove this guarantee. In that + case, the WITH query can be folded into the primar= y + query much as though it were a simple sub-SELECT i= n + the primary query's FROM clause. This results in + duplicate computations if the primary query refers to + that WITH query more than once; but if each such u= se + requires only a few rows of the WITH query's total + output, MATERIALIZE OFF can provide a net savings = by + allowing the queries to be optimized jointly. + MATERIALIZE OFF is ignored if it is attached to + a WITH query that is recursive or is not + side-effect-free (i.e., is not a plain SELECT + containing no volatile functions). + + + + By default, a side-effect-free WITH query is folde= d + into the primary query if it is used exactly once in the primary + query's FROM clause. This allows joint optimizati= on + of the two query levels in situations where that should be semantical= ly + invisible. However, such folding can be prevented by marking the + WITH query with MATERIALIZE [ ON ]. + That might be useful, for example, if the WITH que= ry + is being used as an optimization fence to prevent the planner from + choosing a bad plan. + PostgreSQL versions before v12 never did + such folding, so queries written for older versions might rely on + WITH to act as an optimization fence. = @@ -2087,6 +2120,11 @@ SELECT distributors.* WHERE distributors.name =3D '= Westward'; ROWS FROM( ... ) is an extension of the SQL standa= rd. + + + The MATERIALIZE ON/OFF options + of WITH are extensions of the SQL standard. + = diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index b44ead2..e15724b 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2536,6 +2536,7 @@ _copyCommonTableExpr(const CommonTableExpr *from) = COPY_STRING_FIELD(ctename); COPY_NODE_FIELD(aliascolnames); + COPY_SCALAR_FIELD(ctematerialized); COPY_NODE_FIELD(ctequery); COPY_LOCATION_FIELD(location); COPY_SCALAR_FIELD(cterecursive); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs= .c index 1e169e0..31499eb 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2791,6 +2791,7 @@ _equalCommonTableExpr(const CommonTableExpr *a, cons= t CommonTableExpr *b) { COMPARE_STRING_FIELD(ctename); COMPARE_NODE_FIELD(aliascolnames); + COMPARE_SCALAR_FIELD(ctematerialized); COMPARE_NODE_FIELD(ctequery); COMPARE_LOCATION_FIELD(location); COMPARE_SCALAR_FIELD(cterecursive); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index f97cf37..0f31fca 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2979,6 +2979,7 @@ _outCommonTableExpr(StringInfo str, const CommonTabl= eExpr *node) = WRITE_STRING_FIELD(ctename); WRITE_NODE_FIELD(aliascolnames); + WRITE_ENUM_FIELD(ctematerialized, CTEMaterialize); WRITE_NODE_FIELD(ctequery); WRITE_LOCATION_FIELD(location); WRITE_BOOL_FIELD(cterecursive); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 3b00277..5aa4224 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -418,6 +418,7 @@ _readCommonTableExpr(void) = READ_STRING_FIELD(ctename); READ_NODE_FIELD(aliascolnames); + READ_ENUM_FIELD(ctematerialized, CTEMaterialize); READ_NODE_FIELD(ctequery); READ_LOCATION_FIELD(location); READ_BOOL_FIELD(cterecursive); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/= plan/planner.c index b223972..5b0c90f 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -646,8 +646,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse, root->partColsUpdated =3D false; = /* - * If there is a WITH list, process each WITH query and build an initpla= n - * SubPlan structure for it. + * If there is a WITH list, process each WITH query and either convert i= t + * to RTE_SUBQUERY RTE(s) or build an initplan SubPlan structure for it. */ if (parse->cteList) SS_process_ctes(root); diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimize= r/plan/subselect.c index 915c6d0..c721054 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -57,6 +57,14 @@ typedef struct finalize_primnode_context Bitmapset *paramids; /* Non-local PARAM_EXEC paramids found */ } finalize_primnode_context; = +typedef struct inline_cte_walker_context +{ + const char *ctename; /* name and relative level of target CTE */ + int levelsup; + int refcount; /* number of remaining references */ + Query *ctequery; /* query to substitute */ +} inline_cte_walker_context; + = static Node *build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *su= broot, List *plan_params, @@ -75,6 +83,10 @@ static Node *convert_testexpr_mutator(Node *node, static bool subplan_is_hashable(Plan *plan); static bool testexpr_is_hashable(Node *testexpr); static bool hash_ok_operator(OpExpr *expr); +static bool contain_dml(Node *node); +static bool contain_dml_walker(Node *node, void *context); +static void inline_cte(PlannerInfo *root, CommonTableExpr *cte); +static bool inline_cte_walker(Node *node, inline_cte_walker_context *cont= ext); static bool simplify_EXISTS_query(PlannerInfo *root, Query *query); static Query *convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect, Node **testexpr, List **paramIds); @@ -804,10 +816,13 @@ hash_ok_operator(OpExpr *expr) /* * SS_process_ctes: process a query's WITH list * - * We plan each interesting WITH item and convert it to an initplan. + * Consider each CTE in the WITH list and either ignore it (if it's an + * unreferenced SELECT), "inline" it to create a regular sub-SELECT-in-FR= OM, + * or convert it to an initplan. + * * A side effect is to fill in root->cte_plan_ids with a list that * parallels root->parse->cteList and provides the subplan ID for - * each CTE's initplan. + * each CTE's initplan, or a dummy ID (-1) if we didn't make an initplan. */ void SS_process_ctes(PlannerInfo *root) @@ -839,6 +854,44 @@ SS_process_ctes(PlannerInfo *root) } = /* + * Consider inlining the CTE (creating RTE_SUBQUERY RTE(s)) instead of + * implementing it as a separately-planned CTE. + * + * We cannot inline if any of these conditions hold: + * + * 1. The user said not to (the CTEMaterializeAlways option). + * + * 2. The CTE is recursive. + * + * 3. The CTE has side-effects; this includes either not being a plain + * SELECT, or containing volatile functions. Inlining might change + * the side-effects, which would be bad. + * + * Otherwise, we have an option whether to inline or not. That should + * always be a win if there's just a single reference, but if the CTE + * is multiply-referenced then it's unclear: inlining adds duplicate + * computations, but the ability to absorb restrictions from the outer + * query level could outweigh that. We do not have nearly enough + * information at this point to tell whether that's true, so we let + * the user express a preference. Our default behavior is to inline + * only singly-referenced CTEs, but a CTE marked CTEMaterializeNever + * will be inlined even if multiply referenced. + */ + if ((cte->ctematerialized =3D=3D CTEMaterializeNever || + (cte->ctematerialized =3D=3D CTEMaterializeDefault && + cte->cterefcount =3D=3D 1)) && + !cte->cterecursive && + cmdType =3D=3D CMD_SELECT && + !contain_dml(cte->ctequery) && + !contain_volatile_functions(cte->ctequery)) + { + inline_cte(root, cte); + /* Make a dummy entry in cte_plan_ids */ + root->cte_plan_ids =3D lappend_int(root->cte_plan_ids, -1); + continue; + } + + /* * Copy the source Query node. Probably not necessary, but let's keep * this similar to make_subplan. */ @@ -935,6 +988,127 @@ SS_process_ctes(PlannerInfo *root) } = /* + * contain_dml: is any subquery not a plain SELECT? + * + * We reject SELECT FOR UPDATE/SHARE as well as INSERT etc. + */ +static bool +contain_dml(Node *node) +{ + return contain_dml_walker(node, NULL); +} + +static bool +contain_dml_walker(Node *node, void *context) +{ + if (node =3D=3D NULL) + return false; + if (IsA(node, Query)) + { + Query *query =3D (Query *) node; + + if (query->commandType !=3D CMD_SELECT || + query->rowMarks !=3D NIL) + return true; + + return query_tree_walker(query, contain_dml_walker, context, 0); + } + return expression_tree_walker(node, contain_dml_walker, context); +} + +/* + * inline_cte: convert RTE_CTE references to given CTE into RTE_SUBQUERYs + */ +static void +inline_cte(PlannerInfo *root, CommonTableExpr *cte) +{ + struct inline_cte_walker_context context; + + context.ctename =3D cte->ctename; + /* Start at levelsup =3D -1 because we'll immediately increment it */ + context.levelsup =3D -1; + context.refcount =3D cte->cterefcount; + context.ctequery =3D castNode(Query, cte->ctequery); + + (void) inline_cte_walker((Node *) root->parse, &context); + + /* Assert we replaced all references */ + Assert(context.refcount =3D=3D 0); +} + +static bool +inline_cte_walker(Node *node, inline_cte_walker_context *context) +{ + if (node =3D=3D NULL) + return false; + if (IsA(node, Query)) + { + Query *query =3D (Query *) node; + + context->levelsup++; + + /* + * Visit the query's RTE nodes after their contents; otherwise + * query_tree_walker would descend into the newly inlined CTE query, + * which we don't want. + */ + (void) query_tree_walker(query, inline_cte_walker, context, + QTW_EXAMINE_RTES_AFTER); + + context->levelsup--; + + return false; + } + else if (IsA(node, RangeTblEntry)) + { + RangeTblEntry *rte =3D (RangeTblEntry *) node; + + if (rte->rtekind =3D=3D RTE_CTE && + strcmp(rte->ctename, context->ctename) =3D=3D 0 && + rte->ctelevelsup =3D=3D context->levelsup) + { + /* + * Found a reference to replace. Generate a copy of the CTE query + * with appropriate level adjustment for outer references (e.g., + * to other CTEs). + */ + Query *newquery =3D copyObject(context->ctequery); + + if (context->levelsup > 0) + IncrementVarSublevelsUp((Node *) newquery, context->levelsup, 1); + + /* + * Convert the RTE_CTE RTE into a RTE_SUBQUERY. + * + * Historically, a FOR UPDATE clause has been treated as extending + * into views and subqueries, but not into CTEs. We preserve this + * distinction by not trying to push rowmarks into the new + * subquery. + */ + rte->rtekind =3D RTE_SUBQUERY; + rte->subquery =3D newquery; + rte->security_barrier =3D false; + + /* Zero out CTE-specific fields */ + rte->ctename =3D NULL; + rte->ctelevelsup =3D 0; + rte->self_reference =3D false; + rte->coltypes =3D NIL; + rte->coltypmods =3D NIL; + rte->colcollations =3D NIL; + + /* Count the number of replacements we've done */ + context->refcount--; + } + + return false; + } + + return expression_tree_walker(node, inline_cte_walker, context); +} + + +/* * convert_ANY_sublink_to_join: try to convert an ANY SubLink to a join * * The caller has found an ANY SubLink at the top level of one of the que= ry's diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c1faf41..5f3745a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -479,7 +479,7 @@ static Node *makeRecursiveViewSelect(char *relname, Li= st *aliases, Node *query); %type row explicit_row implicit_row type_list array_expr_list %type case_expr case_arg when_clause case_default %type when_clause_list -%type sub_type +%type sub_type cte_opts %type NumericOnly %type NumericOnly_list %type alias_clause opt_alias_clause @@ -11291,6 +11291,8 @@ simple_select: * * We don't currently support the SEARCH or CYCLE clause. * + * Postgres-specific options can be inserted after AS. + * * Recognizing WITH_LA here allows a CTE to be named TIME or ORDINALITY. */ with_clause: @@ -11322,17 +11324,50 @@ cte_list: | cte_list ',' common_table_expr { $$ =3D lappend($1, $3); } ; = -common_table_expr: name opt_name_list AS '(' PreparableStmt ')' +common_table_expr: name opt_name_list AS cte_opts '(' PreparableStmt ')' { CommonTableExpr *n =3D makeNode(CommonTableExpr); n->ctename =3D $1; n->aliascolnames =3D $2; - n->ctequery =3D $5; + n->ctematerialized =3D $4; + n->ctequery =3D $6; n->location =3D @1; $$ =3D (Node *) n; } ; = +/* + * In general, we could allow arbitrary options for a CTE; for the moment + * this piggybacks on EXPLAIN's option productions. Since only one optio= n + * is actually supported, just reduce the list to an enum result immediat= ely. + */ +cte_opts: explain_option_list + { + int matopt =3D CTEMaterializeDefault; + ListCell *option; + foreach(option, $1) + { + DefElem *defel =3D (DefElem *) lfirst(option); + + if (strcmp(defel->defname, "materialize") =3D=3D 0) + { + if (defGetBoolean(defel)) + matopt =3D CTEMaterializeAlways; + else + matopt =3D CTEMaterializeNever; + } + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized WITH option \"%s\"", + defel->defname), + parser_errposition(defel->location))); + } + $$ =3D matopt; + } + | /*EMPTY*/ { $$ =3D CTEMaterializeDefault; } + ; + opt_with_clause: with_clause { $$ =3D $1; } | /*EMPTY*/ { $$ =3D NULL; } @@ -16214,6 +16249,7 @@ makeRecursiveViewSelect(char *relname, List *alias= es, Node *query) /* create common table expression */ cte->ctename =3D relname; cte->aliascolnames =3D aliases; + cte->ctematerialized =3D CTEMaterializeDefault; cte->ctequery =3D query; cte->location =3D -1; = diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/rul= eutils.c index 17a28c2..4103602 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5166,7 +5166,19 @@ get_with_clause(Query *query, deparse_context *cont= ext) } appendStringInfoChar(buf, ')'); } - appendStringInfoString(buf, " AS ("); + appendStringInfoString(buf, " AS "); + switch (cte->ctematerialized) + { + case CTEMaterializeDefault: + break; + case CTEMaterializeAlways: + appendStringInfoString(buf, "MATERIALIZE ON "); + break; + case CTEMaterializeNever: + appendStringInfoString(buf, "MATERIALIZE OFF "); + break; + } + appendStringInfoChar(buf, '('); if (PRETTY_INDENT(context)) appendContextKeyword(context, "", 0, 0, 0); get_query_def((Query *) cte->ctequery, buf, context->namespaces, NULL, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes= .h index 2fe14d7..14bea39 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1402,11 +1402,19 @@ typedef struct OnConflictClause * * We don't currently support the SEARCH or CYCLE clause. */ +typedef enum CTEMaterialize +{ + CTEMaterializeDefault, /* no option specified */ + CTEMaterializeAlways, /* MATERIALIZE ON */ + CTEMaterializeNever /* MATERIALIZE OFF */ +} CTEMaterialize; + typedef struct CommonTableExpr { NodeTag type; char *ctename; /* query name (never qualified) */ List *aliascolnames; /* optional list of column names */ + CTEMaterialize ctematerialized; /* is this an optimization fence? */ /* SelectStmt/InsertStmt/etc before parse analysis, Query afterwards: */ Node *ctequery; /* the CTE's subquery */ int location; /* token location, or -1 if unknown */ diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/= expected/rowsecurity.out index 1d12b01..8971f88 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -2179,7 +2179,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test; Filter: (((a % 2) =3D 0) AND f_leak(b)) (2 rows) = -PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) S= ELECT * FROM q,z2; +PREPARE plancache_test2 AS WITH q AS MATERIALIZE ON (SELECT * FROM z1 WHE= RE f_leak(b)) SELECT * FROM q,z2; EXPLAIN (COSTS OFF) EXECUTE plancache_test2; QUERY PLAN = ------------------------------------------------- @@ -2192,7 +2192,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test2; -> Seq Scan on z2 (7 rows) = -PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z= 1 WHERE f_leak(z1.b); +PREPARE plancache_test3 AS WITH q AS MATERIALIZE ON (SELECT * FROM z2) SE= LECT * FROM q,z1 WHERE f_leak(z1.b); EXPLAIN (COSTS OFF) EXECUTE plancache_test3; QUERY PLAN = ----------------------------------------------------- @@ -2826,7 +2826,7 @@ ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; GRANT ALL ON t1 TO regress_rls_bob; INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x); SET SESSION AUTHORIZATION regress_rls_bob; -WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1; +WITH cte1 AS MATERIALIZE ON (SELECT * FROM t1 WHERE f_leak(b)) SELECT * F= ROM cte1; NOTICE: f_leak =3D> cfcd208495d565ef66e7dff9f98764da NOTICE: f_leak =3D> c81e728d9d4c2f636f067f89cc14862c NOTICE: f_leak =3D> a87ff679a2f3e71d9181a67b7542122c @@ -2853,7 +2853,8 @@ NOTICE: f_leak =3D> 98f13708210194c475687be6106a3b8= 4 20 | 98f13708210194c475687be6106a3b84 (11 rows) = -EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELEC= T * FROM cte1; +EXPLAIN (COSTS OFF) +WITH cte1 AS MATERIALIZE ON (SELECT * FROM t1 WHERE f_leak(b)) SELECT * F= ROM cte1; QUERY PLAN = ------------------------------------------------- CTE Scan on cte1 diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/exp= ected/rowtypes.out index 054faabb..e02f99a 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1048,7 +1048,7 @@ from (values (1,row(1,2)), (1,row(null,null)), (1,nu= ll), (6 rows) = explain (verbose, costs off) -with r(a,b) as +with r(a,b) as materialize on (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) select r, r is null as isnull, r is not null as isnotnull from r; @@ -1061,7 +1061,7 @@ select r, r is null as isnull, r is not null as isno= tnull from r; Output: "*VALUES*".column1, "*VALUES*".column2 (5 rows) = -with r(a,b) as +with r(a,b) as materialize on (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) select r, r is null as isnull, r is not null as isnotnull from r; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expect= ed/rules.out index 2c8e21b..2137abd 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3110,7 +3110,7 @@ explain (costs off) INSERT INTO hats VALUES ('h8', '= forbidden') RETURNING *; (5 rows) = -- ensure upserting into a rule, with a CTE (different offsets!) works -WITH data(hat_name, hat_color) AS ( +WITH data(hat_name, hat_color) AS MATERIALIZE ON ( VALUES ('h8', 'green'), ('h9', 'blue'), ('h7', 'forbidden') @@ -3124,7 +3124,8 @@ RETURNING *; h9 | blue = (2 rows) = -EXPLAIN (costs off) WITH data(hat_name, hat_color) AS ( +EXPLAIN (costs off) +WITH data(hat_name, hat_color) AS MATERIALIZE ON ( VALUES ('h8', 'green'), ('h9', 'blue'), ('h7', 'forbidden') diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/ex= pected/subselect.out index a54b4a5..a7ccbc1 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1154,3 +1154,159 @@ fetch backward all in c1; (2 rows) = commit; +-- +-- Tests for CTE inlining behavior +-- +-- Basic subquery that can be inlined +explain (verbose, costs off) +with x as (select * from (select f1 from subselect_tbl) ss) +select * from x where f1 =3D 1; + QUERY PLAN = +---------------------------------- + Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1 + Filter: (subselect_tbl.f1 =3D 1) +(3 rows) + +-- Explicitly request materialization +explain (verbose, costs off) +with x as materialize on (select * from (select f1 from subselect_tbl) ss= ) +select * from x where f1 =3D 1; + QUERY PLAN = +------------------------------------------ + CTE Scan on x + Output: x.f1 + Filter: (x.f1 =3D 1) + CTE x + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1 +(6 rows) + +-- Stable functions are safe to inline +explain (verbose, costs off) +with x as (select * from (select f1, now() from subselect_tbl) ss) +select * from x where f1 =3D 1; + QUERY PLAN = +----------------------------------- + Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, now() + Filter: (subselect_tbl.f1 =3D 1) +(3 rows) + +-- Volatile functions prevent inlining +explain (verbose, costs off) +with x as (select * from (select f1, random() from subselect_tbl) ss) +select * from x where f1 =3D 1; + QUERY PLAN = +---------------------------------------------- + CTE Scan on x + Output: x.f1, x.random + Filter: (x.f1 =3D 1) + CTE x + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, random() +(6 rows) + +-- SELECT FOR UPDATE cannot be inlined +explain (verbose, costs off) +with x as (select * from (select f1 from subselect_tbl for update) ss) +select * from x where f1 =3D 1; + QUERY PLAN = +-------------------------------------------------------------------- + CTE Scan on x + Output: x.f1 + Filter: (x.f1 =3D 1) + CTE x + -> Subquery Scan on ss + Output: ss.f1 + -> LockRows + Output: subselect_tbl.f1, subselect_tbl.ctid + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, subselect_tbl.ctid +(10 rows) + +-- Multiply-referenced CTEs are inlined only when requested +explain (verbose, costs off) +with x as (select * from (select f1, now() as n from subselect_tbl) ss) +select * from x, x x2 where x.n =3D x2.n; + QUERY PLAN = +------------------------------------------- + Merge Join + Output: x.f1, x.n, x2.f1, x2.n + Merge Cond: (x.n =3D x2.n) + CTE x + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, now() + -> Sort + Output: x.f1, x.n + Sort Key: x.n + -> CTE Scan on x + Output: x.f1, x.n + -> Sort + Output: x2.f1, x2.n + Sort Key: x2.n + -> CTE Scan on x x2 + Output: x2.f1, x2.n +(16 rows) + +explain (verbose, costs off) +with x as materialize off (select * from (select f1, now() as n from subs= elect_tbl) ss) +select * from x, x x2 where x.n =3D x2.n; + QUERY PLAN = = +-------------------------------------------------------------------------= --- + Result + Output: subselect_tbl.f1, now(), subselect_tbl_1.f1, now() + One-Time Filter: (now() =3D now()) + -> Nested Loop + Output: subselect_tbl.f1, subselect_tbl_1.f1 + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.= f3 + -> Materialize + Output: subselect_tbl_1.f1 + -> Seq Scan on public.subselect_tbl subselect_tbl_1 + Output: subselect_tbl_1.f1 +(11 rows) + +-- Check handling of outer references +explain (verbose, costs off) +with x as (select * from int4_tbl) +select * from (with y as (select * from x) select * from y) ss; + QUERY PLAN = +----------------------------- + Seq Scan on public.int4_tbl + Output: int4_tbl.f1 +(2 rows) + +explain (verbose, costs off) +with x as materialize on (select * from int4_tbl) +select * from (with y as (select * from x) select * from y) ss; + QUERY PLAN = +------------------------------------- + CTE Scan on x + Output: x.f1 + CTE x + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 +(5 rows) + +-- Ensure that we inline the currect CTE when there are +-- multiple CTEs with the same name +explain (verbose, costs off) +with x as (select 1 as y) +select * from (with x as (select 2 as y) select * from x) ss; + QUERY PLAN = +------------- + Result + Output: 2 +(2 rows) + +-- Row marks are not pushed into CTEs +explain (verbose, costs off) +with x as (select * from subselect_tbl) +select * from x for update; + QUERY PLAN = +---------------------------------------------------------------- + Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3 +(2 rows) + diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/r= owsecurity.sql index 38e9b38..589e13f 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -840,10 +840,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b)= ; PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b); EXPLAIN (COSTS OFF) EXECUTE plancache_test; = -PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) S= ELECT * FROM q,z2; +PREPARE plancache_test2 AS WITH q AS MATERIALIZE ON (SELECT * FROM z1 WHE= RE f_leak(b)) SELECT * FROM q,z2; EXPLAIN (COSTS OFF) EXECUTE plancache_test2; = -PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z= 1 WHERE f_leak(z1.b); +PREPARE plancache_test3 AS WITH q AS MATERIALIZE ON (SELECT * FROM z2) SE= LECT * FROM q,z1 WHERE f_leak(z1.b); EXPLAIN (COSTS OFF) EXECUTE plancache_test3; = SET ROLE regress_rls_group1; @@ -1071,8 +1071,9 @@ INSERT INTO t1 (SELECT x, md5(x::text) FROM generate= _series(0,20) x); = SET SESSION AUTHORIZATION regress_rls_bob; = -WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1; -EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELEC= T * FROM cte1; +WITH cte1 AS MATERIALIZE ON (SELECT * FROM t1 WHERE f_leak(b)) SELECT * F= ROM cte1; +EXPLAIN (COSTS OFF) +WITH cte1 AS MATERIALIZE ON (SELECT * FROM t1 WHERE f_leak(b)) SELECT * F= ROM cte1; = WITH cte1 AS (UPDATE t1 SET a =3D a + 1 RETURNING *) SELECT * FROM cte1; = --fail WITH cte1 AS (UPDATE t1 SET a =3D a RETURNING *) SELECT * FROM cte1; --ok diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowt= ypes.sql index 454d462..8eda763 100644 --- a/src/test/regress/sql/rowtypes.sql +++ b/src/test/regress/sql/rowtypes.sql @@ -434,12 +434,12 @@ from (values (1,row(1,2)), (1,row(null,null)), (1,nu= ll), (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b)= ; = explain (verbose, costs off) -with r(a,b) as +with r(a,b) as materialize on (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) select r, r is null as isnull, r is not null as isnotnull from r; = -with r(a,b) as +with r(a,b) as materialize on (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) select r, r is null as isnull, r is not null as isnotnull from r; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.s= ql index f4ee30e..dc7317c 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1132,7 +1132,7 @@ SELECT tablename, rulename, definition FROM pg_rules explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING= *; = -- ensure upserting into a rule, with a CTE (different offsets!) works -WITH data(hat_name, hat_color) AS ( +WITH data(hat_name, hat_color) AS MATERIALIZE ON ( VALUES ('h8', 'green'), ('h9', 'blue'), ('h7', 'forbidden') @@ -1140,7 +1140,8 @@ WITH data(hat_name, hat_color) AS ( INSERT INTO hats SELECT * FROM data RETURNING *; -EXPLAIN (costs off) WITH data(hat_name, hat_color) AS ( +EXPLAIN (costs off) +WITH data(hat_name, hat_color) AS MATERIALIZE ON ( VALUES ('h8', 'green'), ('h9', 'blue'), ('h7', 'forbidden') diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/sub= select.sql index 843f511..336cf61 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -625,3 +625,61 @@ move forward all in c1; fetch backward all in c1; = commit; + +-- +-- Tests for CTE inlining behavior +-- + +-- Basic subquery that can be inlined +explain (verbose, costs off) +with x as (select * from (select f1 from subselect_tbl) ss) +select * from x where f1 =3D 1; + +-- Explicitly request materialization +explain (verbose, costs off) +with x as materialize on (select * from (select f1 from subselect_tbl) ss= ) +select * from x where f1 =3D 1; + +-- Stable functions are safe to inline +explain (verbose, costs off) +with x as (select * from (select f1, now() from subselect_tbl) ss) +select * from x where f1 =3D 1; + +-- Volatile functions prevent inlining +explain (verbose, costs off) +with x as (select * from (select f1, random() from subselect_tbl) ss) +select * from x where f1 =3D 1; + +-- SELECT FOR UPDATE cannot be inlined +explain (verbose, costs off) +with x as (select * from (select f1 from subselect_tbl for update) ss) +select * from x where f1 =3D 1; + +-- Multiply-referenced CTEs are inlined only when requested +explain (verbose, costs off) +with x as (select * from (select f1, now() as n from subselect_tbl) ss) +select * from x, x x2 where x.n =3D x2.n; + +explain (verbose, costs off) +with x as materialize off (select * from (select f1, now() as n from subs= elect_tbl) ss) +select * from x, x x2 where x.n =3D x2.n; + +-- Check handling of outer references +explain (verbose, costs off) +with x as (select * from int4_tbl) +select * from (with y as (select * from x) select * from y) ss; + +explain (verbose, costs off) +with x as materialize on (select * from int4_tbl) +select * from (with y as (select * from x) select * from y) ss; + +-- Ensure that we inline the currect CTE when there are +-- multiple CTEs with the same name +explain (verbose, costs off) +with x as (select 1 as y) +select * from (with x as (select 2 as y) select * from x) ss; + +-- Row marks are not pushed into CTEs +explain (verbose, costs off) +with x as (select * from subselect_tbl) +select * from x for update; ------- =_aaaaaaaaaa0--