Re: COPY command details

[Benjamin Arai <benjamin(at)araisoft(dot)com>]

I agree, this is true if I cannot defer index updates.  But if it is 
possible to defer index updates until the end then I should be able to 
achieve some sort of speedup.  Rebuilding an index can't be the 
PostgreSQL solution for all cases. I am dealing with databases in the 
hundreds of gigs range and I am adding about 10gigs of data a week.  At 
some point its going to take longer than a week to rebuild all of the 
indexes in the database.

On the other hand, if I am to partition the data into several tables 
then it might not be such a big deal since I am only adding and never 
deleting... This makes it a little more of a pain in the ass.

Benjamin

Tom Lane wrote:
> Benjamin Arai <benjamin(at)araisoft(dot)com> writes:
>   
> > I would prefer not to drop the index because the database is several 
> > hundred gigs.  I would prefer to incrementally add to the index.
> >     
>
> This may well be false economy.  I don't have numbers at hand, but a
> full rebuild can be substantially faster than adding a large number
> of rows to the index incrementally.  Also, you don't end up with a
> physically disordered index, so there is some ongoing performance
> benefit.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>