WIP: POC: Restrict the dashboard to only show issues/MRs related to logged-in user
What does this MR do?
Restrict the dashboard to only show issues/MRs related to logged-in user.
What related to logged-in user is following
- authored by logged-in user
- added todos to logged-in user(following is detail)
- assigned to logged-in user (now and past)
- mentioned to logged-in user
- build_failed because of logged-in user's commit
- marked todo by logged-in user
- approval_required to logged-in user (EE-only feature)
- unmergeable because of logged-in user's commit
- directly_addressed (sorry, I don't know what this mean from source code. Maybe similar to mention?)
- commented by logged-in user
- subscribed or unsubscribed by logged-in user
- reacted (added some emojis) by logged-in user
Are there points in the code the reviewer needs to double check?
- UX
- SQL performance (It needs to add index on
subscriptions.user_id)
Query generated by /dashboard/issues is following
Test data
SELECT COUNT(*) FROM issues; count
12337
SELECT COUNT(*) FROM notes; count
1009
SELECT COUNT(*) FROM award_emoji; count
1017
SELECT COUNT(*) FROM subscriptions; count
1017
SELECT COUNT(*) FROM todos; count
10359
Query
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "issues"."deleted_at" IS NULL AND ("issues"."state" IN ('opened')) AND "projects"."archived" = 'f' AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" WHERE "issues"."deleted_at" IS NULL AND ("issues"."state" IN ('opened')) AND "projects"."archived" = 'f') AND ("project_features"."issues_access_level" IN (10, 20) OR "project_features"."issues_access_level" IS NULL) AND (issues.id IN (SELECT "issues"."id" FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "issues"."deleted_at" IS NULL AND ("issues"."state" IN ('opened')) AND "projects"."archived" = 'f' AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" WHERE "issues"."deleted_at" IS NULL AND ("issues"."state" IN ('opened')) AND "projects"."archived" = 'f') AND ("project_features"."issues_access_level" IN (10, 20) OR "project_features"."issues_access_level" IS NULL) AND "issues"."author_id" = 1
UNION
SELECT "award_emoji"."awardable_id" FROM "award_emoji" WHERE "award_emoji"."user_id" = 1 AND "award_emoji"."awardable_type" = 'Issue'
UNION
SELECT "subscriptions"."subscribable_id" FROM "subscriptions" WHERE "subscriptions"."user_id" = 1 AND "subscriptions"."subscribable_type" = 'Issue'
UNION
SELECT "todos"."target_id" FROM "todos" WHERE "todos"."user_id" = 1 AND "todos"."target_type" = 'Issue')) AND "projects"."archived" = 'f' ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 20 OFFSET 0
Query plan
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=28456.85..28456.90 rows=20 width=347) (actual time=32.234..32.250 rows=20 loops=1)
-> Sort (cost=28456.85..28464.45 rows=3040 width=347) (actual time=32.234..32.248 rows=20 loops=1)
Sort Key: issues.created_at DESC, issues.id DESC
Sort Method: top-N heapsort Memory: 32kB
-> Hash Left Join (cost=27369.67..28375.96 rows=3040 width=347) (actual time=25.211..31.640 rows=972 loops=1)
Hash Cond: (projects.id = project_features.project_id)
Filter: ((project_features.issues_access_level = ANY ('{10,20}'::integer[])) OR (project_features.issues_access_level IS NULL))
-> Hash Join (cost=27295.25..28252.13 rows=3040 width=351) (actual time=24.595..30.592 rows=972 loops=1)
Hash Cond: (projects.id = projects_1.id)
-> Hash Join (cost=26121.90..27036.78 rows=3059 width=351) (actual time=12.004..17.661 rows=972 loops=1)
Hash Cond: (issues.project_id = projects.id)
-> Hash Join (cost=7494.76..8365.78 rows=3590 width=347) (actual time=10.501..15.800 rows=972 loops=1)
Hash Cond: (issues.id = issues_2.id)
-> Seq Scan on issues (cost=0.00..835.16 rows=7181 width=347) (actual time=0.016..4.017 rows=7185 loops=1)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text))
Rows Removed by Filter: 5152
-> Hash (cost=7475.58..7475.58 rows=1534 width=4) (actual time=10.426..10.426 rows=1488 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 69kB
-> HashAggregate (cost=7444.90..7460.24 rows=1534 width=4) (actual time=10.034..10.209 rows=1488 loops=1)
Group Key: issues_2.id
-> Append (cost=111.34..7441.07 rows=1534 width=4) (actual time=1.215..9.495 rows=1560 loops=1)
-> Hash Left Join (cost=111.34..7239.33 rows=502 width=4) (actual time=1.215..8.537 rows=527 loops=1)
Hash Cond: (projects_2.id = project_features_1.project_id)
Filter: ((project_features_1.issues_access_level = ANY ('{10,20}'::integer[])) OR (project_features_1.issues_access_level IS NULL))
-> Nested Loop Semi Join (cost=36.91..7156.75 rows=502 width=8) (actual time=0.321..7.373 rows=527 loops=1)
-> Nested Loop (cost=36.35..6443.54 rows=505 width=12) (actual time=0.296..3.211 rows=527 loops=1)
-> Bitmap Heap Scan on issues issues_2 (cost=36.07..765.07 rows=593 width=8) (actual time=0.261..1.021 rows=527 loops=1)
Recheck Cond: (author_id = 1)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text))
Rows Removed by Filter: 495
Heap Blocks: exact=528
-> Bitmap Index Scan on index_issues_on_author_id (cost=0.00..35.92 rows=1018 width=0) (actual time=0.194..0.194 rows=1022 loops=1)
Index Cond: (author_id = 1)
-> Index Scan using projects_pkey on projects projects_2 (cost=0.28..9.57 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=527)
Index Cond: (id = issues_2.project_id)
Filter: ((NOT archived) AND ((alternatives: SubPlan 3 or hashed SubPlan 4) OR (visibility_level = ANY ('{0,10,20}'::integer[]))))
SubPlan 3
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.28..8.30 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=527)
Index Cond: ((user_id = 1) AND (project_id = projects_2.id))
Heap Fetches: 523
SubPlan 4
-> Bitmap Heap Scan on project_authorizations project_authorizations_3 (cost=4.42..18.29 rows=18 width=4) (never executed)
Recheck Cond: (user_id = 1)
-> Bitmap Index Scan on index_project_authorizations_on_user_id_project_id_access_level (cost=0.00..4.42 rows=18 width=0) (never executed)
Index Cond: (user_id = 1)
-> Nested Loop (cost=0.57..2.34 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=527)
-> Index Scan using projects_pkey on projects projects_3 (cost=0.28..0.60 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=527)
Index Cond: (id = projects_2.id)
Filter: (NOT archived)
-> Index Scan using index_issues_on_project_id_and_due_date_and_id_and_state on issues issues_3 (cost=0.29..1.71 rows=3 width=4) (actual time=0.005..0.005 rows=1 loops=527)
Index Cond: ((project_id = projects_3.id) AND ((state)::text = 'opened'::text))
Filter: (deleted_at IS NULL)
-> Hash (cost=46.41..46.41 rows=2241 width=8) (actual time=0.810..0.810 rows=2237 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 120kB
-> Seq Scan on project_features project_features_1 (cost=0.00..46.41 rows=2241 width=8) (actual time=0.008..0.446 rows=2237 loops=1)
-> Index Scan using index_award_emoji_on_user_id_and_name on award_emoji (cost=0.28..8.29 rows=1 width=4) (actual time=0.026..0.032 rows=1 loops=1)
Index Cond: (user_id = 1)
Filter: ((awardable_type)::text = 'Issue'::text)
-> Index Scan using index_subscriptions_on_user_id on subscriptions (cost=0.28..8.29 rows=1 width=4) (actual time=0.012..0.018 rows=1 loops=1)
Index Cond: (user_id = 1)
Filter: ((subscribable_type)::text = 'Issue'::text)
-> Bitmap Heap Scan on todos (cost=36.30..169.81 rows=1030 width=4) (actual time=0.128..0.730 rows=1031 loops=1)
Recheck Cond: (user_id = 1)
Filter: ((target_type)::text = 'Issue'::text)
Rows Removed by Filter: 3
Heap Blocks: exact=96
-> Bitmap Index Scan on index_todos_on_user_id (cost=0.00..36.04 rows=1034 width=0) (actual time=0.103..0.103 rows=1034 loops=1)
Index Cond: (user_id = 1)
-> Hash (cost=18603.47..18603.47 rows=1894 width=4) (actual time=1.484..1.484 rows=2237 loops=1)
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 111kB
-> Seq Scan on projects (cost=0.00..18603.47 rows=1894 width=4) (actual time=0.096..1.089 rows=2237 loops=1)
Filter: ((NOT archived) AND (NOT archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))))
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.28..8.30 rows=1 width=0) (never executed)
Index Cond: ((user_id = 1) AND (project_id = projects.id))
Heap Fetches: 0
SubPlan 2
-> Bitmap Heap Scan on project_authorizations project_authorizations_1 (cost=4.42..18.29 rows=18 width=4) (actual time=0.049..0.061 rows=17 loops=1)
Recheck Cond: (user_id = 1)
Heap Blocks: exact=3
-> Bitmap Index Scan on index_project_authorizations_on_user_id_project_id_access_level (cost=0.00..4.42 rows=18 width=0) (actual time=0.044..0.044 rows=18 loops=1)
Index Cond: (user_id = 1)
-> Hash (cost=1145.61..1145.61 rows=2219 width=8) (actual time=12.548..12.548 rows=1475 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 90kB
-> HashAggregate (cost=1123.42..1145.61 rows=2219 width=8) (actual time=11.999..12.235 rows=1475 loops=1)
Group Key: issues_1.project_id
-> Hash Join (cost=172.02..1105.47 rows=7181 width=8) (actual time=2.200..10.392 rows=7185 loops=1)
Hash Cond: (issues_1.project_id = projects_1.id)
-> Seq Scan on issues issues_1 (cost=0.00..835.16 rows=7181 width=4) (actual time=0.012..6.147 rows=7185 loops=1)
Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text))
Rows Removed by Filter: 5152
-> Hash (cost=144.23..144.23 rows=2223 width=4) (actual time=2.113..2.113 rows=2237 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 111kB
-> Seq Scan on projects projects_1 (cost=0.00..144.23 rows=2223 width=4) (actual time=0.014..1.496 rows=2237 loops=1)
Filter: (NOT archived)
-> Hash (cost=46.41..46.41 rows=2241 width=8) (actual time=0.592..0.592 rows=2237 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 120kB
-> Seq Scan on project_features (cost=0.00..46.41 rows=2241 width=8) (actual time=0.007..0.296 rows=2237 loops=1)
Planning time: 7.765 ms
Execution time: 33.270 ms
Why was this MR needed?
Displaying dashboard without any filter is very slow, and meaningless.
See #25504 (moved)
Screenshots (if relevant)
Nothing.
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Documentation created/updated -
API support added -
Tests added for this feature/bug - Review
-
Has been reviewed by UX -
Has been reviewed by Frontend -
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together
What are the relevant issue numbers?
Closes #25504 (moved)
Edited by username-removed-7736