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

Inventory filtering by Item status is slow (when searching item status: Available)

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      Core: F - Sprint 90
    • Story Points:
      1
    • Development Team:
      Prokopovych

      Description

      Overview
      This was observed in bugfest fameflower. Filtering items by status is either slow or sometimes even hangs up the DB
      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
      

      Steps to reproduce
      1.Login to bugfest
      2. Go to Inventory-> Item tab
      3. check the filter "Available"

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                bohdan-suprun Bohdan Suprun
                Reporter:
                KVupp Kruthi Vuppala
                Tester Assignee:
                Charlotte Whitt Charlotte Whitt
                Votes:
                0 Vote for this issue
                Watchers:
                10 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases