Skip to content

Commit 391c381

Browse files
tglsfdctglsfdc
committed
Rename SortMem and VacuumMem to work_mem and maintenance_work_mem.
Make btree index creation and initial validation of foreign-key constraints use maintenance_work_mem rather than work_mem as their memory limit. Add some code to guc.c to allow these variables to be referenced by their old names in SHOW and SET commands, for backwards compatibility.
1 parent 39d715b commit 391c381

34 files changed

+269
-188
lines changed

‎contrib/tablefunc/tablefunc.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -865,7 +865,7 @@ get_crosstab_tuplestore(char *sql,
865865
MemoryContext SPIcontext;
866866

867867
/* initialize our tuplestore */
868-
tupstore = tuplestore_begin_heap(true, false, SortMem);
868+
tupstore = tuplestore_begin_heap(true, false, work_mem);
869869

870870
/* Connect to SPI manager */
871871
if ((ret = SPI_connect()) < 0)
@@ -1246,7 +1246,7 @@ connectby(char *relname,
12461246
oldcontext = MemoryContextSwitchTo(per_query_ctx);
12471247

12481248
/* initialize our tuplestore */
1249-
tupstore = tuplestore_begin_heap(true, false, SortMem);
1249+
tupstore = tuplestore_begin_heap(true, false, work_mem);
12501250

12511251
MemoryContextSwitchTo(oldcontext);
12521252

‎doc/src/sgml/backup.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.34 2004/01/19 20:12:30 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.35 2004/02/03 17:34:02 tgl Exp $
33
-->
44
<chapter id="backup">
55
<title>Backup and Restore</title>
@@ -156,8 +156,8 @@ pg_dump -h <replaceable>host1</> <replaceable>dbname</> | psql -h <replaceable>h
156156
<tip>
157157
<para>
158158
Restore performance can be improved by increasing the
159-
configuration parameter <varname>sort_mem</varname> (see <xref
160-
linkend="runtime-config-resource-memory">).
159+
configuration parameter <varname>maintenance_work_mem</varname>
160+
(see <xref linkend="runtime-config-resource-memory">).
161161
</para>
162162
</tip>
163163
</sect2>

‎doc/src/sgml/installation.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.193 2004/01/19 21:20:06 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.194 2004/02/03 17:34:02 tgl Exp $ -->
22

33
<chapter id="installation">
44
<title><![%standalone-include[<productname>PostgreSQL</>]]>
@@ -1399,7 +1399,7 @@ kill `cat /usr/local/pgsql/data/postmaster.pid`
13991399
not designed for optimum performance. To achieve optimum
14001400
performance, several server parameters must be adjusted, the two
14011401
most common being <varname>shared_buffers</varname> and
1402-
<varname> sort_mem</varname> mentioned in the documentation.
1402+
<varname>work_mem</varname>.
14031403
Other parameters mentioned in the documentation also affect
14041404
performance.
14051405
</para>

‎doc/src/sgml/perform.sgml

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.40 2004/01/11 05:46:58 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.41 2004/02/03 17:34:02 tgl Exp $
33
-->
44

55
<chapter id="performance-tips">
@@ -684,16 +684,18 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
684684
</para>
685685
</sect2>
686686

687-
<sect2 id="populate-sort-mem">
688-
<title>Increase <varname>sort_mem</varname></title>
687+
<sect2 id="populate-work-mem">
688+
<title>Increase <varname>maintenance_work_mem</varname></title>
689689

690690
<para>
691-
Temporarily increasing the <varname>sort_mem</varname>
691+
Temporarily increasing the <varname>maintenance_work_mem</varname>
692692
configuration variable when restoring large amounts of data can
693693
lead to improved performance. This is because when a B-tree index
694694
is created from scratch, the existing content of the table needs
695-
to be sorted. Allowing the merge sort to use more buffer pages
696-
means that fewer merge passes will be required.
695+
to be sorted. Allowing the merge sort to use more memory
696+
means that fewer merge passes will be required. A larger setting for
697+
<varname>maintenance_work_mem</varname> may also speed up validation
698+
of foreign-key constraints.
697699
</para>
698700
</sect2>
699701

‎doc/src/sgml/plpgsql.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.34 2004/01/24 22:05:08 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.35 2004/02/03 17:34:02 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -1354,7 +1354,7 @@ SELECT * FROM some_func();
13541354
allow users to define set-returning functions
13551355
that do not have this limitation. Currently, the point at
13561356
which data begins being written to disk is controlled by the
1357-
<varname>sort_mem</> configuration variable. Administrators
1357+
<varname>work_mem</> configuration variable. Administrators
13581358
who have sufficient memory to store larger result sets in
13591359
memory should consider increasing this parameter.
13601360
</para>

‎doc/src/sgml/ref/postgres-ref.sgml

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/postgres-ref.sgml,v 1.42 2003/11/29 19:51:39 pgsql Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/postgres-ref.sgml,v 1.43 2004/02/03 17:34:02 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -41,7 +41,7 @@ PostgreSQL documentation
4141
<arg>-s</arg>
4242
<arg>-t<group choice="plain"><arg>pa</arg><arg>pl</arg><arg>ex</arg></group></arg>
4343
</group>
44-
<arg>-S <replaceable>sort-mem</replaceable></arg>
44+
<arg>-S <replaceable>work-mem</replaceable></arg>
4545
<arg>-W <replaceable>seconds</replaceable></arg>
4646
<arg>--<replaceable>name</replaceable>=<replaceable>value</replaceable></arg>
4747
<arg choice="plain"><replaceable>database</replaceable></arg>
@@ -64,7 +64,7 @@ PostgreSQL documentation
6464
<arg>-s</arg>
6565
<arg>-t<group choice="plain"><arg>pa</arg><arg>pl</arg><arg>ex</arg></group></arg>
6666
</group>
67-
<arg>-S <replaceable>sort-mem</replaceable></arg>
67+
<arg>-S <replaceable>work-mem</replaceable></arg>
6868
<arg>-v <replaceable>protocol</replaceable></arg>
6969
<arg>-W <replaceable>seconds</replaceable></arg>
7070
<arg>--<replaceable>name</replaceable>=<replaceable>value</replaceable></arg>
@@ -197,16 +197,13 @@ PostgreSQL documentation
197197
</varlistentry>
198198

199199
<varlistentry>
200-
<term><option>-S</option> <replaceable class="parameter">sort-mem</replaceable></term>
200+
<term><option>-S</option> <replaceable class="parameter">work-mem</replaceable></term>
201201
<listitem>
202202
<para>
203203
Specifies the amount of memory to be used by internal sorts and hashes
204-
before resorting to temporary disk files. The value is specified in
205-
kilobytes, and defaults to 1024. Note that for a complex query,
206-
several sorts and/or hashes might be running in parallel, and each one
207-
will be allowed to use as much as
208-
<replaceable class="parameter">sort-mem</replaceable> kilobytes
209-
before it starts to put data into temporary files.
204+
before resorting to temporary disk files. See the description of the
205+
<varname>work_mem</> configuration parameter in <xref
206+
linkend="runtime-config-resource-memory">.
210207
</para>
211208
</listitem>
212209
</varlistentry>

‎doc/src/sgml/ref/postmaster.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/postmaster.sgml,v 1.44 2003/12/14 00:15:03 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/postmaster.sgml,v 1.45 2004/02/03 17:34:02 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -541,10 +541,10 @@ PostgreSQL documentation
541541
<para>
542542
Named run-time parameters can be set in either of these styles:
543543
<screen>
544-
<prompt>$</prompt> <userinput>postmaster -c sort_mem=1234</userinput>
545-
<prompt>$</prompt> <userinput>postmaster --sort-mem=1234</userinput>
544+
<prompt>$</prompt> <userinput>postmaster -c work_mem=1234</userinput>
545+
<prompt>$</prompt> <userinput>postmaster --work-mem=1234</userinput>
546546
</screen>
547-
Either form overrides whatever setting might exist for <varname>sort_mem</>
547+
Either form overrides whatever setting might exist for <varname>work_mem</>
548548
in <filename>postgresql.conf</>. Notice that underscores in parameter
549549
names can be written as either underscore or dash on the command line.
550550
</para>

‎doc/src/sgml/runtime.sgml

Lines changed: 44 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.235 2004/01/27 16:51:43 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.236 2004/02/03 17:34:02 tgl Exp $
33
-->
44

55
<Chapter Id="runtime">
@@ -850,37 +850,41 @@ SET ENABLE_SEQSCAN TO OFF;
850850
</varlistentry>
851851

852852
<varlistentry>
853-
<term><varname>sort_mem</varname> (<type>integer</type>)</term>
853+
<term><varname>work_mem</varname> (<type>integer</type>)</term>
854854
<listitem>
855855
<para>
856-
Specifies the amount of memory to be used by internal sort operations and
857-
hash tables before switching to temporary disk files. The value is
856+
Specifies the amount of memory to be used by internal sort operations
857+
and hash tables before switching to temporary disk files. The value is
858858
specified in kilobytes, and defaults to 1024 kilobytes (1 MB).
859859
Note that for a complex query, several sort or hash operations might be
860860
running in parallel; each one will be allowed to use as much memory
861861
as this value specifies before it starts to put data into temporary
862-
files. Also, several running sessions could be doing
863-
sort operations simultaneously. So the total memory used could be many
864-
times the value of <varname>sort_mem</varname>. Sort operations are used
865-
by <literal>ORDER BY</>, merge joins, and <command>CREATE INDEX</>.
862+
files. Also, several running sessions could be doing such operations
863+
concurrently. So the total memory used could be many
864+
times the value of <varname>work_mem</varname>; it is necessary to
865+
keep this fact in mind when choosing the value. Sort operations are
866+
used for <literal>ORDER BY</>, <literal>DISTINCT</>, and
867+
merge joins.
866868
Hash tables are used in hash joins, hash-based aggregation, and
867-
hash-based processing of <literal>IN</> subqueries. Because
868-
<command>CREATE INDEX</> is used when restoring a database,
869-
increasing <varname>sort_mem</varname> before doing a large
870-
restore operation can improve performance.
869+
hash-based processing of <literal>IN</> subqueries.
871870
</para>
872871
</listitem>
873872
</varlistentry>
874873

875874
<varlistentry>
876-
<term><varname>vacuum_mem</varname> (<type>integer</type>)</term>
875+
<term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
877876
<listitem>
878877
<para>
879-
Specifies the maximum amount of memory to be used by
880-
<command>VACUUM</command> to keep track of to-be-reclaimed
881-
rows. The value is specified in kilobytes, and defaults to
882-
8192 kB. Larger settings may improve the speed of
883-
vacuuming large tables that have many deleted rows.
878+
Specifies the maximum amount of memory to be used in maintenance
879+
operations, such as <command>VACUUM</command>, <command>CREATE
880+
INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>.
881+
The value is specified in kilobytes, and defaults to 16384 kilobytes
882+
(16 MB). Since only one of these operations can be executed at
883+
a time by a database session, and an installation normally doesn't
884+
have very many of them happening concurrently, it's safe to set this
885+
value significantly larger than <varname>work_mem</varname>. Larger
886+
settings may improve performance for vacuuming and for restoring
887+
database dumps.
884888
</para>
885889
</listitem>
886890
</varlistentry>
@@ -1412,25 +1416,25 @@ SET ENABLE_SEQSCAN TO OFF;
14121416
<para>
14131417
Various tuning parameters for the genetic query optimization
14141418
algorithm. The recommended one to modify is
1415-
<varname>geqo_effort</varname>, which can range from 1 to 10 with
1416-
a default of 5. Larger values increase the time spent in planning
1417-
but make it more likely that a good plan will be found.
1418-
<varname>geqo_effort</varname> doesn't actually do anything directly,
1419-
it is just used to compute the default values for the other
1420-
parameters. If you prefer, you can set the other parameters by hand
1421-
instead.
1422-
The pool size is the number of individuals in the genetic population.
1423-
It must be at least two, and useful values are typically 100 to 1000.
1424-
If it is set to zero (the default setting) then a suitable default
1425-
is chosen based on <varname>geqo_effort</varname> and the number of
1426-
tables in the query.
1427-
Generations specifies the number of iterations of the algorithm.
1428-
It must be at least one, and useful values are in the same range
1429-
as the pool size.
1430-
If it is set to zero (the default setting) then a suitable default
1431-
is chosen based on the pool size.
1432-
The run time of the algorithm is roughly proportional to the sum of
1433-
pool size and generations.
1419+
<varname>geqo_effort</varname>, which can range from 1 to 10 with
1420+
a default of 5. Larger values increase the time spent in planning
1421+
but make it more likely that a good plan will be found.
1422+
<varname>geqo_effort</varname> doesn't actually do anything directly,
1423+
it is just used to compute the default values for the other
1424+
parameters. If you prefer, you can set the other parameters by hand
1425+
instead.
1426+
The pool size is the number of individuals in the genetic population.
1427+
It must be at least two, and useful values are typically 100 to 1000.
1428+
If it is set to zero (the default setting) then a suitable default
1429+
is chosen based on <varname>geqo_effort</varname> and the number of
1430+
tables in the query.
1431+
Generations specifies the number of iterations of the algorithm.
1432+
It must be at least one, and useful values are in the same range
1433+
as the pool size.
1434+
If it is set to zero (the default setting) then a suitable default
1435+
is chosen based on the pool size.
1436+
The run time of the algorithm is roughly proportional to the sum of
1437+
pool size and generations.
14341438
The selection bias is the selective pressure within the
14351439
population. Values can be from 1.50 to 2.00; the latter is the
14361440
default.
@@ -2840,7 +2844,7 @@ $ <userinput>postmaster -o '-S 1024 -s'</userinput>
28402844
<row>
28412845
<entry><option>-S <replaceable>x</replaceable></option><footnoteref linkend="fn.runtime-config-short">
28422846
</entry>
2843-
<entry><literal>sort_mem = <replaceable>x</replaceable></></entry>
2847+
<entry><literal>work_mem = <replaceable>x</replaceable></></entry>
28442848
</row>
28452849

28462850
<row>
@@ -3230,7 +3234,7 @@ kernel.shmmax = 134217728
32303234
<para>
32313235
In OS X 10.2 and earlier, edit the file
32323236
<filename>/System/Library/StartupItems/SystemTuning/SystemTuning</>
3233-
and change the values in the following commands:
3237+
and change the values in the following commands:
32343238
<programlisting>
32353239
sysctl -w kern.sysv.shmmax
32363240
sysctl -w kern.sysv.shmmin
@@ -3239,7 +3243,7 @@ sysctl -w kern.sysv.shmseg
32393243
sysctl -w kern.sysv.shmall
32403244
</programlisting>
32413245
In OS X 10.3, these commands have been moved to <filename>/etc/rc</>
3242-
and must be edited there.
3246+
and must be edited there.
32433247
</para>
32443248
</listitem>
32453249
</varlistentry>

‎src/backend/access/nbtree/nbtree.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* Portions Copyright (c) 1994, Regents of the University of California
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.109 2004/01/07 18:56:24 neilc Exp $
15+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.110 2004/02/03 17:34:02 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -117,13 +117,14 @@ btbuild(PG_FUNCTION_ARGS)
117117

118118
if (buildstate.usefast)
119119
{
120-
buildstate.spool = _bt_spoolinit(index, indexInfo->ii_Unique);
120+
buildstate.spool = _bt_spoolinit(index, indexInfo->ii_Unique, false);
121121

122122
/*
123-
* Different from spool, the uniqueness isn't checked for spool2.
123+
* If building a unique index, put dead tuples in a second spool
124+
* to keep them out of the uniqueness check.
124125
*/
125126
if (indexInfo->ii_Unique)
126-
buildstate.spool2 = _bt_spoolinit(index, false);
127+
buildstate.spool2 = _bt_spoolinit(index, false, true);
127128
}
128129

129130
/* do the heap scan */

‎src/backend/access/nbtree/nbtsort.c

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,7 @@
3636
* Portions Copyright (c) 1994, Regents of the University of California
3737
*
3838
* IDENTIFICATION
39-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.80 2004/01/07 18:56:24 neilc Exp $
39+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.81 2004/02/03 17:34:02 tgl Exp $
4040
*
4141
*-------------------------------------------------------------------------
4242
*/
@@ -112,14 +112,25 @@ static void _bt_load(Relation index, BTSpool *btspool, BTSpool *btspool2);
112112
* create and initialize a spool structure
113113
*/
114114
BTSpool *
115-
_bt_spoolinit(Relation index, bool isunique)
115+
_bt_spoolinit(Relation index, bool isunique, bool isdead)
116116
{
117117
BTSpool *btspool = (BTSpool *) palloc0(sizeof(BTSpool));
118+
int btKbytes;
118119

119120
btspool->index = index;
120121
btspool->isunique = isunique;
121122

122-
btspool->sortstate = tuplesort_begin_index(index, isunique, false);
123+
/*
124+
* We size the sort area as maintenance_work_mem rather than work_mem to
125+
* speed index creation. This should be OK since a single backend can't
126+
* run multiple index creations in parallel. Note that creation of a
127+
* unique index actually requires two BTSpool objects. We expect that the
128+
* second one (for dead tuples) won't get very full, so we give it only
129+
* work_mem.
130+
*/
131+
btKbytes = isdead ? work_mem : maintenance_work_mem;
132+
btspool->sortstate = tuplesort_begin_index(index, isunique,
133+
btKbytes, false);
123134

124135
/*
125136
* Currently, tuplesort provides sort functions on IndexTuples. If we

0 commit comments

Comments
 (0)