On Wed, 7 Jan 2004 18:08:06 +0100
"Eric Jain" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to