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

filtering /item-storage/items when used with 'query=id=XXX' is too slow

    XMLWordPrintable

    Details

    • Template:
    • Development Team:
      Core: Platform

      Description

      When calling the api request to mod-circulation /circulation/loans?query=(userId=<useId>)&limit=1 (the entry in the Jenkins perf job is mod-circulation GET 200 circulation_loans check loans by user d).

      you issue the DB query:

      SELECT * FROM diku_mod_inventory_storage.items_mt_view WHERE lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('d1600ee1-c084-4da7-9c85-cdbcc143cb4c')) LIMIT 1 OFFSET 0

      This query does not scale well due to not using indexes. This is observed on tenants that are much larger then those that the vagrant box contains, on the order of 300k items

      The preferred fix is to change the query to no longer use the view

      Current theory is that the view misleads the cql translation into the current form rather then a higher performing and simpler version.

      Eric Valuk list the CQL query string (requests) and generated SQL we want to optimize.

      The query string:

      /item-storage/items?query=id=d1600ee1-c084-4da7-9c85-cdbcc143cb4c&limit=1
      

      results in

      SELECT * FROM diku_mod_inventory_storage.items_mt_view  WHERE lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('d1600ee1-c084-4da7-9c85-cdbcc143cb4c')) LIMIT 1 OFFSET 0
      

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                evaluk Eric Valuk
                Reporter:
                jakub Jakub Skoczen
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases