Investigate growth in total_time for project_authorizations query

It is suspected that this query might contribute to the growth in CPU usage in the primary DB server (https://gitlab.com/gitlab-org/gitlab/-/issues/395711)

We can see in the charts below, this query is taking up more time compared to Dec 2022/Jan 2023

queryid: 2068844745664171016

/application:sidekiq,correlation_id:38a19786a1660f170ca89320d2ef1bbc,jid:a7b83037175417d7d4789674,endpoint_id:AuthorizedProjectUpdate::UserRefreshFromReplicaWorker,db_config_name:main_replica/ WITH RECURSIVE "namespaces_cte" AS ((SELECT "namespaces"."id", "members"."access_level" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" = $1 AND "members"."source_type" = $2 AND "namespaces"."type" = $3 AND "members"."user_id" = $4 AND "members"."requested_at" IS NULL AND (access_level >= $5) AND "members"."state" = $6) UNION (SELECT "namespaces"."id", LEAST("members"."access_level", "group_group_links"."group_access") AS access_level FROM "namespaces" INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id" INNER JOIN "members" ON "group_group_links"."shared_with_group_id" = "members"."source_id" AND "members"."source_type" = $7 AND "members"."requested_at" IS NULL AND "members"."user_id" = $8 AND "members"."state" = $9 AND "members"."access_level" > $10 WHERE "namespaces"."type" = $11) UNION (SELECT "namespaces"."id", GREATEST("members"."access_level", "namespaces_cte"."access_level") AS access_level FROM "namespaces" INNER JOIN "namespaces_cte" ON "namespaces_cte"."id" = "namespaces"."parent_id" LEFT OUTER JOIN "members" ON "members"."source_id" = "namespaces"."id" AND "members"."source_type" = $12 AND "members"."requested_at" IS NULL AND "members"."user_id" = $13 AND "members"."state" = $14 AND "members"."access_level" > $15 WHERE "namespaces"."type" = $16)) SELECT "project_authorizations"."project_id", MAX(access_level) AS access_level FROM ((SELECT projects.id AS project_id, "members"."access_level" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "members"."type" IN ($17, $18) AND "members"."source_type" = $19 AND "members"."user_id" = $20 AND "members"."requested_at" IS NULL AND "members"."state" = $21) UNION (SELECT projects.id AS project_id, $22 AS access_level FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = $23 AND "namespaces"."type" = $24) UNION (SELECT "projects"."id" AS project_id, "namespaces"."access_level" FROM "namespaces_cte" "namespaces" INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id") UNION (SELECT "project_group_links"."project_id", LEAST("namespaces"."access_level", "project_group_links"."group_access") AS access_level FROM "namespaces_cte" "namespaces" INNER JOIN project_group_links ON project_group_links.group_id = namespaces.id INNER JOIN projects ON projects.id = project_group_links.project_id INNER JOIN namespaces p_ns ON p_ns.id = projects.namespace_id WHERE (p_ns.share_with_group_lock IS FALSE))) project_authorizations GROUP BY "project_authorizations"."project_id"

Thanos (total_time):

https://thanos.gitlab.net/graph?g0.expr=%0A%20%20%20%20rate(pg_stat_statements_seconds_total%7Benvironment%3D%22gprd%22%2C%20monitor%3D%22db%22%2C%20type%3D%22patroni%22%2C%20fqdn%3D%22patroni-main-2004-04-db-gprd.c.gitlab-production.internal%22%2C%20queryid%3D%222068844745664171016%22%7D%5B1h%5D)%0A&g0.tab=0&g0.stacked=0&g0.range_input=8w&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D

8 weeks up to 2012-01-31	8 weeks up to 2023-03-21

Thanos (total_calls):

https://thanos.gitlab.net/graph?g0.expr=%0A%20%20%20%20rate(pg_stat_statements_calls%7Benvironment%3D%22gprd%22%2C%20monitor%3D%22db%22%2C%20type%3D%22patroni%22%2C%20fqdn%3D%22patroni-main-2004-04-db-gprd.c.gitlab-production.internal%22%2C%20queryid%3D%222068844745664171016%22%7D%5B1h%5D)%0A&g0.tab=0&g0.stacked=0&g0.range_input=8w&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=1&g0.store_matches=%5B%5D&g0.end_input=2023-03-21%2020%3A37%3A16&g0.moment_input=2023-03-21%2020%3A37%3A16

8 weeks up to 2012-01-31	8 weeks up to 2023-03-21