On Wed, 7 Jan 2004, Eric Jain 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?
Note that you can issue the following command to see all the index definitions for a table: select * from pg_indexes where tablename='sometable'; And store those elsewhere to be reused when you need to recreate the index. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
