Hi there,
i recently stumbled upon a performance issue which i can't really understand.
The issue occured when i (roughly) did the following without a commit in between:
- Replace the PK column of a table A which has a referencing table B - I have removed the FK from the referencing tables B and have recreated them afterwards
- Now following i am working in one of the referencing tables B, updating columns. This takes an extremely large amount of time. This means, e.g. updating 1000 rows would now need 35-40 seconds.
- The "explain" tells, that the Foreign Key trigger in B referencing A causes this mishap.
- Re-creating the Index in B for the column referencing A does not cause any performance gain.
- If i again remove the FK to A from B this again shrinks back to some milliseconds.
The question is, what does cause the FK trigger to be less performant than recreating the FK constraint? If executed on 100k or even 1m rows the operation takes hours or even days.
Thank you very much.
Sincerely, Per Kaminsky