Jeff Janes <jeff.janes@gmail.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