Обсуждение: Alter Table/Indexing

Hi,

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. Does changing type or setting default on an 
indexed column require sorting?

Thanks, Zdravko

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

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