Uploaded image for project: 'mod-circulation-storage'
  1. mod-circulation-storage
  2. CIRCSTORE-215

Missing Indexes Warnings

    XMLWordPrintable

Details

    • 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

            Activity

              People

                bohdan-suprun Bohdan Suprun
                mtraneis Martin Tran
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases