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.