Thread: Index creation

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?

--
Eric Jain

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/

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.