Re: on update / on delete performance of foreign keys

Harald Fuchs <hf0722x(at)protecting(dot)net> writes:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
>>> PostgreSQL doesn't automatically add indexes to foreign-key
>>> columns. That sounds like the issue to me.

>> Oh... *feeling a bit stupid*... Seems that I got confused, because it
>> requires an index to exist on the referenced table (To speed up inserts,
>> updates), but not on the referencing table...

> That's not quite right. What PostgreSQL (or any other DBMS) requires
> from the referenced table is a UNIQUE constraint on the column in
> question so that the referencing table points to a single row, and
> UNIQUE constraints are usually implemented by indices.

You missed the point. PG does force you to put an index on the
referenced column, but it does not force you to put one on the
referencing column. However, deletions in the referenced table are
going to be really slow if there is no index on the referencing column,
because there's no fast way to look up referencing rows matching the
key to be deleted.

regards, tom lane