Improve billable users query
What does this MR do and why?
- remove distinct from queries
- add limit logic to not do queries unless needed
- add index as per analysis
- add logging for notification as per https://gitlab.com/gitlab-org/gitlab/-/issues/384527#note_1202287178
queries with complex namespace id that will run against in production
users being queried | original buffer size | remove duplicate distinct(buffer size) | with limit added(buffer size) | with specialized member index | without large namespace union(use_traversal_ids_for_ancestor_scopes feature flag enabled in production) |
---|---|---|---|---|---|
group_member_user_ids | 585 | 582 | 582 | 481 | n/a |
project_member_user_ids | 645 | 642 | 642 | 580 | n/a |
shared_group_user_ids | 210 | 207 | 207 | 138 | 207 |
shared_project_user_ids | 2284 | 2281 | 2281 | 2183 | 2257 |
queries with gitlab-org namespace
will never run against in production since it will only run on free/private namespaces, but is a good for benchmarking
users being queried | original buffer size | remove duplicate distinct(buffer size) | with limit added(buffer size) | with specialized member index | without large namespace union(use_traversal_ids_for_ancestor_scopes feature flag enabled in production) |
---|---|---|---|---|---|
group_member_user_ids | 32119 48ms | 32116 | 23036 | 21879 31ms | n/a |
project_member_user_ids | 21960 44ms | 21957 | 18145 | 17989 31ms | n/a |
shared_group_user_ids | 34623 59ms | 34620 | 25340 | 23895 | 23905 41ms |
shared_project_user_ids | 48907 67ms | 48904 | 39596 | 38121 | 38157 53ms |
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #383264
Edited by Doug Stull