Details
-
Task
-
Status: Closed (View Workflow)
-
P2
-
Resolution: Done
-
None
-
-
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
-
CIRC-448 investigate and verify backend API performance issues for UICHKIN-111
-
- Closed
-
- relates to
-
CHAL-79 Check in takes ~4 sec when item has request on it
-
- Closed
-
-
MODPERMS-67 verify and reduce the cost of expanding permissions
-
- Closed
-
-
UICHKIN-111 Check in takes ~4 sec when item has request on it
-
- Closed
-