Slow query in production Queryid 2686325980
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" = 1263493 AND "namespaces"."type" IN ('Group') AND "members"."access_level" = 50 AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL) OR namespace_id = 1515046) ORDER BY "projects"."created_at" DESC LIMIT 20 OFFSET 0; QUERY PLAN
Limit (cost=24.50..37.88 rows=20 width=770) (actual time=529.296..6400.333 rows=11 loops=1) -> Nested Loop (cost=24.50..1342473.52 rows=2007074 width=770) (actual time=529.295..6400.324 rows=11 loops=1) -> Index Scan Backward using index_projects_on_created_at on projects (cost=24.07..385601.67 rows=2007074 width=770) (actual time=529.228..6400.078 rows=11 loops=1) Filter: ((hashed SubPlan 1) OR (namespace_id = 1515046)) Rows Removed by Filter: 4102974 SubPlan 1 -> Nested Loop (cost=0.86..23.64 rows=1 width=4) (actual time=0.301..0.301 rows=0 loops=1) -> Index Scan using index_members_on_user_id on members (cost=0.43..20.18 rows=1 width=4) (actual time=0.299..0.299 rows=0 loops=1) Index Cond: (user_id = 1263493) Filter: ((requested_at IS NULL) AND ((source_type)::text = 'Namespace'::text) AND (access_level = 50) AND ((type)::text = 'GroupMember'::text)) Rows Removed by Filter: 11 -> 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) (actual time=0.013..0.013 rows=1 loops=11) Index Cond: (id = projects.namespace_id) Heap Fetches: 0 Planning time: 1.191 ms Execution time: 6400.458 ms (19 rows)