On 3/27/22 09:30, Per Kaminsky wrote:
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.
Post the query and the explain.
Also have you run vacuum and/or analyze on the tables involved?
* 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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
