Uploaded image for project: 'perf-testing'
  1. perf-testing
  2. PERF-48

Inventory. Slow query when filtering on Item status: Available

    XMLWordPrintable

Details

    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:

      1. Login to bugfest, go to Inventory
      2. In the Search and filter pane, then select the Item segment
      3. 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

          Activity

            People

              varunjavalkar Varun Javalkar
              charlotte Charlotte Whitt
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: