AuthorizedProjectsWorker, AuthorizedProjectUpdate::UserRefreshWithLowUrgencyWorker is issuing many hotspot recursive CTE namespace queries

This morning GitLab.com alerting fired with an SLO alert for the rails_sql SLI:

https://dashboards.gitlab.net/d/patroni-main/patroni-overview?orgId=1

There were also SLO alerts for Sidekiq at the same time:

https://dashboards.gitlab.net/d/sidekiq-main/sidekiq-overview?orgId=1&from=1616483958715&to=1616503979310

Looking at the postgres slowlog, and pg_stat_statements metrics, we can see that the server is dominated by requests for queryid -7232084447659837857.

This query:

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))
  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" = $6
      AND "members"."requested_at" IS NULL
      AND "members"."user_id" = $7
      AND "members"."access_level" > $8
  WHERE
    "namespaces"."type" = $9)
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" = $10
      AND "members"."requested_at" IS NULL
      AND "members"."user_id" = $11
      AND "members"."access_level" > $12
  WHERE
    "namespaces"."type" = $13))
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" = $14
      AND "members"."source_type" = $15
      AND "members"."user_id" = $16
      AND "members"."requested_at" IS NULL)
  UNION (
    SELECT
      projects.id AS project_id,
      $17 AS access_level
    FROM
      "projects"
      INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
    WHERE
      "namespaces"."owner_id" = $18
      AND "namespaces"."type" IS NULL)
  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"

Charting at the total time spent in statements, we can see this query dominating during the slowdowns:

https://thanos.gitlab.net/graph?g0.range_input=6h&g0.max_source_resolution=0s&g0.expr=topk(10%2C%20rate(pg_stat_statements_seconds_total%7Benv%3D%22gprd%22%2Cfqdn%3D%22patroni-03-db-gprd.c.gitlab-production.internal%22%7D%5B5m%5D))&g0.tab=0

From the slowlog we can deduce that the problem is AuthorizedProjectsWorker.

cc @lmcandrew @dsatcher @mushakov