Slow SQL running in production queryid 3257994922

Postgres seems to want to search backwards across all projects to find the most recent bunch matching these subqueries.

I haven't identified the controller for this yet but it's probably the user's profile activity or something like that.

https://performance.gitlab.net/dashboard/db/postgres-single-query-drill-down?orgId=1&from=now-7d&to=now&var-environment=prd&var-queryid=3257994922&var-fqdn=All

gitlabhq_production=# explain analyze SELECT  "projects".* FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE (namespace_id IN (SELECT "namespaces"."id" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."source_type" = 'Namespace' AND "namespaces"."type" IN ('Group') AND "members"."user_id" = 632546 AND "namespaces"."type" IN ('Group') AND "members"."access_level" = 50 AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL) OR namespace_id = 752513)  ORDER BY "projects"."created_at" DESC LIMIT 20 OFFSET 0;
                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=23.39..36.84 rows=20 width=624) (actual time=5820.136..5820.136 rows=0 loops=1)
   ->  Nested Loop  (cost=23.39..1316809.96 rows=1958622 width=624) (actual time=5820.135..5820.135 rows=0 loops=1)
         ->  Index Scan Backward using index_projects_on_created_at on projects  (cost=22.96..383885.39 rows=1958622 width=624) (actual time=5820.134..5820.134 rows=0 loops=1)
               Filter: ((hashed SubPlan 1) OR (namespace_id = 752513))
               Rows Removed by Filter: 3992860
               SubPlan 1
                 ->  Nested Loop  (cost=0.86..22.53 rows=1 width=4) (actual time=0.122..0.122 rows=0 loops=1)
                       ->  Index Scan using index_members_on_user_id on members  (cost=0.43..19.07 rows=1 width=4) (actual time=0.122..0.122 rows=0 loops=1)
                             Index Cond: (user_id = 632546)
                             Filter: ((requested_at IS NULL) AND ((source_type)::text = 'Namespace'::text) AND (access_level = 50) AND ((type)::text = 'GroupMember'::text))
                             Rows Removed by Filter: 1
                       ->  Index Scan using namespaces_pkey on namespaces namespaces_1  (cost=0.43..3.45 rows=1 width=4) (never executed)
                             Index Cond: (id = members.source_id)
                             Filter: ((type)::text = 'Group'::text)
         ->  Index Only Scan using namespaces_pkey on namespaces  (cost=0.43..0.47 rows=1 width=4) (never executed)
               Index Cond: (id = projects.namespace_id)
               Heap Fetches: 0
 Planning time: 1.343 ms
 Execution time: 5820.235 ms
(19 rows)