Skip to content

Using != filters are slow

In https://gitlab.com/gitlab-org/gitlab/merge_requests?scope=all&utf8=%E2%9C%93&state=merged&label_name[]=Pick%20into%2012.7&not[label_name][]=documentation, I see:

image

SELECT
  "merge_requests".*
FROM
  "merge_requests"
  INNER JOIN "label_links" ON "label_links"."target_id" = "merge_requests"."id"
  AND "label_links"."target_type" = 'MergeRequest'
  INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
WHERE
  "merge_requests"."target_project_id" = 278964
  AND ("merge_requests"."state_id" IN (3))
  AND "labels"."title" = 'Pick into 12.7'
  AND "merge_requests"."id" NOT IN (
    SELECT
      "merge_requests"."id"
    FROM
      "merge_requests"
      INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
      INNER JOIN "label_links" ON "label_links"."target_id" = "merge_requests"."id"
      AND "label_links"."target_type" = 'MergeRequest'
      INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
      LEFT JOIN project_features ON projects.id = project_features.project_id
    WHERE
      (
        EXISTS (
          SELECT
            1
          FROM
            "project_authorizations"
          WHERE
            "project_authorizations"."user_id" = 64248
            AND (project_authorizations.project_id = projects.id)
            AND (project_authorizations.access_level >= 20)
        )
        OR projects.visibility_level IN (10, 20)
      )
      AND (
        "project_features"."merge_requests_access_level" IS NULL
        OR "project_features"."merge_requests_access_level" IN (20, 30)
        OR (
          "project_features"."merge_requests_access_level" = 10
          AND EXISTS (
            SELECT
              1
            FROM
              "project_authorizations"
            WHERE
              "project_authorizations"."user_id" = 64248
              AND (project_authorizations.project_id = projects.id)
              AND (project_authorizations.access_level >= 20)
          )
        )
      )
      AND "labels"."title" = 'documentation'
    ORDER BY
      "merge_requests"."id" DESC
  )
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN output: https://explain.depesz.com/s/Qzv

Did we ever do database reviews for this functionality?

Edited by Stan Hu