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

Per Kaminsky <per(dot)kaminsky(at)hawk-intech(dot)com> writes:
> # ('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',)

Sure looks like B_shiftinstanceid_fkey and B_to_A_fkey are running
seq scans for lack of any matching index.

> 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 to make a rollback on any problem without causing an abnormal data state regarding the program.

Hmm, are you dropping and re-adding the reference-side indexes as part of
the same transaction that is doing this? I'm too short of caffeine to
recall details, but I think there are cases where a newly-made index
cannot be used right away.

regards, tom lane