Explore partitionin pruning for cross project dependencies
When a job is assigned for execution to a runner we check that all its dependencies are valid:
ee/app/models/ee/ci/build_dependencies.rb:32:in `fetch_cross_project',
ee/app/models/ee/ci/build_dependencies.rb:17:in `block in cross_project',
ee/app/models/ee/ci/build_dependencies.rb:16:in `cross_project',
ee/app/models/ee/ci/build_dependencies.rb:23:in `valid_cross_project?',
app/models/ci/build_dependencies.rb:22:in `valid?',
app/models/ci/build.rb:863:in `has_valid_build_dependencies?',
app/services/ci/register_job_service.rb:293:in `block in pre_assign_runner_checks',
app/services/ci/register_job_service.rb:243:in `block in assign_runner!',
app/services/ci/register_job_service.rb:243:in `each',
app/services/ci/register_job_service.rb:243:in `find',
app/services/ci/register_job_service.rb:243:in `assign_runner!',
app/services/ci/register_job_service.rb:178:in `process_build',
app/services/ci/register_job_service.rb:85:in `block in process_queue',
app/services/ci/register_job_service.rb:136:in `block in each_build',
app/services/ci/register_job_service.rb:136:in `each',
app/services/ci/register_job_service.rb:136:in `each_build',
app/services/ci/register_job_service.rb:58:in `process_queue',
app/services/ci/register_job_service.rb:34:in `block in execute',
lib/gitlab/ci/queue/metrics.rb:96:in `observe_queue_time',
app/services/ci/register_job_service.rb:33:in `execute',
lib/api/ci/runner.rb:205:in `block (2 levels) in <class:Runner>'
Full SQL query
SELECT
p_ci_builds.status,
p_ci_builds.finished_at,
p_ci_builds.created_at,
p_ci_builds.updated_at,
p_ci_builds.started_at,
p_ci_builds.runner_id,
p_ci_builds.coverage,
p_ci_builds.commit_id,
p_ci_builds.name,
p_ci_builds.options,
p_ci_builds.allow_failure,
p_ci_builds.stage,
p_ci_builds.trigger_request_id,
p_ci_builds.stage_idx,
p_ci_builds.tag,
p_ci_builds.ref,
p_ci_builds.user_id,
p_ci_builds.type,
p_ci_builds.target_url,
p_ci_builds.description,
p_ci_builds.project_id,
p_ci_builds.erased_by_id,
p_ci_builds.erased_at,
p_ci_builds.artifacts_expire_at,
p_ci_builds.environment,
p_ci_builds.when,
p_ci_builds.yaml_variables,
p_ci_builds.queued_at,
p_ci_builds.lock_version,
p_ci_builds.coverage_regex,
p_ci_builds.auto_canceled_by_id,
p_ci_builds.retried,
p_ci_builds.protected,
p_ci_builds.failure_reason,
p_ci_builds.scheduled_at,
p_ci_builds.token_encrypted,
p_ci_builds.upstream_pipeline_id,
p_ci_builds.resource_group_id,
p_ci_builds.waiting_for_resource_at,
p_ci_builds.processed,
p_ci_builds.scheduling_type,
p_ci_builds.id,
p_ci_builds.stage_id,
p_ci_builds.partition_id,
p_ci_builds.auto_canceled_by_partition_id
FROM
(
SELECT
p_ci_builds.status,
p_ci_builds.finished_at,
p_ci_builds.created_at,
p_ci_builds.updated_at,
p_ci_builds.started_at,
p_ci_builds.runner_id,
p_ci_builds.coverage,
p_ci_builds.commit_id,
p_ci_builds.name,
p_ci_builds.options,
p_ci_builds.allow_failure,
p_ci_builds.stage,
p_ci_builds.trigger_request_id,
p_ci_builds.stage_idx,
p_ci_builds.tag,
p_ci_builds.ref,
p_ci_builds.user_id,
p_ci_builds.type,
p_ci_builds.target_url,
p_ci_builds.description,
p_ci_builds.project_id,
p_ci_builds.erased_by_id,
p_ci_builds.erased_at,
p_ci_builds.artifacts_expire_at,
p_ci_builds.environment,
p_ci_builds.when,
p_ci_builds.yaml_variables,
p_ci_builds.queued_at,
p_ci_builds.lock_version,
p_ci_builds.coverage_regex,
p_ci_builds.auto_canceled_by_id,
p_ci_builds.retried,
p_ci_builds.protected,
p_ci_builds.failure_reason,
p_ci_builds.scheduled_at,
p_ci_builds.token_encrypted,
p_ci_builds.upstream_pipeline_id,
p_ci_builds.resource_group_id,
p_ci_builds.waiting_for_resource_at,
p_ci_builds.processed,
p_ci_builds.scheduling_type,
p_ci_builds.id,
p_ci_builds.stage_id,
p_ci_builds.partition_id,
p_ci_builds.auto_canceled_by_partition_id
FROM
p_ci_builds
WHERE
p_ci_builds.type = $1 AND
p_ci_builds.id IN (
SELECT
max( p_ci_builds.id ) AS id
FROM
p_ci_builds
WHERE
p_ci_builds.type = $2 AND
(
p_ci_builds.retried = $3 OR
p_ci_builds.retried IS NULL
) AND
p_ci_builds.status IN ( $4 ) AND
p_ci_builds.name = $5 AND
p_ci_builds.ref = $6 AND
p_ci_builds.project_id = $7
)
) AS p_ci_builds
WHERE
p_ci_builds.type = $8
LIMIT $9;
This query doesn't have a partition_id
filter and sometimes it times out. We've had 60 timeout entries for gitlab-com/gl-infra/production#17367 (closed)
Proposal
Explore a way to use partition pruning for this query.
Edited by Marius Bobin