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