Re: Alter Table/Indexing

At 02:20 AM 3/25/2009, pgsql-sql-owner(at)postgresql(dot)org wrote:
>To: Zdravko Balorda <zdravko(dot)balorda(at)siix(dot)com>
>cc: pgsql-sql(at)postgresql(dot)org
>Subject: Re: Alter Table/Indexing
>In-reply-to: <49C89FEA(dot)8060804(at)siix(dot)com>
>References: <49C89FEA(dot)8060804(at)siix(dot)com>
>Comments: In-reply-to Zdravko Balorda <zdravko(dot)balorda(at)siix(dot)com>
> message dated "Tue, 24 Mar 2009 09:55:06 +0100"
>Date: Tue, 24 Mar 2009 10:35:31 -0400
>Message-ID: <27189(dot)1237905331(at)sss(dot)pgh(dot)pa(dot)us>
>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>X-Archive-Number: 200903/84
>X-Sequence-Number: 32327
>
>Zdravko Balorda <zdravko(dot)balorda(at)siix(dot)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