Details
-
Story
-
Status: Closed (View Workflow)
-
P2
-
Resolution: Done
-
None
-
-
Core: F - Sprint 91, Core: F - Sprint 92
-
5
-
Prokopovych
Description
Overview:
During a 45 minutes performance test run of the check-in/out workflow against FameFlower release, over 64000 missing index lines were logged by mod-circulation-storage. After deduping for similar lines with different UUIDs these were:
WARNING: Doing SQL query without index for scheduled_notice.jsonb->>'nextRunTime', CQL >>> SQL: nextRunTime < >>> scheduled_notice.jsonb->>'nextRunTime' <'2020-05-20T03:07:08.090Z'
WARNING: Doing FT search without index for request.jsonb->>'pickupServicePointId', CQL >>> SQL: pickupServicePointId = 130d8bff-bdbd-4dc5-a4ac-6d970f4918ff >>> to_tsvector('simple', f_unaccent(request.jsonb->>'pickupServicePointId')) @@ replace((to_tsquery('simple', f_unaccent('''130d8bff-bdbd-4dc5-a4ac-6d970f4918ff''')))::text, '&', '<->')::tsquery
WARNING: Doing FT search without index for request.jsonb->>'requesterId', CQL >>> SQL: requesterId = 001164c0-5466-4822-86f2-dcd2393a7ef7 >>> to_tsvector('simple', f_unaccent(request.jsonb->>'requesterId')) @@ replace((to_tsquery('simple', f_unaccent('''001164c0-5466-4822-86f2-dcd2393a7ef7''')))::text, '&', '<->')::tsquery
WARNING: Doing FT search without index for request.jsonb->>'status', CQL >>> SQL: status = Open >>> to_tsvector('simple', f_unaccent(request.jsonb->>'status')) @@ replace((to_tsquery('simple', f_unaccent('''Open''')))::text, '&', '<->')::tsquery
WARNING: Doing LIKE search without index for jsonb->>'requestId', CQL >>> SQL: requestId == 01819cdb-de38-4562-9835-14071dcaf53d >>> lower(f_unaccent(jsonb->>'requestId')) LIKE lower(f_unaccent('01819cdb-de38-4562-9835-14071dcaf53d'))
WARNING: Doing LIKE search without index for request.jsonb->>'requesterId', CQL >>> SQL: requesterId == 005de89f-bfe1-4bf7-a1e3-f34a707ace21 >>> lower(f_unaccent(request.jsonb->>'requesterId')) LIKE lower(f_unaccent('005de89f-bfe1-4bf7-a1e3-f34a707ace21'))
Note: UUIDs are interchangeable, i.e., it's not complaining about a specific UUID in the query, but the query itself.
Addressing these missing indexes will eliminate this high number of logging and may speed up response times.
Also note that these queries may need to be revisited because of their use of "=" sign, e.g., pickupServicePointId = 130d8bff-bdbd-4dc5-a4ac-6d970f4918ff
WARNING: Doing FT search without index for request.jsonb->>'pickupServicePointId', CQL >>> SQL: pickupServicePointId = 130d8bff-bdbd-4dc5-a4ac-6d970f4918ff >>> to_tsvector('simple', f_unaccent(request.jsonb->>'pickupServicePointId')) @@ replace((to_tsquery('simple', f_unaccent('''130d8bff-bdbd-4dc5-a4ac-6d970f4918ff''')))::text, '&', '<->')::tsquery
WARNING: Doing FT search without index for request.jsonb->>'requesterId', CQL >>> SQL: requesterId = 001164c0-5466-4822-86f2-dcd2393a7ef7 >>> to_tsvector('simple', f_unaccent(request.jsonb->>'requesterId')) @@ replace((to_tsquery('simple', f_unaccent('''001164c0-5466-4822-86f2-dcd2393a7ef7''')))::text, '&', '<->')::tsquery
When searching by an ID, in the query it's better to use "==" as it uses the B-tree index to search, whereas a query using "=" will result in a full text search, which is not efficient for searching when there is already an index on the ID.
Steps to Reproduce:
- Navigate to the Checkout page
- Submit a patron barcode (any) to search the patron.
- Submit an item barcode (any)
Because it's a test that involves both checkin and checkout worfklows, we'll need to check in any item: - Navigate to the Checkin page
- Submit the barcode to check in
*Expected Results: *
The workflow above does not produce these warnings
Actual Results:
These warnings were seen in mod-circulation-storage and in a high number when the workflow is repeated over and over in a performance test run.
Interested parties:
Customers, everyone.
Acceptance criteria:
These warnings are not logged anymore when executing the above workflows.
TestRail: Results
Attachments
Issue Links
- has to be done after
-
PERF-62
Identify circulation CQL queries that could be improved using exact match search operator (==)
-
- Closed
-
- has to be done before
-
PERF-87
Rerun Check-in-Check-out workflow (PERF-25) with mod-circulation-storage v12.1.0
-
- Closed
-
- is cloned by
-
CIRCSTORE-216
Missing Indexes Warnings
-
- Closed
-
- relates to
-
PERF-9
Run Checkin-checkout workflow tests against FF
-
- Closed
-
-
UICHKOUT-641
Use right truncation to fetch all open requests
-
- Closed
-
-
FOLIO-2573 Paused due to POC of Elastic Search. All Search Options Should be Supported by a Database Index
-
- Closed
-
-
PERF-37
Check in items
-
- Closed
-