1. Proposed title of this feature request

Optimize model.permission.get_org_wide_permissions (if possible)

2. What is the nature and description of the request?

Hello team. One of our architects was able to trace the issue back to the query below and this function
https://github.com/quay/quay/blob/master/auth/permissions.py#L155
that takes between 20 seconds and even 1 hour while running directly with pgadmin or dbeaver against the database.. That's why gunicorn was timing out.

We increased from 14 to 18 vCPUs for Postgresql and so far queries are going in less than a second. Even when Postgresql CPU consumption is still at 100% most of the time.

SELECT "t1"."id", "t1"."name", "t1"."organization_id", "t1"."role_id", "t1"."description", "t2"."id", "t2"."uuid", "t2"."username", "t2"."password_hash", "t2"."email", "t2"."verified", "t2"."stripe_id", "t2"."organization", "t2"."robot", "t2"."invoice_email", "t2"."invalid_login_attempts", "t2"."last_invalid_login", "t2"."removed_tag_expiration_s", "t2"."enabled", "t2"."invoice_email_address", "t2"."given_name", "t2"."family_name", "t2"."company", "t2"."location", "t2"."maximum_queued_builds_count", "t2"."creation_date", "t2"."last_accessed", "t3"."id", "t3"."name" FROM "team" AS "t1" INNER JOIN "teamrole" AS "t3" ON ("t1"."role_id" = "t3"."id") INNER JOIN "user" AS "t2" ON ("t1"."organization_id" = "t2"."id") INNER JOIN "teammember" AS "t4" ON ("t4"."team_id" = "t1"."id") INNER JOIN "user" AS "t5" ON ("t4"."user_id" = "t5"."id")
WHERE (("t5"."id" = 1) AND ("t2"."organization" = True))

We are still testing, but the preliminary results look promising.

3. Why does the customer need this? (List the business requirements here)

18 vCPUs is a lot of vCPUs for a PostgreSQL instance! The code should be optimized to use fewer CPU resources.

4. List any affected packages or components.

Quay