Draft: Fetch pending builds with denormalized shared runners

Ref: #329762 (closed)

What does this MR do?

This MR fetches pending builds with denormalized shared runners.

Queries

Old query:

WITH project_builds AS MATERIALIZED (
    SELECT
        ci_running_builds.project_id,
        COUNT(
            *
) AS running_builds
    FROM
        ci_running_builds
    WHERE
        ci_running_builds.runner_type = 1
    GROUP BY
        ci_running_builds.project_id
)
SELECT
    ci_pending_builds.*
FROM
    ci_pending_builds
    INNER JOIN projects ON ci_pending_builds.project_id = projects.id
-    LEFT JOIN project_features ON ci_pending_builds.project_id = project_features.project_id
    LEFT JOIN project_builds ON ci_pending_builds.project_id = project_builds.project_id
 WHERE
-    projects.shared_runners_enabled = TRUE
-    AND projects.pending_delete = FALSE
-    AND (project_features.builds_access_level IS NULL
-        OR project_features.builds_access_level > 0)
    AND (projects.visibility_level = 20
        OR EXISTS (
            SELECT
                1
            FROM
                namespaces
                INNER JOIN namespaces AS project_namespaces ON project_namespaces.id = projects.namespace_id
                LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id
            WHERE (namespaces.id = project_namespaces.traversal_ids[1])
            AND (COALESCE(namespaces.shared_runners_minutes_limit, 0, 0) = 0
                OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (0 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60)))
AND (NOT EXISTS (
        SELECT
            1
        FROM
            taggings
        WHERE
            taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_pending_builds.build_id)
            AND 1 = 1))
ORDER BY
    COALESCE(project_builds.running_builds, 0) ASC,
    ci_pending_builds.build_id ASC

New query:

WITH project_builds AS MATERIALIZED (
    SELECT
        ci_running_builds.project_id,
        COUNT(
            *
) AS running_builds
    FROM
        ci_running_builds
    WHERE
        ci_running_builds.runner_type = 1
    GROUP BY
        ci_running_builds.project_id
)
SELECT
    ci_pending_builds.*
FROM
    ci_pending_builds
    INNER JOIN projects ON ci_pending_builds.project_id = projects.id
    LEFT JOIN project_builds ON ci_pending_builds.project_id = project_builds.project_id
WHERE
+    ci_pending_builds.instance_runners_enabled = TRUE
    AND (projects.visibility_level = 20
        OR EXISTS (
            SELECT
                1
            FROM
                namespaces
                INNER JOIN namespaces AS project_namespaces ON project_namespaces.id = projects.namespace_id
                LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id
            WHERE (namespaces.id = project_namespaces.traversal_ids[1])
            AND (COALESCE(namespaces.shared_runners_minutes_limit, 0, 0) = 0
                OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (0 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60)))
AND (NOT EXISTS (
        SELECT
            1
        FROM
            taggings
        WHERE
            taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_pending_builds.build_id)
            AND 1 = 1))
ORDER BY
    COALESCE(project_builds.running_builds, 0) ASC,
    ci_pending_builds.build_id ASC

• Old query:
• New query:

Does this MR meet the acceptance criteria?

Conformity

• I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
• I have added/updated documentation, or it's not needed. (Is documentation required?)
• I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
• I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
• I have self-reviewed this MR per code review guidelines.
• This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
• I have followed the style guides.
• This change is backwards compatible across updates, or this does not apply.