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

    • Core: F - Sprint 90
    • 1
    • 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

          1. screenshot-1.png
            26 kB
            Bohdan Suprun
          2. screenshot-2.png
            383 kB
            Bohdan Suprun

          Issue Links

            relates to

            Task - A task that needs to be done. FOLIO-2563 SPIKE: propose prevention of DoS via CQL query

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Story - Created by Jira Software - do not edit or delete. Issue type for a user story. MODINVSTOR-500 Instance source index for = search

            • P3 - low priority level, item will be considered for inclusion in the next dev cycle
            • Closed

            Story - Created by Jira Software - do not edit or delete. Issue type for a user story. PERF-48 Inventory. Slow query when filtering on Item status: Available

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Task - A task that needs to be done. FOLIO-2573 Paused due to POC of Elastic Search. All Search Options Should be Supported by a Database Index

            • P3 - low priority level, item will be considered for inclusion in the next dev cycle
            • Closed

            Activity

              People

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

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases