GraphQL: Add STALE value to CiRunnerStatus enum
What does this MR do and why?
Describe in detail what your merge request does and why.
This MR does a couple of things:
- it builds on !74545 (merged) to add the
STALEvalue to theCiRunnerStatusenum; - since the new
STALEvalue breaks backward-compatibility on:offline/:not_connectedvalues, I introduced aRunnerStatusResolverwith alegacyModeargument that defaults to version 14.5 (pre-breaking changes). See screenshots for an example of how this works; - while creating this MR, I noticed that a previous recent change introduced a backward-incompatible change, which caused
:pausedto no longer be returned fromstatus, so I took advantage of thelegacyModeto ensure we call the right behavior.
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
I'm not sure why legacyMode is showing a squiggly line here. There was a point during development where legacyMode showed up in the documentation.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Register some runners against the GDK:
for i in $(seq 1 4); do gitlab-runner register -config /tmp/config.gdk.toml \ --non-interactive \ --executor "shell" \ --url "http://gdk.localhost:3000/" \ --description "gitlab.org test runner" \ --tag-list "shell,gdk,mac,test" \ --run-untagged="false" \ --locked="false" \ --access-level="not_protected" \ --registration-token="<gitlab.org group runner registration token>"; \ done -
Update the
created_atdate so that some are considered stale:# Run in Rails console namespace_path = 'gitlab-org' Ci::Runner.belonging_to_group(Group.find_by(path: namespace_path)).limit(2).update_all(created_at: 3.months.ago) -
Submit GraphQL query in GraphiQL:
GraphQL query
query getRunners { runners(last: 4) { nodes { id description shortSha contactedAt legacyStatus: status legacyStatusWithExplicitVersion: status(legacyMode: "14.5") newStatus: status(legacyMode: null) } } }
Query plans
Existing query
SELECT
"ci_runners".*
FROM ((
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 (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 22)
UNION (
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces")))
UNION (
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" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 22)
UNION (
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces")))) ci_runners
ORDER BY
"ci_runners"."created_at" DESC,
"ci_runners"."id" DESC
LIMIT 100Existing query execution plan
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7277/commands/25895
Limit (cost=9875.88..9876.13 rows=100 width=3765) (actual time=0.103..0.108 rows=0 loops=1)
Buffers: shared hit=14
I/O Timings: read=0.000 write=0.000
-> Sort (cost=9875.88..9876.84 rows=384 width=3765) (actual time=0.101..0.106 rows=0 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=14
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=9853.53..9857.37 rows=384 width=3765) (actual time=0.058..0.063 rows=0 loops=1)
Group Key: ci_runners.id, ci_runners.token, ci_runners.created_at, ci_runners.updated_at, ci_runners.description, ci_runners.contacted_at, ci_runners.active, ci_runners.name, ci_runners.version, ci_runners.revision, ci_runners.platform, ci_runners.architecture, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.ip_address, ci_runners.maximum_timeout, ci_runners.runner_type, ci_runners.token_encrypted, ci_runners.public_projects_minutes_cost_factor, ci_runners.private_projects_minutes_cost_factor, ci_runners.config
Buffers: shared hit=8
I/O Timings: read=0.000 write=0.000
-> Append (cost=2191.69..9832.41 rows=384 width=3765) (actual time=0.056..0.060 rows=0 loops=1)
Buffers: shared hit=8
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2191.69..2330.07 rows=5 width=233) (actual time=0.020..0.022 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2191.26..2326.85 rows=5 width=4) (actual time=0.019..0.021 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=2190.83..2191.30 rows=47 width=8) (actual time=0.019..0.021 rows=0 loops=1)
Group Key: namespaces.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1743.27..2190.71 rows=47 width=8) (actual time=0.018..0.020 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=1742.71..1744.71 rows=200 width=4) (actual time=0.018..0.020 rows=0 loops=1)
Group Key: namespaces_1.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces_1 (cost=1735.85..1740.07 rows=211 width=4) (actual time=0.016..0.018 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1735.85 rows=211 width=352) (actual time=0.015..0.016 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_3 (cost=0.43..3.46 rows=1 width=352) (actual time=0.012..0.013 rows=0 loops=1)
Index Cond: (namespaces_3.id = 22)
Filter: ((namespaces_3.type)::text = 'Group'::text)
Rows Removed by Filter: 1
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..172.82 rows=21 width=352) (actual time=0.002..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_4 (cost=0.56..17.24 rows=2 width=352) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_4.parent_id = base_and_descendants.id)
Filter: ((namespaces_4.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_namespaces_on_type_and_id on public.namespaces (cost=0.56..2.23 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((namespaces.type = 'Group'::text) AND (namespaces.id = namespaces_1.id))
Heap Fetches: 0
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.43..1.99 rows=89 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runner_namespaces.namespace_id = namespaces.id)
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners (cost=0.43..0.65 rows=1 width=233) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2466.02..7496.57 rows=379 width=233) (actual time=0.035..0.037 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2465.59..7269.84 rows=379 width=4) (actual time=0.035..0.036 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=2465.16..2508.88 rows=4372 width=4) (actual time=0.035..0.036 rows=0 loops=1)
Group Key: projects.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1743.15..2454.23 rows=4372 width=4) (actual time=0.013..0.014 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=1742.71..1744.71 rows=200 width=4) (actual time=0.013..0.014 rows=0 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces_2 (cost=1735.85..1740.07 rows=211 width=4) (actual time=0.012..0.013 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1735.85 rows=211 width=352) (actual time=0.012..0.013 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_5 (cost=0.43..3.46 rows=1 width=352) (actual time=0.010..0.011 rows=0 loops=1)
Index Cond: (namespaces_5.id = 22)
Filter: ((namespaces_5.type)::text = 'Group'::text)
Rows Removed by Filter: 1
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..172.82 rows=21 width=352) (actual time=0.001..0.001 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_6 (cost=0.56..17.24 rows=2 width=352) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_6.parent_id = base_and_descendants_1.id)
Filter: ((namespaces_6.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..3.34 rows=21 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects.namespace_id = namespaces_2.id)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects (cost=0.43..0.85 rows=24 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runner_projects.project_id = projects.id)
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners ci_runners_1 (cost=0.43..0.60 rows=1 width=233) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners_1.id = ci_runner_projects.runner_id)
I/O Timings: read=0.000 write=0.000New query
SELECT
"ci_runners".*
FROM ((
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 (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 22)
UNION (
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces")))
UNION (
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" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 22)
UNION (
SELECT
"namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces")))) ci_runners
WHERE (ci_runners.created_at < '2021-08-16 14:29:02.893077'
AND (ci_runners.contacted_at IS NULL
OR ci_runners.contacted_at < '2021-08-16 14:29:02.893077'))
ORDER BY
"ci_runners"."created_at" DESC,
"ci_runners"."id" DESC
LIMIT 100New query execution plan
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7277/commands/25894
Limit (cost=9848.56..9848.81 rows=100 width=3765) (actual time=2.986..2.993 rows=0 loops=1)
Buffers: shared hit=13 read=1
I/O Timings: read=2.855 write=0.000
-> Sort (cost=9848.56..9848.95 rows=158 width=3765) (actual time=2.984..2.990 rows=0 loops=1)
Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=13 read=1
I/O Timings: read=2.855 write=0.000
-> HashAggregate (cost=9839.63..9841.21 rows=158 width=3765) (actual time=2.941..2.947 rows=0 loops=1)
Group Key: ci_runners.id, ci_runners.token, ci_runners.created_at, ci_runners.updated_at, ci_runners.description, ci_runners.contacted_at, ci_runners.active, ci_runners.name, ci_runners.version, ci_runners.revision, ci_runners.platform, ci_runners.architecture, ci_runners.run_untagged, ci_runners.locked, ci_runners.access_level, ci_runners.ip_address, ci_runners.maximum_timeout, ci_runners.runner_type, ci_runners.token_encrypted, ci_runners.public_projects_minutes_cost_factor, ci_runners.private_projects_minutes_cost_factor, ci_runners.config
Buffers: shared hit=7 read=1
I/O Timings: read=2.855 write=0.000
-> Append (cost=2191.69..9830.94 rows=158 width=3765) (actual time=2.939..2.945 rows=0 loops=1)
Buffers: shared hit=7 read=1
I/O Timings: read=2.855 write=0.000
-> Nested Loop (cost=2191.69..2330.10 rows=2 width=233) (actual time=2.886..2.890 rows=0 loops=1)
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> Nested Loop (cost=2191.26..2326.85 rows=5 width=4) (actual time=2.885..2.888 rows=0 loops=1)
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> HashAggregate (cost=2190.83..2191.30 rows=47 width=8) (actual time=2.885..2.888 rows=0 loops=1)
Group Key: namespaces.id
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> Nested Loop (cost=1743.27..2190.71 rows=47 width=8) (actual time=2.884..2.887 rows=0 loops=1)
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> HashAggregate (cost=1742.71..1744.71 rows=200 width=4) (actual time=2.884..2.886 rows=0 loops=1)
Group Key: namespaces_1.id
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> CTE Scan on base_and_descendants namespaces_1 (cost=1735.85..1740.07 rows=211 width=4) (actual time=2.881..2.883 rows=0 loops=1)
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1735.85 rows=211 width=352) (actual time=2.880..2.881 rows=0 loops=1)
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_3 (cost=0.43..3.46 rows=1 width=352) (actual time=2.874..2.874 rows=0 loops=1)
Index Cond: (namespaces_3.id = 22)
Filter: ((namespaces_3.type)::text = 'Group'::text)
Rows Removed by Filter: 1
Buffers: shared hit=3 read=1
I/O Timings: read=2.855 write=0.000
-> Nested Loop (cost=0.56..172.82 rows=21 width=352) (actual time=0.004..0.005 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.003 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_4 (cost=0.56..17.24 rows=2 width=352) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_4.parent_id = base_and_descendants.id)
Filter: ((namespaces_4.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_namespaces_on_type_and_id on public.namespaces (cost=0.56..2.23 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((namespaces.type = 'Group'::text) AND (namespaces.id = namespaces_1.id))
Heap Fetches: 0
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.43..1.99 rows=89 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runner_namespaces.namespace_id = namespaces.id)
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners (cost=0.43..0.65 rows=1 width=233) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners.id = ci_runner_namespaces.runner_id)
Filter: ((ci_runners.created_at < '2021-08-16 14:29:02.893077'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at < '2021-08-16 14:29:02.893077'::timestamp without time zone)))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2466.02..7498.47 rows=156 width=233) (actual time=0.051..0.053 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=2465.59..7269.84 rows=379 width=4) (actual time=0.050..0.052 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=2465.16..2508.88 rows=4372 width=4) (actual time=0.050..0.052 rows=0 loops=1)
Group Key: projects.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1743.15..2454.23 rows=4372 width=4) (actual time=0.023..0.024 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=1742.71..1744.71 rows=200 width=4) (actual time=0.023..0.024 rows=0 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces_2 (cost=1735.85..1740.07 rows=211 width=4) (actual time=0.021..0.022 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1735.85 rows=211 width=352) (actual time=0.020..0.021 rows=0 loops=1)
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_5 (cost=0.43..3.46 rows=1 width=352) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (namespaces_5.id = 22)
Filter: ((namespaces_5.type)::text = 'Group'::text)
Rows Removed by Filter: 1
Buffers: shared hit=4
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..172.82 rows=21 width=352) (actual time=0.002..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.002 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_6 (cost=0.56..17.24 rows=2 width=352) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_6.parent_id = base_and_descendants_1.id)
Filter: ((namespaces_6.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..3.34 rows=21 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects.namespace_id = namespaces_2.id)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_ci_runner_projects_on_project_id on public.ci_runner_projects (cost=0.43..0.85 rows=24 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runner_projects.project_id = projects.id)
I/O Timings: read=0.000 write=0.000
-> Index Scan using ci_runners_pkey on public.ci_runners ci_runners_1 (cost=0.43..0.60 rows=1 width=233) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (ci_runners_1.id = ci_runner_projects.runner_id)
Filter: ((ci_runners_1.created_at < '2021-08-16 14:29:02.893077'::timestamp without time zone) AND ((ci_runners_1.contacted_at IS NULL) OR (ci_runners_1.contacted_at < '2021-08-16 14:29:02.893077'::timestamp without time zone)))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000MR 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.
Closes #342799 (closed)
Edited by Pedro Pombeiro