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@xxxxxxxxxxx
