Details
-
Type:
Story
-
Status: Closed (View Workflow)
-
Priority:
P2
-
Resolution: Cannot Reproduce
-
Labels:
-
Template:customfield_11100 35952
-
Epic Link:
Description
App name/area of issue: Inventory (Search and filter)
Brief statement of issue: Filtering on Item status
UIIN-1091- Note:* This issue was observed in bugfest fameflower. Filtering items by status is either slow or sometimes even hangs up the DB
How many users at any given time: This is a search with a staff user probably never would do intentionally, but it could happen by accident.
Volume of data: Gigantic search, if the tenant is a large library as uChicago (approximately: 10 mio item records)
Expected response time:
Outline steps for test:
- Login to bugfest, go to Inventory
- In the Search and filter pane, then select the Item segment
- When setting check mark in the Item status filter: "Available"
This is the query that was observed to take the longest time:
WITH headrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>'title')) AS title 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 to_tsvector('simple', f_unaccent(item.jsonb->'status'->>'name')) @@ replace((to_tsquery('simple', f_unaccent('''Available''')))::text, '&', '<->')::tsquery))) 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 title 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 to_tsvector('simple', f_unaccent(item.jsonb->'status'->>'name')) @@ replace((to_tsquery('simple', f_unaccent('''Available''')))::text, '&', '<->')::tsquery))) AND (SELECT COUNT(*) FROM headrecords) < 100 ) SELECT jsonb, title, 0 AS count FROM headrecords WHERE (SELECT COUNT(*) FROM headrecords) >= 100 UNION (SELECT jsonb, title, (SELECT COUNT(*) FROM allrecords) AS count FROM allrecords ORDER BY title LIMIT 100 OFFSET 0 ) ORDER BY title
The same search in FOLIO Snapshot is: https://folio-snapshot.aws.indexdata.com/inventory?filters=itemStatus.Available&segment=items&sort=Title
Attachments
Issue Links
- relates to
-
FOLIO-2573 Paused due to POC of Elastic Search. All Search Options Should be Supported by a Database Index
-
- Closed
-
-
MODINVSTOR-510 Inventory filtering by Item status is slow (when searching item status: Available)
-
- Closed
-