Re: Multiple indexes, huge table

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> 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?

Foreign key triggers do their checks retail, though, so you really want
the probe for any referencing rows for a particular row-being-updated
to be able to use an index.

(It would be nice if we could replace that with a mass revalidation
once it got to be a big fraction of the table, but we don't have a
mechanism for that. Yet.)

regards, tom lane