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)
Edited by Gregory Stark