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

            blocks

            Task - A task that needs to be done. CIRC-448 investigate and verify backend API performance issues for UICHKIN-111

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

            Bug - A problem which impairs or prevents the functions of the product. CHAL-79 Check in takes ~4 sec when item has request on it

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

            Task - A task that needs to be done. MODPERMS-67 verify and reduce the cost of expanding permissions

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

            Bug - A problem which impairs or prevents the functions of the product. UICHKIN-111 Check in takes ~4 sec when item has request on it

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

            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