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

Add indexes to improve performance

    XMLWordPrintable

Details

    • CP: sprint 73
    • 0.5
    • 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

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

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases