Details
-
Bug
-
Status: Closed (View Workflow)
-
P3
-
Resolution: Cannot Reproduce
-
19.4.3
-
None
-
Prokopovych
-
R1 2021
-
GBV
Description
Overview:
When searching for Items that are Available and of (material) type Book, the query that is invoked in the back end runs for at least 40 minutes and consumes lots CPU.
Steps to Reproduce:
- Go to Inventory app
- Select "Available" in the Item Status dropdown list
- Select "book" in the Material type dropdown list
Expected Results:
Results coming back quickly, within seconds at the most
Actual Results:
No results coming back. The database gets busy querying for 40 minutes and sometimes the database's CPU utilization spiked to as high as 90% . See the blue lines in this graph:
Additional Information:
Reproducible in Bugfest and PTF environments
CQL Query:
/instance-storage/instances?query=(item.materialTypeId=="025ba2c5-5e96-4667-a677-8186463aee69"+and+item.status.name=="Available")+sortby+title&limit=100&offset=0
SQL Query:
WITH headrecords AS ( SELECT jsonb, (lower(f_unaccent(jsonb->>'title')) ) AS data_column FROM fs09000000_mod_inventory_storage.instance WHERE ((instance.id IN ( SELECT instanceId FROM holdings_record WHERE holdings_record.id IN ( SELECT holdingsRecordId FROM item WHERE materialTypeId='025ba2c5-5e96-4667-a677-8186463aee69'))) AND (instance.id IN ( SELECT instanceId FROM holdings_record WHERE holdings_record.id IN ( SELECT holdingsRecordId FROM item WHERE lower(f_unaccent(item.jsonb->'status'->>'name')) LIKE lower(f_unaccent('Available')))))) AND left(lower(f_unaccent(jsonb->>'title')) ,600) < ( SELECT left(lower(f_unaccent(jsonb->>'title')) ,600) FROM fs09000000_mod_inventory_storage.instance ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600) OFFSET 10000 LIMIT 1 ) ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600) LIMIT 100 OFFSET 0 ), allrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>'title')) AS data_column FROM fs09000000_mod_inventory_storage.instance WHERE ((instance.id IN ( SELECT instanceId FROM holdings_record WHERE holdings_record.id IN ( SELECT holdingsRecordId FROM item WHERE materialTypeId='025ba2c5-5e96-4667-a677-8186463aee69'))) AND (instance.id IN ( SELECT instanceId FROM holdings_record WHERE holdings_record.id IN ( SELECT holdingsRecordId FROM item WHERE lower(f_unaccent(item.jsonb->'status'->>'name')) LIKE lower(f_unaccent('Available')))))) AND (SELECT COUNT(*) FROM headrecords) < 100 ), totalCount AS ( SELECT fs09000000_mod_inventory_storage.count_estimate(' SELECT lower(f_unaccent(jsonb->>''title'')) AS data_column FROM fs09000000_mod_inventory_storage.instance WHERE (instance.id IN( SELECT instanceId FROM holdings_record WHERE holdings_record.id IN ( SELECT holdingsRecordId FROM item WHERE materialTypeId=''025ba2c5-5e96-4667-a677-8186463aee69''))) AND (instance.id IN ( SELECT instanceId FROM holdings_record WHERE holdings_record.id IN ( SELECT holdingsRecordId FROM item WHERE lower(f_unaccent(item.jsonb->''status''->>''name'')) LIKE lower(f_unaccent(''Available'')))))') AS count) SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM headrecords WHERE (SELECT COUNT(*) FROM headrecords) >= 100 UNION ( SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM allrecords ORDER BY data_column LIMIT 100 OFFSET 0 ) ORDER BY data_column
Interested parties: julianladisch marcjohnson cboerema Emelianov
TestRail: Results
Attachments
Issue Links
- is duplicated by
-
MODINVSTOR-629 item search for "england" keyword status "available" takes 1.4 minutes
-
- Closed
-