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