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