On Wed, May 21, 2014 at 7:48 PM, Joe Van Dyk <j...@tanga.com> wrote: > On Wednesday, May 21, 2014, Jeff Janes <jeff.ja...@gmail.com> wrote: > >> >> On Wed, May 21, 2014 at 1:11 PM, Joe Van Dyk <j...@tanga.com> wrote: >> >>> I came across >>> http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/ >>> which seems to indicate so. >>> >>> When I run the following test script, having 50 foreign keys takes >>> about twice as long to do the update. Is there a reason for that? >>> Seems like the RI triggers wouldn't have to run on updates if the >>> value doesn't change. >>> >> >> That's kind of a question of definitions. Perhaps the trigger itself >> doesn't need to run, but the code that decides whether the trigger needs to >> run does need to run. Where do you draw the line around what is the >> trigger proper and what is just infrastructure? >> >> However you wish to define it, change your function so that it actually >> does change the key field, and see how much slower that is than the >> behavior where you update the row without updating the key. >> >> > > I was expecting that the RI update triggers would have a "when (new.key is > distinct from old.key)" condition on them, which would mean that the number > of referencing tables wouldn't matter. >
But that condition is checked for each constraint individually, not for all constraints simultaneously. A table can be referenced on multiple combinations of columns, so just one check may not suffice. I guess the triggers could be organized into groups of identical firing criteria and then checked only once per group, but that seems like a pretty obscure optimization to make. I don't know how you would reorganize such groupings in a concurrency safe way when constraints were added or removed. Cheers, Jeff
