Use Lateral join in DeploymentPreloader
Problem
DeploymentPreloader
generates an efficient query to fetch latest deployments of multiple environments. But the query length could be very long if it fetches from many environments.
By default, fetching environments is paginated or hard-limited (as per MR performance guideline), so this is not a concern at the moment. However, the following approach can resolve the issue if it's the case.
Proposal
- The
Gitlab::SQL::Union
class does support keeping theORDER BY
clause so we could just use the class: https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/preloaders/environments/deployment_preloader.rb#L24
- See the
remove_order: false
parameter: https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/sql/set_operator.rb#L21- It looks like each sub-query in the union loads a different deployment row, so we don't need to dedup the results (fixme). We could use
UNION ALL
. This can be done with theremove_duplicates: true
parameter.- Do we expect many deployment subqueries here? We might generate a huge query here. Lateral join would reduce the query size significantly:
Example query:
SELECT deployments.* FROM (VALUES (436), (8077762), (7110656), (6444461)) environments(id), LATERAL ( SELECT "deployments".* FROM "deployments" WHERE "deployments"."environment_id" = environments.id AND "deployments"."status" IN (1, 2, 3, 4, 6) ORDER BY "deployments"."id" DESC LIMIT 1 ) as deployments
Plan https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11377/commands/40704
Edited by Shinya Maeda