Add user-callout for disabled web-hooks
What does this MR do and why?
See: #362662 (closed)
This adds helpers to the Rails code to render a new user callout if any web-hook a user might be responsible for is disabled.
Queries and plans:
The following finders are added:
WebHooks::CalloutsFinder
This is always called as callouts.execute.limit(CALLOUT_BATCH_SIZE).delete_all
The query plans depend on the hook:
With a system hook:
DELETE FROM "user_callouts"
WHERE "user_callouts"."id" IN (SELECT "user_callouts"."id" FROM "user_callouts" WHERE "user_callouts"."feature_name" = 54 AND "user_callouts"."user_id" IN (SELECT "users"."id" FROM "users" WHERE "users"."admin" = TRUE) LIMIT 100)Query Plan
ModifyTable on public.user_callouts (cost=951.37..954.40 rows=1 width=34) (actual time=1592.905..1592.910 rows=0 loops=1)
Buffers: shared hit=452 read=559 dirtied=30
I/O Timings: read=1577.366 write=0.000
-> Nested Loop (cost=951.37..954.40 rows=1 width=34) (actual time=1592.830..1592.834 rows=0 loops=1)
Buffers: shared hit=452 read=559 dirtied=30
I/O Timings: read=1577.366 write=0.000
-> HashAggregate (cost=950.94..950.95 rows=1 width=32) (actual time=1592.830..1592.832 rows=0 loops=1)
Group Key: "ANY_subquery".id
Buffers: shared hit=452 read=559 dirtied=30
I/O Timings: read=1577.366 write=0.000
-> Subquery Scan on ANY_subquery (cost=0.87..950.94 rows=1 width=32) (actual time=1592.826..1592.828 rows=0 loops=1)
Buffers: shared hit=452 read=559 dirtied=30
I/O Timings: read=1577.366 write=0.000
-> Limit (cost=0.87..950.93 rows=1 width=4) (actual time=1592.825..1592.826 rows=0 loops=1)
Buffers: shared hit=452 read=559 dirtied=30
I/O Timings: read=1577.366 write=0.000
-> Nested Loop (cost=0.87..950.93 rows=1 width=4) (actual time=1592.823..1592.824 rows=0 loops=1)
Buffers: shared hit=452 read=559 dirtied=30
I/O Timings: read=1577.366 write=0.000
-> Index Scan using index_users_on_admin on public.users (cost=0.43..72.12 rows=267 width=4) (actual time=13.020..1072.523 rows=249 loops=1)
Index Cond: (users.admin = true)
Buffers: shared hit=1 read=263 dirtied=30
I/O Timings: read=1066.457 write=0.000
-> Index Scan using index_user_callouts_on_user_id_and_feature_name on public.user_callouts user_callouts_1 (cost=0.43..3.28 rows=1 width=8) (actual time=2.077..2.077 rows=0 loops=249)
Index Cond: ((user_callouts_1.user_id = users.id) AND (user_callouts_1.feature_name = 54))
Buffers: shared hit=451 read=296
I/O Timings: read=510.909 write=0.000
-> Index Scan using user_callouts_pkey on public.user_callouts (cost=0.43..3.45 rows=1 width=10) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (user_callouts.id = "ANY_subquery".id)
I/O Timings: read=0.000 write=0.000Timings
Time: 1.598 s
- planning: 4.962 ms
- execution: 1.593 s
- I/O read: 1.577 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 452 (~3.50 MiB) from the buffer pool
- reads: 559 (~4.40 MiB) from the OS file cache, including disk I/O
- dirtied: 30 (~240.00 KiB)
- writes: 0With a project hook:
DELETE FROM "user_callouts"
WHERE "user_callouts"."id" IN (
SELECT "user_callouts"."id" FROM "user_callouts"
WHERE "user_callouts"."feature_name" = 54
AND "user_callouts"."user_id" IN (
SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE "members"."type" = 'ProjectMember'
AND "members"."source_id" = 1
AND "members"."source_type" = 'Project'
AND "members"."requested_at" IS NULL
AND (("members"."user_id" IS NULL AND "members"."invite_token" IS NOT NULL) OR "users"."state" = 'active')
AND "members"."requested_at" IS NULL
AND (members.access_level > 5) AND "members"."access_level" IN (50, 40))
LIMIT 100)Query Plan
ModifyTable on public.user_callouts (cost=31.24..34.27 rows=1 width=34) (actual time=11.640..11.647 rows=0 loops=1)
Buffers: shared hit=6 read=6
I/O Timings: read=11.391 write=0.000
-> Nested Loop (cost=31.24..34.27 rows=1 width=34) (actual time=11.637..11.643 rows=0 loops=1)
Buffers: shared hit=6 read=6
I/O Timings: read=11.391 write=0.000
-> HashAggregate (cost=30.81..30.82 rows=1 width=32) (actual time=11.636..11.641 rows=0 loops=1)
Group Key: "ANY_subquery".id
Buffers: shared hit=6 read=6
I/O Timings: read=11.391 write=0.000
-> Subquery Scan on ANY_subquery (cost=18.49..30.81 rows=1 width=32) (actual time=11.633..11.637 rows=0 loops=1)
Buffers: shared hit=6 read=6
I/O Timings: read=11.391 write=0.000
-> Limit (cost=18.49..30.80 rows=1 width=4) (actual time=11.631..11.635 rows=0 loops=1)
Buffers: shared hit=6 read=6
I/O Timings: read=11.391 write=0.000
-> Nested Loop (cost=18.49..30.80 rows=1 width=4) (actual time=11.629..11.632 rows=0 loops=1)
Buffers: shared hit=6 read=6
I/O Timings: read=11.391 write=0.000
-> HashAggregate (cost=18.05..18.08 rows=3 width=4) (actual time=11.559..11.562 rows=1 loops=1)
Group Key: members.user_id
Buffers: shared hit=3 read=6
I/O Timings: read=11.391 write=0.000
-> Nested Loop Left Join (cost=1.00..18.05 rows=3 width=4) (actual time=11.541..11.549 rows=1 loops=1)
Filter: (((members.user_id IS NULL) AND (members.invite_token IS NOT NULL)) OR ((users.state)::text = 'active'::text))
Rows Removed by Filter: 0
Buffers: shared hit=3 read=6
I/O Timings: read=11.391 write=0.000
-> Index Scan using index_non_requested_project_members_on_source_id_and_type on public.members (cost=0.56..7.65 rows=3 width=69) (actual time=7.060..7.065 rows=1 loops=1)
Index Cond: ((members.source_id = 1) AND ((members.source_type)::text = 'Project'::text))
Filter: ((members.access_level > 5) AND (members.access_level = ANY ('{50,40}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared read=5
I/O Timings: read=6.964 write=0.000
-> Index Scan using users_pkey on public.users (cost=0.43..3.45 rows=1 width=11) (actual time=4.469..4.469 rows=1 loops=1)
Index Cond: (users.id = members.user_id)
Buffers: shared hit=3 read=1
I/O Timings: read=4.427 write=0.000
-> Index Scan using index_user_callouts_on_user_id_and_feature_name on public.user_callouts user_callouts_1 (cost=0.43..3.29 rows=1 width=8) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: ((user_callouts_1.user_id = members.user_id) AND (user_callouts_1.feature_name = 54))
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Index Scan using user_callouts_pkey on public.user_callouts (cost=0.43..3.45 rows=1 width=10) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (user_callouts.id = "ANY_subquery".id)
I/O Timings: read=0.000 write=0.000Timings
Time: 15.495 ms
- planning: 3.658 ms
- execution: 11.837 ms
- I/O read: 11.391 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 6 (~48.00 KiB) from the buffer pool
- reads: 6 (~48.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0With a group hook:
DELETE FROM "user_callouts"
WHERE "user_callouts"."id" IN (
SELECT "user_callouts"."id" FROM "user_callouts"
WHERE "user_callouts"."feature_name" = 54
AND "user_callouts"."user_id" IN (
SELECT "users"."id" FROM "users" INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_id" = 22
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."access_level" = 50
)
LIMIT 100)Query Plan
ModifyTable on public.user_callouts (cost=7.99..11.01 rows=1 width=34) (actual time=6.756..6.761 rows=0 loops=1)
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Nested Loop (cost=7.99..11.01 rows=1 width=34) (actual time=6.752..6.757 rows=0 loops=1)
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> HashAggregate (cost=7.55..7.56 rows=1 width=32) (actual time=6.752..6.756 rows=0 loops=1)
Group Key: "ANY_subquery".id
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Subquery Scan on ANY_subquery (cost=7.48..7.55 rows=1 width=32) (actual time=6.749..6.753 rows=0 loops=1)
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Limit (cost=7.48..7.54 rows=1 width=4) (actual time=6.748..6.751 rows=0 loops=1)
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Nested Loop (cost=7.48..7.54 rows=1 width=4) (actual time=6.746..6.749 rows=0 loops=1)
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> HashAggregate (cost=7.05..7.06 rows=1 width=8) (actual time=6.745..6.747 rows=0 loops=1)
Group Key: users.id
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Nested Loop (cost=1.00..7.04 rows=1 width=8) (actual time=6.742..6.744 rows=0 loops=1)
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..3.59 rows=1 width=4) (actual time=6.740..6.741 rows=0 loops=1)
Index Cond: ((members.source_id = 22) AND ((members.source_type)::text = 'Namespace'::text))
Filter: ((members.requested_at IS NULL) AND (members.access_level <> 5) AND ((members.type)::text = 'GroupMember'::text) AND (members.access_level = 50))
Rows Removed by Filter: 0
Buffers: shared read=4
I/O Timings: read=6.590 write=0.000
-> Index Only Scan using users_pkey on public.users (cost=0.43..3.45 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (users.id = members.user_id)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_user_callouts_on_user_id_and_feature_name on public.user_callouts user_callouts_1 (cost=0.43..0.46 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((user_callouts_1.user_id = users.id) AND (user_callouts_1.feature_name = 54))
I/O Timings: read=0.000 write=0.000
-> Index Scan using user_callouts_pkey on public.user_callouts (cost=0.43..3.45 rows=1 width=10) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (user_callouts.id = "ANY_subquery".id)
I/O Timings: read=0.000 write=0.000Timings
Time: 8.854 ms
- planning: 1.856 ms
- execution: 6.998 ms
- I/O read: 6.590 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 0 from the buffer pool
- reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
WebHooks::HooksFinder
Called to find hooks a user has access to. Currently only called with any? in the finder to find disabled hooks:
WebHooks::HooksFinder.new(current_user: user, params: { disabled: true }).any?(using myself as the user, since I have access as a maintainer to a number of web-hooks):
The result of this query is cached in Redis to prevent frequent execution.
SELECT 1 AS one FROM ((SELECT "web_hooks".* FROM "web_hooks" WHERE "web_hooks"."type" = 'ProjectHook' AND "web_hooks"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 3614858 AND (project_authorizations.access_level >= 40) AND "projects"."hidden" = FALSE ORDER BY "projects"."id" DESC))
UNION
(SELECT "web_hooks".* FROM "web_hooks" WHERE "web_hooks"."type" = 'SystemHook')
UNION
(SELECT "web_hooks".* FROM "web_hooks" WHERE "web_hooks"."type" = 'GroupHook' AND "web_hooks"."group_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" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "namespaces"."type" = 'Group' AND "members"."user_id" = 3614858 AND "members"."requested_at" IS NULL AND (access_level >= 10) AND (members.access_level >= 50))
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" ORDER BY "namespaces"."id" DESC))) web_hooks WHERE (recent_failures > 3 OR disabled_until >= '2022-06-23 15:51:20.706677') LIMIT 1Query Plan
Limit (cost=7407.49..7407.51 rows=1 width=4) (actual time=455.948..455.965 rows=0 loops=1)
Buffers: shared hit=592 read=239 dirtied=5
I/O Timings: read=445.243 write=0.000
-> Subquery Scan on web_hooks (cost=7407.49..7408.75 rows=63 width=4) (actual time=455.946..455.962 rows=0 loops=1)
Buffers: shared hit=592 read=239 dirtied=5
I/O Timings: read=445.243 write=0.000
-> HashAggregate (cost=7407.49..7408.12 rows=63 width=801) (actual time=455.945..455.959 rows=0 loops=1)
Group Key: web_hooks_1.id, web_hooks_1.project_id, web_hooks_1.created_at, web_hooks_1.updated_at, web_hooks_1.type, web_hooks_1.service_id, web_hooks_1.push_events, web_hooks_1.issues_events, web_hooks_1.merge_requests_events, web_hooks_1.tag_push_events, web_hooks_1.group_id, web_hooks_1.note_events, web_hooks_1.enable_ssl_verification, web_hooks_1.wiki_page_events, web_hooks_1.pipeline_events, web_hooks_1.confidential_issues_events, web_hooks_1.repository_update_events, web_hooks_1.job_events, web_hooks_1.confidential_note_events, web_hooks_1.push_events_branch_filter, web_hooks_1.encrypted_token, web_hooks_1.encrypted_token_iv, web_hooks_1.encrypted_url, web_hooks_1.encrypted_url_iv, web_hooks_1.deployment_events, web_hooks_1.feature_flag_events, web_hooks_1.releases_events, web_hooks_1.member_events, web_hooks_1.subgroup_events, web_hooks_1.recent_failures, web_hooks_1.backoff_count, web_hooks_1.disabled_until, web_hooks_1.encrypted_url_variables, web_hooks_1.encrypted_url_variables_iv
Buffers: shared hit=592 read=239 dirtied=5
I/O Timings: read=445.243 write=0.000
-> Append (cost=2910.32..7402.13 rows=63 width=801) (actual time=455.940..455.955 rows=0 loops=1)
Buffers: shared hit=592 read=239 dirtied=5
I/O Timings: read=445.243 write=0.000
-> Nested Loop (cost=2910.32..6296.15 rows=61 width=320) (actual time=378.960..378.965 rows=0 loops=1)
Buffers: shared hit=530 read=203 dirtied=4
I/O Timings: read=369.797 write=0.000
-> HashAggregate (cost=2909.90..2917.65 rows=775 width=4) (actual time=309.046..309.183 rows=89 loops=1)
Group Key: projects.id
Buffers: shared hit=280 read=175 dirtied=4
I/O Timings: read=302.052 write=0.000
-> Nested Loop (cost=1.01..2900.21 rows=775 width=4) (actual time=20.159..308.554 rows=89 loops=1)
Buffers: shared hit=280 read=175 dirtied=4
I/O Timings: read=302.052 write=0.000
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..223.63 rows=776 width=4) (actual time=11.135..58.609 rows=89 loops=1)
Index Cond: ((project_authorizations.user_id = 3614858) AND (project_authorizations.access_level >= 40))
Heap Fetches: 9
Buffers: shared hit=61 read=38 dirtied=3
I/O Timings: read=56.706 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.44..3.45 rows=1 width=4) (actual time=2.802..2.802 rows=1 loops=89)
Index Cond: (projects.id = project_authorizations.project_id)
Filter: (NOT projects.hidden)
Rows Removed by Filter: 0
Buffers: shared hit=219 read=137 dirtied=1
I/O Timings: read=245.346 write=0.000
-> Index Scan using index_web_hooks_on_project_id on public.web_hooks web_hooks_1 (cost=0.43..4.35 rows=1 width=320) (actual time=0.782..0.782 rows=0 loops=89)
Index Cond: (web_hooks_1.project_id = projects.id)
Filter: (((web_hooks_1.type)::text = 'ProjectHook'::text) AND ((web_hooks_1.recent_failures > 3) OR (web_hooks_1.disabled_until >= '2022-06-23 15:51:20.706677+00'::timestamp with time zone)))
Rows Removed by Filter: 0
Buffers: shared hit=250 read=28
I/O Timings: read=67.745 write=0.000
-> Index Scan using index_web_hooks_on_type on public.web_hooks web_hooks_2 (cost=0.43..2.15 rows=1 width=320) (actual time=0.048..0.048 rows=0 loops=1)
Index Cond: ((web_hooks_2.type)::text = 'SystemHook'::text)
Filter: ((web_hooks_2.recent_failures > 3) OR (web_hooks_2.disabled_until >= '2022-06-23 15:51:20.706677+00'::timestamp with time zone))
Rows Removed by Filter: 0
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=516.17..1102.89 rows=1 width=320) (actual time=76.928..76.934 rows=0 loops=1)
Buffers: shared hit=59 read=36 dirtied=1
I/O Timings: read=75.447 write=0.000
-> HashAggregate (cost=515.89..517.89 rows=200 width=4) (actual time=76.799..76.810 rows=8 loops=1)
Group Key: namespaces.id
Buffers: shared hit=43 read=36 dirtied=1
I/O Timings: read=75.447 write=0.000
-> Sort (cost=512.58..513.13 rows=221 width=4) (actual time=76.781..76.788 rows=8 loops=1)
Sort Key: namespaces.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=43 read=36 dirtied=1
I/O Timings: read=75.447 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=1.13..499.55 rows=221 width=365) (actual time=19.860..76.646 rows=8 loops=1)
Buffers: shared hit=40 read=36 dirtied=1
I/O Timings: read=75.447 write=0.000
-> Nested Loop (cost=1.13..12.95 rows=1 width=365) (actual time=19.843..70.478 rows=7 loops=1)
Buffers: shared hit=18 read=33 dirtied=1
I/O Timings: read=69.666 write=0.000
-> Index Scan using index_members_on_user_id_source_id_source_type on public.members (cost=0.56..9.36 rows=1 width=4) (actual time=12.923..33.504 rows=7 loops=1)
Index Cond: ((members.user_id = 3614858) AND ((members.source_type)::text = 'Namespace'::text))
Filter: ((members.requested_at IS NULL) AND (members.access_level >= 10) AND (members.access_level >= 50) AND ((members.type)::text = 'GroupMember'::text))
Rows Removed by Filter: 5
Buffers: shared hit=2 read=14
I/O Timings: read=33.251 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.56..3.58 rows=1 width=365) (actual time=5.271..5.271 rows=1 loops=7)
Index Cond: (namespaces_1.id = members.source_id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=16 read=19 dirtied=1
I/O Timings: read=36.415 write=0.000
-> Nested Loop (cost=0.43..48.22 rows=22 width=365) (actual time=2.377..3.021 rows=0 loops=2)
Buffers: shared hit=22 read=3
I/O Timings: read=5.780 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.005 rows=4 loops=2)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_groups_on_parent_id_id on public.namespaces namespaces_2 (cost=0.43..4.78 rows=2 width=365) (actual time=0.751..0.751 rows=0 loops=8)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Buffers: shared hit=22 read=3
I/O Timings: read=5.780 write=0.000
-> CTE Scan on base_and_descendants namespaces (cost=0.00..4.42 rows=221 width=4) (actual time=19.865..76.693 rows=8 loops=1)
Buffers: shared hit=40 read=36 dirtied=1
I/O Timings: read=75.447 write=0.000
-> Index Scan using index_web_hooks_on_group_id on public.web_hooks web_hooks_3 (cost=0.28..2.92 rows=1 width=320) (actual time=0.014..0.014 rows=0 loops=8)
Index Cond: (web_hooks_3.group_id = namespaces.id)
Filter: ((web_hooks_3.recent_failures > 3) OR (web_hooks_3.disabled_until >= '2022-06-23 15:51:20.706677+00'::timestamp with time zone))
Rows Removed by Filter: 0
Buffers: shared hit=16
I/O Timings: read=0.000 write=0.000
</details>
<details>
<summary>Timings:</summary>
Time: 460.519 ms
- planning: 3.919 ms
- execution: 456.600 ms
- I/O read: 445.243 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 592 (~4.60 MiB) from the buffer pool
- reads: 239 (~1.90 MiB) from the OS file cache, including disk I/O
- dirtied: 5 (~40.00 KiB)
- writes: 0
</details>
When used to find records, the query is:
```sql
SELECT "web_hooks".* FROM (
(SELECT "web_hooks".* FROM "web_hooks"
WHERE "web_hooks"."type" = 'ProjectHook'
AND "web_hooks"."project_id" IN (
SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
WHERE "project_authorizations"."user_id" = 14
AND (project_authorizations.access_level >= 40)
AND "projects"."hidden" = FALSE
ORDER BY "projects"."id" DESC
)
)
UNION
(SELECT "web_hooks".* FROM "web_hooks"
WHERE "web_hooks"."type" = 'GroupHook'
AND "web_hooks"."group_id" IN (
WITH
"descendants_base_cte" AS MATERIALIZED (
SELECT "namespaces"."traversal_ids", "namespaces"."id" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group' AND "members"."user_id" = 14
AND "members"."requested_at" IS NULL AND (access_level >= 10) AND (members.access_level >= 50)
),
"superset" AS (
SELECT d1.traversal_ids FROM descendants_base_cte d1
WHERE NOT EXISTS (
SELECT 1
FROM descendants_base_cte d2
WHERE d2.id = ANY(d1.traversal_ids)
AND d2.id <> d1.id
)
)
SELECT DISTINCT id FROM "superset", "namespaces"
WHERE "namespaces"."type" = 'Group'
AND next_traversal_ids_sibling("superset"."traversal_ids") > "namespaces"."traversal_ids"
AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
ORDER BY "namespaces"."id" DESC
)
)) web_hooks
WHERE (recent_failures > 3 OR disabled_until >= '2022-06-10 15:58:38')Query Plan
HashAggregate (cost=523050.09..523050.74 rows=65 width=737) (actual time=221.672..221.688 rows=0 loops=1)
Group Key: web_hooks.id, web_hooks.project_id, web_hooks.created_at, web_hooks.updated_at, web_hooks.type, web_hooks.service_id, web_hooks.push_events, web_hooks.issues_events, web_hooks.merge_requests_events, web_hooks.tag_push_events, web_hooks.group_id, web_hooks.note_events, web_hooks.enable_ssl_verification, web_hooks.wiki_page_events, web_hooks.pipeline_events, web_hooks.confidential_issues_events, web_hooks.repository_update_events, web_hooks.job_events, web_hooks.confidential_note_events, web_hooks.push_events_branch_filter, web_hooks.encrypted_token, web_hooks.encrypted_token_iv, web_hooks.encrypted_url, web_hooks.encrypted_url_iv, web_hooks.deployment_events, web_hooks.feature_flag_events, web_hooks.releases_events, web_hooks.member_events, web_hooks.subgroup_events, web_hooks.recent_failures, web_hooks.backoff_count, web_hooks.disabled_until
Buffers: shared hit=105 read=114
I/O Timings: read=219.039 write=0.000
-> Append (cost=3139.57..523044.89 rows=65 width=737) (actual time=221.666..221.681 rows=0 loops=1)
Buffers: shared hit=105 read=114
I/O Timings: read=219.039 write=0.000
-> Nested Loop (cost=3139.57..6637.97 rows=64 width=288) (actual time=199.788..199.795 rows=0 loops=1)
Buffers: shared hit=105 read=104
I/O Timings: read=197.361 write=0.000
-> HashAggregate (cost=3139.14..3147.15 rows=801 width=4) (actual time=158.824..158.899 rows=28 loops=1)
Group Key: projects.id
Buffers: shared hit=51 read=74
I/O Timings: read=157.021 write=0.000
-> Nested Loop (cost=1.01..3129.13 rows=801 width=4) (actual time=21.510..158.605 rows=28 loops=1)
Buffers: shared hit=51 read=74
I/O Timings: read=157.021 write=0.000
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..366.17 rows=801 width=4) (actual time=11.665..13.824 rows=28 loops=1)
Index Cond: ((project_authorizations.user_id = 14) AND (project_authorizations.access_level >= 40))
Heap Fetches: 24
Buffers: shared hit=6 read=7
I/O Timings: read=13.546 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.44..3.45 rows=1 width=4) (actual time=5.163..5.163 rows=1 loops=28)
Index Cond: (projects.id = project_authorizations.project_id)
Filter: (NOT projects.hidden)
Rows Removed by Filter: 0
Buffers: shared hit=45 read=67
I/O Timings: read=143.475 write=0.000
-> Index Scan using index_web_hooks_on_project_id on public.web_hooks (cost=0.43..4.35 rows=1 width=288) (actual time=1.458..1.458 rows=0 loops=28)
Index Cond: (web_hooks.project_id = projects.id)
Filter: (((web_hooks.type)::text = 'ProjectHook'::text) AND ((web_hooks.recent_failures > 3) OR (web_hooks.disabled_until >= '2022-06-10 15:58:38+00'::timestamp with time zone)))
Rows Removed by Filter: 0
Buffers: shared hit=54 read=30
I/O Timings: read=40.340 write=0.000
-> Merge Join (cost=509817.10..516405.94 rows=1 width=288) (actual time=21.873..21.880 rows=0 loops=1)
Merge Cond: (namespaces.id = web_hooks_1.group_id)
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> Unique (cost=507541.90..509424.11 rows=376442 width=4) (actual time=21.870..21.875 rows=0 loops=1)
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
CTE descendants_base_cte
-> Nested Loop (cost=1.13..12.95 rows=1 width=32) (actual time=21.834..21.836 rows=0 loops=1)
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> Index Scan using index_members_on_user_id_source_id_source_type on public.members (cost=0.56..9.37 rows=1 width=4) (actual time=21.831..21.832 rows=0 loops=1)
Index Cond: ((members.user_id = 14) AND ((members.source_type)::text = 'Namespace'::text))
Filter: ((members.requested_at IS NULL) AND (members.access_level >= 10) AND (members.access_level >= 50) AND ((members.type)::text = 'GroupMember'::text))
Rows Removed by Filter: 6
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.56..3.58 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_1.id = members.source_id)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Sort (cost=507528.95..508470.05 rows=376442 width=4) (actual time=21.868..21.871 rows=0 loops=1)
Sort Key: namespaces.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> Nested Loop (cost=0.80..472666.52 rows=376442 width=4) (actual time=21.841..21.843 rows=0 loops=1)
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> Nested Loop Anti Join (cost=0.00..0.07 rows=1 width=32) (actual time=21.840..21.840 rows=0 loops=1)
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> CTE Scan on descendants_base_cte d1 (cost=0.00..0.02 rows=1 width=36) (actual time=21.838..21.838 rows=0 loops=1)
Buffers: shared read=10
I/O Timings: read=21.678 write=0.000
-> CTE Scan on descendants_base_cte d2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_traversal_ids_for_groups_btree on public.namespaces (cost=0.80..468902.04 rows=376442 width=32) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((namespaces.traversal_ids < next_traversal_ids_sibling(d1.traversal_ids)) AND (namespaces.traversal_ids >= d1.traversal_ids))
I/O Timings: read=0.000 write=0.000
-> Sort (cost=2275.21..2275.75 rows=219 width=288) (actual time=0.000..0.000 rows=0 loops=0)
Sort Key: web_hooks_1.group_id DESC
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_web_hooks_on_type on public.web_hooks web_hooks_1 (cost=0.43..2266.69 rows=219 width=288) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((web_hooks_1.type)::text = 'GroupHook'::text)
Filter: ((web_hooks_1.recent_failures > 3) OR (web_hooks_1.disabled_until >= '2022-06-10 15:58:38+00'::timestamp with time zone))
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
Timings:
Time: 236.422 ms
- planning: 12.702 ms
- execution: 223.720 ms
- I/O read: 219.039 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 105 (~840.00 KiB) from the buffer pool
- reads: 114 (~912.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0MR 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 by Alex Kalderimis