Re: Multiple indexes, huge table

>
>> There are also rare cases where I might want to make a correction. For example, one of the columns is sample name which is a foreign key to a samples table defined with " ON UPDATE CASCADE." I decided to change a sample name in the samples table which should affect about 20 million rows out of the previously mentioned 500 million. That query has now been running for five days and isn't finished yet.
>
> That sounds like you lack an index on the referencing column of the
> foreign key constraint. Postgres doesn't require you to keep such
> an index, but it's a really good idea if you ever update the referenced
> column.

For updating 20 million out of 500 million rows, wouldn't a full table
scan generally be preferable to an index scan anyway?

But, if he doesn't drop those other indexes during this process, the
maintenance on them is going to kill his performance anyway, just like
it does for bulk loading. If you figure 20,000,000 * (1 table + 5
index) / 15,000 rpm, it comes out to around 5 days.

Cheers,

Jeff