Optimise query for Deployments::LinkMergeRequestWorker (Deployment#previous_environment_deployment)

What does this MR do?

Optimise query for #previous_environment_deployment

Rewrite to a much simpler query

• No need to search by name, unique index guarantees there can only be one environment by that name for a project
• Also no need to have where claus for deployments.project_id. An environment must belong to the same project_id anyway.

See also #325345 (comment 533462424)

Hypothesis

#325345 (comment 533408823) will improve

The query from #325345 (closed) will no longer appear in top 10 by total time reports.

Query for previous_environment_deployment

Before

SELECT "deployments".* FROM "deployments" INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id" WHERE "deployments"."project_id" = 21235576 AND "deployments"."status" = 2 AND "environments"."name" = 'testing' AND "deployments"."id" != 137394521 ORDER BY "deployments"."id" DESC LIMIT 1

SELECT
    "deployments".*
FROM
    "deployments"
    INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
    "deployments"."project_id" = 21235576
    AND "deployments"."status" = 2
    AND "environments"."name" = 'testing'
    AND "deployments"."id" != 137394521
ORDER BY
    "deployments"."id" DESC
LIMIT 1

https://explain.depesz.com/s/jrIn

After

SELECT "deployments".* FROM "deployments" WHERE "deployments"."environment_id" = 2925059 AND "deployments"."status" = 2 AND "deployments"."id" != 137394521 ORDER BY "deployments"."id" DESC LIMIT 1

SELECT
    "deployments".*
FROM
    "deployments"
WHERE
    "deployments"."environment_id" = 2925059
    AND "deployments"."status" = 2
    AND "deployments"."id" != 137394521
ORDER BY
    "deployments"."id" DESC
LIMIT 1

https://explain.depesz.com/s/gksy

You can see we have completely eliminated the nested loop

---

Query for previous_deployment

Before

SELECT
    "deployments".*
FROM
    "deployments"
    INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
    "deployments"."project_id" = 21235576
    AND "environments"."name" = 'testing'
    AND "deployments"."ref" = 'master'
    AND "deployments"."id" != 137394521
ORDER BY
    "deployments"."id" DESC
LIMIT 1

https://explain.depesz.com/s/lgsY

After

SELECT
    "deployments".*
FROM
    "deployments"
WHERE
    "deployments"."environment_id" = 2925059
    AND "deployments"."ref" = 'master'
    AND "deployments"."id" != 137394521
ORDER BY
    "deployments"."id" DESC
LIMIT 1

https://explain.depesz.com/s/G67c

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • [-] I have not included a changelog entry because _____.
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• [-] Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.