Investigate query ID `-6715295008265123183` and query ID `3417312371075049744` as part of https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/work_items/28600. ### Details (Refs: https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/work_items/28600#note_3285345014, https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/work_items/28600#note_3288785483) Both these queries pertain to the `with_executing_builds` scope, so their solutions could be closely related. #### Query `-6715295008265123183` - Call location: https://gitlab.com/gitlab-org/gitlab/-/blob/fefa87e975d663ed44a7cb96a56f89601e9d7070/app/models/ci/runner.rb#L133-138 - avg_call_rate: `34.4605` - locks_slowpath: `402` - locks_fastpath: `16` <details> <summary>Normalized query text</summary> ```sql SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."id" = $1 AND (EXISTS (SELECT $2 FROM "p_ci_builds" WHERE "p_ci_builds"."type" = $3 AND "p_ci_builds"."status" IN ($4, $5) AND ("p_ci_builds".runner_id = "ci_runners".id))) ``` </details> #### Query `3417312371075049744` - Call location: https://gitlab.com/gitlab-org/gitlab/-/blob/4c26e1b451eafa4670413a029535a27f32258306/app/models/ci/runner_manager.rb#L98-106 - avg_call_rate: `56.5376` - locks_slowpath: `435` - locks_fastpath: `16` <details> <summary>Normalized query text</summary> ```sql SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."id" = $1 AND (EXISTS (SELECT $2 FROM "p_ci_builds" INNER JOIN "p_ci_runner_machine_builds" ON "p_ci_runner_machine_builds"."partition_id" IS NOT NULL AND "p_ci_runner_machine_builds"."build_id" = "p_ci_builds"."id" AND "p_ci_runner_machine_builds"."partition_id" = "p_ci_builds"."partition_id" WHERE "p_ci_builds"."type" = $3 AND "p_ci_builds"."status" IN ($4, $5) AND ("p_ci_builds".runner_id = "ci_runner_machines".runner_id) AND ("p_ci_runner_machine_builds".runner_machine_id = "ci_runner_machines".id))) ``` </details> ## Proposal Investigate whether these queries can be improved with partition pruning or other strategies to reduce LWLock acquisition overhead, and apply the identified improvements if they are safe and effective. Suggested work: - Confirm the current execution path and query frequency for both queries. - Verify whether partition pruning is already applied. - If pruning is already present, evaluate alternative strategies such as query reshaping, index changes, cache/read-path changes, or moving read load to a replica. - Apply the selected improvement. - Document findings and estimated or observed impact on LWLock acquisition rate.