Re: Foreign Keys Constraints, perforamance analysis

=?iso-8859-1?Q?Daniel_=C5kerud?= <zilch(at)home(dot)se> writes:
> Deleting really showed what the MySQL team means. The deletion was sometime=
> s 30 seconds to < 1 second.

Well, if I understand your rather vague description, you're comparing a
simple bulk delete of all the tuples in the tables, versus a case where
one table sees a bulk delete but the other ones see retail deletion (one
tuple deleted per triggered query, and that tuple has to be searched for
via an indexscan). Not surprising that it's much slower. The real
question is what this scenario has to do with production activities.

> If anyone could help, I would really appriciate if someone could tell me wh=
> y the child/child_fkc difference was so much more than the married/married_=
> fkc difference...

That strikes me as odd too, since the one case has only one FK reference
and the other has two ... seems like it should have been the other way
'round. Experimental noise maybe? Did you repeat the test to make sure
the numbers were reproducible? Do you care to post all the details
(scripts etc) so that others can try to reproduce it?

> I doubt is was becuase of the lack of VACUUM ANALYSE.

You *should* be worried about that. The queries triggered by
foreign-key checks are planned by the regular planner.

regards, tom lane