Fetch pending builds with denormalized ci minutes

Ref: #329763 (closed)

What does this MR do?

Following up !64443 (merged) where we start to denormalize ci minutes information in ci_pending_builds.

This MR fetches pending builds with our denormalized ci minutes 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)))
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_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 ci_pending_builds.minutes_exceeded = FALSE
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/5428/commands/18865
• New query: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5428/commands/18869

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.