Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-472

Search queries without database index

    XMLWordPrintable

Details

    • Core: F - Sprint 92, Core: F - Sprint 93
    • 3
    • Prokopovych
    • Q2 2020 Hot Fix #1

    Description

      Overview
      This was observed in bugfest. Logs were having a lot of noise like below:

      WARNING: Doing FT search without index for holdings_record.jsonb->>'discoverySuppress', CQL >>> SQL: holdingsRecords.discoverySuppress = true >>> to_tsvector('simple', f_unaccent(holdings_record.jsonb->>'discoverySuppress')) @@ replace((to_tsquery('simple', f_unaccent('''true''')))::text, '&', '<->')::tsquery
      
      WARNING: Doing FT search without index for instance.jsonb->>'staffSuppress', CQL >>> SQL: staffSuppress = true >>> to_tsvector('simple', f_unaccent(instance.jsonb->>'staffSuppress')) @@ replace((to_tsquery('simple', f_unaccent('''true''')))::text, '&', '<->')::tsquery
      
      WARNING: Doing LIKE search without index for location.jsonb->>'primaryServicePoint', CQL >>> SQL: primaryServicePoint == 7068e104-aa14-4f30-a8bf-71f71cc15e07 >>> lower(f_unaccent(location.jsonb->>'primaryServicePoint')) LIKE lower(f_unaccent('7068e104-aa14-4f30-a8bf-71f71cc15e07'))
      
      WARNING: Doing FT search without index for item.jsonb->'status'->>'name', CQL >>> SQL: item.status.name = Available >>> to_tsvector('simple', f_unaccent(item.jsonb->'status'->>'name')) @@ replace((to_tsquery('simple', f_unaccent('''Available''')))::text, '&', '<->')::tsquery
      
      WARNING: Doing LIKE search without index for item.jsonb->>'purchaseOrderLineIdentifier', CQL >>> SQL: purchaseOrderLineIdentifier == 76c0ddee-cd91-4a0e-9295-fec513e47252 >>> lower(f_unaccent(item.jsonb->>'purchaseOrderLineIdentifier')) LIKE lower(f_unaccent('76c0ddee-cd91-4a0e-9295-fec513e47252'))
      
      WARNING: Doing FT search without index for holdings_record.jsonb->>'hrid', CQL >>> SQL: holdingsRecords.hrid = ho1757203 >>> to_tsvector('simple', f_unaccent(holdings_record.jsonb->>'hrid')) @@ replace((to_tsquery('simple', f_unaccent('''ho1757203''')))::text, '&', '<->')::tsquery
      
      WARNING: Doing LIKE search without index for service_point.jsonb->>'pickupLocation', CQL >>> SQL: pickupLocation == true >>> lower(f_unaccent(service_point.jsonb->>'pickupLocation')) LIKE lower(f_unaccent('true'))
      

      The logs for mod-inventory-storage are huge, therefore they can't be attached here. Please contact Zmiivska for logs if need be.

      Acceptance Criteria

      • the indexes are added
      • the warnings are disappeared

      Description from UIIN-1062 - Bugfest: Inventory. Instance, holdings, item segment. Query search - poor response time (about a minute):

      Overview: Query search in all three segments are very slow (poor performance) in Bugfest environment, compared to uChicago's environment (Edelweiss). The same search do also sometimes result in: 1000 records found - which is the new 999,999,999 records found.

      Steps to reproduce:

      1. Login to the bugfest environment, go to inventory
      2. Select the Instance segment, and search option Query search
      3. When searching: publication = "MIT Press" - Search (https://bugfest.folio.ebsco.com/inventory?qindex=querySearch&query=publication%20%3D%20%22MIT%20Press%22&sort=Title)

      Expected result:
      I get my search result up quickly - in about > 1 sec (https://wiki.folio.org/pages/viewpage.action?pageId=25729156)

      Actual result:
      First time I tried it took about 60 sec. And I got: 7.080 records found.

      Second time I tried it took less than 10 sec. And I got (only): 1000 records found

      Some more Query search tests:

      Segment Query search Time log
      Stop watch
      Results found
      Instance subjects = "history" 00:05 sec 35,813 records found
      Instance series = "cooperative information systems" 00:43 sec 7 records found
      Holdings holdingsRecords.callNumber = "HD1697.R9F79" 00:33 sec 1 record found
      Holdings holdingsRecords.holdingsStatements = "v.57:no.1-3" 00:16 sec 24 records found
      Item item.enumeration = "v.71" 00:08 sec 44,604 records found
      Item item.formerIds = "hzn64519" 00:37 sec 1 record found

      Other observations:
      Query search fails when search using parentheses, e.g. in the holdingsRecords.holdingsStatements = ".57:no.1-3(2014:Jul-2015:Jan) - then following error message pop up:
      https://bugfest.folio.ebsco.com/inventory/view/5917b896-7ca1-4f69-8b60-f4a9a9d2ddf0?qindex=querySearch&query=holdingsRecords.holdingsStatements%20%20%3D%20%22.57%3Ano.1-3%282014%3AJul-2015%3AJan%29&segment=holdings

      I'll investigate this some more, and file another bug ticket for this.

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                bohdan-suprun Bohdan Suprun
                Zmiivska Svitlana Zmiivska
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases