GET /api/:version/projects/:id/deployments with updated_before param is slow

Spun from gitlab-com/gl-infra/production#4024 (closed)...

Out of 6,342,556 requests to /api/:version/projects/:id/deployments for last 7 days:

• 571,218 requests had 1 or more requests on primary DB (9%)
• 30,620 requests had 1s or more on primary DB
  • Out of these 26,057 requests includes the updated_before param
• 78 (not from this one root namespace)

*NOTE 4.3 million of hits last 7 days come from this one ~customer root namespace. See https://log.gprd.gitlab.net/goto/d34f8655c2696d7abe7d72e55edccd5c

Proposal

Improve this query that uses updated_before param:

https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2834/commands/8874

SELECT "deployments".* FROM "deployments" INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id" WHERE "deployments"."project_id" = 22679027 AND "deployments"."updated_at" <= '2020-12-04 05:06:26' AND "environments"."name" = '*****' ORDER BY "deployments"."iid" DESC LIMIT 100 OFFSET 0

SELECT
    "deployments".*
FROM
    "deployments"
    INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
    "deployments"."project_id" = 22679027
    AND "deployments"."updated_at" <= '2020-12-04 05:06:26'
    AND "environments"."name" = '*****'
ORDER BY
    "deployments"."iid" DESC
LIMIT 100 OFFSET 0

Links

See also !57043 (comment 534300638)