Slow query to retrieve projects for a user

From a recent checkup of queries on GitLab.com, this stood out: The query takes 8s on average. It was called 360 times per the 30 minutes observation interval.

A plan for my user can be found here (warm cache, 6.8s): https://explain.depesz.com/s/s3Ey

The query can be optimized for to change runtime to <20ms.

The goals of this issue are:

• Understand the impact to changes to this query better. Presumably this is something we use across the site for retrieving a list of relevant projects (public and private ones with authorization).
• Optimize the query to reduce runtime
• Look for other places where we have this pattern (public + private with auth)

SELECT
    "projects".*
FROM
    "projects"
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 1562869
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (10, 20))
AND "projects"."pending_delete" = FALSE
ORDER BY
    "projects"."created_at" DESC,
    "projects"."id" DESC
LIMIT 100 OFFSET 0