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.
