Re: Alter Table/Indexing

At 02:20 AM 3/25/2009, pgsql-sql-owner@postgresql.org wrote:
>To: Zdravko Balorda <zdravko.balorda@siix.com>
>cc: pgsql-sql@postgresql.org
>Subject: Re: Alter Table/Indexing
>In-reply-to: <49C89FEA.8060804@siix.com>
>References: <49C89FEA.8060804@siix.com>
>Comments: In-reply-to Zdravko Balorda <zdravko.balorda@siix.com>
>         message dated "Tue, 24 Mar 2009 09:55:06 +0100"
>Date: Tue, 24 Mar 2009 10:35:31 -0400
>Message-ID: <27189.1237905331@sss.pgh.pa.us>
>From: Tom Lane <tgl@sss.pgh.pa.us>
>X-Archive-Number: 200903/84
>X-Sequence-Number: 32327
>
>Zdravko Balorda <zdravko.balorda@siix.com> writes:
> > I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT 
> does
> > in a sense it may be faster to drop and recreate index than sorting 
>
> > after every row inserted.
>
>ALTER TABLE TYPE already rebuilds the indexes; you won't make the
>overall process any faster by doing that by hand.
>
>                         regards, tom lane

I had a case (a long time ago) where I was on MS SQL in a production 
environment. We had a number of indices which were system related - 
meaning they were used infrequently to speed up certain administrative 
functions. When doing a bulk load we found that if we dropped these 
indices (but kept the ones that were crucial for production) we could 
significantly speed up the "effective downtime" of the system b/c any 
DDL statement was executed faster. We would then schedule these indices 
to be re-created at later dates, spreading out the load (b/c the system 
was in production at that point).

I wonder if Postgres functions similarly for such a use case? As Tom 
says, the total processing time is fixed: you have to upload the data 
and rebuild all the indices, but if there are non-critical indices, you 
can go from "zero" to "data loaded" faster by dropping them and 
rebuilding them manually later?

Thanks for any insight on that (and I hope my question helps the OP as 
well - if this seems off topic let me know),

Steve