Re: Multiple indexes, huge table

On Thu, Sep 6, 2012 at 4:22 PM, Aram Fingal <fingal@multifactorial.com> wrote:
> I have a table which currently has about 500 million rows.  For the most part, the situation is going to be that I
willimport a few hundred million more rows from text files once every few months but otherwise there won't be any
insert,update or delete queries.  I have created five indexes, some of them multi-column, which make a tremendous
differencein performance for the statistical queries which I need to run frequently (seconds versus hours.)  When
addingdata to the table, however, I have found that it is much faster to drop all the indexes, copy the data to the
tableand then create the indexes again (hours versus days.)  So, my question is whether this is really the best way.
ShouldI write a script which drops all the indexes, copies the data and then recreates the indexes or is there a better
wayto do this? 
>
> There are also rare cases where I might want to make a correction.  For example, one of the columns is sample name
whichis a foreign key to a samples table defined with " ON UPDATE CASCADE."  I decided to change a sample name in the
samplestable which should affect about 20 million rows out of the previously mentioned 500 million.  That query has now
beenrunning for five days and isn't finished yet. 

Your case might do well with partitioning, particularly if you are
time bottlenecked during the import.  It will require some careful
though before implementing, but the general schema is to insert the
new data into a child table that gets its own index: this prevents you
from having to reindex the whole table.  Partitioning makes other
things more complicated though (like RI).

merlin