Allow to order by blocking issues count
What does this MR do?
Allows to order issues by the number of other issues they are blocking.
Query for issues list on projects
Keep in mind that this ordering will only be added when selected in the dropdown.
I executed it on production database using GitLab project id.
- Duration before warmup: 412ms
- Duration after warmup: 124ms
EXPLAIN ANALYZE SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 278964
AND (
"issues"."state_id" IN (1))
ORDER BY blocking_issues_count DESC nulls last,
"issues"."id" DESC limit 20 offset 0
Query plan
QUERY PLAN
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
----------------------------------------
Limit (cost=40642.92..40642.97 rows=20 width=1252) (actual time=124.718..124.721 rows=20 loops=1)
-> Sort (cost=40642.92..40734.87 rows=36781 width=1252) (actual time=124.717..124.718 rows=20 loops=
1)
Sort Key: blocking_issues_count DESC NULLS LAST, id DESC
Sort Method: top-N heapsort Memory: 93kB
-> Index Scan using index_issue_on_project_id_state_id_and_blocking_issues_count on issues (co
st=0.56..39664.19 rows=36781 width=1252) (actual time=0.034..92.959 rows=32865 loops=1)
Index Cond: ((project_id = 278964) AND (state_id = 1))
Planning Time: 0.340 ms
Execution Time: 124.758 ms
(8 rows)
Query for issues list on groups
Executed with gitlab-org group id:
- Duration before warmup: 595ms
- Duration after warmup: 213ms
EXPLAIN ANALYZE 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 "projects"."namespace_id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970) UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
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 (
"project_features"."issues_access_level" > 0
OR "project_features"."issues_access_level" IS NULL)
AND (
"issues"."state_id" IN (1))
AND "projects"."archived" = false
AND "projects"."archived" = false
ORDER BY blocking_issues_count DESC nulls last,
"issues"."id" DESC limit 20 offset 0
Query plan
QUERY PLAN
Limit (cost=46729.49..46729.54 rows=20 width=1252) (actual time=213.078..213.081 rows=20 loops=1)
-> Sort (cost=46729.49..46745.68 rows=6475 width=1252) (actual time=213.077..213.078 rows=20 loops=1
)
Sort Key: issues.blocking_issues_count DESC NULLS LAST, issues.id DESC
Sort Method: top-N heapsort Memory: 72kB
-> Nested Loop (cost=1432.74..46557.20 rows=6475 width=1252) (actual time=1.899..168.133 rows=
47834 loops=1)
-> Nested Loop Left Join (cost=1432.18..16675.67 rows=2402 width=4) (actual time=1.872..
16.699 rows=894 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_acce
ss_level IS NULL))
Rows Removed by Filter: 93
-> Nested Loop (cost=1431.74..15478.17 rows=2447 width=4) (actual time=1.854..9.70
5 rows=987 loops=1)
-> HashAggregate (cost=1431.31..1432.82 rows=151 width=4) (actual time=1.801
..1.868 rows=154 loops=1)
Group Key: namespaces.id
-> CTE Scan on base_and_descendants namespaces (cost=1426.40..1429.42
rows=151 width=4) (actual time=0.027..1.748 rows=154 loops=1)
CTE base_and_descendants
-> Recursive Union (cost=0.43..1426.40 rows=151 width=345) (ac
tual time=0.025..1.543 rows=154 loops=1)
-> Index Scan using namespaces_pkey on namespaces namespa
ces_1 (cost=0.43..3.45 rows=1 width=345) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: (id = 9970)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=0.56..141.99 rows=15 width=345) (ac
tual time=0.023..0.241 rows=31 loops=5)
-> WorkTable Scan on base_and_descendants (cost=0.
00..0.20 rows=10 width=4) (actual time=0.000..0.002 rows=31 loops=5)
-> Index Scan using index_namespaces_on_parent_id_a
nd_id on namespaces namespaces_2 (cost=0.56..14.16 rows=2 width=345) (actual time=0.005..0.007 rows=1 lo
ops=154)
Index Cond: (parent_id = base_and_descendants.
id)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=
0.43..92.86 rows=16 width=8) (actual time=0.012..0.050 rows=6 loops=154)
Index Cond: (namespace_id = namespaces.id)
Filter: ((NOT archived) AND (NOT archived) AND ((alternatives: SubPlan 1
or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 0
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_proj
ect_id_access_level on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004
rows=1 loops=987)
Index Cond: ((user_id = 1) AND (project_id = projects.id))
Heap Fetches: 108
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_proj
ect_id_access_level on project_authorizations project_authorizations_1 (cost=0.57..237.64 rows=1628 widt
h=4) (never executed)
Index Cond: (user_id = 1)
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on project_features (cost
=0.43..0.48 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=987)
Index Cond: (projects.id = project_id)
-> Index Scan using index_issue_on_project_id_state_id_and_blocking_issues_count on issue
s (cost=0.56..8.63 rows=381 width=1252) (actual time=0.008..0.159 rows=54 loops=894)
Index Cond: ((project_id = projects.id) AND (state_id = 1))
Planning Time: 2.320 ms
Execution Time: 213.329 ms
(40 rows)
Backend portion of #34247 (closed)