Enable design and wiki events on contribution calendar
What does this MR do?
Known changes in behaviour
So previously there was a single query for commented actions (i.e. notes added), which was guarded by the :merge_requests feature.
This appears to me to be a bug - it has the consequence that if I make a comment on an issue, then my contribution is not counted if the project does not have merge-requests enabled.
To preserve the MR gating aspect, I split this into two queries - one for all comments on MRs, and one for all comments on other things, applying the :merge_requests feature gating only the the former. This now means that if I comment on an issue, and the project does not have issues enabled, then my contribution will be counted. But a) how did I comment on the issue anyway? and b) it would have in previous code, if merge requests were enabled and issues were not.
SQL queries
The Event.contributions query is used in ContributionsCalendar.events_by_date where it produces the following SQL (see https://explain.depesz.com/s/DYmV):
-- using my user_id so that we hit gitlab-org/gitlab as one of the projects
SELECT "events".* FROM "events" WHERE (action = 5
OR (action = 1 AND target_type IN ('MergeRequest','Issue','DesignManagement::Design','WikiPage::Meta'))
OR (action = 3 AND target_type IN ('MergeRequest','Issue'))
OR (action = 7 AND target_type IN ('MergeRequest'))
OR (action = 2 AND target_type IN ('DesignManagement::Design','WikiPage::Meta'))
OR (action = 6 AND target_type IN ('Note'))
) AND "events"."author_id" = 3614858 AND "events"."created_at" BETWEEN '2020-08-16 00:00:00' AND '2020-08-16 23:59:59.999999' AND "events"."project_id" IN (SELECT "projects"."id" FROM ((SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (SELECT DISTINCT "events"."project_id" FROM "events" WHERE (action = 5
OR (action = 1 AND target_type IN ('MergeRequest','Issue','DesignManagement::Design','WikiPage::Meta'))
OR (action = 3 AND target_type IN ('MergeRequest','Issue'))
OR (action = 7 AND target_type IN ('MergeRequest'))
OR (action = 2 AND target_type IN ('DesignManagement::Design','WikiPage::Meta'))
OR (action = 6 AND target_type IN ('Note'))
) AND "events"."author_id" = 3614858 AND (created_at > '2019-08-26 10:09:35.999128')) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 86))
UNION
(SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (SELECT DISTINCT "events"."project_id" FROM "events" WHERE (action = 5
OR (action = 1 AND target_type IN ('MergeRequest','Issue','DesignManagement::Design','WikiPage::Meta'))
OR (action = 3 AND target_type IN ('MergeRequest','Issue'))
OR (action = 7 AND target_type IN ('MergeRequest'))
OR (action = 2 AND target_type IN ('DesignManagement::Design','WikiPage::Meta'))
OR (action = 6 AND target_type IN ('Note'))
) AND "events"."author_id" = 3614858 AND (created_at > '2019-08-26 10:09:36.014385')) AND "projects"."visibility_level" IN (0, 10, 20))) projects ORDER BY "projects"."id" DESC)which explains as follows:
Hash Join (cost=8029.52..8032.28 rows=1 width=72) (actual time=11.898..11.899 rows=0 loops=1)
Hash Cond: (projects.id = events.project_id)
Buffers: shared hit=1 read=3
I/O Timings: read=11.795
-> HashAggregate (cost=8025.88..8027.88 rows=200 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Group Key: projects.id
-> Sort (cost=8017.13..8018.59 rows=583 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Sort Key: projects.id DESC
-> Subquery Scan on projects (cost=7978.69..7990.35 rows=583 width=4) (actual time=0.000..0.000 rows=0 loops=0)
-> HashAggregate (cost=7978.69..7984.52 rows=583 width=4316) (actual time=0.000..0.000 rows=0 loops=0)
Group Key: projects_1.id, projects_1.name, projects_1.path, projects_1.description, projects_1.created_at, projects_1.updated_at, projects_1.creator_id, projects_1.namespace_id, projects_1.last_activity_at, projects_1.import_url, projects_1.visibility_level, projects_1.archived, projects_1.merge_requests_template, projects_1.star_count, projects_1.merge_requests_rebase_enabled, projects_1.import_type, projects_1.import_source, projects_1.avatar, projects_1.approvals_before_merge, projects_1.reset_approvals_on_push, projects_1.merge_requests_ff_only_enabled, projects_1.issues_template, projects_1.mirror, projects_1.mirror_last_update_at, projects_1.mirror_last_successful_update_at, projects_1.mirror_user_id, projects_1.shared_runners_enabled, projects_1.runners_token, projects_1.build_coverage_regex, projects_1.build_allow_git_fetch, projects_1.build_timeout, projects_1.mirror_trigger_builds, projects_1.public_builds, projects_1.pending_delete, projects_1.last_repository_check_failed, projects_1.last_repository_check_at, projects_1.container_registry_enabled, projects_1.only_allow_merge_if_pipeline_succeeds, projects_1.has_external_issue_tracker, projects_1.repository_storage, projects_1.request_access_enabled, projects_1.has_external_wiki, projects_1.repository_read_only, projects_1.lfs_enabled, projects_1.description_html, projects_1.only_allow_merge_if_all_discussions_are_resolved, projects_1.repository_size_limit, projects_1.service_desk_enabled, projects_1.printing_merge_request_link_enabled, projects_1.auto_cancel_pending_pipelines, projects_1.cached_markdown_version, projects_1.last_repository_updated_at, projects_1.ci_config_path, projects_1.disable_overriding_approvers_per_merge_request, projects_1.delete_error, projects_1.storage_version, projects_1.resolve_outdated_diff_discussions, projects_1.remote_mirror_available_overridden, projects_1.only_mirror_protected_branches, projects_1.pull_mirror_available_overridden, projects_1.jobs_cache_index, projects_1.external_authorization_classification_label, projects_1.mirror_overwrites_diverged_branches, projects_1.external_webhook_token, projects_1.pages_https_only, projects_1.packages_enabled, projects_1.merge_requests_author_approval, projects_1.pool_repository_id, projects_1.runners_token_encrypted, projects_1.bfg_object_map, projects_1.detected_repository_languages, projects_1.merge_requests_disable_committers_approval, projects_1.require_password_to_approve, projects_1.emails_disabled, projects_1.max_pages_size, projects_1.max_artifacts_size, projects_1.pull_mirror_branch_prefix, projects_1.remove_source_branch_after_merge, projects_1.marked_for_deletion_at, projects_1.marked_for_deletion_by_user_id, projects_1.suggestion_commit_message, projects_1.autoclose_referenced_issues
-> Append (cost=1219.55..7859.18 rows=583 width=4316) (actual time=0.000..0.000 rows=0 loops=0)
-> Nested Loop Semi Join (cost=1219.55..4340.99 rows=1 width=723) (actual time=0.000..0.000 rows=0 loops=0)
-> Nested Loop (cost=1218.55..3506.96 rows=660 width=727) (actual time=0.000..0.000 rows=0 loops=0)
-> HashAggregate (cost=1218.11..1224.71 rows=660 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Group Key: events_1.project_id
-> Index Scan using index_events_on_author_id_and_created_at on public.events events_1 (cost=0.57..1216.46 rows=660 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((events_1.author_id = 3614858) AND (events_1.created_at > '2019-08-26 10:09:35.999128+00'::timestamp with time zone))
Filter: ((events_1.action = 5) OR ((events_1.action = 1) AND ((events_1.target_type)::text = ANY ('{MergeRequest,Issue,DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_1.action = 3) AND ((events_1.target_type)::text = ANY ('{MergeRequest,Issue}'::text[]))) OR ((events_1.action = 7) AND ((events_1.target_type)::text = 'MergeRequest'::text)) OR ((events_1.action = 2) AND ((events_1.target_type)::text = ANY ('{DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_1.action = 6) AND ((events_1.target_type)::text = 'Note'::text)))
Rows Removed by Filter: 0
-> Index Scan using projects_pkey on public.projects projects_1 (cost=0.43..3.45 rows=1 width=723) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_1.id = events_1.project_id)
-> Nested Loop (cost=1.01..1.25 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
-> Index Only Scan using projects_pkey on public.projects projects_2 (cost=0.43..0.47 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_2.id = projects_1.id)
Heap Fetches: 0
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..0.78 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations.user_id = 86) AND (project_authorizations.project_id = projects_2.id))
Heap Fetches: 0
-> Nested Loop (cost=1218.55..3509.44 rows=582 width=723) (actual time=0.000..0.000 rows=0 loops=0)
-> HashAggregate (cost=1218.11..1224.71 rows=660 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Group Key: events_2.project_id
-> Index Scan using index_events_on_author_id_and_created_at on public.events events_2 (cost=0.57..1216.46 rows=660 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((events_2.author_id = 3614858) AND (events_2.created_at > '2019-08-26 10:09:36.014385+00'::timestamp with time zone))
Filter: ((events_2.action = 5) OR ((events_2.action = 1) AND ((events_2.target_type)::text = ANY ('{MergeRequest,Issue,DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_2.action = 3) AND ((events_2.target_type)::text = ANY ('{MergeRequest,Issue}'::text[]))) OR ((events_2.action = 7) AND ((events_2.target_type)::text = 'MergeRequest'::text)) OR ((events_2.action = 2) AND ((events_2.target_type)::text = ANY ('{DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_2.action = 6) AND ((events_2.target_type)::text = 'Note'::text)))
Rows Removed by Filter: 0
-> Index Scan using projects_pkey on public.projects projects_3 (cost=0.43..3.45 rows=1 width=723) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_3.id = events_2.project_id)
Filter: (projects_3.visibility_level = ANY ('{0,10,20}'::integer[]))
Rows Removed by Filter: 0
-> Hash (cost=3.63..3.63 rows=1 width=72) (actual time=11.894..11.894 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=1 read=3
I/O Timings: read=11.795
-> Index Scan using index_events_on_author_id_and_created_at on public.events (cost=0.57..3.63 rows=1 width=72) (actual time=11.893..11.893 rows=0 loops=1)
Index Cond: ((events.author_id = 3614858) AND (events.created_at >= '2020-08-16 00:00:00+00'::timestamp with time zone) AND (events.created_at <= '2020-08-16 23:59:59.999999+00'::timestamp with time zone))
Filter: ((events.action = 5) OR ((events.action = 1) AND ((events.target_type)::text = ANY ('{MergeRequest,Issue,DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events.action = 3) AND ((events.target_type)::text = ANY ('{MergeRequest,Issue}'::text[]))) OR ((events.action = 7) AND ((events.target_type)::text = 'MergeRequest'::text)) OR ((events.action = 2) AND ((events.target_type)::text = ANY ('{DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events.action = 6) AND ((events.target_type)::text = 'Note'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=1 read=3
I/O Timings: read=11.795Summary:
Time: 16.410 ms
- planning: 3.951 ms
- execution: 12.459 ms
- I/O read: 11.795 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0The other modified query is in activity_dates which produces the following SQL:
WITH "user_events_in_range" AS (SELECT "events"."id", "events"."project_id", "events"."target_type", "events"."target_id", "events"."action", date(events.created_at + INTERVAL '0 seconds') as date FROM "events" WHERE (action = 5
OR (action = 1 AND target_type IN ('MergeRequest','Issue','DesignManagement::Design','WikiPage::Meta'))
OR (action = 3 AND target_type IN ('MergeRequest','Issue'))
OR (action = 7 AND target_type IN ('MergeRequest'))
OR (action = 2 AND target_type IN ('DesignManagement::Design','WikiPage::Meta'))
OR (action = 6 AND target_type IN ('Note'))
) AND "events"."created_at" >= '2019-09-04 00:00:00'
AND "events"."created_at" <= '2020-09-04 23:59:59.999999'
AND "events"."author_id" = 3614858
)
SELECT date as key, sum(total_amount)::int as value
FROM ((SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" WHERE "events"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) AND ("project_features"."repository_access_level" > 0 OR "project_features"."repository_access_level" IS NULL)) GROUP BY "events"."project_id", "target_type", "action", "date" HAVING "events"."action" = 5)
UNION ALL
(SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" WHERE "events"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)) GROUP BY "events"."project_id", "target_type", "action", "date" HAVING "events"."action" IN (1, 3) AND "events"."target_type" = 'Issue')
UNION ALL
(SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" WHERE "events"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) AND ("project_features"."wiki_access_level" > 0 OR "project_features"."wiki_access_level" IS NULL)) GROUP BY "events"."project_id", "target_type", "action", "date" HAVING "events"."action" IN (1, 2) AND "events"."target_type" = 'WikiPage::Meta')
UNION ALL
(SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" WHERE "events"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) AND ("project_features"."merge_requests_access_level" > 0 OR "project_features"."merge_requests_access_level" IS NULL)) GROUP BY "events"."project_id", "target_type", "action", "date" HAVING "events"."action" IN (7, 1, 3) AND "events"."target_type" = 'MergeRequest')
UNION ALL
(SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" WHERE "events"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)) GROUP BY "events"."project_id", "target_type", "action", "date" HAVING "events"."action" IN (1, 2) AND "events"."target_type" = 'DesignManagement::Design')
UNION ALL
(SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" INNER JOIN notes ON target_type = 'Note' AND target_id = notes.id WHERE "events"."project_id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) AND ("project_features"."merge_requests_access_level" > 0 OR "project_features"."merge_requests_access_level" IS NULL)) GROUP BY "events"."project_id", "target_type", "action", "date", notes.noteable_type HAVING (action = 6 AND notes.noteable_type = 'MergeRequest'))
UNION ALL
(SELECT "events"."project_id", "target_type", "action", "date", COUNT("events"."id") AS total_amount FROM "user_events_in_range" AS "events" INNER JOIN notes ON target_type = 'Note' AND target_id = notes.id WHERE "events"."project_id" IN (278964, 53, 42, 41, 40, 39, 38, 37, 36, 35) GROUP BY "events"."project_id", "target_type", "action", "date", notes.noteable_type HAVING (action = 6 AND notes.noteable_type != 'MergeRequest'))) events GROUP BY dateWhich has the following monster of an explain (https://explain.depesz.com/s/PBIK):
Group Key: "*SELECT* 1".date
Buffers: shared hit=15317 read=7926 dirtied=118
I/O Timings: read=1160.433
CTE user_events_in_range
-> Index Scan using index_events_on_author_id_and_created_at on public.events events_7 (cost=0.57..1250.61 rows=675 width=27) (actual time=15.142..742.475 rows=3970 loops=1)
Index Cond: ((events_7.author_id = 3614858) AND (events_7.created_at >= '2019-09-04 00:00:00+00'::timestamp with time zone) AND (events_7.created_at <= '2020-09-04 23:59:59.999999+00'::timestamp with time zone))
Filter: ((events_7.action = 5) OR ((events_7.action = 1) AND ((events_7.target_type)::text = ANY ('{MergeRequest,Issue,DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_7.action = 3) AND ((events_7.target_type)::text = ANY ('{MergeRequest,Issue}'::text[]))) OR ((events_7.action = 7) AND ((events_7.target_type)::text = 'MergeRequest'::text)) OR ((events_7.action = 2) AND ((events_7.target_type)::text = ANY ('{DesignManagement::Design,WikiPage::Meta}'::text[]))) OR ((events_7.action = 6) AND ((events_7.target_type)::text = 'Note'::text)))
Rows Removed by Filter: 2328
Buffers: shared hit=443 read=5857 dirtied=80
I/O Timings: read=707.939
-> Sort (cost=311.76..311.78 rows=8 width=12) (actual time=1252.562..1252.619 rows=684 loops=1)
Sort Key: "*SELECT* 1".date
Sort Method: quicksort Memory: 57kB
Buffers: shared hit=15317 read=7926 dirtied=118
I/O Timings: read=1160.433
-> Append (cost=71.35..311.64 rows=8 width=12) (actual time=787.594..1252.316 rows=684 loops=1)
Buffers: shared hit=15314 read=7926 dirtied=118
I/O Timings: read=1160.433
-> Subquery Scan on *SELECT* 1 (cost=71.35..71.42 rows=2 width=12) (actual time=787.592..788.490 rows=227 loops=1)
Buffers: shared hit=498 read=5874 dirtied=81
I/O Timings: read=740.639
-> Aggregate (cost=71.35..71.40 rows=2 width=50) (actual time=787.591..788.446 rows=227 loops=1)
Group Key: events.project_id, events.target_type, events.action, events.date
Buffers: shared hit=498 read=5874 dirtied=81
I/O Timings: read=740.639
-> Sort (cost=71.35..71.35 rows=2 width=46) (actual time=787.580..787.724 rows=1899 loops=1)
Sort Key: events.project_id, events.target_type, events.date
Sort Method: quicksort Memory: 138kB
Buffers: shared hit=498 read=5874 dirtied=81
I/O Timings: read=740.639
-> Nested Loop Semi Join (cost=0.87..71.34 rows=2 width=46) (actual time=53.609..785.917 rows=1899 loops=1)
Buffers: shared hit=492 read=5874 dirtied=81
I/O Timings: read=740.639
-> CTE Scan on user_events_in_range events (cost=0.00..15.19 rows=3 width=46) (actual time=15.148..747.940 rows=2076 loops=1)
Filter: (events.action = 5)
Rows Removed by Filter: 1894
Buffers: shared hit=443 read=5857 dirtied=80
I/O Timings: read=707.939
-> Materialize (cost=0.87..55.72 rows=10 width=4) (actual time=0.004..0.017 rows=6 loops=2076)
Buffers: shared hit=49 read=17 dirtied=1
I/O Timings: read=32.701
-> Nested Loop Left Join (cost=0.87..55.67 rows=10 width=4) (actual time=7.774..33.061 rows=6 loops=1)
Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=49 read=17 dirtied=1
I/O Timings: read=32.701
-> Index Only Scan using projects_pkey on public.projects (cost=0.43..21.02 rows=10 width=4) (actual time=4.299..17.469 rows=6 loops=1)
Index Cond: (projects.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[]))
Heap Fetches: 3
Buffers: shared hit=32 read=10 dirtied=1
I/O Timings: read=17.260
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..3.45 rows=1 width=8) (actual time=2.591..2.591 rows=1 loops=6)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=17 read=7
I/O Timings: read=15.441
-> Subquery Scan on *SELECT* 2 (cost=41.32..41.36 rows=1 width=12) (actual time=1.881..1.979 rows=90 loops=1)
Buffers: shared hit=1116
-> Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=1.880..1.960 rows=90 loops=1)
Group Key: events_1.project_id, events_1.target_type, events_1.action, events_1.date
Buffers: shared hit=1116
-> Sort (cost=41.32..41.33 rows=1 width=46) (actual time=1.874..1.886 rows=139 loops=1)
Sort Key: events_1.project_id, events_1.action, events_1.date
Sort Method: quicksort Memory: 35kB
Buffers: shared hit=1116
-> Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=0.075..1.675 rows=139 loops=1)
Buffers: shared hit=1113
-> CTE Scan on user_events_in_range events_1 (cost=0.00..16.88 rows=1 width=46) (actual time=0.008..0.725 rows=164 loops=1)
Filter: ((events_1.action = ANY ('{1,3}'::integer[])) AND ((events_1.target_type)::text = 'Issue'::text))
Rows Removed by Filter: 3806
-> Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=164)
Filter: ((project_features_1.issues_access_level > 0) OR (project_features_1.issues_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=1113
-> Index Only Scan using projects_pkey on public.projects projects_1 (cost=0.43..20.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=164)
Index Cond: ((projects_1.id = events_1.project_id) AND (projects_1.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
Heap Fetches: 139
Buffers: shared hit=557
-> Index Scan using index_project_features_on_project_id on public.project_features project_features_1 (cost=0.43..3.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=139)
Index Cond: (projects_1.id = project_features_1.project_id)
Buffers: shared hit=556
-> Subquery Scan on *SELECT* 3 (cost=41.32..41.36 rows=1 width=12) (actual time=2.471..2.471 rows=0 loops=1)
-> Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=2.470..2.470 rows=0 loops=1)
Group Key: events_2.project_id, events_2.target_type, events_2.action, events_2.date
-> Sort (cost=41.32..41.33 rows=1 width=46) (actual time=2.468..2.468 rows=0 loops=1)
Sort Key: events_2.project_id, events_2.action, events_2.date
Sort Method: quicksort Memory: 25kB
-> Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=2.453..2.453 rows=0 loops=1)
-> CTE Scan on user_events_in_range events_2 (cost=0.00..16.88 rows=1 width=46) (actual time=0.242..0.870 rows=701 loops=1)
Filter: ((events_2.action = ANY ('{1,2}'::integer[])) AND ((events_2.target_type)::text = 'WikiPage::Meta'::text))
Rows Removed by Filter: 3269
-> Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=701)
Filter: ((project_features_2.wiki_access_level > 0) OR (project_features_2.wiki_access_level IS NULL))
Rows Removed by Filter: 0
-> Index Only Scan using projects_pkey on public.projects projects_2 (cost=0.43..20.96 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=701)
Index Cond: ((projects_2.id = events_2.project_id) AND (projects_2.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features project_features_2 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_2.id = project_features_2.project_id)
-> Subquery Scan on *SELECT* 4 (cost=42.16..42.20 rows=1 width=12) (actual time=2.002..2.103 rows=93 loops=1)
Buffers: shared hit=1033
-> Aggregate (cost=42.16..42.19 rows=1 width=50) (actual time=2.001..2.084 rows=93 loops=1)
Group Key: events_3.project_id, events_3.target_type, events_3.action, events_3.date
Buffers: shared hit=1033
-> Sort (cost=42.16..42.17 rows=1 width=46) (actual time=1.994..2.005 rows=129 loops=1)
Sort Key: events_3.project_id, events_3.action, events_3.date
Sort Method: quicksort Memory: 35kB
Buffers: shared hit=1033
-> Nested Loop Semi Join (cost=0.87..42.15 rows=1 width=46) (actual time=0.078..1.818 rows=129 loops=1)
Buffers: shared hit=1033
-> CTE Scan on user_events_in_range events_3 (cost=0.00..17.72 rows=1 width=46) (actual time=0.007..0.652 rows=141 loops=1)
Filter: (((events_3.target_type)::text = 'MergeRequest'::text) AND (events_3.action = ANY ('{7,1,3}'::integer[])))
Rows Removed by Filter: 3829
-> Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=141)
Filter: ((project_features_3.merge_requests_access_level > 0) OR (project_features_3.merge_requests_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=1033
-> Index Only Scan using projects_pkey on public.projects projects_3 (cost=0.43..20.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=141)
Index Cond: ((projects_3.id = events_3.project_id) AND (projects_3.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
Heap Fetches: 129
Buffers: shared hit=517
-> Index Scan using index_project_features_on_project_id on public.project_features project_features_3 (cost=0.43..3.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=129)
Index Cond: (projects_3.id = project_features_3.project_id)
Buffers: shared hit=516
-> Subquery Scan on *SELECT* 5 (cost=41.32..41.36 rows=1 width=12) (actual time=0.676..0.676 rows=0 loops=1)
-> Aggregate (cost=41.32..41.35 rows=1 width=50) (actual time=0.675..0.675 rows=0 loops=1)
Group Key: events_4.project_id, events_4.target_type, events_4.action, events_4.date
-> Sort (cost=41.32..41.33 rows=1 width=46) (actual time=0.673..0.673 rows=0 loops=1)
Sort Key: events_4.project_id, events_4.action, events_4.date
Sort Method: quicksort Memory: 25kB
-> Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=46) (actual time=0.661..0.661 rows=0 loops=1)
-> CTE Scan on user_events_in_range events_4 (cost=0.00..16.88 rows=1 width=46) (actual time=0.357..0.641 rows=1 loops=1)
Filter: ((events_4.action = ANY ('{1,2}'::integer[])) AND ((events_4.target_type)::text = 'DesignManagement::Design'::text))
Rows Removed by Filter: 3969
-> Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.018..0.019 rows=0 loops=1)
Filter: ((project_features_4.issues_access_level > 0) OR (project_features_4.issues_access_level IS NULL))
Rows Removed by Filter: 0
-> Index Only Scan using projects_pkey on public.projects projects_4 (cost=0.43..20.96 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: ((projects_4.id = events_4.project_id) AND (projects_4.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features project_features_4 (cost=0.43..3.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (projects_4.id = project_features_4.project_id)
-> Subquery Scan on *SELECT* 6 (cost=44.91..44.95 rows=1 width=12) (actual time=450.001..450.515 rows=185 loops=1)
Buffers: shared hit=8580 read=2052 dirtied=37
I/O Timings: read=419.794
-> Aggregate (cost=44.91..44.94 rows=1 width=60) (actual time=449.999..450.476 rows=185 loops=1)
Group Key: events_5.project_id, events_5.target_type, events_5.action, events_5.date, notes.noteable_type
Buffers: shared hit=8580 read=2052 dirtied=37
I/O Timings: read=419.794
-> Sort (cost=44.91..44.92 rows=1 width=56) (actual time=449.990..450.049 rows=640 loops=1)
Sort Key: events_5.project_id, events_5.date
Sort Method: quicksort Memory: 75kB
Buffers: shared hit=8580 read=2052 dirtied=37
I/O Timings: read=419.794
-> Nested Loop (cost=1.44..44.90 rows=1 width=56) (actual time=3.519..448.874 rows=640 loops=1)
Buffers: shared hit=8580 read=2052 dirtied=37
I/O Timings: read=419.794
-> Nested Loop Semi Join (cost=0.87..41.31 rows=1 width=50) (actual time=0.056..15.997 rows=818 loops=1)
Buffers: shared hit=6545
-> CTE Scan on user_events_in_range events_5 (cost=0.00..16.88 rows=1 width=50) (actual time=0.003..1.827 rows=887 loops=1)
Filter: (((events_5.target_type)::text = 'Note'::text) AND (events_5.action = 6))
Rows Removed by Filter: 3083
-> Nested Loop Left Join (cost=0.87..24.43 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=887)
Filter: ((project_features_5.merge_requests_access_level > 0) OR (project_features_5.merge_requests_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=6545
-> Index Only Scan using projects_pkey on public.projects projects_5 (cost=0.43..20.96 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=887)
Index Cond: ((projects_5.id = events_5.project_id) AND (projects_5.id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
Heap Fetches: 818
Buffers: shared hit=3273
-> Index Scan using index_project_features_on_project_id on public.project_features project_features_5 (cost=0.43..3.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=818)
Index Cond: (projects_5.id = project_features_5.project_id)
Buffers: shared hit=3272
-> Index Scan using notes_pkey on public.notes (cost=0.57..3.59 rows=1 width=14) (actual time=0.528..0.528 rows=1 loops=818)
Index Cond: (notes.id = events_5.target_id)
Filter: ((notes.noteable_type)::text = 'MergeRequest'::text)
Rows Removed by Filter: 0
Buffers: shared hit=2035 read=2052 dirtied=37
I/O Timings: read=419.794
-> Subquery Scan on *SELECT* 7 (cost=28.92..28.96 rows=1 width=12) (actual time=5.830..5.958 rows=89 loops=1)
Buffers: shared hit=4087
-> Aggregate (cost=28.92..28.95 rows=1 width=60) (actual time=5.829..5.941 rows=89 loops=1)
Group Key: events_6.project_id, events_6.target_type, events_6.action, events_6.date, notes_1.noteable_type
Buffers: shared hit=4087
-> Sort (cost=28.92..28.92 rows=1 width=56) (actual time=5.821..5.835 rows=175 loops=1)
Sort Key: events_6.project_id, events_6.date, notes_1.noteable_type
Sort Method: quicksort Memory: 38kB
Buffers: shared hit=4087
-> Nested Loop (cost=0.57..28.91 rows=1 width=56) (actual time=0.042..5.697 rows=175 loops=1)
Buffers: shared hit=4087
-> CTE Scan on user_events_in_range events_6 (cost=0.00..25.31 rows=1 width=50) (actual time=0.006..0.951 rows=818 loops=1)
Filter: (((events_6.target_type)::text = 'Note'::text) AND (events_6.action = 6) AND (events_6.project_id = ANY ('{278964,53,42,41,40,39,38,37,36,35}'::integer[])))
Rows Removed by Filter: 3152
-> Index Scan using notes_pkey on public.notes notes_1 (cost=0.57..3.59 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=818)
Index Cond: (notes_1.id = events_6.target_id)
Filter: ((notes_1.noteable_type)::text <> 'MergeRequest'::text)
Rows Removed by Filter: 1
Buffers: shared hit=4087
see https://explain.depesz.com/s/xiMA
and see for the query before the sum aggregation: https://explain.depesz.com/s/rWr3
Summary:
Time: 1.262 s
- planning: 8.341 ms
- execution: 1.254 s
- I/O read: 1.160 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 15317 (~119.70 MiB) from the buffer pool
- reads: 7926 (~61.90 MiB) from the OS file cache, including disk I/O
- dirtied: 118 (~944.00 KiB)
- writes: 0This replaces a query with the following timings:
Time: 10.460 s
- planning: 7.630 ms
- execution: 10.452 s
- I/O read: 10.324 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 18129 (~141.60 MiB) from the buffer pool
- reads: 5876 (~45.90 MiB) from the OS file cache, including disk I/O
- dirtied: 92 (~736.00 KiB)
- writes: 0See: old contribution calendar query
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Edited by Alex Kalderimis