Re: Foreign Keys Constraints, perforamance analysis
От | Daniel Åkerud |
---|---|
Тема | Re: Foreign Keys Constraints, perforamance analysis |
Дата | |
Msg-id | 002101c0fc23$8672f6b0$c901a8c0@automatic100 обсуждение исходный текст |
Ответ на | Foreign Keys Constraints, perforamance analysis (Daniel Åkerud <zilch@home.se>) |
Ответы |
Re: Foreign Keys Constraints, perforamance analysis
|
Список | pgsql-general |
> > 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. It has nothing to do with production activities. I just want to know how, and how much, Foreign Keys Constraints affect performance. I compare (1) manual deletion of person, married and child versus (2) deletion of person which implies automatic deletion of married_fkc and child_fkc using ON DELETE CASCADE. > > 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? Well, the tests were run with quite high values and took quite some time, so I doubt it was experimental noise. And that was what I thought too, 2 FK versus 1. > > 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. I'll rerun the test using VACUUM ANALYSE in between inserting into married/married_fkc and child/child_fkc, and post the results! > regards, tom lane This whole thing is about making myself aware of the performance impace of Foreign Keys Constraints.
В списке pgsql-general по дате отправления: