Uploaded image for project: 'mod-circulation-storage'
  1. mod-circulation-storage
  2. CIRCSTORE-115

Improve loan related query performance

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      Core: Platform - Sprint 59
    • Story Points:
      1
    • Development Team:
      Core: Platform

      Description

      The SQLs (see db.log.tar.gz) generated by nightly perf test include many loan related querires. Most of those are not using any indexes. For example:

      duration: 988.684 ms  statement: SELECT COUNT(*) FROM supertenant_mod_circulation_storage.loan  WHERE lower(f_unaccent(loan.jsonb->>'userId')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))f9785fc5-83a1-4d1d-9e5b-166a6a8bdfb1($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))
      
      duration: 181.304 ms  statement: SELECT COUNT(*) FROM supertenant_mod_circulation_storage.loan  WHERE (lower(f_unaccent(loan.jsonb->>'itemId')) LIKE lower(f_unaccent('c63c0524-305e-4b35-b922-c112c90dfb25'))) AND (lower(f_unaccent(loan.jsonb->'status'->>'name')) LIKE lower(f_unaccent('Open')))
      
      duration: 32.081 ms statement: SELECT COUNT(*) FROM supertenant_mod_circulation_storage.loan WHERE (jsonb->>'id')='7e375420-9656-433b-a685-4e89ad55c0a4'
      

      Suggest to add relevant indexes in schema.json to speed up those queries. Note, the above second query has been improved since CIRCSTORE-114.

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                hji Hongwei Ji
                Reporter:
                hji Hongwei Ji
                Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases