Queries used for getting events limit to authorized projects in an in-efficient manner

For example:

SELECT
  "events"."id" AS t0_r0,
  "events"."target_type" AS t0_r1,
  "events"."target_id" AS t0_r2,
  "events"."title" AS t0_r3,
  "events"."data" AS t0_r4,
  "events"."project_id" AS t0_r5,
  "events"."created_at" AS t0_r6,
  "events"."updated_at" AS t0_r7,
  "events"."action" AS t0_r8,
  "events"."author_id" AS t0_r9,
  "projects"."id" AS t1_r0,
  "projects"."name" AS t1_r1,
  "projects"."path" AS t1_r2,
  "projects"."description" AS t1_r3,
...
FROM
"events"
LEFT OUTER JOIN "projects" ON "projects"."id" = "events"."project_id" AND "projects"."pending_delete" = 'f'
LEFT OUTER JOIN "routes" ON "routes"."source_id" = "projects"."id" AND "routes"."source_type" = 'Project'
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL
LEFT OUTER JOIN "users" ON "users"."id" = "events"."author_id"
WHERE
("events"."author_id" IS NOT NULL)
AND "events"."author_id" = 423915
AND "projects"."pending_delete" = 'f'
AND (projects.id IN
  (SELECT "projects"."id" FROM "projects"
   INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
   WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 758045
   UNION
   SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10)))
ORDER BY "events"."id" DESC
LIMIT 20;

This can be re-written using more or less the following SQL:

SELECT
  "events"."id" AS t0_r0,
  "events"."target_type" AS t0_r1,
  "events"."target_id" AS t0_r2,
  "events"."title" AS t0_r3,
  "events"."data" AS t0_r4,
  "events"."project_id" AS t0_r5,
  "events"."created_at" AS t0_r6,
  "events"."updated_at" AS t0_r7,
  "events"."action" AS t0_r8,
  "events"."author_id" AS t0_r9,
  "projects"."id" AS t1_r0,
  "projects"."name" AS t1_r1,
  "projects"."path" AS t1_r2,
  "projects"."description" AS t1_r3,
...
FROM "events"
JOIN "projects" ON "projects"."id" = "events"."project_id" AND "projects"."pending_delete" = 'f'
LEFT OUTER JOIN "routes" ON "routes"."source_id" = "projects"."id" AND "routes"."source_type" = 'Project'
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL
LEFT OUTER JOIN "users" ON "users"."id" = "events"."author_id"
WHERE 
      "events"."author_id" = 423915
AND (projects.visibility_level = 10 or projects.visibility_level = 20 or exists (select 1 from project_authorizations where projects.id = project_authorizations.project_id AND project_authorizations.user_id = 758045))
ORDER BY "events"."id" DESC
LIMIT 20;

Basically we replace the WHERE IN ( authorized projects ) with WHERE EXISTS ( ... ) and don't explicitly select public/internal projects, instead filtering them using an OR.

See https://gitlab.com/gitlab-com/infrastructure/issues/1448#note_27855683 (GitLab employees only) for more information.