The tables have Index to each other on each foreign key. The index itself was not touched though, and a remove/recreate did not help. Could it be possible, that when the PK and FK values are replaced the Index is not (immediately) updated and thus cannot be used?
The temporary table is not shown. It is created to insert the new values from file, then used to update the correct table with the new values, and then removed, it has no connection (FK or something else) to any other table.
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Monday, March 28, 2022 17:41
To: Per Kaminsky <per.kaminsky@hawk-intech.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: Performance issues on FK Triggers after replacing a primary column
On 3/28/22 00:22, Per Kaminsky wrote:
> Sorry, i forgot to add the following:
>
> Explain / Analyze for the last "update type on B" call, normally there
> the table has million of rows but i removed most of them since otherwise
> it would not finish sometime soon:
>
> ('Update on B (cost=0.00..71.50 rows=1000 width=244) (actual
> time=18.015..18.015 rows=0 loops=1)',)
> (' Buffers: shared hit=26141 read=21 dirtied=77 written=54',)
> (' -> Seq Scan on B (cost=0.00..71.50 rows=1000 width=244) (actual
> time=0.102..0.536 rows=1000 loops=1)',)
> (' Filter: ((type IS NULL) AND (firmid = 1))',)
> (' Buffers: shared hit=59',)
> ('Planning Time: 0.430 ms',)
> # ('Trigger for constraint B_firmid_fkey: time=25.592 calls=1000',)
> # ('Trigger for constraint B_type_fkey: time=14.899 calls=1000',)
> # ('Trigger for constraint B_userid_fkey: time=16.146 calls=1000',)
> # ('Trigger for constraint B_version_fkey: time=11.285 calls=1000',)
> # ('Trigger for constraint B_common_fkey: time=7.913 calls=1000',)
> # ('Trigger for constraint B_shiftinstanceid_fkey: time=8543.369
> calls=1000',)
> ('Trigger for constraint B_to_A_fkey: time=27246.413 calls=1000',)
> ('Execution Time: 35884.978 ms',)
>
> The tables have Index on each other. The vacuum can not be called, since
> all those table modifications are part of one big transaction to be able
Analyze can be run by itself in the transaction. As Tom said I am not
seeing any information about indexes on the tables(s). Also, which one
of the tables you showed is the temporary one or was that not shown?
> to make a rollback on any problem without causing an abnormal data state
> regarding the program.
>
--
Adrian Klaver
adrian.klaver@aklaver.com