<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <HTML ><HEAD ><TITLE >Populating a Database</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.4.1 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Performance Tips" HREF="performance-tips.html"><LINK REL="PREVIOUS" TITLE="Controlling the Planner with Explicit JOIN Clauses" HREF="explicit-joins.html"><LINK REL="NEXT" TITLE="Server Administration" HREF="admin.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META NAME="creation" CONTENT="2003-12-22T03:48:47"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 7.4.1 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="explicit-joins.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="performance-tips.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 13. Performance Tips</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="performance-tips.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="admin.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="POPULATE" >13.4. Populating a Database</A ></H1 ><P > One may need to do a large number of table insertions when first populating a database. Here are some tips and techniques for making that as efficient as possible. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="DISABLE-AUTOCOMMIT" >13.4.1. Disable Autocommit</A ></H2 ><A NAME="AEN13954" ></A ><P > Turn off autocommit and just do one commit at the end. (In plain SQL, this means issuing <TT CLASS="COMMAND" >BEGIN</TT > at the start and <TT CLASS="COMMAND" >COMMIT</TT > at the end. Some client libraries may do this behind your back, in which case you need to make sure the library does it when you want it done.) If you allow each insertion to be committed separately, <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > is doing a lot of work for each row added. An additional benefit of doing all insertions in one transaction is that if the insertion of one row were to fail then the insertion of all rows inserted up to that point would be rolled back, so you won't be stuck with partially loaded data. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="POPULATE-COPY-FROM" >13.4.2. Use <TT CLASS="COMMAND" >COPY FROM</TT ></A ></H2 ><P > Use <TT CLASS="COMMAND" >COPY FROM STDIN</TT > to load all the rows in one command, instead of using a series of <TT CLASS="COMMAND" >INSERT</TT > commands. This reduces parsing, planning, etc. overhead a great deal. If you do this then it is not necessary to turn off autocommit, since it is only one command anyway. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="POPULATE-RM-INDEXES" >13.4.3. Remove Indexes</A ></H2 ><P > If you are loading a freshly created table, the fastest way is to create the table, bulk load the table's data using <TT CLASS="COMMAND" >COPY</TT >, then create any indexes needed for the table. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded. </P ><P > If you are augmenting an existing table, you can drop the index, load the table, then recreate the index. Of course, the database performance for other users may be adversely affected during the time that the index is missing. One should also think twice before dropping unique indexes, since the error checking afforded by the unique constraint will be lost while the index is missing. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="POPULATE-SORT-MEM" >13.4.4. Increase <VAR CLASS="VARNAME" >sort_mem</VAR ></A ></H2 ><P > Temporarily increasing the <VAR CLASS="VARNAME" >sort_mem</VAR > configuration variable when restoring large amounts of data can lead to improved performance. This is because when a B-tree index is created from scratch, the existing content of the table needs to be sorted. Allowing the merge sort to use more buffer pages means that fewer merge passes will be required. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="POPULATE-ANALYZE" >13.4.5. Run <TT CLASS="COMMAND" >ANALYZE</TT > Afterwards</A ></H2 ><P > It's a good idea to run <TT CLASS="COMMAND" >ANALYZE</TT > or <TT CLASS="COMMAND" >VACUUM ANALYZE</TT > anytime you've added or updated a lot of data, including just after initially populating a table. This ensures that the planner has up-to-date statistics about the table. With no statistics or obsolete statistics, the planner may make poor choices of query plans, leading to bad performance on queries that use your table. </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="explicit-joins.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="admin.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Controlling the Planner with Explicit <TT CLASS="LITERAL" >JOIN</TT > Clauses</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="performance-tips.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Server Administration</TD ></TR ></TABLE ></DIV ></BODY ></HTML >