search

msupply-foundation / conforma-server

Conforma application manager (IRIMS) back-end
GNU Affero General Public License v3.0
4 stars 1 forks source link

1077 improve application list performance #1079

Closed andreievg closed 11 months ago

andreievg commented 11 months ago

Partially fixes: https://github.com/msupply-foundation/conforma-server/issues/1077

Replaced review_assignment_available_sections (which is called for every row), with a view and function that return array and an array comparison, approximate 1.5x improvement without row level permissions.

Creates a view permission_flattened, which is used in policies instead of a in statement that extracts array from jwt (i think postgres can't quite optimise in statements with arrays of values) approximate 2.5x improvement.

@CarlosNZ, snapshot loading is super fast (considering the size of datafile), thanks for doing the work on that, super cool.

We may want to remove some unused stuff from trigger/helpers (like review_actions functions etc.., it's a carry over from previous application_list). Also probably should remove jwtPermissionbigint and jwtPermission_arraybigint from permission policy generation

andreievg commented 11 months ago

We should probably talk through the main logic in the office tomorrow if you're around?

Yep that's sounds good, thanks for review so far

andreievg commented 11 months ago

I rebased to: https://github.com/msupply-foundation/conforma-server/pull/1080, and I think did all of the change request, then check out https://github.com/msupply-foundation/conforma-server/pull/1080, and loaded snapshot. Stopped server, checked out this branch, change version in package.json to 0.8.0. Started server, and saw the performance increase, i think it's working as expected with migrations

andreievg commented 11 months ago

There is a carry over TODO, to remove unused views/functions, there is also a carry over to only expose things we are updating/viewing in API