Support epic board lists negated filters on GraphQL
What does this MR do?
Allow filtering epic on epic boards lists by negated params. Parameters are in the same format
of negated issue board params: filters: { not: { authorUsername: "Joe", .....
Sample query
query epicBoardListEpics {
group(fullPath: "Flightjs"){
epicBoard(id: "gid://gitlab/Boards::EpicBoard/1") {
lists {
nodes {
epics(filters: { not: { authorUsername: "Joe", labelName: ["Staging", "Production"], myReactionEmoji: "thumbsup" } }) {
nodes {
id
}
}
}
}
}
}
}Query to fetch epics without given emoji name
SELECT "epics".*
FROM "epics"
LEFT OUTER JOIN "boards_epic_board_positions"
ON "boards_epic_board_positions"."epic_id" = "epics"."id"
WHERE "epics"."group_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 "epics"."state_id" = 1
AND NOT (
EXISTS
(
SELECT true
FROM "award_emoji"
WHERE "award_emoji"."user_id" = 426128
AND "award_emoji"."awardable_type" = 'Epic'
AND "award_emoji"."awardable_id" = "epics"."id"
AND "award_emoji"."name" = 'thumbsup'))
AND (
"boards_epic_board_positions"."epic_board_id" = 4
OR "boards_epic_board_positions"."epic_board_id" IS NULL)
ORDER BY boards_epic_board_positions.relative_position ASC nulls last,
epics.id DESC limit 100Query plan(production explain analyze)
Limit (cost=4114.97..4115.00 rows=13 width=1204) (actual time=117.353..117.368 rows=100 loops=1)
-> Sort (cost=4114.97..4115.00 rows=13 width=1204) (actual time=117.351..117.358 rows=100 loops=1)
Sort Key: boards_epic_board_positions.relative_position, epics.id DESC
Sort Method: top-N heapsort Memory: 214kB
-> Nested Loop Anti Join (cost=4055.77..4114.73 rows=13 width=1204) (actual time=110.626..114.
592 rows=3875 loops=1)
Join Filter: (award_emoji.awardable_id = epics.id)
-> Hash Right Join (cost=4055.21..4110.95 rows=13 width=1204) (actual time=109.199..111.
724 rows=3875 loops=1)
Hash Cond: (boards_epic_board_positions.epic_id = epics.id)
Filter: ((boards_epic_board_positions.epic_board_id = 4) OR (boards_epic_board_posit
ions.epic_board_id IS NULL))
-> Seq Scan on boards_epic_board_positions (cost=0.00..51.30 rows=1130 width=20) (
actual time=0.005..0.005 rows=0 loops=1)
-> Hash (cost=4039.40..4039.40 rows=1265 width=1200) (actual time=109.171..109.171
rows=3875 loops=1)
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 3562
kB
-> Nested Loop (cost=1435.52..4039.40 rows=1265 width=1200) (actual time=11.
171..102.632 rows=3875 loops=1)
-> HashAggregate (cost=1435.10..1436.81 rows=171 width=4) (actual time
=10.918..11.050 rows=233 loops=1)
Group Key: namespaces.id
-> CTE Scan on base_and_descendants namespaces (cost=1429.54..14
32.96 rows=171 width=4) (actual time=0.095..10.803 rows=233 loops=1)
CTE base_and_descendants
-> Recursive Union (cost=0.43..1429.54 rows=171 width=34
4) (actual time=0.092..10.358 rows=233 loops=1)
-> Index Scan using index_namespaces_on_type_and_id
_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.084..0.085 rows=1
loops=1)
Index Cond: (((type)::text = 'Group'::text) AN
D (id = 9970))
-> Nested Loop (cost=0.56..142.27 rows=17 width=34
4) (actual time=0.085..1.586 rows=39 loops=6)
-> WorkTable Scan on base_and_descendants (c
ost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.007 rows=39 loops=6)
-> Index Scan using index_namespaces_on_paren
t_id_and_id on namespaces namespaces_2 (cost=0.56..14.19 rows=2 width=344) (actual time=0.025..0.040 row
s=1 loops=233)
Index Cond: (parent_id = base_and_descen
dants.id)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using index_epics_on_group_id_and_iid on epics (cost=0.4
2..15.15 rows=7 width=1200) (actual time=0.108..0.388 rows=17 loops=233)
Index Cond: (group_id = namespaces.id)
Filter: (state_id = 1)
Rows Removed by Filter: 9
-> Materialize (cost=0.56..3.58 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=3
875)
-> Index Only Scan using idx_award_emoji_on_user_emoji_name_awardable_type_awardabl
e_id on award_emoji (cost=0.56..3.58 rows=1 width=4) (actual time=1.421..1.421 rows=0 loops=1)
Index Cond: ((user_id = 426128) AND (name = 'thumbsup'::text) AND (awardable_t
ype = 'Epic'::text))
Heap Fetches: 0
Planning Time: 17.626 ms
Execution Time: 118.219 ms
(35 rows)- EXPLAIN ANALYZE output https://explain.depesz.com/s/rqEw. I used
gitlab-orggroup id, should be one of the worst scenarios given its count of subgroups and epics.
related to #322686 (closed)
Edited by Felipe Artur