Skip to content

Optimization: Use left join instead of exists when filtering for authorized projects

Problem

The current implementation for filtering authorized projects uses an EXISTS condition, which results in suboptimal query performance with high heap fetches.

Solution

Replace the EXISTS condition with a LEFT JOIN when filtering for authorized projects.

Performance Impact

The proposed change significantly reduces heap fetches and improves query performance.

Context and Rationale

The EXISTS condition was introduced to handle potential duplicate authorizations after the unique index was temporarily removed. However, switching to LEFT JOIN is now safe for the following reasons:

  1. No reported duplicate issues: Despite existing code paths that assume uniqueness, we haven't received any complaints about downstream dependencies (e.g. Projects API) from producing duplicates.
  2. Uniqueness restored: The uniqueness guarantee was restored 2 months after the index removal via this merge request.
  3. Low duplicate risk: The probability of duplicates created during the index removal period is minimal because the model validation was never removed.

Given the significant performance improvements demonstrated in the query analysis, the minimal risk of encountering duplicates is worth taking for the substantial performance benefit this change provides.

Note

There's an ongoing (but inactive) effort to restore the unique index: Restore unique index on project_authorizations ... (#418205)

Implementation guide

  1. Update Project.public_or_visible_to_user to apply a LEFT JOIN when filtering for authorized projects
         LEFT JOIN "project_authorizations" ON "project_authorizations"."project_id" = "projects"."id"
    AND "project_authorizations"."user_id" = 1
WHERE ("project_authorizations"."project_id" IS NOT NULL OR "projects"."visibility_level" IN (10, 20))
Edited by 🤖 GitLab Bot 🤖