Skip site navigation (1) Skip section navigation (2)

Re: Index creation

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: (view raw, whole thread or download thread mbox)
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 

In response to

pgsql-performance by date

Next:From: Andrew SullivanDate: 2004-01-07 23:06:08
Subject: failures on machines using jfs
Previous:From: Greg StarkDate: 2004-01-07 19:45:24
Subject: RAID array stripe sizes

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group