Re: COPY command details

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@araisoft.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
>
>