GraphQL: Add membership filter to ProjectRunnersResolver (!116373) · Merge requests · GitLab.org / GitLab

Pedro Pombeiro requested to merge pedropombeiro/383510/add-membership-argument-to-resolver into master Mar 31, 2023

What does this MR do and why?

This MR aims to close #383510 and #328808.

Screenshots or screen recordings

header	header
nil
`DIRECT`
`OWNED_OR_INSTANCE_WIDE`
`PARENT_GROUPS`

How to set up and validate locally

Go to the shell in your GDK gitlab directory and run bundle exec rake "gitlab:seed:runner_fleet". This will seed your GDK with some runners and jobs required for testing this MR.
Visit http://gdk.test:3000/-/graphql-explorer

Use this query, and change the membership param:

query projectFinder {
  project(
    fullPath: "rf-top-level-group-1/rf-group-1.1/rf-group-1.1.1/rf-project-1-1-1-1"
  ) {
    id
    fullPath
    runners(membership: PARENT_GROUPS) {
      count
    }
  }
}

query projectFinder {
  project(
    fullPath: "rf2-top-level-group-1/rf2-group-1.1/rf2-group-1.1.1/rf2-project-1-1-1-1"
  ) {
    id
    fullPath
    runners(
      membership: DIRECT
      paused: false
      status: NEVER_CONTACTED
      type: PROJECT_TYPE
      tagList: ["shell"]
      upgradeStatus: RECOMMENDED
      search: "R"
      sort: CREATED_DESC
    ) {
      nodes {
        id
      }
    }
  }
}

Notice the count of runners changes with different membership params.

Database query plans

`DIRECT`

active=true

project.runners query plan

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE "ci_runner_projects"."project_id" = 29102510
  AND "ci_runners"."active" = TRUE
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58529

 Limit  (cost=6248.15..6248.40 rows=101 width=283) (actual time=3878.849..3878.876 rows=101 loops=1)
   Buffers: shared hit=2587 read=3134 dirtied=504
   I/O Timings: read=3802.342 write=0.000
   ->  Sort  (cost=6248.15..6251.32 rows=1268 width=283) (actual time=3878.848..3878.865 rows=101 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: top-N heapsort  Memory: 77kB
         Buffers: shared hit=2587 read=3134 dirtied=504
         I/O Timings: read=3802.342 write=0.000
         ->  Nested Loop  (cost=0.86..6199.60 rows=1268 width=283) (actual time=28.119..3872.526 rows=1142 loops=1)
               Buffers: shared hit=2581 read=3134 dirtied=504
               I/O Timings: read=3802.342 write=0.000
               ->  Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects  (cost=0.43..1735.75 rows=1303 width=4) (actual time=13.843..1226.729 rows=1142 loops=1)
                     Index Cond: (ci_runner_projects.project_id = 29102510)
                     Buffers: shared hit=134 read=1013 dirtied=47
                     I/O Timings: read=1210.544 write=0.000
               ->  Index Scan using index_ci_runners_on_active on public.ci_runners  (cost=0.43..3.43 rows=1 width=283) (actual time=2.310..2.310 rows=1 loops=1142)
                     Index Cond: ((ci_runners.active = true) AND (ci_runners.id = ci_runner_projects.runner_id))
                     Buffers: shared hit=2447 read=2121 dirtied=457
                     I/O Timings: read=2591.799 write=0.000

full query

project.runners query plan

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
  INNER JOIN "ci_runner_versions" "runner_version" ON "runner_version"."version" = "ci_runners"."version"
  INNER JOIN "taggings" "ci::runner_taggings_ec122f5" ON "ci::runner_taggings_ec122f5"."taggable_id" = "ci_runners"."id"
    AND "ci::runner_taggings_ec122f5"."taggable_type" = 'Ci::Runner'
    AND "ci::runner_taggings_ec122f5"."tag_id" IN (
      SELECT "tags"."id"
      FROM "tags"
    WHERE "tags"."name" LIKE 'shell' ESCAPE '!')
WHERE "ci_runner_projects"."project_id" = 29102510
  AND ("ci_runners"."token" = 'R'
    OR "ci_runners"."description" ILIKE 'R')
  AND "ci_runners"."active" = TRUE
  AND "ci_runners"."contacted_at" IS NULL
  AND "runner_version"."status" = 3
  AND "ci_runners"."runner_type" = 1

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58532

 Aggregate  (cost=99.06..99.07 rows=1 width=8) (actual time=0.219..0.220 rows=1 loops=1)
   Buffers: shared hit=84
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=2.15..99.05 rows=1 width=0) (actual time=0.217..0.218 rows=0 loops=1)
         Buffers: shared hit=84
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=1.72..98.58 rows=1 width=4) (actual time=0.217..0.217 rows=0 loops=1)
               Buffers: shared hit=84
               I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=1.44..95.28 rows=1 width=11) (actual time=0.216..0.217 rows=0 loops=1)
                     Buffers: shared hit=84
                     I/O Timings: read=0.000 write=0.000
                     ->  Nested Loop  (cost=1.01..94.81 rows=1 width=23) (actual time=0.216..0.217 rows=0 loops=1)
                           Buffers: shared hit=84
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..91.20 rows=1 width=11) (actual time=0.216..0.216 rows=0 loops=1)
                                 Index Cond: (ci_runners.runner_type = 1)
                                 Filter: (ci_runners.active AND (ci_runners.contacted_at IS NULL) AND (((ci_runners.token)::text = 'R'::text) OR ((ci_runners.description)::text ~~* 'R'::text)))
                                 Rows Removed by Filter: 77
                                 Buffers: shared hit=84
                                 I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on public.taggings ci::runner_taggings_ec122f5  (cost=0.58..3.60 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (("ci::runner_taggings_ec122f5".taggable_id = ci_runners.id) AND (("ci::runner_taggings_ec122f5".taggable_type)::text = 'Ci::Runner'::text))
                                 I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects  (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((ci_runner_projects.runner_id = "ci::runner_taggings_ec122f5".taggable_id) AND (ci_runner_projects.project_id = 29102510))
                           Heap Fetches: 0
                           I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..3.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: ((runner_version.status = 3) AND (runner_version.version = (ci_runners.version)::text))
                     Heap Fetches: 0
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using tags_pkey on public.tags  (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (tags.id = "ci::runner_taggings_ec122f5".tag_id)
               Filter: ((tags.name)::text ~~ 'shell'::text)
               Rows Removed by Filter: 0
               I/O Timings: read=0.000 write=0.000

`OWNED_OR_INSTANCE_WIDE`

active=true

::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan

WITH "base_ancestors_cte" AS MATERIALIZED (
  SELECT "namespaces"."traversal_ids"
  FROM "namespaces"
    INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
  WHERE "namespaces"."type" = 'Group'
    AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
  INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
    FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534

 Nested Loop  (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   CTE base_ancestors_cte
     ->  Nested Loop  (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
           Buffers: shared hit=2
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects  (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                 Index Cond: (projects.id = 29102510)
                 Buffers: shared hit=2
                 I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1  (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
                 Index Cond: (namespaces_1.id = projects.namespace_id)
                 I/O Timings: read=0.000 write=0.000
   ->  HashAggregate  (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
         Group Key: unnest(base_ancestors_cte.traversal_ids)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000
         ->  ProjectSet  (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
               Buffers: shared hit=2
               I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on base_ancestors_cte  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
                     Buffers: shared hit=2
                     I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_groups_on_path_and_id on public.namespaces  (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
         Heap Fetches: 0
         I/O Timings: read=0.000 write=0.000

owned_or_instance_wide query plan 🔴

SELECT "ci_runners".*
FROM ((
    SELECT "ci_runners".*
    FROM "ci_runners"
      INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_projects"."project_id" = 29102510)
  UNION ALL (
    SELECT "ci_runners".*
    FROM "ci_runners"
      INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788))
  UNION ALL (
    SELECT "ci_runners".*
    FROM "ci_runners"
    WHERE "ci_runners"."runner_type" = 3)) ci_runners
WHERE "ci_runners"."active" = TRUE
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58544

 Limit  (cost=469970.49..469982.27 rows=101 width=283) (actual time=6287.854..6372.831 rows=101 loops=1)
   Buffers: shared hit=700001 read=144131 dirtied=39879
   I/O Timings: read=9931.832 write=0.000
   ->  Gather Merge  (cost=469970.49..892572.26 rows=3622048 width=283) (actual time=6287.848..6372.806 rows=101 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=700001 read=144131 dirtied=39879
         I/O Timings: read=9931.832 write=0.000
         ->  Sort  (cost=468970.46..473498.02 rows=1811024 width=283) (actual time=6280.392..6280.487 rows=68 loops=3)
               Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
               Sort Method: top-N heapsort  Memory: 92kB
               Buffers: shared hit=700001 read=144131 dirtied=39879
               I/O Timings: read=9931.832 write=0.000
               ->  Parallel Append  (cost=0.85..399624.44 rows=1811024 width=283) (actual time=7.112..5824.036 rows=484501 loops=3)
                     Buffers: shared hit=699967 read=144131 dirtied=39879
                     I/O Timings: read=9931.832 write=0.000
                     ->  Nested Loop  (cost=0.85..77.12 rows=14 width=283) (actual time=1.290..1.333 rows=0 loops=1)
                           Buffers: shared hit=8 read=8
                           I/O Timings: read=1.216 write=0.000
                           ->  Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces  (cost=0.42..28.85 rows=14 width=4) (actual time=1.289..1.298 rows=0 loops=1)
                                 Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
                                 Buffers: shared hit=8 read=8
                                 I/O Timings: read=1.216 write=0.000
                           ->  Index Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..3.45 rows=1 width=283) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
                                 Filter: ci_runners.active
                                 Rows Removed by Filter: 0
                                 I/O Timings: read=0.000 write=0.000
                     ->  Nested Loop  (cost=22.96..5439.39 rows=746 width=283) (actual time=14.209..1241.003 rows=1142 loops=1)
                           Buffers: shared hit=3084 read=2500 dirtied=223
                           I/O Timings: read=1194.835 write=0.000
                           ->  Parallel Bitmap Heap Scan on public.ci_runner_projects  (cost=22.53..2815.21 rows=766 width=4) (actual time=9.341..748.702 rows=1142 loops=1)
                                 Buffers: shared hit=1 read=1014 dirtied=46
                                 I/O Timings: read=734.416 write=0.000
                                 ->  Bitmap Index Scan using index_ci_runner_projects_on_project_id  (cost=0.00..22.20 rows=1303 width=0) (actual time=7.089..7.096 rows=1144 loops=1)
                                       Index Cond: (ci_runner_projects.project_id = 29102510)
                                       Buffers: shared hit=1 read=7
                                       I/O Timings: read=6.054 write=0.000
                           ->  Index Scan using index_ci_runners_on_active on public.ci_runners ci_runners_1  (cost=0.43..3.43 rows=1 width=283) (actual time=0.426..0.426 rows=1 loops=1142)
                                 Index Cond: ((ci_runners_1.active = true) AND (ci_runners_1.id = ci_runner_projects.runner_id))
                                 Buffers: shared hit=3083 read=1486 dirtied=177
                                 I/O Timings: read=460.419 write=0.000
                     ->  Parallel Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2  (cost=0.43..366938.91 rows=754213 width=283) (actual time=1.972..5350.958 rows=484120 loops=3)
                           Index Cond: (ci_runners_2.runner_type = 3)
                           Filter: ci_runners_2.active
                           Rows Removed by Filter: 14041
                           Buffers: shared hit=696875 read=141623 dirtied=39656
                           I/O Timings: read=8735.780 write=0.000

full query

::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan

WITH "base_ancestors_cte" AS MATERIALIZED (
  SELECT "namespaces"."traversal_ids"
  FROM "namespaces"
    INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
  WHERE "namespaces"."type" = 'Group'
    AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
  INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
    FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534

 Nested Loop  (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   CTE base_ancestors_cte
     ->  Nested Loop  (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
           Buffers: shared hit=2
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects  (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                 Index Cond: (projects.id = 29102510)
                 Buffers: shared hit=2
                 I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1  (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
                 Index Cond: (namespaces_1.id = projects.namespace_id)
                 I/O Timings: read=0.000 write=0.000
   ->  HashAggregate  (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
         Group Key: unnest(base_ancestors_cte.traversal_ids)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000
         ->  ProjectSet  (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
               Buffers: shared hit=2
               I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on base_ancestors_cte  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
                     Buffers: shared hit=2
                     I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_groups_on_path_and_id on public.namespaces  (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
         Heap Fetches: 0
         I/O Timings: read=0.000 write=0.000

owned_or_instance_wide

SELECT COUNT(*)
FROM ((
    SELECT "ci_runners".*
    FROM "ci_runners"
      INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_projects"."project_id" = 29102510)
  UNION ALL (
    SELECT "ci_runners".*
    FROM "ci_runners"
      INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788))
  UNION ALL (
    SELECT "ci_runners".*
    FROM "ci_runners"
    WHERE "ci_runners"."runner_type" = 1)) ci_runners
  INNER JOIN "ci_runner_versions" "runner_version" ON "runner_version"."version" = "ci_runners"."version"
  INNER JOIN "taggings" "ci::runner_taggings_ec122f5" ON "ci::runner_taggings_ec122f5"."taggable_id" = "ci_runners"."id"
    AND "ci::runner_taggings_ec122f5"."taggable_type" = 'Ci::Runner'
    AND "ci::runner_taggings_ec122f5"."tag_id" IN (
      SELECT "tags"."id"
      FROM "tags"
    WHERE "tags"."name" LIKE 'shell' ESCAPE '!')
WHERE ("ci_runners"."token" = 'R'
  OR "ci_runners"."description" ILIKE 'R')
AND "ci_runners"."active" = TRUE
AND "ci_runners"."contacted_at" IS NULL
AND "runner_version"."status" = 3
AND "ci_runners"."runner_type" = 3

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58547

 Aggregate  (cost=671.70..671.71 rows=1 width=8) (actual time=43.333..43.342 rows=1 loops=1)
   Buffers: shared hit=161 read=558 dirtied=93
   I/O Timings: read=34.311 write=0.000
   ->  Nested Loop  (cost=226.59..671.69 rows=1 width=0) (actual time=43.330..43.338 rows=0 loops=1)
         Buffers: shared hit=161 read=558 dirtied=93
         I/O Timings: read=34.311 write=0.000
         ->  Nested Loop  (cost=226.17..671.22 rows=1 width=4) (actual time=43.329..43.336 rows=0 loops=1)
               Buffers: shared hit=161 read=558 dirtied=93
               I/O Timings: read=34.311 write=0.000
               ->  Nested Loop  (cost=225.89..668.92 rows=1 width=11) (actual time=43.328..43.336 rows=0 loops=1)
                     Buffers: shared hit=161 read=558 dirtied=93
                     I/O Timings: read=34.311 write=0.000
                     ->  Append  (cost=225.31..658.09 rows=3 width=11) (actual time=43.328..43.335 rows=0 loops=1)
                           Buffers: shared hit=161 read=558 dirtied=93
                           I/O Timings: read=34.311 write=0.000
                           ->  Subquery Scan on *SELECT* 1  (cost=225.31..489.46 rows=1 width=11) (actual time=42.806..42.810 rows=0 loops=1)
                                 Buffers: shared hit=154 read=550 dirtied=93
                                 I/O Timings: read=33.882 write=0.000
                                 ->  Nested Loop  (cost=225.31..489.45 rows=1 width=3340) (actual time=42.805..42.808 rows=0 loops=1)
                                       Buffers: shared hit=154 read=550 dirtied=93
                                       I/O Timings: read=33.882 write=0.000
                                       ->  Bitmap Heap Scan on public.ci_runners  (cost=224.88..421.73 rows=34 width=11) (actual time=42.804..42.806 rows=0 loops=1)
                                             Filter: (ci_runners.active AND (ci_runners.contacted_at IS NULL) AND (ci_runners.runner_type = 3))
                                             Rows Removed by Filter: 15
                                             Buffers: shared hit=154 read=550 dirtied=93
                                             I/O Timings: read=33.882 write=0.000
                                             ->  BitmapOr  (cost=224.88..224.88 rows=124 width=0) (actual time=25.136..25.137 rows=0 loops=1)
                                                   Buffers: shared hit=154 read=301
                                                   I/O Timings: read=20.985 write=0.000
                                                   ->  Bitmap Index Scan using index_uniq_ci_runners_on_token  (cost=0.00..1.94 rows=1 width=0) (actual time=0.266..0.267 rows=0 loops=1)
                                                         Index Cond: ((ci_runners.token)::text = 'R'::text)
                                                         Buffers: shared read=3
                                                         I/O Timings: read=0.242 write=0.000
                                                   ->  Bitmap Index Scan using index_ci_runners_on_description_trigram  (cost=0.00..222.92 rows=123 width=0) (actual time=24.867..24.867 rows=252 loops=1)
                                                         Index Cond: ((ci_runners.description)::text ~~* 'R'::text)
                                                         Buffers: shared hit=154 read=298
                                                         I/O Timings: read=20.743 write=0.000
                                       ->  Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects  (cost=0.43..1.99 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                             Index Cond: ((ci_runner_projects.runner_id = ci_runners.id) AND (ci_runner_projects.project_id = 29102510))
                                             Heap Fetches: 0
                                             I/O Timings: read=0.000 write=0.000
                           ->  Subquery Scan on *SELECT* 2  (cost=0.85..77.41 rows=1 width=11) (actual time=0.517..0.518 rows=0 loops=1)
                                 Buffers: shared hit=7 read=8
                                 I/O Timings: read=0.429 write=0.000
                                 ->  Nested Loop  (cost=0.85..77.40 rows=1 width=3340) (actual time=0.515..0.516 rows=0 loops=1)
                                       Buffers: shared hit=7 read=8
                                       I/O Timings: read=0.429 write=0.000
                                       ->  Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces  (cost=0.42..28.85 rows=14 width=4) (actual time=0.513..0.514 rows=0 loops=1)
                                             Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
                                             Buffers: shared hit=7 read=8
                                             I/O Timings: read=0.429 write=0.000
                                       ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_1  (cost=0.43..3.46 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
                                             Index Cond: ((ci_runners_1.runner_type = 3) AND (ci_runners_1.id = ci_runner_namespaces.runner_id))
                                             Filter: (ci_runners_1.active AND (ci_runners_1.contacted_at IS NULL) AND (((ci_runners_1.token)::text = 'R'::text) OR ((ci_runners_1.description)::text ~~* 'R'::text)))
                                             Rows Removed by Filter: 0
                                             I/O Timings: read=0.000 write=0.000
                           ->  Subquery Scan on *SELECT* 3  (cost=0.43..91.21 rows=1 width=11) (actual time=0.002..0.003 rows=0 loops=1)
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Result  (cost=0.43..91.20 rows=1 width=3340) (actual time=0.001..0.002 rows=0 loops=1)
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2  (cost=0.43..91.20 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
                                             Index Cond: (ci_runners_2.runner_type = 1)
                                             Filter: (ci_runners_2.active AND (ci_runners_2.contacted_at IS NULL) AND (((ci_runners_2.token)::text = 'R'::text) OR ((ci_runners_2.description)::text ~~* 'R'::text)))
                                             Rows Removed by Filter: 0
                                             I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on public.taggings ci::runner_taggings_ec122f5  (cost=0.58..3.60 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (("ci::runner_taggings_ec122f5".taggable_id = "*SELECT* 1".id) AND (("ci::runner_taggings_ec122f5".taggable_type)::text = 'Ci::Runner'::text))
                           I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..2.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: ((runner_version.status = 3) AND (runner_version.version = ("*SELECT* 1".version)::text))
                     Heap Fetches: 0
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using tags_pkey on public.tags  (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (tags.id = "ci::runner_taggings_ec122f5".tag_id)
               Filter: ((tags.name)::text ~~ 'shell'::text)
               Rows Removed by Filter: 0
               I/O Timings: read=0.000 write=0.000

`PARENT_GROUPS`

active=true

::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan

WITH "base_ancestors_cte" AS MATERIALIZED (
  SELECT "namespaces"."traversal_ids"
  FROM "namespaces"
    INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
  WHERE "namespaces"."type" = 'Group'
    AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
  INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
    FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534

 Nested Loop  (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   CTE base_ancestors_cte
     ->  Nested Loop  (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
           Buffers: shared hit=2
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects  (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                 Index Cond: (projects.id = 29102510)
                 Buffers: shared hit=2
                 I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1  (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
                 Index Cond: (namespaces_1.id = projects.namespace_id)
                 I/O Timings: read=0.000 write=0.000
   ->  HashAggregate  (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
         Group Key: unnest(base_ancestors_cte.traversal_ids)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000
         ->  ProjectSet  (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
               Buffers: shared hit=2
               I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on base_ancestors_cte  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
                     Buffers: shared hit=2
                     I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_groups_on_path_and_id on public.namespaces  (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
         Heap Fetches: 0
         I/O Timings: read=0.000 write=0.000

belonging_to_group (called from belonging_to_parent_group_of_project) query plan

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788)
  AND "ci_runners"."active" = TRUE
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 101

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58540

 Limit  (cost=77.38..77.42 rows=14 width=283) (actual time=0.087..0.088 rows=0 loops=1)
   Buffers: shared hit=21
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=77.38..77.42 rows=14 width=283) (actual time=0.086..0.087 rows=0 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=21
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.85..77.12 rows=14 width=283) (actual time=0.057..0.058 rows=0 loops=1)
               Buffers: shared hit=15
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces  (cost=0.42..28.85 rows=14 width=4) (actual time=0.056..0.056 rows=0 loops=1)
                     Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
                     Buffers: shared hit=15
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..3.45 rows=1 width=283) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
                     Filter: ci_runners.active
                     Rows Removed by Filter: 0
                     I/O Timings: read=0.000 write=0.000

full query

::Group.joins(:projects).where(projects: { id: project_id }).self_and_ancestors query plan

WITH "base_ancestors_cte" AS MATERIALIZED (
  SELECT "namespaces"."traversal_ids"
  FROM "namespaces"
    INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
  WHERE "namespaces"."type" = 'Group'
    AND "projects"."id" = 29102510
)
SELECT "id"
FROM "namespaces"
  INNER JOIN ( SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
    FROM base_ancestors_cte) AS ancestors (ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58534

 Nested Loop  (cost=6.87..20.53 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=1)
   Buffers: shared hit=2
   I/O Timings: read=0.000 write=0.000
   CTE base_ancestors_cte
     ->  Nested Loop  (cost=0.27..6.64 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=1)
           Buffers: shared hit=2
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects  (cost=0.14..3.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                 Index Cond: (projects.id = 29102510)
                 Buffers: shared hit=2
                 I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_groups_on_path_and_id on public.namespaces namespaces_1  (cost=0.12..3.14 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
                 Index Cond: (namespaces_1.id = projects.namespace_id)
                 I/O Timings: read=0.000 write=0.000
   ->  HashAggregate  (cost=0.10..0.23 rows=10 width=4) (actual time=0.011..0.012 rows=0 loops=1)
         Group Key: unnest(base_ancestors_cte.traversal_ids)
         Buffers: shared hit=2
         I/O Timings: read=0.000 write=0.000
         ->  ProjectSet  (cost=0.00..0.08 rows=10 width=4) (actual time=0.010..0.011 rows=0 loops=1)
               Buffers: shared hit=2
               I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on base_ancestors_cte  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
                     Buffers: shared hit=2
                     I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using index_groups_on_path_and_id on public.namespaces  (cost=0.12..1.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: (namespaces.id = (unnest(base_ancestors_cte.traversal_ids)))
         Heap Fetches: 0
         I/O Timings: read=0.000 write=0.000

belonging_to_group (called from belonging_to_parent_group_of_project) query plan

SELECT COUNT(*)
FROM ((
    SELECT "ci_runners".*
    FROM "ci_runners"
      INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_projects"."project_id" = 29102510)
  UNION ALL (
    SELECT "ci_runners".*
    FROM "ci_runners"
      INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_namespaces"."namespace_id" IN (13132392, 13132068, 5849309, 3730788))
  UNION ALL (
    SELECT "ci_runners".*
    FROM "ci_runners"
    WHERE "ci_runners"."runner_type" = 1)) ci_runners
  INNER JOIN "ci_runner_versions" "runner_version" ON "runner_version"."version" = "ci_runners"."version"
  INNER JOIN "taggings" "ci::runner_taggings_ec122f5" ON "ci::runner_taggings_ec122f5"."taggable_id" = "ci_runners"."id"
    AND "ci::runner_taggings_ec122f5"."taggable_type" = 'Ci::Runner'
    AND "ci::runner_taggings_ec122f5"."tag_id" IN (
      SELECT "tags"."id"
      FROM "tags"
    WHERE "tags"."name" LIKE 'shell' ESCAPE '!')
WHERE ("ci_runners"."token" = 'R'
  OR "ci_runners"."description" ILIKE 'R')
AND "ci_runners"."active" = TRUE
AND "ci_runners"."contacted_at" IS NULL
AND "runner_version"."status" = 3
AND "ci_runners"."runner_type" = 3

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/17459/commands/58541

 Aggregate  (cost=671.70..671.71 rows=1 width=8) (actual time=8.477..8.484 rows=1 loops=1)
   Buffers: shared hit=719
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=226.59..671.69 rows=1 width=0) (actual time=8.473..8.480 rows=0 loops=1)
         Buffers: shared hit=719
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=226.17..671.22 rows=1 width=4) (actual time=8.473..8.479 rows=0 loops=1)
               Buffers: shared hit=719
               I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=225.89..668.92 rows=1 width=11) (actual time=8.472..8.478 rows=0 loops=1)
                     Buffers: shared hit=719
                     I/O Timings: read=0.000 write=0.000
                     ->  Append  (cost=225.31..658.09 rows=3 width=11) (actual time=8.472..8.477 rows=0 loops=1)
                           Buffers: shared hit=719
                           I/O Timings: read=0.000 write=0.000
                           ->  Subquery Scan on *SELECT* 1  (cost=225.31..489.46 rows=1 width=11) (actual time=8.361..8.363 rows=0 loops=1)
                                 Buffers: shared hit=704
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Nested Loop  (cost=225.31..489.45 rows=1 width=3340) (actual time=8.360..8.362 rows=0 loops=1)
                                       Buffers: shared hit=704
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Bitmap Heap Scan on public.ci_runners  (cost=224.88..421.73 rows=34 width=11) (actual time=8.358..8.360 rows=0 loops=1)
                                             Filter: (ci_runners.active AND (ci_runners.contacted_at IS NULL) AND (ci_runners.runner_type = 3))
                                             Rows Removed by Filter: 15
                                             Buffers: shared hit=704
                                             I/O Timings: read=0.000 write=0.000
                                             ->  BitmapOr  (cost=224.88..224.88 rows=124 width=0) (actual time=4.750..4.751 rows=0 loops=1)
                                                   Buffers: shared hit=455
                                                   I/O Timings: read=0.000 write=0.000
                                                   ->  Bitmap Index Scan using index_uniq_ci_runners_on_token  (cost=0.00..1.94 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1)
                                                         Index Cond: ((ci_runners.token)::text = 'R'::text)
                                                         Buffers: shared hit=3
                                                         I/O Timings: read=0.000 write=0.000
                                                   ->  Bitmap Index Scan using index_ci_runners_on_description_trigram  (cost=0.00..222.92 rows=123 width=0) (actual time=4.701..4.701 rows=252 loops=1)
                                                         Index Cond: ((ci_runners.description)::text ~~* 'R'::text)
                                                         Buffers: shared hit=452
                                                         I/O Timings: read=0.000 write=0.000
                                       ->  Index Only Scan using index_unique_ci_runner_projects_on_runner_id_and_project_id on public.ci_runner_projects  (cost=0.43..1.99 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                             Index Cond: ((ci_runner_projects.runner_id = ci_runners.id) AND (ci_runner_projects.project_id = 29102510))
                                             Heap Fetches: 0
                                             I/O Timings: read=0.000 write=0.000
                           ->  Subquery Scan on *SELECT* 2  (cost=0.85..77.41 rows=1 width=11) (actual time=0.106..0.107 rows=0 loops=1)
                                 Buffers: shared hit=15
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Nested Loop  (cost=0.85..77.40 rows=1 width=3340) (actual time=0.105..0.105 rows=0 loops=1)
                                       Buffers: shared hit=15
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Index Scan using index_ci_runner_namespaces_on_namespace_id on public.ci_runner_namespaces  (cost=0.42..28.85 rows=14 width=4) (actual time=0.104..0.104 rows=0 loops=1)
                                             Index Cond: (ci_runner_namespaces.namespace_id = ANY ('{13132392,13132068,5849309,3730788}'::integer[]))
                                             Buffers: shared hit=15
                                             I/O Timings: read=0.000 write=0.000
                                       ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_1  (cost=0.43..3.46 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
                                             Index Cond: ((ci_runners_1.runner_type = 3) AND (ci_runners_1.id = ci_runner_namespaces.runner_id))
                                             Filter: (ci_runners_1.active AND (ci_runners_1.contacted_at IS NULL) AND (((ci_runners_1.token)::text = 'R'::text) OR ((ci_runners_1.description)::text ~~* 'R'::text)))
                                             Rows Removed by Filter: 0
                                             I/O Timings: read=0.000 write=0.000
                           ->  Subquery Scan on *SELECT* 3  (cost=0.43..91.21 rows=1 width=11) (actual time=0.002..0.003 rows=0 loops=1)
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Result  (cost=0.43..91.20 rows=1 width=3340) (actual time=0.001..0.002 rows=0 loops=1)
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners ci_runners_2  (cost=0.43..91.20 rows=1 width=11) (actual time=0.000..0.000 rows=0 loops=0)
                                             Index Cond: (ci_runners_2.runner_type = 1)
                                             Filter: (ci_runners_2.active AND (ci_runners_2.contacted_at IS NULL) AND (((ci_runners_2.token)::text = 'R'::text) OR ((ci_runners_2.description)::text ~~* 'R'::text)))
                                             Rows Removed by Filter: 0
                                             I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_taggings_on_taggable_id_and_taggable_type_and_context on public.taggings ci::runner_taggings_ec122f5  (cost=0.58..3.60 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (("ci::runner_taggings_ec122f5".taggable_id = "*SELECT* 1".id) AND (("ci::runner_taggings_ec122f5".taggable_type)::text = 'Ci::Runner'::text))
                           I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..2.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: ((runner_version.status = 3) AND (runner_version.version = ("*SELECT* 1".version)::text))
                     Heap Fetches: 0
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using tags_pkey on public.tags  (cost=0.43..0.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (tags.id = "ci::runner_taggings_ec122f5".tag_id)
               Filter: ((tags.name)::text ~~ 'shell'::text)
               Rows Removed by Filter: 0
               I/O Timings: read=0.000 write=0.000

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

I have evaluated the MR acceptance checklist for this MR.

Edited Apr 20, 2023 by Pedro Pombeiro

GraphQL: Add membership filter to ProjectRunnersResolver

What does this MR do and why?

Screenshots or screen recordings

How to set up and validate locally

Database query plans

DIRECT

active=true

full query

OWNED_OR_INSTANCE_WIDE

active=true

full query

PARENT_GROUPS

active=true

full query

MR acceptance checklist

Merge request reports

`DIRECT`

`OWNED_OR_INSTANCE_WIDE`

`PARENT_GROUPS`