Statement timeout on /explore/dependencies
Summary
The /explore/dependencies
page is periodically timing out for some users.
SELECT "sbom_occurrences".* FROM "sbom_occurrences"
INNER JOIN "projects" ON "sbom_occurrences"."project_id" = "projects"."id"
INNER JOIN "projects" "projects_sbom_occurrences" ON "projects_sbom_occurrences"."id" = "sbom_occurrences"."project_id"
INNER JOIN "project_authorizations" ON "project_authorizations"."project_id" = "projects_sbom_occurrences"."id"
WHERE "projects"."organization_id" = $1
AND "projects"."archived" = $2
AND "project_authorizations"."user_id" = $3
ORDER BY "sbom_occurrences"."id" ASC
LIMIT $4
Steps to reproduce
Visit https://gitlab.com/explore/dependencies
as a user that is not a member of the gitlab-org
.
Example Project
What is the current bug behavior?
When viewing the https://gitlab.com/explore/dependencies
page it will periodically timeout with an error.
What is the expected correct behavior?
The page does not time out.
Relevant logs and/or screenshots
- Logs
- Query Execution Plan
- Query Execution Plan without Archive
- Query Execution Plan without
order by id asc
- Query Execution Plan with
order by project_id asc
# explain analyze SELECT "sbom_occurrences".*
FROM "sbom_occurrences"
INNER JOIN "projects" ON "sbom_occurrences"."project_id" = "projects"."id"
INNER JOIN "projects" "projects_sbom_occurrences" ON "projects_sbom_occurrences"."id" = "sbom_occurrences"."project_id"
INNER JOIN "project_authorizations" ON "project_authorizations"."project_id" = "projects_sbom_occurrences"."id"
WHERE "projects"."organization_id" = 1
AND "projects"."archived" = FALSE
AND "project_authorizations"."user_id" = 971158
ORDER BY "sbom_occurrences"."id" ASC
LIMIT 20;
QUERY PLAN >
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Limit (cost=1002.30..57455.14 rows=20 width=343) (actual time=234378.266..283303.272 rows=20 loops=1)
-> Nested Loop (cost=1002.30..14540430.84 rows=5151 width=343) (actual time=234378.263..283303.266 rows=20 loops=1)
-> Nested Loop (cost=1001.74..14536861.77 rows=5317 width=351) (actual time=234377.726..283302.669 rows=20 loops=1)
Join Filter: (sbom_occurrences.project_id = projects_sbom_occurrences.id)
-> Gather Merge (cost=1001.16..14516841.95 rows=298915 width=347) (actual time=234375.188..283300.109 rows=20 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=1.14..14481339.72 rows=124548 width=347) (actual time=193085.223..230029.893 rows=10 loops=3)
-> Parallel Index Scan using sbom_occurrences_pkey on sbom_occurrences (cost=0.56..2940292.19 rows=13606780 width=343) (actual time=1.489..185197.242 rows=10921503 loops=3)
-> Index Only Scan using index_project_authorizations_on_project_user_access_level on project_authorizations (cost=0.57..0.84 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=32>
Index Cond: ((project_id = sbom_occurrences.project_id) AND (user_id = 971158))
Heap Fetches: 0
-> Memoize (cost=0.57..2.08 rows=1 width=4) (actual time=0.126..0.127 rows=1 loops=20)
Cache Key: project_authorizations.project_id
Cache Mode: logical
Hits: 19 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using projects_pkey on projects projects_sbom_occurrences (cost=0.56..2.07 rows=1 width=4) (actual time=2.511..2.511 rows=1 loops=1)
Index Cond: (id = project_authorizations.project_id)
Heap Fetches: 0
-> Index Scan using projects_pkey on projects (cost=0.56..0.67 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=20)
Index Cond: (id = sbom_occurrences.project_id)
Filter: ((NOT archived) AND (organization_id = 1))
Planning Time: 274.442 ms
Execution Time: 283303.943 ms
(24 rows)
Possible fixes
Tuning the query generated here.
Edited by mo khan