Expose epic weights sum
What does this MR do?
Expose epic weight sum via GraphQL. This includes all issues, including issues not visible to the user (in private subgroups, or confidential).
The lack of filter means that all users can see the same numbers, which is essential for the use case - in this case, planning.
It should be noted that AppSec has approved this.
Screenshots
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
Database query analysis
Database queries
SQL query for weight summation
Target epic: &1366
API call for reference: https://gitlab.com/api/v4/groups/9970/epics/1366
Ruby code:
Epics::DescendantWeightService.new(epic, current_user).send(:issues_weight_total)EXPLAIN
SELECT
sum(issues.weight) AS sum_weight,
issues.state_id AS issues_state_id
FROM
issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (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 (project_features.issues_access_level > 0
OR project_features.issues_access_level IS NULL)
AND issues.id IN (
SELECT
epic_issues.issue_id
FROM
epic_issues
WHERE
epic_issues.epic_id IN ( WITH RECURSIVE base_and_descendants AS ((
SELECT
epics.*
FROM
epics
WHERE
epics.id = 7651)
UNION (
SELECT
epics.*
FROM
epics,
base_and_descendants
WHERE
epics.parent_id = base_and_descendants.id))
SELECT
epics.id
FROM
base_and_descendants AS epics
WHERE
epics.group_id = 9970))
AND (weight IS NOT NULL
AND weight > 0)
GROUP BY
issues.state_idQuery plan:
- Without execution: https://explain.depesz.com/s/bRgD
content
GroupAggregate (cost=879.43..879.45 rows=1 width=10)
Group Key: issues.state_id
-> Sort (cost=879.43..879.43 rows=1 width=6)
Sort Key: issues.state_id
-> Nested Loop Left Join (cost=846.47..879.42 rows=1 width=6)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
-> Nested Loop (cost=846.04..878.92 rows=1 width=10)
-> Nested Loop (cost=845.60..870.78 rows=1 width=10)
-> HashAggregate (cost=845.04..845.10 rows=6 width=4)
Group Key: epic_issues.issue_id
-> Nested Loop (cost=837.27..845.03 rows=6 width=4)
-> HashAggregate (cost=836.98..836.99 rows=1 width=4)
Group Key: epics.id
-> CTE Scan on base_and_descendants epics (cost=830.87..836.97 rows=1 width=4)
Filter: (group_id = 9970)
CTE base_and_descendants
-> Recursive Union (cost=0.29..830.87 rows=271 width=776)
-> Index Scan using epics_pkey on epics epics_1 (cost=0.29..4.30 rows=1 width=776)
Index Cond: (id = 7651)
-> Nested Loop (cost=0.29..82.11 rows=27 width=776)
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4)
-> Index Scan using index_epics_on_parent_id on epics epics_2 (cost=0.29..8.16 rows=3 width=776)
Index Cond: (parent_id = base_and_descendants.id)
-> Index Scan using index_epic_issues_on_epic_id on epic_issues (cost=0.29..7.98 rows=6 width=8)
Index Cond: (epic_id = epics.id)
-> Index Scan using issues_pkey on issues (cost=0.56..4.27 rows=1 width=14)
Index Cond: (id = epic_issues.issue_id)
Filter: ((weight IS NOT NULL) AND (weight > 0))
-> Index Scan using projects_pkey on projects (cost=0.43..8.13 rows=1 width=4)
Index Cond: (id = issues.project_id)
Filter: ((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.56..4.58 rows=1 width=0)
Index Cond: ((user_id = 1) AND (project_id = projects.id))
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..7.17 rows=149 width=4)
Index Cond: (user_id = 1)
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.48 rows=1 width=8)
Index Cond: (projects.id = project_id)- With execution: https://explain.depesz.com/s/h96m
content
Aggregate (cost=879.43..879.45 rows=1 width=10) (actual time=1.065..1.069 rows=2 loops=1)
Group Key: issues.state_id
Buffers: shared hit=525
-> Sort (cost=879.43..879.43 rows=1 width=6) (actual time=1.060..1.061 rows=24 loops=1)
Sort Key: issues.state_id
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=525
-> Nested Loop Left Join (cost=846.47..879.42 rows=1 width=6) (actual time=0.441..1.019 rows=24 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Rows Removed by Filter: 0
Buffers: shared hit=522
-> Nested Loop (cost=846.04..878.92 rows=1 width=10) (actual time=0.424..0.934 rows=24 loops=1)
Buffers: shared hit=426
-> Nested Loop (cost=845.60..870.78 rows=1 width=10) (actual time=0.368..0.742 rows=24 loops=1)
Buffers: shared hit=233
-> HashAggregate (cost=845.04..845.10 rows=6 width=4) (actual time=0.351..0.356 rows=31 loops=1)
Group Key: epic_issues.issue_id
Buffers: shared hit=77
-> Nested Loop (cost=837.27..845.03 rows=6 width=4) (actual time=0.258..0.340 rows=31 loops=1)
Buffers: shared hit=77
-> HashAggregate (cost=836.98..836.99 rows=1 width=4) (actual time=0.242..0.243 rows=4 loops=1)
Group Key: epics.id
Buffers: shared hit=42
-> CTE Scan on base_and_descendants epics (cost=830.87..836.97 rows=1 width=4) (actual time=0.109..0.238 rows=4 loops=1)
Filter: (epics.group_id = 9970)
Rows Removed by Filter: 0
Buffers: shared hit=42
CTE base_and_descendants
-> Recursive Union (cost=0.29..830.87 rows=271 width=776) (actual time=0.102..0.223 rows=4 loops=1)
Buffers: shared hit=42
-> Index Scan using epics_pkey on public.epics epics_1 (cost=0.29..4.30 rows=1 width=776) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (epics_1.id = 7651)
Buffers: shared hit=3
-> Nested Loop (cost=0.29..82.11 rows=27 width=776) (actual time=0.015..0.021 rows=2 loops=2)
Buffers: shared hit=11
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=2 loops=2)
-> Index Scan using index_epics_on_parent_id on public.epics epics_2 (cost=0.29..8.16 rows=3 width=776) (actual time=0.006..0.009 rows=1 loops=4)
Index Cond: (epics_2.parent_id = base_and_descendants.id)
Buffers: shared hit=11
-> Index Scan using index_epic_issues_on_epic_id on public.epic_issues (cost=0.29..7.98 rows=6 width=8) (actual time=0.008..0.023 rows=8 loops=4)
Index Cond: (epic_issues.epic_id = epics.id)
Buffers: shared hit=35
-> Index Scan using issues_pkey on public.issues (cost=0.56..4.27 rows=1 width=14) (actual time=0.012..0.012 rows=1 loops=31)
Index Cond: (issues.id = epic_issues.issue_id)
Filter: ((issues.weight IS NOT NULL) AND (issues.weight > 0))
Rows Removed by Filter: 0
Buffers: shared hit=156
-> Index Scan using projects_pkey on public.projects (cost=0.43..8.13 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=24)
Index Cond: (projects.id = issues.project_id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=193
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.56..4.58 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=24)
Index Cond: ((project_authorizations.user_id = 1) AND (project_authorizations.project_id = projects.id))
Heap Fetches: 0
Buffers: shared hit=97
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_1 (cost=0.56..7.17 rows=149 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (project_authorizations_1.user_id = 1)
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.48 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=24)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=96Summary:
Time: 8.766 ms
- planning: 7.352 ms
- execution: 1.414 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 525 (~4.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0FE changes: !18957 (merged)
Related to #5164 (closed)
Edited by 🤖 GitLab Bot 🤖