pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff <threshar@torgo.978.org>
To: Eric Jain <Eric.Jain@isb-sib.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index creation
Date: Wed, 7 Jan 2004 12:20:15 -0500
Message-ID: <20040107122015.1466a8c4.threshar@torgo.978.org> (raw)
In-Reply-To: <001101c3d540$d3ea8090$c300000a@caliente>
References: <001101c3d540$d3ea8090$c300000a@caliente>

On Wed, 7 Jan 2004 18:08:06 +0100
"Eric Jain" <Eric.Jain@isb-sib.ch> wrote:

> Any tips for speeding up index creation?
> 
> I need to bulk load a large table with 100M rows and several indexes,
> some of which span two columns.
> 
> By dropping all indexes prior to issuing the 'copy from' command, the
> operation completes 10x as fast (1.5h vs 15h).
> 
> Unfortunately, recreating a single index takes nearly as long as
> loading all of the data into the table; this more or less eliminates
> the time gained by dropping the index in the first place.
> 
> Also, there doesn't seem to be a simple way to disable/recreate all
> indexes for a specific table short of explicitely dropping and later
> recreating each index?

Before creating your index bump up your sort_mem high.

set sort_mem = 64000  
create index foo on baz(a, b);

BIG increases.
[This also helps on FK creation]


-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



view thread (3+ messages)  latest in thread

Message-ID: <20040107122015.1466a8c4.threshar@torgo.978.org>
Permalink:  ../20040107122015.1466a8c4.threshar@torgo.978.org/
Also on:    postgresql.org/message-id/20040107122015.1466a8c4.threshar@torgo.978.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: threshar@torgo.978.org, Eric.Jain@isb-sib.ch
  Subject: Re: Index creation
  In-Reply-To: <20040107122015.1466a8c4.threshar@torgo.978.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox