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

On 3/28/22 10:49 AM, Per Kaminsky wrote:
> The usage of "on update cascade" certainly sounds plausible. Since the
> update does only happen rarely if there is a special need to completely
> change the backend structure it is not added to the FK at the moment.
> Just ran the whole thing again with the "ANALYZE A; ANALYZE B" but with
> no gain unfortunately.
> Just looking at the output i am also quite puzzled why an update to a
> field in B which has no connection itself to A would trigger all the
> foreign keys, except if this is a deferred call from updating A only now
> triggered by touching data in B.
>

An UPDATE in Postgres is essentially a DELETE of the old row version and
an INSERT of the new row version. I'm going to guess the INSERT of the
new row version fires the FK triggers on B.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com