De-normalize tags used to retrieve builds queue (!65648) · Merge requests · GitLab.org / GitLab

Grzegorz Bizon requested to merge feature/gb/efficient-ci-taggings into master Jul 07, 2021

What does this MR do?

This merge request de-normalizes builds tags used to retrieve builds queue from the database after matching tags.

Conformity

I have included changelog trailers
I have added information for database reviewers in the MR description
I have self-reviewed this MR per code review guidelines
This change is backwards compatible across updates

Database review

1️⃣ New column

DB migration

$ bin/rails db:migrate
== 20210707113056 AddTagsArrayToCiPendingBuilds: migrating ====================
-- add_column(:ci_pending_builds, :tag_ids, :integer, {:array=>true, :default=>[]})
   -> 0.0038s
== 20210707113056 AddTagsArrayToCiPendingBuilds: migrated (0.0305s) ===========

DB rollback

$ bin/rails db:rollback
== 20210707113056 AddTagsArrayToCiPendingBuilds: reverting ====================
-- remove_column(:ci_pending_builds, :tag_ids)
   -> 0.0012s
== 20210707113056 AddTagsArrayToCiPendingBuilds: reverted (0.0173s) ===========

2️⃣ New index

DB migration

$ bin/rails db:migrate
== 20210818185548 AddTagIdsIndexToCiPendingBuild: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pending_builds, :tag_ids, {:name=>"index_ci_pending_builds_on_tag_ids", :where=>"cardinality(tag_ids) > 0", :algorithm=>:concurrently})
   -> 0.0042s
-- execute("SET statement_timeout TO 0")
   -> 0.0010s
-- add_index(:ci_pending_builds, :tag_ids, {:name=>"index_ci_pending_builds_on_tag_ids", :where=>"cardinality(tag_ids) > 0", :algorithm=>:concurrently})
   -> 0.0104s
-- execute("RESET statement_timeout")
   -> 0.0008s
== 20210818185548 AddTagIdsIndexToCiPendingBuild: migrated (0.0252s) ==========

DB rollback

$ bin/rails db:rollback
== 20210818185548 AddTagIdsIndexToCiPendingBuild: reverting ===================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pending_builds, {:name=>"index_ci_pending_builds_on_tag_ids"}, {:algorithm=>:concurrently})
   -> 0.0032s
== 20210818185548 AddTagIdsIndexToCiPendingBuild: reverted (0.0037s) ==========

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.build_id
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 (taggings.tag_id NOT IN (71377, 71378, 71379, 71380, 71381, 71382, 71383, 71384, 71385, 71386, 71387, 71388, 71389, 71390, 71391, 71392, 71393, 71394, 71395, 71396, 71397, 71398, 71399, 71400, 71401, 71402, 71403, 71404, 71405, 71406, 71407, 71408, 71409, 71410, 71411, 71412, 71413, 71414, 71415, 71416))))
-  AND (EXISTS
-         (SELECT 1
-          FROM "taggings"
-          WHERE "taggings"."taggable_type" = 'CommitStatus'
-            AND "taggings"."context" = 'tags'
-            AND (taggable_id = "ci_pending_builds"."build_id")))
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.build_id
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 ci_pending_builds.tags <@ ARRAY[71377, 71378, 71379, 71380, 71381, 71382, 71383, 71384, 71385, 71386, 71387, 71388, 71389, 71390, 71391, 71392, 71393, 71394, 71395, 71396, 71397, 71398, 71399, 71400, 71401, 71402, 71403, 71404, 71405, 71406, 71407, 71408, 71409, 71410, 71411, 71412, 71413, 71414, 71415, 71416]::bigint[]
+  AND cardinality(tags) > 0
ORDER BY COALESCE(project_builds.running_builds, 0) ASC, ci_pending_builds.build_id ASC;

Results from the Database Lab:

Query plans from database lab (snapshot from 30.06 12:55):

Not excluding builds without tags

Old query: https://explain.depesz.com/s/Qufg
New query (no GIN index on denormalized tags): https://explain.depesz.com/s/agZ9
New query (with gin_index_on_ci_pending_builds_tags): https://explain.depesz.com/s/7bcG

Excluding builds without tags

Old query: https://explain.depesz.com/s/85de
New query (no GIN index): https://explain.depesz.com/s/kVvK
New query (with GIN index): https://explain.depesz.com/s/NcRC
Different set of tags to return some results: https://explain.depesz.com/s/ok7n

Issues

See #330734 (closed)

Edited Aug 18, 2021 by Max Orefice

De-normalize tags used to retrieve builds queue