At 02:20 AM 3/25/2009, pgsql-sql-ow...@postgresql.org wrote:
To: Zdravko Balorda
cc: pgsql-sql@postgresql.org
Subject: Re: Alter Table/Indexing
In-reply-to: <49c89fea.8060...@siix.com>
References: <49c89fea.8060...@siix.com>
Comments: In-reply-to Zdravko Balorda
message dated "Tue, 24 Mar 2009 09:55:06 +0100"
Date: Tue, 24 Mar 2009 10:35:31 -0400
Message-ID: <27189.1237905...@sss.pgh.pa.us>
From: Tom Lane
X-Archive-Number: 200903/84
X-Sequence-Number: 32327
Zdravko Balorda 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql