Namespace#all_projects performance degradation for complex group structures
With #260327 (closed) we've introduced recursive lookup in Namespace#all_projects
in 13.6 under a feature flag, and removed the feature flag in 13.10 with !55043 (merged).
We've validated on GitLab.com, from @ahegyi in #260327 (comment 427899547) we've noticed that the "maximum" should be around 400ms. However, it seems that the "worst" case scenario can get worse exponentially - I'm assuming based on the complex hierarchy structure of a group.
A large Ultimate customer is reporting performance problems with Namespace#all_projects
after upgrading to 13.12 from 13.9 in their staging environment (blocking their upgrade in production), there is an execution plan in the ZD ticket (internal only), key points based on what I'm seeing:
The Materialize has (loops=21574
), and the execution time for Namespace#all_projects
looks like:
Planning Time: 1.391 ms
Execution Time: 294786.284 ms
For background, simply attempting to load projects and groups part of this complex group hierarchy fails in ProjectsController#show*
, Project Exists? (220113.3ms)
for an example query (more specifically, ee/app/models/ee/namespace.rb:250:in `any_project_with_shared_runners_enabled?'
).
It doesn't seem like there's any workaround for this other than manually hot-patching the method to use the previous way of doing this.