Improve IssuableFinder performance
What does this MR do?
When using IssuableFinder
all filters are applied before projects filter. Projects filter is applied in the end based on issues that were already filtered. That should improve performance of Gitlab.com as it has a lot of public projects.
Does this MR meet the acceptance criteria?
-
Changelog entry added -
Documentation created/updated -
API support added - Tests
-
Added for this feature/bug -
All builds are passing
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Branch has no merge conflicts with master
(if it does - rebase it please) -
Squashed related commits together
What are the relevant issue numbers?
Fixes #25503 (closed)
Merge request reports
Activity
marked the task Changelog entry added as completed
marked the task Squashed related commits together as completed
marked the task Conform by the style guides as completed
marked the task Conform by the merge request performance guides as completed
assigned to @timothyandrew
added 128 commits
-
0e11354c...bbb7fbcd - 127 commits from branch
gitlab-org:master
- 3808790f - Filter by projects in the end of search
-
0e11354c...bbb7fbcd - 127 commits from branch
@jarka You can get closer to a production-like environment by feeding local database with something like $33946.
Basically thousands of public projects and issues/MRs vs authored issues/MRs.
Edited by Oswaldo Ferreira- Resolved by Jarka Košanová
@jarka: Assigning this back to you. At the moment, there isn't enough evidence to show that this change makes any difference to the performance of the query.
assigned to @jarka
added 1169 commits
-
3808790f...176cd2ba - 1168 commits from branch
gitlab-org:master
- 7fdc5664 - Improve issues filtering performance
-
3808790f...176cd2ba - 1168 commits from branch
added 114 commits
-
dd1c8cfe...a2a4fb85 - 113 commits from branch
gitlab-org:master
- f85168b4 - Improve issues filtering performance
-
dd1c8cfe...a2a4fb85 - 113 commits from branch
@timothyandrew Thanks for the first check. I revisited it and changed the way of the filtering by projects.
Results (data from https://gitlab.com/gitlab-org/gitlab-ce/snippets/33946 ):
- Dashboard Issues filtering by author / assignee has improved from 2500-3500 ms to 200 - 450ms
- Dashboard Issues filtering without params set has improved from 17000-19000 ms to 8000-9000 ms
Also merge request dashboard was improved (from around 3000 ms to 400 ms when filtered by assignee and 4500 ms -> 3000 ms when no filtering params set)
assigned to @timothyandrew
- Resolved by Jarka Košanová
- Resolved by Jarka Košanová
@jarka: LGTM, but for one comment regarding the need for moving the
by_project
line.assigned to @jarka
@timothyandrew Thanks for the review. I tried to explain that part. I might be missing something but tried to explain my view on that...
assigned to @timothyandrew
assigned to @jarka
Quick summary
- this change improves performace of
IssuableFinder
generally withby_project
both in the previous original position and also in the end - when searching by assignee or author having
by_project
in the end helps a lot, searching without any special params is not affected that much (might be slightly worse than havingby_project
in the middle however better than the original version we have in master even whenby_project
is in the end)
Manual testing of webpage loading
I tried to reload webpage multiple time with 3 different filters (state filter always included): no additional filter, assignee filter, author filter. When assignee or author filter is set having
by_project
in the end makes quite a big difference. For filtering only by state the difference is quite small having project not in the end might be slightly better.Explain analysis
Explain analysis suggest having
by_project
in the end. Quick comparism (details in the file)Solution IssuesFinder.new(User.last, assignee_id: User.last.id, state: 'opened') IssuesFinder.new(User.last, state: 'opened') Original (master) solution Planning time: 1.270 ms, Execution time: 1316.630 ms Planning time: 0.678 ms, Execution time: 12138.156 ms With change by_project before by_search Planning time: 1.118 ms, Execution time: 1146.743 ms Planning time: 2.210 ms, Execution time: 5678.962 ms With change by_project in the end Planning time: 1.509 ms, Execution time: 1.859 ms Planning time: 2.558 ms, Execution time: 6236.276 ms All there results are based on my local data - basic seed and data taken from https://gitlab.com/gitlab-org/gitlab-ce/snippets/33946 .
Suggestion
Lets go with the version with
by_project
in the end and create a new issue for figuring out what is best for different filters combinations / orders. I think it is not in the scope of this issue.- this change improves performace of
assigned to @timothyandrew
@smcgivern Could you also take a look? Would be nice to get it done soon...
- Resolved by Jarka Košanová
- Resolved by Jarka Košanová
- Resolved by Jarka Košanová
@jarka thanks for the detailed measurements. I tried this on a staging console, and I don't see the same speed increase as you do locally
Here is an example:
irb(main):001:0> class IssuesFinderImproved < IssuesFinder irb(main):002:1> def item_project_ids(items) irb(main):003:2> items ? items.reorder(nil).select(:project_id) : nil irb(main):004:2> end irb(main):005:1> irb(main):006:1* def execute irb(main):007:2> items = init_collection irb(main):008:2> items = by_scope(items) irb(main):009:2> items = by_state(items) irb(main):010:2> items = by_group(items) irb(main):011:2> items = by_search(items) irb(main):012:2> items = by_milestone(items) irb(main):013:2> items = by_assignee(items) irb(main):014:2> items = by_author(items) irb(main):015:2> items = by_label(items) irb(main):016:2> items = by_due_date(items) irb(main):017:2> items = by_non_archived(items) irb(main):018:2> items = by_iids(items) irb(main):019:2> # Filtering by project HAS TO be the last because we use the project IDs yielded by the issuable query thus far irb(main):020:2* items = by_project(items) irb(main):021:2> sort(items) irb(main):022:2> end irb(main):023:1> irb(main):024:1* def by_iids(items) irb(main):025:2> params[:iids].present? ? items.where(iid: params[:iids]) : items irb(main):026:2> end irb(main):027:1> end => :by_iids irb(main):028:0> def run_finder(finder, username) irb(main):029:1> user = User.find_by_username(username) irb(main):030:1> puts finder irb(main):031:1> puts Benchmark.measure { finder.new(user, assignee_id: user.id, state: 'opened').execute.to_a } irb(main):032:1> end => :run_finder irb(main):033:0> run_finder(IssuesFinderImproved, 'smcgivern') IssuesFinderImproved 0.330000 0.000000 0.330000 ( 3.250003) => nil irb(main):034:0> run_finder(IssuesFinder, 'smcgivern') IssuesFinder 0.250000 0.000000 0.250000 ( 3.155272) => nil
One thing I wonder is that if your results locally are affected by being an admin user? I believe the results you saw, I just want to be able to reproduce them!
@smcgivern Hmm, when I use non admin user the query is more complicated but I got similar results (
0.020000 0.010000 0.030000 ( 0.036645)
vs0.250000 0.040000 0.290000 ( 2.389906)
),EXPLAIN
also shows improvement. However it might be that the dataset is just too different from production.Could you may be run
EXPLAIN
on staging (508743 is id of my user)?SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = 'f' WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND ("project_features"."issues_access_level" = 20 OR "project_features"."issues_access_level" IS NULL) UNION SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_authorizations"."user_id" = 508743 AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_features"."issues_access_level" = 10) AND "issues"."assignee_id" = 508743 ORDER BY "issues"."id" DESC
and
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = 'f' WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743 AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND ("project_features"."issues_access_level" = 20 OR "project_features"."issues_access_level" IS NULL) UNION SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND "project_authorizations"."user_id" = 508743 AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND "project_features"."issues_access_level" = 10) ORDER BY "issues"."id" DESC
@maratkalibek is running
EXPLAIN ANALYZE
on this in productionSELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = 'f' WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND ("project_features"."issues_access_level" = 20 OR "project_features"."issues_access_level" IS NULL) UNION SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_authorizations"."user_id" = 508743 AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "project_features"."issues_access_level" = 10) AND "issues"."assignee_id" = 508743 ORDER BY "issues"."id" DESC
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=824802.79..824802.82 rows=10 width=1393) (actual time=11985.918..11985.919 rows=3 loops=1) Sort Key: issues.id DESC Sort Method: quicksort Memory: 30kB -> Merge Join (cost=808913.22..824802.62 rows=10 width=1393) (actual time=11952.941..11985.890 rows=3 loops=1) Merge Cond: (projects_1.id = projects.id) -> Unique (cost=689344.24..693884.02 rows=907955 width=4) (actual time=10418.177..10442.918 rows=28923 loops=1) -> Sort (cost=689344.24..691614.13 rows=907955 width=4) (actual time=10418.175..10426.862 rows=28923 loops=1) Sort Key: projects_1.id Sort Method: external merge Disk: 3256kB -> Append (cost=249447.41..587077.33 rows=907955 width=4) (actual time=4547.636..10194.424 rows=238141 loops=1) -> Hash Right Join (cost=249447.41..341180.26 rows=907954 width=4) (actual time=4547.633..7017.701 rows=238137 loops=1) Hash Cond: (project_features.project_id = projects_1.id) Filter: ((project_features.issues_access_level = 20) OR (project_features.issues_access_level IS NULL)) Rows Removed by Filter: 17735 -> Seq Scan on project_features (cost=0.00..48766.71 rows=2342571 width=8) (actual time=0.077..1091.719 rows=2351358 loops=1) -> Hash (cost=233567.91..233567.91 rows=967880 width=4) (actual time=4542.512..4542.512 rows=255869 loops=1) Buckets: 524288 Batches: 4 Memory Usage: 6355kB -> Hash Join (cost=120267.36..233567.91 rows=967880 width=4) (actual time=1566.929..4392.775 rows=255869 loops=1) Hash Cond: (projects_1.id = projects_2.id) -> Seq Scan on projects projects_1 (cost=0.00..103551.40 rows=1935761 width=4) (actual time=0.041..1532.806 rows=1940906 loops=1) Filter: (NOT pending_delete) Rows Removed by Filter: 3800 -> Hash (cost=117148.56..117148.56 rows=249504 width=4) (actual time=1565.347..1565.347 rows=256318 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 11060kB -> HashAggregate (cost=112158.48..114653.52 rows=249504 width=4) (actual time=1274.049..1467.784 rows=256318 loops=1) Group Key: projects_2.id -> Append (cost=0.86..111534.72 rows=249504 width=4) (actual time=0.203..1055.719 rows=256441 loops=1) -> Nested Loop (cost=0.86..639.18 rows=66 width=4) (actual time=0.202..7.455 rows=143 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..81.15 rows=66 width=4) (actual time=0.145..0.772 rows=143 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 89 -> Index Scan using projects_pkey on projects projects_2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.045..0.045 rows=1 loops=143) Index Cond: (id = project_authorizations.project_id) Filter: (NOT pending_delete) -> Seq Scan on projects projects_3 (cost=0.00..108400.50 rows=249438 width=4) (actual time=0.021..930.744 rows=256298 loops=1) Filter: (visibility_level = ANY ('{20,10}'::integer[])) Rows Removed by Filter: 1688408 -> Nested Loop (cost=230883.60..236817.52 rows=1 width=4) (actual time=2921.471..3070.098 rows=4 loops=1) -> Hash Join (cost=230883.17..236808.93 rows=16 width=16) (actual time=2814.176..3065.993 rows=143 loops=1) Hash Cond: (projects_5.id = projects_4.id) -> HashAggregate (cost=112158.48..114653.52 rows=249504 width=4) (actual time=1346.208..1531.099 rows=256318 loops=1) Group Key: projects_5.id -> Append (cost=0.86..111534.72 rows=249504 width=4) (actual time=0.113..1130.421 rows=256441 loops=1) -> Nested Loop (cost=0.86..639.18 rows=66 width=4) (actual time=0.112..1.825 rows=143 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.43..81.15 rows=66 width=4) (actual time=0.069..0.200 rows=143 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 89 -> Index Scan using projects_pkey on projects projects_5 (cost=0.43..8.45 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=143) Index Cond: (id = project_authorizations_2.project_id) Filter: (NOT pending_delete) -> Seq Scan on projects projects_6 (cost=0.00..108400.50 rows=249438 width=4) (actual time=0.042..994.030 rows=256298 loops=1) Filter: (visibility_level = ANY ('{20,10}'::integer[])) Rows Removed by Filter: 1688408 -> Hash (cost=118724.28..118724.28 rows=33 width=12) (actual time=1457.849..1457.849 rows=143 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 15kB -> Hash Join (cost=112798.48..118724.28 rows=33 width=12) (actual time=1184.690..1457.597 rows=143 loops=1) Hash Cond: (projects_7.id = projects_4.id) -> HashAggregate (cost=112158.48..114653.52 rows=249504 width=4) (actual time=1177.851..1376.098 rows=256318 loops=1) Group Key: projects_7.id -> Append (cost=0.86..111534.72 rows=249504 width=4) (actual time=0.022..968.499 rows=256441 loops=1) -> Nested Loop (cost=0.86..639.18 rows=66 width=4) (actual time=0.020..1.158 rows=143 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..81.15 rows=66 width=4) (actual time=0.009..0.091 rows=143 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 89 -> Index Scan using projects_pkey on projects projects_7 (cost=0.43..8.45 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=143) Index Cond: (id = project_authorizations_3.project_id) Filter: (NOT pending_delete) -> Seq Scan on projects projects_8 (cost=0.00..108400.50 rows=249438 width=4) (actual time=0.025..845.266 rows=256298 loops=1) Filter: (visibility_level = ANY ('{20,10}'::integer[])) Rows Removed by Filter: 1688408 -> Hash (cost=639.18..639.18 rows=66 width=8) (actual time=1.434..1.434 rows=143 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB -> Nested Loop (cost=0.86..639.18 rows=66 width=8) (actual time=0.047..1.373 rows=143 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..81.15 rows=66 width=4) (actual time=0.023..0.142 rows=143 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 89 -> Index Scan using projects_pkey on projects projects_4 (cost=0.43..8.45 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=143) Index Cond: (id = project_authorizations_1.project_id) Filter: (NOT pending_delete) -> Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.43..0.53 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=143) Index Cond: (project_id = projects_4.id) Filter: (issues_access_level = 10) Rows Removed by Filter: 1 -> Sort (cost=119568.98..119569.03 rows=20 width=1401) (actual time=1534.343..1534.345 rows=3 loops=1) Sort Key: issues.project_id Sort Method: quicksort Memory: 30kB -> Hash Join (cost=113642.78..119568.55 rows=20 width=1401) (actual time=1365.013..1534.283 rows=3 loops=1) Hash Cond: (projects_9.id = projects.id) -> HashAggregate (cost=112158.48..114653.52 rows=249504 width=4) (actual time=1266.792..1451.560 rows=256318 loops=1) Group Key: projects_9.id -> Append (cost=0.86..111534.72 rows=249504 width=4) (actual time=0.042..1063.712 rows=256441 loops=1) -> Nested Loop (cost=0.86..639.18 rows=66 width=4) (actual time=0.040..1.701 rows=143 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.43..81.15 rows=66 width=4) (actual time=0.025..0.344 rows=143 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 89 -> Index Scan using projects_pkey on projects projects_9 (cost=0.43..8.45 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=143) Index Cond: (id = project_authorizations_4.project_id) Filter: (NOT pending_delete) -> Seq Scan on projects projects_10 (cost=0.00..108400.50 rows=249438 width=4) (actual time=0.024..931.276 rows=256298 loops=1) Filter: (visibility_level = ANY ('{20,10}'::integer[])) Rows Removed by Filter: 1688408 -> Hash (cost=1483.81..1483.81 rows=39 width=1397) (actual time=0.460..0.460 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Nested Loop (cost=636.89..1483.81 rows=39 width=1397) (actual time=0.305..0.447 rows=3 loops=1) -> Bitmap Heap Scan on issues (cost=636.46..1154.07 rows=39 width=1393) (actual time=0.270..0.382 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 1))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.109..0.109 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 1 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_11 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_5.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects (cost=0.43..8.45 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=3) Index Cond: (id = issues.project_id) Filter: ((NOT pending_delete) AND (NOT pending_delete)) Planning time: 12.298 ms Execution time: 11998.009 ms (124 rows)
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = 'f' WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743 AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND "projects"."id" IN (SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND ("project_features"."issues_access_level" = 20 OR "project_features"."issues_access_level" IS NULL) UNION SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND "project_authorizations"."user_id" = 508743 AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743) UNION SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."id" IN (SELECT "issues"."project_id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ( issues.confidential IS NULL OR issues.confidential IS FALSE OR (issues.confidential = TRUE AND (issues.author_id = 508743 OR issues.assignee_id = 508743 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 508743 AND (project_authorizations.access_level >= 20) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "issues"."assignee_id" = 508743))) AND "project_features"."issues_access_level" = 10) ORDER BY "issues"."id" DESC
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=11709.89..11709.90 rows=1 width=1393) (actual time=103.428..103.439 rows=3 loops=1) Sort Key: issues.id DESC Sort Method: quicksort Memory: 30kB -> Nested Loop (cost=11700.96..11709.88 rows=1 width=1393) (actual time=36.938..103.396 rows=3 loops=1) -> Merge Semi Join (cost=11069.49..11069.56 rows=1 width=12) (actual time=0.785..0.796 rows=2 loops=1) Merge Cond: (projects.id = projects_3.id) -> Sort (cost=2780.96..2780.97 rows=6 width=8) (actual time=0.265..0.267 rows=2 loops=1) Sort Key: projects.id Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=2730.46..2780.88 rows=6 width=8) (actual time=0.250..0.255 rows=2 loops=1) -> HashAggregate (cost=2730.03..2730.09 rows=6 width=4) (actual time=0.246..0.247 rows=2 loops=1) Group Key: projects_1.id -> Append (cost=1156.44..2730.01 rows=6 width=4) (actual time=0.134..0.242 rows=4 loops=1) -> Nested Loop (cost=1156.44..1245.56 rows=1 width=4) (actual time=0.133..0.193 rows=2 loops=1) -> Merge Join (cost=1156.02..1237.10 rows=1 width=8) (actual time=0.122..0.171 rows=2 loops=1) Merge Cond: (project_authorizations.project_id = issues_1.project_id) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..81.15 rows=66 width=4) (actual time=0.019..0.052 rows=43 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 26 -> Sort (cost=1155.58..1155.68 rows=39 width=4) (actual time=0.098..0.098 rows=2 loops=1) Sort Key: issues_1.project_id Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.088..0.089 rows=2 loops=1) Group Key: issues_1.project_id -> Bitmap Heap Scan on issues issues_1 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.043..0.080 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 3))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.023..0.023 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 3 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_7 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_13 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_7.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=2) Index Cond: (id = project_authorizations.project_id) Filter: (NOT pending_delete) -> Nested Loop (cost=1154.59..1484.40 rows=5 width=4) (actual time=0.040..0.046 rows=2 loops=1) -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.035..0.036 rows=2 loops=1) Group Key: issues_2.project_id -> Bitmap Heap Scan on issues issues_2 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.018..0.029 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 2))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.010..0.010 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 2 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_6 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_12 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_6.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=2) Index Cond: (id = issues_2.project_id) Filter: (visibility_level = ANY ('{20,10}'::integer[])) -> Index Scan using projects_pkey on projects (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=2) Index Cond: (id = projects_1.id) Filter: ((NOT pending_delete) AND (NOT pending_delete)) -> Sort (cost=8288.53..8288.55 rows=7 width=4) (actual time=0.517..0.518 rows=2 loops=1) Sort Key: projects_3.id Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=8288.29..8288.36 rows=7 width=4) (actual time=0.506..0.506 rows=2 loops=1) Group Key: projects_3.id -> Append (cost=2730.89..8288.27 rows=7 width=4) (actual time=0.168..0.502 rows=2 loops=1) -> Nested Loop Left Join (cost=2730.89..2784.10 rows=6 width=4) (actual time=0.168..0.185 rows=2 loops=1) Filter: ((project_features.issues_access_level = 20) OR (project_features.issues_access_level IS NULL)) -> Nested Loop (cost=2730.46..2780.88 rows=6 width=4) (actual time=0.158..0.164 rows=2 loops=1) -> HashAggregate (cost=2730.03..2730.09 rows=6 width=4) (actual time=0.154..0.154 rows=2 loops=1) Group Key: projects_4.id -> Append (cost=1156.44..2730.01 rows=6 width=4) (actual time=0.065..0.149 rows=4 loops=1) -> Nested Loop (cost=1156.44..1245.56 rows=1 width=4) (actual time=0.064..0.104 rows=2 loops=1) -> Merge Join (cost=1156.02..1237.10 rows=1 width=8) (actual time=0.059..0.093 rows=2 loops=1) Merge Cond: (project_authorizations_1.project_id = issues_3.project_id) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..81.15 rows=66 width=4) (actual time=0.004..0.029 rows=43 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 26 -> Sort (cost=1155.58..1155.68 rows=39 width=4) (actual time=0.042..0.042 rows=2 loops=1) Sort Key: issues_3.project_id Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.032..0.033 rows=2 loops=1) Group Key: issues_3.project_id -> Bitmap Heap Scan on issues issues_3 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.015..0.027 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 9))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.009..0.009 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 9 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_13 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_19 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_13.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_4 (cost=0.43..8.45 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2) Index Cond: (id = project_authorizations_1.project_id) Filter: (NOT pending_delete) -> Nested Loop (cost=1154.59..1484.40 rows=5 width=4) (actual time=0.038..0.043 rows=2 loops=1) -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.033..0.034 rows=2 loops=1) Group Key: issues_4.project_id -> Bitmap Heap Scan on issues issues_4 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.016..0.028 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 8))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.010..0.010 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 8 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_12 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_18 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_12.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_5 (cost=0.43..8.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2) Index Cond: (id = issues_4.project_id) Filter: (visibility_level = ANY ('{20,10}'::integer[])) -> Index Scan using projects_pkey on projects projects_3 (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2) Index Cond: (id = projects_4.id) Filter: (NOT pending_delete) -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.52 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=2) Index Cond: (projects_3.id = project_id) -> Hash Join (cost=5503.96..5504.11 rows=1 width=4) (actual time=0.317..0.317 rows=0 loops=1) Hash Cond: (projects_7.id = projects_6.id) -> HashAggregate (cost=2730.03..2730.09 rows=6 width=4) (actual time=0.142..0.142 rows=1 loops=1) Group Key: projects_7.id -> Append (cost=1156.44..2730.01 rows=6 width=4) (actual time=0.056..0.140 rows=4 loops=1) -> Nested Loop (cost=1156.44..1245.56 rows=1 width=4) (actual time=0.054..0.090 rows=2 loops=1) -> Merge Join (cost=1156.02..1237.10 rows=1 width=8) (actual time=0.050..0.081 rows=2 loops=1) Merge Cond: (project_authorizations_3.project_id = issues_5.project_id) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..81.15 rows=66 width=4) (actual time=0.004..0.028 rows=43 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 26 -> Sort (cost=1155.58..1155.68 rows=39 width=4) (actual time=0.043..0.043 rows=2 loops=1) Sort Key: issues_5.project_id Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.030..0.030 rows=2 loops=1) Group Key: issues_5.project_id -> Bitmap Heap Scan on issues issues_5 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.017..0.029 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 7))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.010..0.010 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 7 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_11 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_17 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_11.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_7 (cost=0.43..8.45 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=2) Index Cond: (id = project_authorizations_3.project_id) Filter: (NOT pending_delete) -> Nested Loop (cost=1154.59..1484.40 rows=5 width=4) (actual time=0.040..0.045 rows=2 loops=1) -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.036..0.038 rows=2 loops=1) Group Key: issues_6.project_id -> Bitmap Heap Scan on issues issues_6 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.020..0.031 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 6))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.014..0.014 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 6 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_10 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_16 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_10.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_8 (cost=0.43..8.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2) Index Cond: (id = issues_6.project_id) Filter: (visibility_level = ANY ('{20,10}'::integer[])) -> Hash (cost=2773.91..2773.91 rows=1 width=16) (actual time=0.164..0.164 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Nested Loop (cost=2731.32..2773.91 rows=1 width=16) (actual time=0.163..0.163 rows=0 loops=1) -> Nested Loop (cost=2730.89..2773.38 rows=1 width=12) (actual time=0.143..0.154 rows=2 loops=1) -> Nested Loop (cost=2730.46..2764.92 rows=1 width=8) (actual time=0.139..0.146 rows=2 loops=1) -> HashAggregate (cost=2730.03..2730.09 rows=6 width=4) (actual time=0.132..0.133 rows=2 loops=1) Group Key: projects_9.id -> Append (cost=1156.44..2730.01 rows=6 width=4) (actual time=0.048..0.129 rows=4 loops=1) -> Nested Loop (cost=1156.44..1245.56 rows=1 width=4) (actual time=0.047..0.086 rows=2 loops=1) -> Merge Join (cost=1156.02..1237.10 rows=1 width=8) (actual time=0.043..0.077 rows=2 loops=1) Merge Cond: (project_authorizations_4.project_id = issues_7.project_id) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.43..81.15 rows=66 width=4) (actual time=0.003..0.022 rows=43 loops=1) Index Cond: (user_id = 508743) Heap Fetches: 26 -> Sort (cost=1155.58..1155.68 rows=39 width=4) (actual time=0.037..0.038 rows=2 loops=1) Sort Key: issues_7.project_id Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.027..0.028 rows=2 loops=1) Group Key: issues_7.project_id -> Bitmap Heap Scan on issues issues_7 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.013..0.023 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 5))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.008..0.008 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 5 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_9 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_15 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_9.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_9 (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2) Index Cond: (id = project_authorizations_4.project_id) Filter: (NOT pending_delete) -> Nested Loop (cost=1154.59..1484.40 rows=5 width=4) (actual time=0.035..0.040 rows=2 loops=1) -> HashAggregate (cost=1154.16..1154.55 rows=39 width=4) (actual time=0.031..0.032 rows=2 loops=1) Group Key: issues_8.project_id -> Bitmap Heap Scan on issues issues_8 (cost=636.46..1154.07 rows=39 width=4) (actual time=0.015..0.027 rows=3 loops=1) Recheck Cond: (assignee_id = 508743) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 4))))) Rows Removed by Filter: 14 Heap Blocks: exact=17 -> Bitmap Index Scan on index_issues_on_assignee_id (cost=0.00..5.41 rows=130 width=0) (actual time=0.007..0.007 rows=17 loops=1) Index Cond: (assignee_id = 508743) SubPlan 4 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_8 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_14 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_8.project_id) Filter: (NOT pending_delete) -> Index Scan using projects_pkey on projects projects_10 (cost=0.43..8.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2) Index Cond: (id = issues_8.project_id) Filter: (visibility_level = ANY ('{20,10}'::integer[])) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.43..5.79 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2) Index Cond: ((user_id = 508743) AND (project_id = projects_9.id)) Heap Fetches: 1 -> Index Scan using projects_pkey on projects projects_6 (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2) Index Cond: (id = project_authorizations_2.project_id) Filter: (NOT pending_delete) -> Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.43..0.53 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=2) Index Cond: (project_id = projects_6.id) Filter: (issues_access_level = 10) Rows Removed by Filter: 1 -> Index Scan using index_issues_on_project_id_and_iid on issues (cost=631.47..640.31 rows=1 width=1393) (actual time=19.703..51.292 rows=2 loops=2) Index Cond: (project_id = projects.id) Filter: ((deleted_at IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND (assignee_id = 508743) AND ((confidential IS NULL) OR (confidential IS FALSE) OR (confidential AND ((author_id = 508743) OR (assignee_id = 508743) OR (hashed SubPlan 1))))) Rows Removed by Filter: 11432 SubPlan 1 -> Nested Loop (cost=0.86..630.88 rows=65 width=4) (never executed) -> Index Only Scan Backward using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.43..81.30 rows=65 width=4) (never executed) Index Cond: ((user_id = 508743) AND (access_level >= 20)) Heap Fetches: 0 -> Index Scan using projects_pkey on projects projects_11 (cost=0.43..8.45 rows=1 width=4) (never executed) Index Cond: (id = project_authorizations_5.project_id) Filter: (NOT pending_delete) Planning time: 11.287 ms Execution time: 104.846 ms (270 rows)
@jarka @smcgivern done
@jarka wow!
@jarka ah, of course. This looks good, in that case - nice work! Can you please take a look at my comments?
@maratkalibek would you mind just verifying that this returns the same rows for both?
changed milestone to %9.0
assigned to @jarka
added 487 commits
-
b4be1d60...df63d9db - 486 commits from branch
gitlab-org:master
- 9e7b659a - Improve issues filtering performance
-
b4be1d60...df63d9db - 486 commits from branch
@smcgivern I tried to address your comments, please review.
assigned to @smcgivern
- Resolved by Jarka Košanová
- Resolved by Jarka Košanová
assigned to @jarka
mentioned in issue #29050 (moved)
assigned to @smcgivern
117 118 elsif group 118 119 GroupProjectsFinder.new(group).execute(current_user) 119 120 else 120 ProjectsFinder.new.execute(current_user) 121 projects_finder.execute(current_user, item_project_ids(items)) 121 122 end 122 123 123 124 @projects = projects.with_feature_available_for_user(klass, current_user).reorder(nil) enabled an automatic merge when the pipeline for 8c456feb succeeds
Thanks @jarka, I'm excited to try this on GitLab.com!
@jarka can you check the failures please? Some seem random, but https://gitlab.com/jarka/gitlab-ce/builds/11658599 looks related.
assigned to @jarka
Similar here: https://gitlab.com/jarka/gitlab-ce/builds/11658590
enabled an automatic merge when the pipeline for d369acb5 succeeds
mentioned in merge request gitlab-com/www-gitlab-com!4780 (merged)
mentioned in issue #46648 (closed)