Issue: 

Performance of inventory app degrades greatly with updatecompleteupdateddate_item_insert_update trigger enabled. Specifically changing item data in UI failed with the trigger enabled. Changing item data directly through the database took 1 minute with he trigger enabled.

With trigger disabled changing item data in UI was done successfully, changing item data directly through the database took 4 ms.

Environment: pcp1

Query example:

update fs07000001_mod_inventory_storage.item

  set jsonb = jsonb || '{"barcode": "$1"}' where id='$2'

Query plan with trigger enabled:

"Update on item  (cost=0.42..8.45 rows=1 width=211) (actual time=66849.137..66849.138 rows=0 loops=1)"

"  ->  Index Scan using item_pkey on item  (cost=0.42..8.45 rows=1 width=211) (actual time=0.037..0.041 rows=1 loops=1)"

"        Index Cond: (id = '59f2a85f-0e9c-47ae-91c5-773270b20eea'::uuid)"

"Planning Time: 0.142 ms"

"Trigger set_id_in_jsonb: time=0.036 calls=1"

"Trigger set_item_md_json_trigger: time=0.086 calls=1"

"Trigger set_item_ol_version_trigger: time=0.048 calls=1"

"Trigger update_item_references: time=0.032 calls=1"

"Trigger update_item_status_date: time=0.030 calls=1"

"Trigger updatecompleteupdateddate_item_insert_update: time=66848.199 calls=1"

"Execution Time: 66849.187 ms"

Query plan with trigger disabled:

"Update on item  (cost=0.42..8.45 rows=1 width=211) (actual time=3.433..3.433 rows=0 loops=1)"

"  ->  Index Scan using item_pkey on item  (cost=0.42..8.45 rows=1 width=211) (actual time=1.365..1.367 rows=1 loops=1)"

"        Index Cond: (id = 'd9b90f23-cc0d-4f7c-a318-9bcb1f36145c'::uuid)"

"Planning Time: 0.146 ms"

"Trigger set_id_in_jsonb: time=0.041 calls=1"

"Trigger set_item_md_json_trigger: time=0.087 calls=1"

"Trigger set_item_ol_version_trigger: time=0.050 calls=1"

"Trigger update_item_references: time=0.034 calls=1"

"Trigger update_item_status_date: time=0.031 calls=1"

"Execution Time: 3.484 ms"