Idea to consider: Precalculate expensive parts of query

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Label this issue
• Close this issue

This issue discusses an idea that if we would precalculate a significant portions of a big query for queueing we could considerably reduce it's execution time.

Big query

explain analyze
SELECT "ci_builds".* FROM "ci_builds"
  INNER JOIN "projects" ON "projects"."id" = "ci_builds"."project_id"
  LEFT JOIN project_features ON ci_builds.project_id = project_features.project_id
  LEFT JOIN (
    SELECT "ci_builds"."project_id", count(*) AS running_builds FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND ("ci_builds"."status" IN ('running')) AND "ci_builds"."runner_id" IN (SELECT "ci_runners"."id" FROM "ci_runners" WHERE "ci_runners"."runner_type" = 1) GROUP BY "ci_builds"."project_id"
  ) AS project_builds ON ci_builds.project_id=project_builds.project_id
WHERE
  ("ci_builds"."status" IN ('pending')) AND "ci_builds"."runner_id" IS NULL AND
  "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_builds"."type" = 'Ci::Build' AND
  ("projects"."visibility_level" = 20 OR (EXISTS (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE (namespaces.id = projects.namespace_id))                                                                                          UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT 1 FROM "base_and_ancestors" AS "namespaces" LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id WHERE "namespaces"."parent_id" IS NULL AND (COALESCE(namespaces.shared_runners_minutes_limit, 400, 0) = 0 OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (400 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60))))
ORDER BY COALESCE(project_builds.running_builds, 0) ASC, ci_builds.id ASC;

Maybe we can precalculate and put into a single flag:

• have is_shared, is_group and is_project

# is_shared:

"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 (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE (namespaces.id = projects.namespace_id))                                                                                          UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT 1 FROM "base_and_ancestors" AS "namespaces" LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id WHERE "namespaces"."parent_id" IS NULL AND (COALESCE(namespaces.shared_runners_minutes_limit, 400, 0) = 0 OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (400 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60))))

# is_group:

"project_ci_cd_settings"."group_runners_enabled" = TRUE AND "projects"."pending_delete" = FALSE AND
 (project_features.builds_access_level IS NULL or project_features.builds_access_level > 0)

# is_project:

"projects"."pending_delete" = FALSE AND (project_features.builds_access_level IS NULL or project_features.builds_access_level > 0)
```