At 02:20 AM 3/25/2009, [email protected] wrote:
>To: Zdravko Balorda <[email protected]>
>cc: [email protected]
>Subject: Re: Alter Table/Indexing
>In-reply-to: <[email protected]>
>References: <[email protected]>
>Comments: In-reply-to Zdravko Balorda <[email protected]>
> message dated "Tue, 24 Mar 2009 09:55:06 +0100"
>Date: Tue, 24 Mar 2009 10:35:31 -0400
>Message-ID: <[email protected]>
>From: Tom Lane <[email protected]>
>X-Archive-Number: 200903/84
>X-Sequence-Number: 32327
>
>Zdravko Balorda <[email protected]> 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
