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.

No,
I compare
DELETE FROM person;
against
DELETE FROM person;
DELETE FROM married;
DELETE FROM child;

Which I think has very much to do with performane of real-worl applications
i think. I often think of Accounts, where there are numerous records stored
for this account - which should be deleted when the account is deleted.

> regards, tom lane

Daniel Åkerud