Improve performance of List a group’s subgroups API under load into next tier

The TTFB (Time to First Byte) of the List a group’s subgroups API is over our performance target under load:

* Environment:                1k
* Environment Version:        16.9.0-pre `b3c9b11e892`
* Option:                     60s_20rps
* Date:                       2024-02-06
* Run Time:                   1h 32m 40.43s (Start: 04:31:46 UTC, End: 06:04:27 UTC)
* GPT Version:                v2.13.0
❯ Overall Results Score: 95.58%
NAME                                                     | RPS  | RPS RESULT         | TTFB AVG  | TTFB P90              | REQ STATUS     | RESULT  
---------------------------------------------------------|------|--------------------|-----------|-----------------------|----------------|---------
api_v4_groups_group_subgroups                            | 20/s | 14.38/s (>16.00/s) | 1252.86ms | 1424.82ms (<1000ms)   | 100.00% (>99%) | FAILED¹²

Older Results (16.3.0-pre)

* Environment:                10k
* Environment Version:        16.3.0-pre `609852eb5b9`
* Option:                     60s_200rps
* Date:                       2023-08-10
* Run Time:                   1h 40m 31.71s (Start: 04:58:27 UTC, End: 06:38:59 UTC)
* GPT Version:                v2.13.0

NAME                                                     | RPS   | RPS RESULT           | TTFB AVG  | TTFB P90              | REQ STATUS     | RESULT  
---------------------------------------------------------|-------|----------------------|-----------|-----------------------|----------------|---------
api_v4_groups_group_subgroups                            | 200/s | 196.08/s (>160.00/s) | 235.43ms  | 286.64ms (<200ms)     | 100.00% (>99%) | FAILED²

• Last successful run was at https://gitlab.com/gitlab-org/quality/performance/-/wikis/Benchmarks/Latest/10k?version_id=a4fdc173c17e413500e3e8f44d9b2715db5d2604, when response time was 168.98ms, Environment Version: 16.3.0-pre 10431006956

Test Details

Testing was done on our 10k Reference Architecture environment with our lab condition GitLab Performance Tool pipeline. The project being tested is a copy of gitlabhq (tarball can be found here). GitLab Performance Tool tests information is listed at Current test details page.

The latest GitLab Performance pipeline results can always be found here. Through this page full Server Metrics can be found via the Metrics Dashboard link on that page.

As per our performance targets this endpoint's TTFB metric is above the target of 100 ms which is severity3. Task is to improve the endpoint's performance into next tier.