Uploaded image for project: 'mod-permissions'
  1. mod-permissions
  2. MODPERMS-66

Add indexes to improve performance

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      CP: sprint 73
    • Story Points:
      0.5
    • Development Team:
      Core: Platform

      Description

      During BugFest q3.2-2019 testing, noticed below SQL queries are slow. Adding indexes can help (see the timing number before and after).

      create index on fs00001000_mod_permissions.permissions using gin ((lower(f_unaccent(jsonb->>'permissionName'))) gin_trgm_ops);
      SELECT COUNT(*) FROM fs00001000_mod_permissions.permissions  WHERE (((((((((((lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.items.collection.get'))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.material-types.item.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.material-types.collection.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.loan-types.item.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.loan-types.collection.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.locations.item.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.locations.collection.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.holdings.collection.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.holdings.item.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.instances.collection.get')))) OR (lower(f_unaccent(permissions.jsonb->>'permissionName')) LIKE lower(f_unaccent('inventory-storage.instances.item.get')))) AND (lower(f_unaccent(permissions.jsonb->>'dummy')) LIKE lower(f_unaccent('false')))
      30.873 ms vs 3.697 ms
      

        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