Fetch pending builds with denormalized shared runners

Ref: #329762 (closed)

What does this MR do?

Following up !64644 (merged) where we start to denormalize shared runners information in ci_pending_builds.

This MR fetches pending builds with our denormalized shared runners data and get rid of joins condition to accelerate our big SQL query.

Why are we doing this?

This is part of our epic to scale up the Ci::Build table.

By avoiding to query the ci_builds we simplify our queuing system to determine which build will get pick up by 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: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5187/commands/18324
• New query: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5187/commands/18326

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.