pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index creation
3+ messages / 3 participants
[nested] [flat]

* Index creation
@ 2004-01-07 17:08 Eric Jain <Eric.Jain@isb-sib.ch>
  2004-01-07 17:20 ` Re: Index creation Jeff <threshar@torgo.978.org>
  2004-01-07 19:51 ` Re: Index creation scott.marlowe <scott.marlowe@ihs.com>
  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:08 Index creation Eric Jain <Eric.Jain@isb-sib.ch>
@ 2004-01-07 17:20 ` Jeff <threshar@torgo.978.org>
  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 17:08 Index creation Eric Jain <Eric.Jain@isb-sib.ch>
@ 2004-01-07 19:51 ` scott.marlowe <scott.marlowe@ihs.com>
  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