Re: Foreign Keys Constraints, perforamance analysis

=?iso-8859-1?Q?Daniel_=C5kerud?= <zilch@home.se> writes:
>> ... Not surprising that it's much slower.  The real
>> question is what this scenario has to do with production activities.

> It has nothing to do with production activities. I just want to know how,
> and how much, Foreign Keys Constraints affect performance.

My point is that unless bulk delete is an operation you do a lot,
this measurement has little to do with everyday performance.  A more
reasonable test (I think) would be to time deletion of a *single* person
record --- and the associated implicit deletion of a small number of
dependent records --- against deletion of the same person record and
explicit deletion of the same number of dependent records.  That
actually has something to do with performance of real-world applications
that delete individual records.  As is, you are measuring (in effect)
    DELETE FROM married;
against
    FOR akey IN (SELECT key FROM married) DO
        DELETE FROM married WHERE key = akey;
and then blaming the speed difference on foreign keys.  It's got nothing
to do with foreign keys and everything to do with number of queries
issued.

            regards, tom lane