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.klaver@aklaver.com