title: Most Efficient Way to Create an Index in Postgres
body: <p>Is it more efficient to create an index after loading data is complete or before, or does it not matter? </p>
  
  <p>For example, say I have 500 files to load into a Postgres 8.4 DB. Here are the two index creation scenarios I could use:</p>
  
  <ol>
  <li>Create index when table is created, then load each file into table; or</li>
  <li>Create index after all files have been loaded into the table.</li>
  </ol>
  
  <p>The table data itself is about 45 Gigabytes. The index is about 12 Gigabytes. I'm using a standard index. It is created like this:</p>
  
  <pre><code>CREATE INDEX idx_name ON table_name (column_name);
  </code></pre>
  
  <p>My data loading uses <a href="http://www.postgresql.org/docs/8.1/static/sql-copy.html" rel="noreferrer" title="COPY FROM">COPY FROM</a>.</p>
  
  <p>Once all the files are loaded, no updates, deletes or additional loads will occur on the table (it's a day's worth of data that will not change). So I wanted to ask which scenario would be most efficient? Initial testing seems to indicate that loading all the files and then creating the index (scenario 2) is faster, but I have not done a scientific comparison of the two approaches.</p>