Re: Performance issues on FK Triggers after replacing a primary column

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(dot)klaver(at)aklaver(dot)com