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
>
>
