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])