Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit608b167

Browse files
committed
Allow user control of CTE materialization, and change the default behavior.
Historically we've always materialized the full output of a CTE query,treating WITH as an optimization fence (so that, for example, restrictionsfrom the outer query cannot be pushed into it). This is appropriate whenthe CTE query is INSERT/UPDATE/DELETE, or is recursive; but when the CTEquery is non-recursive and side-effect-free, there's no hazard of changingthe query results by pushing restrictions down.Another argument for materialization is that it can avoid duplicatecomputation of an expensive WITH query --- but that only applies ifthe WITH query is called more than once in the outer query. Even thenit could still be a net loss, if each call has restrictions thatwould allow just a small part of the WITH query to be computed.Hence, let's change the behavior for WITH queries that are non-recursiveand side-effect-free. By default, we will inline them into the outerquery (removing the optimization fence) if they are called just once.If they are called more than once, we will keep the old behavior bydefault, but the user can override this and force inlining by specifyingNOT MATERIALIZED. Lastly, the user can force the old behavior byspecifying MATERIALIZED; this would mainly be useful when the query haddeliberately been employing WITH as an optimization fence to prevent apoor choice of plan.Andreas Karlsson, Andrew Gierth, David FetterDiscussion:https://postgr.es/m/87sh48ffhb.fsf@news-spur.riddles.org.uk
1 parent79730e2 commit608b167

File tree

23 files changed

+580
-44
lines changed

23 files changed

+580
-44
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2927,6 +2927,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
29272927

29282928
/* we store the string name because RTE_CTE RTEs need it */
29292929
APP_JUMB_STRING(cte->ctename);
2930+
APP_JUMB(cte->ctematerialized);
29302931
JumbleQuery(jstate,castNode(Query,cte->ctequery));
29312932
}
29322933
break;

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1888,7 +1888,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
18881888

18891889
-- join in CTE
18901890
EXPLAIN (VERBOSE, COSTS OFF)
1891-
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 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
1891+
WITH t (c1_1, c1_3, c2_1) ASMATERIALIZED(SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
18921892
QUERY PLAN
18931893
-------------------------------------------------------------------------------------------------------------------------------------
18941894
Limit
@@ -1905,7 +1905,7 @@ WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2
19051905
Output: t.c1_1, t.c2_1, t.c1_3
19061906
(12 rows)
19071907

1908-
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 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
1908+
WITH t (c1_1, c1_3, c2_1) ASMATERIALIZED(SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
19091909
c1_1 | c2_1
19101910
------+------
19111911
101 | 101

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -493,8 +493,8 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
493493
SELECTt1.c1,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1)ORDER BYt1.c3,t1.c1 OFFSET100LIMIT10 FOR SHARE;
494494
-- join in CTE
495495
EXPLAIN (VERBOSE, COSTS OFF)
496-
WITH t (c1_1, c1_3, c2_1)AS (SELECTt1.c1,t1.c3,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1))SELECT c1_1, c2_1FROM tORDER BY c1_3, c1_1 OFFSET100LIMIT10;
497-
WITH t (c1_1, c1_3, c2_1)AS (SELECTt1.c1,t1.c3,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1))SELECT c1_1, c2_1FROM tORDER BY c1_3, c1_1 OFFSET100LIMIT10;
496+
WITH t (c1_1, c1_3, c2_1)ASMATERIALIZED(SELECTt1.c1,t1.c3,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1))SELECT c1_1, c2_1FROM tORDER BY c1_3, c1_1 OFFSET100LIMIT10;
497+
WITH t (c1_1, c1_3, c2_1)ASMATERIALIZED(SELECTt1.c1,t1.c3,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1))SELECT c1_1, c2_1FROM tORDER BY c1_3, c1_1 OFFSET100LIMIT10;
498498
-- ctid with whole-row reference
499499
EXPLAIN (VERBOSE, COSTS OFF)
500500
SELECTt1.ctid, t1, t2,t1.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1)ORDER BYt1.c3,t1.c1 OFFSET100LIMIT10;

‎doc/src/sgml/queries.sgml

Lines changed: 78 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2199,22 +2199,94 @@ SELECT n FROM t LIMIT 100;
21992199
</para>
22002200

22012201
<para>
2202-
A useful property of <literal>WITH</literal> queries is that they are evaluated
2203-
only once per execution of the parent query, even if they are referred to
2204-
more than once by the parent query or sibling <literal>WITH</literal> queries.
2202+
A useful property of <literal>WITH</literal> queries is that they are
2203+
normally evaluated only once per execution of the parent query, even if
2204+
they are referred to more than once by the parent query or
2205+
sibling <literal>WITH</literal> queries.
22052206
Thus, expensive calculations that are needed in multiple places can be
22062207
placed within a <literal>WITH</literal> query to avoid redundant work. Another
22072208
possible application is to prevent unwanted multiple evaluations of
22082209
functions with side-effects.
2209-
However, the other side of this coin is that the optimizer is less able to
2210-
push restrictions from the parent query down into a <literal>WITH</literal> query
2211-
than an ordinary subquery. The <literal>WITH</literal> query will generally be
2210+
However, the other side of this coin is that the optimizer is not able to
2211+
push restrictions from the parent query down into a multiply-referenced
2212+
<literal>WITH</literal> query, since that might affect all uses of the
2213+
<literal>WITH</literal> query's output when it should affect only one.
2214+
The multiply-referenced <literal>WITH</literal> query will be
22122215
evaluated as written, without suppression of rows that the parent query
22132216
might discard afterwards. (But, as mentioned above, evaluation might stop
22142217
early if the reference(s) to the query demand only a limited number of
22152218
rows.)
22162219
</para>
22172220

2221+
<para>
2222+
However, if a <literal>WITH</literal> query is non-recursive and
2223+
side-effect-free (that is, it is a <literal>SELECT</literal> containing
2224+
no volatile functions) then it can be folded into the parent query,
2225+
allowing joint optimization of the two query levels. By default, this
2226+
happens if the parent query references the <literal>WITH</literal> query
2227+
just once, but not if it references the <literal>WITH</literal> query
2228+
more than once. You can override that decision by
2229+
specifying <literal>MATERIALIZED</literal> to force separate calculation
2230+
of the <literal>WITH</literal> query, or by specifying <literal>NOT
2231+
MATERIALIZED</literal> to force it to be merged into the parent query.
2232+
The latter choice risks duplicate computation of
2233+
the <literal>WITH</literal> query, but it can still give a net savings if
2234+
each usage of the <literal>WITH</literal> query needs only a small part
2235+
of the <literal>WITH</literal> query's full output.
2236+
</para>
2237+
2238+
<para>
2239+
A simple example of these rules is
2240+
<programlisting>
2241+
WITH w AS (
2242+
SELECT * FROM big_table
2243+
)
2244+
SELECT * FROM w WHERE key = 123;
2245+
</programlisting>
2246+
This <literal>WITH</literal> query will be folded, producing the same
2247+
execution plan as
2248+
<programlisting>
2249+
SELECT * FROM big_table WHERE key = 123;
2250+
</programlisting>
2251+
In particular, if there's an index on <structfield>key</structfield>,
2252+
it will probably be used to fetch just the rows having <literal>key =
2253+
123</literal>. On the other hand, in
2254+
<programlisting>
2255+
WITH w AS (
2256+
SELECT * FROM big_table
2257+
)
2258+
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
2259+
WHERE w2.key = 123;
2260+
</programlisting>
2261+
the <literal>WITH</literal> query will be materialized, producing a
2262+
temporary copy of <structname>big_table</structname> that is then
2263+
joined with itself &mdash; without benefit of any index. This query
2264+
will be executed much more efficiently if written as
2265+
<programlisting>
2266+
WITH w AS NOT MATERIALIZED (
2267+
SELECT * FROM big_table
2268+
)
2269+
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
2270+
WHERE w2.key = 123;
2271+
</programlisting>
2272+
so that the parent query's restrictions can be applied directly
2273+
to scans of <structname>big_table</structname>.
2274+
</para>
2275+
2276+
<para>
2277+
An example where <literal>NOT MATERIALIZED</literal> could be
2278+
undesirable is
2279+
<programlisting>
2280+
WITH w AS (
2281+
SELECT key, very_expensive_function(val) as f FROM some_table
2282+
)
2283+
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
2284+
</programlisting>
2285+
Here, materialization of the <literal>WITH</literal> query ensures
2286+
that <function>very_expensive_function</function> is evaluated only
2287+
once per table row, not twice.
2288+
</para>
2289+
22182290
<para>
22192291
The examples above only show <literal>WITH</literal> being used with
22202292
<command>SELECT</command>, but it can be attached in the same way to

‎doc/src/sgml/ref/select.sgml

Lines changed: 48 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -72,7 +72,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
7272

7373
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
7474

75-
<replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
75+
<replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS[ [ NOT ] MATERIALIZED ]( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
7676

7777
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
7878
</synopsis>
@@ -93,7 +93,8 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
9393
These effectively serve as temporary tables that can be referenced
9494
in the <literal>FROM</literal> list. A <literal>WITH</literal> query
9595
that is referenced more than once in <literal>FROM</literal> is
96-
computed only once.
96+
computed only once,
97+
unless specified otherwise with <literal>NOT MATERIALIZED</literal>.
9798
(See <xref linkend="sql-with" endterm="sql-with-title"/> below.)
9899
</para>
99100
</listitem>
@@ -272,22 +273,54 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
272273
that are earlier in the <literal>WITH</literal> list.
273274
</para>
274275

276+
<para>
277+
The primary query and the <literal>WITH</literal> queries are all
278+
(notionally) executed at the same time. This implies that the effects of
279+
a data-modifying statement in <literal>WITH</literal> cannot be seen from
280+
other parts of the query, other than by reading its <literal>RETURNING</literal>
281+
output. If two such data-modifying statements attempt to modify the same
282+
row, the results are unspecified.
283+
</para>
284+
275285
<para>
276286
A key property of <literal>WITH</literal> queries is that they
277-
are evaluated only once per execution of the primary query,
287+
arenormallyevaluated only once per execution of the primary query,
278288
even if the primary query refers to them more than once.
279289
In particular, data-modifying statements are guaranteed to be
280290
executed once and only once, regardless of whether the primary query
281291
reads all or any of their output.
282292
</para>
283293

284294
<para>
285-
The primary query and the <literal>WITH</literal> queries are all
286-
(notionally) executed at the same time. This implies that the effects of
287-
a data-modifying statement in <literal>WITH</literal> cannot be seen from
288-
other parts of the query, other than by reading its <literal>RETURNING</literal>
289-
output. If two such data-modifying statements attempt to modify the same
290-
row, the results are unspecified.
295+
However, a <literal>WITH</literal> query can be marked
296+
<literal>NOT MATERIALIZED</literal> to remove this guarantee. In that
297+
case, the <literal>WITH</literal> query can be folded into the primary
298+
query much as though it were a simple sub-<literal>SELECT</literal> in
299+
the primary query's <literal>FROM</literal> clause. This results in
300+
duplicate computations if the primary query refers to
301+
that <literal>WITH</literal> query more than once; but if each such use
302+
requires only a few rows of the <literal>WITH</literal> query's total
303+
output, <literal>NOT MATERIALIZED</literal> can provide a net savings by
304+
allowing the queries to be optimized jointly.
305+
<literal>NOT MATERIALIZED</literal> is ignored if it is attached to
306+
a <literal>WITH</literal> query that is recursive or is not
307+
side-effect-free (i.e., is not a plain <literal>SELECT</literal>
308+
containing no volatile functions).
309+
</para>
310+
311+
<para>
312+
By default, a side-effect-free <literal>WITH</literal> query is folded
313+
into the primary query if it is used exactly once in the primary
314+
query's <literal>FROM</literal> clause. This allows joint optimization
315+
of the two query levels in situations where that should be semantically
316+
invisible. However, such folding can be prevented by marking the
317+
<literal>WITH</literal> query as <literal>MATERIALIZED</literal>.
318+
That might be useful, for example, if the <literal>WITH</literal> query
319+
is being used as an optimization fence to prevent the planner from
320+
choosing a bad plan.
321+
<productname>PostgreSQL</productname> versions before v12 never did
322+
such folding, so queries written for older versions might rely on
323+
<literal>WITH</literal> to act as an optimization fence.
291324
</para>
292325

293326
<para>
@@ -2087,6 +2120,12 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
20872120
<para>
20882121
<literal>ROWS FROM( ... )</literal> is an extension of the SQL standard.
20892122
</para>
2123+
2124+
<para>
2125+
The <literal>MATERIALIZED</literal> and <literal>NOT
2126+
MATERIALIZED</literal> options of <literal>WITH</literal> are extensions
2127+
of the SQL standard.
2128+
</para>
20902129
</refsect2>
20912130

20922131
</refsect1>

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2536,6 +2536,7 @@ _copyCommonTableExpr(const CommonTableExpr *from)
25362536

25372537
COPY_STRING_FIELD(ctename);
25382538
COPY_NODE_FIELD(aliascolnames);
2539+
COPY_SCALAR_FIELD(ctematerialized);
25392540
COPY_NODE_FIELD(ctequery);
25402541
COPY_LOCATION_FIELD(location);
25412542
COPY_SCALAR_FIELD(cterecursive);

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2791,6 +2791,7 @@ _equalCommonTableExpr(const CommonTableExpr *a, const CommonTableExpr *b)
27912791
{
27922792
COMPARE_STRING_FIELD(ctename);
27932793
COMPARE_NODE_FIELD(aliascolnames);
2794+
COMPARE_SCALAR_FIELD(ctematerialized);
27942795
COMPARE_NODE_FIELD(ctequery);
27952796
COMPARE_LOCATION_FIELD(location);
27962797
COMPARE_SCALAR_FIELD(cterecursive);

‎src/backend/nodes/outfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2989,6 +2989,7 @@ _outCommonTableExpr(StringInfo str, const CommonTableExpr *node)
29892989

29902990
WRITE_STRING_FIELD(ctename);
29912991
WRITE_NODE_FIELD(aliascolnames);
2992+
WRITE_ENUM_FIELD(ctematerialized,CTEMaterialize);
29922993
WRITE_NODE_FIELD(ctequery);
29932994
WRITE_LOCATION_FIELD(location);
29942995
WRITE_BOOL_FIELD(cterecursive);

‎src/backend/nodes/readfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -418,6 +418,7 @@ _readCommonTableExpr(void)
418418

419419
READ_STRING_FIELD(ctename);
420420
READ_NODE_FIELD(aliascolnames);
421+
READ_ENUM_FIELD(ctematerialized,CTEMaterialize);
421422
READ_NODE_FIELD(ctequery);
422423
READ_LOCATION_FIELD(location);
423424
READ_BOOL_FIELD(cterecursive);

‎src/backend/optimizer/plan/planner.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -646,8 +646,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
646646
root->partColsUpdated= false;
647647

648648
/*
649-
* If there is a WITH list, process each WITH query andbuild an initplan
650-
* SubPlan structure for it.
649+
* If there is a WITH list, process each WITH query andeither convert it
650+
*to RTE_SUBQUERY RTE(s) or build an initplanSubPlan structure for it.
651651
*/
652652
if (parse->cteList)
653653
SS_process_ctes(root);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp