From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Eric Jain <Eric(dot)Jain(at)isb-sib(dot)ch> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Index creation |
Date: | 2004-01-07 19:51:30 |
Message-ID: | Pine.LNX.4.33.0401071248290.5671-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2004-01-07 23:06:08 | failures on machines using jfs |
Previous Message | Greg Stark | 2004-01-07 19:45:24 | RAID array stripe sizes |