pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex creation
3+ messages / 3 participants
[nested] [flat]
* Index creation
@ 2004-01-07 17:08 Eric Jain <Eric.Jain@isb-sib.ch>
0 siblings, 2 replies; 3+ messages in thread
From: Eric Jain @ 2004-01-07 17:08 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index creation
@ 2004-01-07 17:20 Jeff <threshar@torgo.978.org>
parent: Eric Jain <Eric.Jain@isb-sib.ch>
1 sibling, 0 replies; 3+ messages in thread
From: Jeff @ 2004-01-07 17:20 UTC (permalink / raw)
To: Eric Jain <Eric.Jain@isb-sib.ch>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index creation
@ 2004-01-07 19:51 scott.marlowe <scott.marlowe@ihs.com>
parent: Eric Jain <Eric.Jain@isb-sib.ch>
1 sibling, 0 replies; 3+ messages in thread
From: scott.marlowe @ 2004-01-07 19:51 UTC (permalink / raw)
To: Eric Jain <Eric.Jain@isb-sib.ch>; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-07 19:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-07 17:08 Index creation Eric Jain <Eric.Jain@isb-sib.ch>
2004-01-07 17:20 ` Jeff <threshar@torgo.978.org>
2004-01-07 19:51 ` scott.marlowe <scott.marlowe@ihs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox