1- <!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.79 2010/04/28 21:23:29 tgl Exp $ -->
1+ <!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.80 2010/05/29 21:08:04 tgl Exp $ -->
22
33 <chapter id="performance-tips">
44 <title>Performance Tips</title>
@@ -870,11 +870,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
870870
871871 <para>
872872 If you are adding large amounts of data to an existing table,
873- it might be a win to drop theindex ,
874- load the table, and then recreate theindex . Of course, the
873+ it might be a win to drop theindexes ,
874+ load the table, and then recreate theindexes . Of course, the
875875 database performance for other users might suffer
876- during the time theindex is missing. One should also think
877- twice before dropping uniqueindexes , since the error checking
876+ during the time theindexes are missing. One should also think
877+ twice before droppinga uniqueindex , since the error checking
878878 afforded by the unique constraint will be lost while the index is
879879 missing.
880880 </para>
@@ -890,6 +890,19 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
890890 the constraints. Again, there is a trade-off between data load
891891 speed and loss of error checking while the constraint is missing.
892892 </para>
893+
894+ <para>
895+ What's more, when you load data into a table with existing foreign key
896+ constraints, each new row requires an entry in the server's list of
897+ pending trigger events (since it is the firing of a trigger that checks
898+ the row's foreign key constraint). Loading many millions of rows can
899+ cause the trigger event queue to overflow available memory, leading to
900+ intolerable swapping or even outright failure of the command. Therefore
901+ it may be <emphasis>necessary</>, not just desirable, to drop and re-apply
902+ foreign keys when loading large amounts of data. If temporarily removing
903+ the constraint isn't acceptable, the only other recourse may be to split
904+ up the load operation into smaller transactions.
905+ </para>
893906 </sect2>
894907
895908 <sect2 id="populate-work-mem">
@@ -930,11 +943,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
930943 When loading large amounts of data into an installation that uses
931944 WAL archiving or streaming replication, it might be faster to take a
932945 new base backup after the load has completed than to process a large
933- amount of incremental WAL data.You might want to disable archiving
934- and streaming replication while loading , by setting
946+ amount of incremental WAL data. To prevent incremental WAL logging
947+ while loading, disable archiving and streaming replication, by setting
935948 <xref linkend="guc-wal-level"> to <literal>minimal</>,
936- <xref linkend="guc-archive-mode"> <literal>off</>, and
937- <xref linkend="guc-max-wal-senders"> to zero) .
949+ <xref linkend="guc-archive-mode">to <literal>off</>, and
950+ <xref linkend="guc-max-wal-senders"> to zero.
938951 But note that changing these settings requires a server restart.
939952 </para>
940953
@@ -1006,7 +1019,8 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
10061019 <application>pg_dump</> dump as quickly as possible, you need to
10071020 do a few extra things manually. (Note that these points apply while
10081021 <emphasis>restoring</> a dump, not while <emphasis>creating</> it.
1009- The same points apply when using <application>pg_restore</> to load
1022+ The same points apply whether loading a text dump with
1023+ <application>psql</> or using <application>pg_restore</> to load
10101024 from a <application>pg_dump</> archive file.)
10111025 </para>
10121026
@@ -1027,10 +1041,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
10271041 <listitem>
10281042 <para>
10291043 If using WAL archiving or streaming replication, consider disabling
1030- them during the restore. To do that, set <varname>archive_mode</> off,
1044+ them during the restore. To do that, set <varname>archive_mode</>
1045+ to <literal>off</>,
10311046 <varname>wal_level</varname> to <literal>minimal</>, and
1032- <varname>max_wal_senders</> zero before loading the dump script,
1033- and afterwards set them back to the right values and take a fresh
1047+ <varname>max_wal_senders</>to zero before loading the dump.
1048+ Afterwards, set them back to the right values and take a fresh
10341049 base backup.
10351050 </para>
10361051 </listitem>
@@ -1044,10 +1059,14 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
10441059 possibly discarding many hours of processing. Depending on how
10451060 interrelated the data is, that might seem preferable to manual cleanup,
10461061 or not. <command>COPY</> commands will run fastest if you use a single
1047- transaction and have WAL archiving turned off.
1048- <application>pg_restore</> also has a <option>--jobs</> option
1049- which allows concurrent data loading and index creation, and has
1050- the performance advantages of doing COPY in a single transaction.
1062+ transaction and have WAL archiving turned off.
1063+ </para>
1064+ </listitem>
1065+ <listitem>
1066+ <para>
1067+ If multiple CPUs are available in the database server, consider using
1068+ <application>pg_restore</>'s <option>--jobs</> option. This
1069+ allows concurrent data loading and index creation.
10511070 </para>
10521071 </listitem>
10531072 <listitem>