1. 24 Nov, 2019 1 commit
    • 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
  2. 22 Nov, 2019 39 commits