• Thong Kuah's avatar
    Optimize query performance in specific case · 5c93bdbe
    Thong Kuah authored
    This becomes especially bad in this query from /api/v4/projects
    endpoint:
    
    ```
    SELECT COUNT(*)
    FROM
      (SELECT 1 AS one
       FROM "projects"
       WHERE (EXISTS
                (SELECT 1
                 FROM "project_authorizations"
                 WHERE "project_authorizations"."user_id" = 1
                   AND (project_authorizations.project_id = projects.id)
                )
                OR projects.visibility_level IN (10,20)
              )
         AND "projects"."visibility_level" = 0
         AND "projects"."pending_delete" = FALSE
       LIMIT 10001) subquery_for_count
    ```
    
    Note we attempt to limit the damage with `LIMIT 10001` but Postgres
    keeps trying to find any row that will match the impossible visiblity
    level combination of (10,20) & (0), which goes way beyond 10001 rows.
    
    So we omit the OR in the case where we encounter a visibility level
    set which is null.
    5c93bdbe
projects_finder_visibility_optimization.yml 125 Bytes