From: | Jeff <threshar(at)torgo(dot)978(dot)org> |
---|---|
To: | "Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)ch> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Index creation |
Date: | 2004-01-07 17:20:15 |
Message-ID: | 20040107122015.1466a8c4.threshar@torgo.978.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, 7 Jan 2004 18:08:06 +0100
"Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)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(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2004-01-07 19:41:04 | Re: [PERFORM] Find original number of rows before applied LIMIT/OFFSET? |
Previous Message | Eric Jain | 2004-01-07 17:08:06 | Index creation |