Skip to content

G
gitlab-ce
Switch branch/tag
  1. 16 Nov, 2021 2 commits
    • Vladimir Shushlin's avatar
      Optimize EnvironmentsByDeploymentsFinder query · 11fb7376
      Vladimir Shushlin authored 
      When implementing
https://gitlab.com/gitlab-org/gitlab/-/merge_requests/72781

We planned to have this subquery:
  AND EXISTS (
    SELECT 1
    FROM "deployments"
    WHERE "deployments"."environment_id" = "environments"."id"
      AND (ref = $4)
  )

But I forgot to that deployments are scoped by project,
and the resulting query is

EXISTS (SELECT "deployments".*
  FROM   "deployments"
  WHERE  "deployments"."project_id" = 50
  AND ( ref = 'main' )
  AND ( environment_id = environments.id )) )

project_id filter forces this query to use
index_deployments_on_project_id_and_ref
instead of index_deployments_on_environment_id_and_ref
which was introduced for this finder

This commit adds unscope(where: :project_id).

Resulting query now is:

SELECT "environments".*
FROM   "environments"
WHERE  "environments"."project_id" = 50
       AND ( "environments"."state" IN ( 'available' ) )
       AND ( EXISTS (SELECT "deployments".*
                     FROM   "deployments"
                     WHERE  ( ref = 'main' )
                            AND ( environment_id = environments.id )) )
ORDER  BY (SELECT Max("deployments"."id")
           FROM   "deployments"
           WHERE  "deployments"."environment_id" = "environments"."id") ASC
          nulls first

It brings execution time from 1.6 minutes to 6 seconds with cold cache.
It's still slow, but much better than it was.
      11fb7376
    • Vladimir Shushlin's avatar
      Scope finder optimization feature flag by project · a3f02794
      Vladimir Shushlin authored
      a3f02794
  3. 15 Nov, 2021 38 commits
GitLab Nexedi Edition | About GitLab | About Nexedi | 沪ICP备2021021310号-2 | 沪ICP备2021021310号-7