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.