|
1 | 1 | <!-- |
2 | | -$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.52 2005/09/0200:57:57 tgl Exp $ |
| 2 | +$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.53 2005/09/0203:19:53 tgl Exp $ |
3 | 3 | --> |
4 | 4 |
|
5 | 5 | <chapter id="performance-tips"> |
@@ -878,6 +878,54 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; |
878 | 878 | statistics. |
879 | 879 | </para> |
880 | 880 | </sect2> |
| 881 | + |
| 882 | + <sect2 id="populate-pg-dump"> |
| 883 | + <title>Some Notes About <application>pg_dump</></title> |
| 884 | + |
| 885 | + <para> |
| 886 | + Dump scripts generated by <application>pg_dump</> automatically apply |
| 887 | + several, but not all, of the above guidelines. To reload a |
| 888 | + <application>pg_dump</> dump as quickly as possible, you need to |
| 889 | + do a few extra things manually. (Note that these points apply while |
| 890 | + <emphasis>restoring</> a dump, not while <emphasis>creating</> it. |
| 891 | + The same points apply when using <application>pg_restore</> to load |
| 892 | + from a <application>pg_dump</> archive file.) |
| 893 | + </para> |
| 894 | + |
| 895 | + <para> |
| 896 | + By default, <application>pg_dump</> uses <command>COPY</>, and when |
| 897 | + it is generating a complete schema-and-data dump, it is careful to |
| 898 | + load data before creating indexes and foreign keys. So in this case |
| 899 | + the first several guidelines are handled automatically. What is left |
| 900 | + for you to do is to set appropriate (i.e., larger than normal) values |
| 901 | + for <varname>maintenance_work_mem</varname> and |
| 902 | + <varname>checkpoint_segments</varname> before loading the dump script, |
| 903 | + and then to run <command>ANALYZE</> afterwards. |
| 904 | + </para> |
| 905 | + |
| 906 | + <para> |
| 907 | + A data-only dump will still use <command>COPY</>, but it does not |
| 908 | + drop or recreate indexes, and it does not normally touch foreign |
| 909 | + keys. |
| 910 | + |
| 911 | + <footnote> |
| 912 | + <para> |
| 913 | + You can get the effect of disabling foreign keys by using |
| 914 | + the <option>-X disable-triggers</> option — but realize that |
| 915 | + that eliminates, rather than just postponing, foreign key |
| 916 | + validation, and so it is possible to insert bad data if you use it. |
| 917 | + </para> |
| 918 | + </footnote> |
| 919 | + |
| 920 | + So when loading a data-only dump, it is up to you to drop and recreate |
| 921 | + indexes and foreign keys if you wish to use those techniques. |
| 922 | + It's still useful to increase <varname>checkpoint_segments</varname> |
| 923 | + while loading the data, but don't bother increasing |
| 924 | + <varname>maintenance_work_mem</varname>; rather, you'd do that while |
| 925 | + manually recreating indexes and foreign keys afterwards. |
| 926 | + And don't forget to <command>ANALYZE</> when you're done. |
| 927 | + </para> |
| 928 | + </sect2> |
881 | 929 | </sect1> |
882 | 930 |
|
883 | 931 | </chapter> |
|