Re: Foreign Keys Constraints, perforamance analysis

=?iso-8859-1?Q?Daniel_=C5kerud?= <zilch(at)home(dot)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