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
Merge request reports
Activity
@victorwu @smcgivern I need feedback.
@hiroponz thanks! I don't think we came to a conclusion on whether or not we wanted to do https://gitlab.com/gitlab-org/gitlab-ce/issues/25504. @victorwu wdyt?
added Community Contribution Discussion ~65372 labels
assigned to @victorwu
@smcgivern Yes. I want to know what is preferable behavior.
I think that the UX Research team may have an idea about this.
https://www.youtube.com/watch?v=587tFIXTee0&feature=youtu.be
@hiroponz : Which view is this? https://gitlab.com/dashboard/issues? What is being done?
@victorwu The issues listed in the dashboard is switched by the logged-in user.
@victorwu I have updated the title and the description.
Thanks for the clarification @hiroponz . This is not something we want to implement into GitLab now. As you referenced https://gitlab.com/gitlab-org/gitlab-ce/issues/25504, this may be helpful for GitLab.com, but certainly it reduces functionality for self-hosted GitLab. So we have not come to a conclusion yet on the final design to solve the problem in #25504 (moved). So unfortunately, we won't merge this. Closing this out now.
I apologize for you working on something that we cannot accept. I encourage you to take a look at our community contribution guidelines here: https://gitlab.com/gitlab-org/gitlab-ce/blob/master/CONTRIBUTING.md if you haven't already done so. That should let you know which features we very likely will accept. And of course, you can always ping me on specific issues before starting work on them. Even though they might not have Accepting Merge Requests , it may be something we are interested in. If you could give me a day to review, I should be able to get back to you very soon. I know you've made many contributions already. So thank you for all of them already!
@victorwu Thanks for reply. I don't think all MRs would be merged, so closing this MR is not problem.
Please register or sign in to reply