Overview:
**

The use of these jobs profiles directly correlate with the RDS's CPU utilization spikes from the below queries.

The following API call was made by these two job profiles:

ACQ3 - GOBI monograph invoice 1880-13

ACQ2 - GOBI monograph invoice 1880-11

Here are the file names triggering these slow queries:

1880110630.edi
1880130629.edi

Expected Results:

A response duration of milliseconds.
Actual Results:

A response duration of 2 to 4 seconds.
Additional Information:
URL: N/A - can happen with any tenant

Example API:

Example queries with response time:

duration: 2370.580 ms  statement: SELECT jsonb,id FROM <tenantId>_mod_orders_storage.titles WHERE ((titles.poLineId IN  ( SELECT id FROM po_line WHERE po_line.purchaseOrderId IN  ( SELECT id FROM purchase_order WHERE get_tsvector(f_unaccent(purchase_order.jsonb->>'acqUnitIds')) @@ tsquery_phrase(f_unaccent('e2a4d34c-66a1-435a-97f2-d14364b58c66'))))) OR ((true) AND ( (titles.poLineId IN  ( SELECT id FROM po_line WHERE po_line.purchaseOrderId IN  ( SELECT id FROM purchase_order WHERE CASE WHEN length(lower(f_unaccent('[]'))) <= 600 THEN left(lower(f_unaccent(purchase_order.jsonb->>'acqUnitIds')),600) NOT LIKE lower(f_unaccent('[]')) ELSE left(lower(f_unaccent(purchase_order.jsonb->>'acqUnitIds')),600) NOT LIKE left(lower(f_unaccent('[]')),600) OR lower(f_unaccent(purchase_order.jsonb->>'acqUnitIds')) NOT LIKE lower(f_unaccent('[]')) END))) IS NOT TRUE))) AND (poLineId='47530b17-b5f4-4605-b497-b6767d520db0') LIMIT 1 OFFSET 0

duration: 3749.440 ms  statement: SELECT <tenantId>_mod_orders_storage.count_estimate('SELECT jsonb,id FROM <tenantId>_mod_orders_storage.titles WHERE ((titles.poLineId IN  ( SELECT id FROM po_line WHERE po_line.purchaseOrderId IN  ( SELECT id FROM purchase_order WHERE get_tsvector(f_unaccent(purchase_order.jsonb->>''acqUnitIds'')) @@ tsquery_phrase(f_unaccent(''e2a4d34c-66a1-435a-97f2-d14364b58c66''))))) OR ((true) AND ( (titles.poLineId IN  ( SELECT id FROM po_line WHERE po_line.purchaseOrderId IN  ( SELECT id FROM purchase_order WHERE CASE WHEN length(lower(f_unaccent(''[]''))) <= 600 THEN left(lower(f_unaccent(purchase_order.jsonb->>''acqUnitIds'')),600) NOT LIKE lower(f_unaccent(''[]'')) ELSE left(lower(f_unaccent(purchase_order.jsonb->>''acqUnitIds'')),600) NOT LIKE left(lower(f_unaccent(''[]'')),600) OR lower(f_unaccent(purchase_order.jsonb->>''acqUnitIds'')) NOT LIKE lower(f_unaccent(''[]'')) END))) IS NOT TRUE))) AND (((((((((((((((poLineId=''2589ec1a-6f4e-4f66-90ab-73d518a8249b'') OR (poLineId=''f4ebc379-ce06-4638-8f8d-e7be0545251e'')) OR (poLineId=''5b81e690-e177-42b7-ae57-0341fdba57a0'')) OR (poLineId=''0fe88e47-d9d6-49d2-8a25-36094fb82e97'')) OR (poLineId=''977f2c94-a39c-4ba4-84e3-1f2292093069'')) OR (poLineId=''ab9e0b22-4b66-4315-ba95-f9e94303c1b6'')) OR (poLineId=''3dd7d4f0-a08f-4043-a21c-b63f27c196b6'')) OR (poLineId=''e8550d81-2741-42ed-9517-2ef55d2c393f'')) OR (poLineId=''1b119a3f-806b-457e-8a72-2c12e8199efa'')) OR (poLineId=''13c8e2b3-a3c7-4fb7-b442-5991c59ab53d'')) OR (poLineId=''13d1bc7a-968f-438d-bdba-809ae848db45'')) OR (poLineId=''955baf26-5655-4404-aa49-33468cc3bbbb'')) OR (poLineId=''279ef532-a0f6-43e2-b5a9-2d823f6d8785'')) OR (poLineId=''7281f069-6d4a-4afe-9c04-f20556b0263f'')) OR (poLineId=''e2f00bec-9a14-484e-b5b7-b300c6eaf696''))')

Module versions:
mod-invoice:5.6.3

mod-invoice-storage:5.6.0

mod-orders:12.6.7

mod-orders-storage:13.5.0

mod-finance:4.7.1

mod-finance-storage:8.4.2

Interested parties: