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

Commit5b89ef3

Browse files
committed
Add an 'enable_material' GUC.
The logic for determining whether to materialize has been significantlyoverhauled for 9.0. In case there should be any doubt about whethermaterialization is a win in any particular case, this should provide aconvenient way of seeing what happens without it; but even with enable_materialturned off, we still materialize in cases where it is required forcorrectness.Thanks to Tom Lane for the review.
1 parent9287567 commit5b89ef3

File tree

8 files changed

+57
-15
lines changed

8 files changed

+57
-15
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.267 2010/04/16 21:46:07 rhaas Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.268 2010/04/19 00:55:25 rhaas Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -2020,6 +2020,22 @@ SET ENABLE_SEQSCAN TO OFF;
20202020
</listitem>
20212021
</varlistentry>
20222022

2023+
<varlistentry id="guc-enable-material" xreflabel="enable_material">
2024+
<term><varname>enable_material</varname> (<type>boolean</type>)</term>
2025+
<indexterm>
2026+
<primary><varname>enable_material</> configuration parameter</primary>
2027+
</indexterm>
2028+
<listitem>
2029+
<para>
2030+
Enables or disables the query planner's use of materialization.
2031+
It is impossible to suppress materialization entirely,
2032+
but turning this variable off prevents the planner from inserting
2033+
materialize nodes except in cases where it is required for correctness.
2034+
The default is <literal>on</>.
2035+
</para>
2036+
</listitem>
2037+
</varlistentry>
2038+
20232039
<varlistentry id="guc-enable-mergejoin" xreflabel="enable_mergejoin">
20242040
<term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
20252041
<indexterm>

‎src/backend/optimizer/path/costsize.c‎

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@
5959
* Portions Copyright (c) 1994, Regents of the University of California
6060
*
6161
* IDENTIFICATION
62-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.216 2010/02/26 02:00:44 momjian Exp $
62+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.217 2010/04/1900:55:25 rhaas Exp $
6363
*
6464
*-------------------------------------------------------------------------
6565
*/
@@ -114,6 +114,7 @@ boolenable_tidscan = true;
114114
boolenable_sort= true;
115115
boolenable_hashagg= true;
116116
boolenable_nestloop= true;
117+
boolenable_material= true;
117118
boolenable_mergejoin= true;
118119
boolenable_hashjoin= true;
119120

@@ -1852,8 +1853,11 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
18521853
mat_inner_cost=inner_run_cost+
18531854
cpu_operator_cost*inner_path_rows*rescanratio;
18541855

1855-
/* Prefer materializing if it looks cheaper */
1856-
if (mat_inner_cost<bare_inner_cost)
1856+
/*
1857+
* Prefer materializing if it looks cheaper, unless the user has asked
1858+
* to suppress materialization.
1859+
*/
1860+
if (enable_material&&mat_inner_cost<bare_inner_cost)
18571861
path->materialize_inner= true;
18581862

18591863
/*
@@ -1867,6 +1871,10 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
18671871
* merge joins can *preserve* the order of their inputs, so they can be
18681872
* selected as the input of a mergejoin, and they don't support
18691873
* mark/restore at present.
1874+
*
1875+
* We don't test the value of enable_material here, because materialization
1876+
* is required for correctness in this case, and turning it off does not
1877+
* entitle us to deliver an invalid plan.
18701878
*/
18711879
elseif (innersortkeys==NIL&&
18721880
!ExecSupportsMarkRestore(inner_path->pathtype))
@@ -1878,8 +1886,11 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
18781886
* pass can be done on-the-fly if it doesn't have to support mark/restore.
18791887
* We don't try to adjust the cost estimates for this consideration,
18801888
* though.
1889+
*
1890+
* Since materialization is a performance optimization in this case, rather
1891+
* than necessary for correctness, we skip it if enable_material is off.
18811892
*/
1882-
elseif (innersortkeys!=NIL&&
1893+
elseif (enable_material&&innersortkeys!=NIL&&
18831894
relation_byte_size(inner_path_rows,inner_path->parent->width)>
18841895
(work_mem*1024L))
18851896
path->materialize_inner= true;

‎src/backend/optimizer/path/joinpath.c‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.132 2010/03/28 22:59:32 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.133 2010/04/19 00:55:25 rhaas Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -437,10 +437,12 @@ match_unsorted_outer(PlannerInfo *root,
437437
elseif (nestjoinOK)
438438
{
439439
/*
440-
* Consider materializing the cheapest inner path, unless it is one
441-
* that materializes its output anyway.
440+
* Consider materializing the cheapest inner path, unless
441+
* enable_material is off or the path in question materializes its
442+
* output anyway.
442443
*/
443-
if (!ExecMaterializesOutput(inner_cheapest_total->pathtype))
444+
if (enable_material&&
445+
!ExecMaterializesOutput(inner_cheapest_total->pathtype))
444446
matpath= (Path*)
445447
create_material_path(innerrel,inner_cheapest_total);
446448

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

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.161 2010/02/26 02:00:46 momjian Exp $
10+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.162 2010/04/1900:55:25 rhaas Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -578,9 +578,11 @@ build_subplan(PlannerInfo *root, Plan *plan, List *rtable, List *rowmarks,
578578
* is pointless for a direct-correlated subplan, since we'd have to
579579
* recompute its results each time anyway.For uncorrelated/undirect
580580
* correlated subplans, we add Material unless the subplan's top plan
581-
* node would materialize its output anyway.
581+
* node would materialize its output anyway. Also, if enable_material
582+
* is false, then the user does not want us to materialize anything
583+
* unnecessarily, so we don't.
582584
*/
583-
elseif (splan->parParam==NIL&&
585+
elseif (splan->parParam==NIL&&enable_material&&
584586
!ExecMaterializesOutput(nodeTag(plan)))
585587
plan=materialize_finished_plan(plan);
586588

‎src/backend/utils/misc/guc.c‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.547 2010/04/12 09:52:29 heikki Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.548 2010/04/19 00:55:25 rhaas Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -643,6 +643,14 @@ static struct config_bool ConfigureNamesBool[] =
643643
&enable_hashagg,
644644
true,NULL,NULL
645645
},
646+
{
647+
{"enable_material",PGC_USERSET,QUERY_TUNING_METHOD,
648+
gettext_noop("Enables the planner's use of materialization."),
649+
NULL
650+
},
651+
&enable_material,
652+
true,NULL,NULL
653+
},
646654
{
647655
{"enable_nestloop",PGC_USERSET,QUERY_TUNING_METHOD,
648656
gettext_noop("Enables the planner's use of nested-loop join plans."),

‎src/backend/utils/misc/postgresql.conf.sample‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -206,6 +206,7 @@
206206
#enable_hashagg = on
207207
#enable_hashjoin = on
208208
#enable_indexscan = on
209+
#enable_material = on
209210
#enable_mergejoin = on
210211
#enable_nestloop = on
211212
#enable_seqscan = on

‎src/include/optimizer/cost.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.100 2010/01/02 16:58:07 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.101 2010/04/19 00:55:26 rhaas Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -57,6 +57,7 @@ extern bool enable_tidscan;
5757
externboolenable_sort;
5858
externboolenable_hashagg;
5959
externboolenable_nestloop;
60+
externboolenable_material;
6061
externboolenable_mergejoin;
6162
externboolenable_hashjoin;
6263
externintconstraint_exclusion;

‎src/test/regress/expected/rangefuncs.out‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,12 +5,13 @@ SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
55
enable_hashagg | on
66
enable_hashjoin | on
77
enable_indexscan | on
8+
enable_material | on
89
enable_mergejoin | on
910
enable_nestloop | on
1011
enable_seqscan | on
1112
enable_sort | on
1213
enable_tidscan | on
13-
(9 rows)
14+
(10 rows)
1415

1516
CREATE TABLE foo2(fooid int, f2 int);
1617
INSERT INTO foo2 VALUES(1, 11);

0 commit comments

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