Investigate long running query - SPAM (!!!)
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = 'f' WHERE "issues"."deleted_at" IS NULL AND (issues.confidential = false OR (issues.confidential = true AND (issues.author_id = XXXXXX OR issues.assignee_id = XXXXXX OR issues.project_id IN(SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "projects"."pending_delete" = 'f' AND "namespaces"."owner_id" = XXXXXX AND "namespaces"."type" IS NULL
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "projects"."pending_delete" = 'f' AND "namespaces"."type" IN ('Group') AND "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "members"."user_id" = XXXXXX
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "projects"."pending_delete" = 'f' AND "members"."type" IN ('ProjectMember') AND "members"."source_type" = 'Project' AND "members"."user_id" = XXXXXX
UNION
SELECT "project_id" FROM "namespaces" INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id" INNER JOIN "projects" ON "projects"."id" = "project_group_links"."project_id" AND "projects"."pending_delete" = 'f' INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "namespaces"."type" IN ('Group') AND "members"."user_id" = XXXXXX)) ORDER BY "projects"."id" DESC)))) AND ("issues"."state" IN ('opened','reopened')) AND "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "projects"."pending_delete" = 'f' AND "namespaces"."owner_id" = XXXXXX AND "namespaces"."type" IS NULL
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "projects"."pending_delete" = 'f' AND "namespaces"."type" IN ('Group') AND "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "members"."user_id" = XXXXXX
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "projects"."pending_delete" = 'f' AND "members"."type" IN ('ProjectMember') AND "members"."source_type" = 'Project' AND "members"."user_id" = XXXXXX
UNION
SELECT "project_id" FROM "namespaces" INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id" INNER JOIN "projects" ON "projects"."id" = "project_group_links"."project_id" AND "projects"."pending_delete" = 'f' INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "namespaces"."type" IN ('Group') AND "members"."user_id" = XXXXXX))
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "issues"."assignee_id" IS NULL AND "projects"."archived" = 'f' ORDER BY "issues"."id" DESC LIMIT 20 OFFSET 0
Limit (cost=58048.69..211326.40 rows=20 width=1154) (actual time=1427.735..4912.981 rows=20 loops=1)
-> Nested Loop Semi Join (cost=58048.69..3831924109.86 rows=499990 width=1154) (actual time=1427.732..4912.936 rows=20 loops=1)
Join Filter: (public.projects.id = public.projects.id)
Rows Removed by Join Filter: 2836511
-> Nested Loop (cost=643.82..1143743.81 rows=999981 width=1158) (actual time=0.049..3.151 rows=23 loops=1)
-> Index Scan Backward using issues_pkey on issues (cost=643.82..433595.98 rows=1014561 width=1154) (actual time=0.031..0.274 rows=23 loops=1)
Filter: ((deleted_at IS NULL) AND (assignee_id IS NULL) AND ((state)::text = ANY ('{opened,reopened}'::text[])) AND ((NOT confidential) OR (confidential AND ((author_id = XXXXXX) OR (assignee_id = XXXXXX) OR (hashed S
ubPlan 1)))))
Rows Removed by Filter: 4
SubPlan 1
-> Sort (cost=643.75..643.79 rows=14 width=4) (never executed)
Sort Key: public.projects.id
-> Nested Loop (cost=523.89..643.48 rows=14 width=4) (never executed)
-> HashAggregate (cost=523.89..524.03 rows=14 width=4) (never executed)
-> Append (cost=0.00..523.85 rows=14 width=4) (never executed)
-> Nested Loop (cost=0.00..189.19 rows=5 width=4) (never executed)
-> Index Scan using index_namespaces_on_owner_id on namespaces (cost=0.00..8.86 rows=3 width=4) (never executed)
Index Cond: (owner_id = XXXXXX)
Filter: (type IS NULL)
-> Index Scan using index_projects_on_namespace_id on projects (cost=0.00..59.97 rows=14 width=8) (never executed)
Index Cond: (namespace_id = public.namespaces.id)
Filter: (NOT pending_delete)
-> Nested Loop (cost=0.00..38.20 rows=1 width=4) (never executed)
-> Nested Loop (cost=0.00..35.30 rows=1 width=8) (never executed)
-> Index Scan using index_members_on_user_id on members (cost=0.00..26.95 rows=1 width=4) (never executed)
Index Cond: (user_id = XXXXXX)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..8.34 rows=1 width=4) (never executed)
Index Cond: (id = public.members.source_id)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using index_projects_on_namespace_id on projects (cost=0.00..2.76 rows=14 width=8) (never executed)
Index Cond: (namespace_id = public.namespaces.id)
Filter: (NOT pending_delete)
-> Nested Loop (cost=0.00..86.61 rows=7 width=4) (never executed)
-> Index Scan using index_members_on_user_id on members (cost=0.00..26.95 rows=7 width=4) (never executed)
Index Cond: (user_id = XXXXXX)
Filter: (((type)::text = 'ProjectMember'::text) AND ((source_type)::text = 'Project'::text))
-> Index Scan using projects_pkey on projects (cost=0.00..8.51 rows=1 width=4) (never executed)
Index Cond: (id = public.members.source_id)
Filter: (NOT pending_delete)
-> Nested Loop (cost=26.96..209.72 rows=1 width=4) (never executed)
-> Nested Loop (cost=26.96..203.11 rows=1 width=4) (never executed)
-> Hash Join (cost=26.96..199.19 rows=1 width=12) (never executed)
Hash Cond: (public.project_group_links.group_id = public.members.source_id)
-> Seq Scan on project_group_links (cost=0.00..143.25 rows=7725 width=8) (never executed)
-> Hash (cost=26.95..26.95 rows=1 width=4) (never executed)
-> Index Scan using index_members_on_user_id on members (cost=0.00..26.95 rows=1 width=4) (never executed)
Index Cond: (user_id = XXXXXX)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..3.91 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.group_id)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using projects_pkey on projects (cost=0.00..6.60 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.project_id)
Filter: (NOT pending_delete)
-> Index Scan using projects_pkey on projects (cost=0.00..8.51 rows=1 width=4) (never executed)
Index Cond: (id = public.projects.id)
Filter: (NOT pending_delete)
-> Index Scan using projects_pkey on projects (cost=0.00..0.69 rows=1 width=4) (actual time=0.045..0.120 rows=1 loops=23)
Index Cond: (id = issues.project_id)
Filter: ((NOT pending_delete) AND (NOT pending_delete) AND (NOT archived))
-> Materialize (cost=57404.87..63789.52 rows=255386 width=4) (actual time=43.804..136.924 rows=123327 loops=23)
-> HashAggregate (cost=57404.87..59958.73 rows=255386 width=4) (actual time=1007.361..1208.665 rows=212678 loops=1)
-> Append (cost=523.89..56766.41 rows=255386 width=4) (actual time=0.201..794.197 rows=212678 loops=1)
-> Nested Loop (cost=523.89..643.48 rows=14 width=4) (actual time=0.201..0.204 rows=1 loops=1)
-> HashAggregate (cost=523.89..524.03 rows=14 width=4) (actual time=0.195..0.195 rows=1 loops=1)
-> Append (cost=0.00..523.85 rows=14 width=4) (actual time=0.151..0.190 rows=1 loops=1)
-> Nested Loop (cost=0.00..189.19 rows=5 width=4) (actual time=0.031..0.031 rows=0 loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces (cost=0.00..8.86 rows=3 width=4) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (owner_id = XXXXXX)
Filter: (type IS NULL)
-> Index Scan using index_projects_on_namespace_id on projects (cost=0.00..59.97 rows=14 width=8) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (namespace_id = public.namespaces.id)
Filter: (NOT pending_delete)
-> Nested Loop (cost=0.00..38.20 rows=1 width=4) (actual time=0.103..0.103 rows=0 loops=1)
-> Nested Loop (cost=0.00..35.30 rows=1 width=8) (actual time=0.101..0.101 rows=0 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.00..26.95 rows=1 width=4) (actual time=0.101..0.101 rows=0 loops=1)
Index Cond: (user_id = XXXXXX)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
Rows Removed by Filter: 1
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..8.34 rows=1 width=4) (never executed)
Index Cond: (id = public.members.source_id)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using index_projects_on_namespace_id on projects (cost=0.00..2.76 rows=14 width=8) (never executed)
Index Cond: (namespace_id = public.namespaces.id)
Filter: (NOT pending_delete)
-> Nested Loop (cost=0.00..86.61 rows=7 width=4) (actual time=0.014..0.017 rows=1 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.00..26.95 rows=7 width=4) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (user_id = XXXXXX)
Filter: (((type)::text = 'ProjectMember'::text) AND ((source_type)::text = 'Project'::text))
-> Index Scan using projects_pkey on projects (cost=0.00..8.51 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (id = public.members.source_id)
Filter: (NOT pending_delete)
-> Nested Loop (cost=26.96..209.72 rows=1 width=4) (actual time=0.035..0.035 rows=0 loops=1)
-> Nested Loop (cost=26.96..203.11 rows=1 width=4) (actual time=0.033..0.033 rows=0 loops=1)
-> Hash Join (cost=26.96..199.19 rows=1 width=12) (actual time=0.032..0.032 rows=0 loops=1)
Hash Cond: (public.project_group_links.group_id = public.members.source_id)
-> Seq Scan on project_group_links (cost=0.00..143.25 rows=7725 width=8) (actual time=0.014..0.014 rows=1 loops=1)
-> Hash (cost=26.95..26.95 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Index Scan using index_members_on_user_id on members (cost=0.00..26.95 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: (user_id = XXXXXX)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
Rows Removed by Filter: 1
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..3.91 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.group_id)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using projects_pkey on projects (cost=0.00..6.60 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.project_id)
Filter: (NOT pending_delete)
-> Index Scan using projects_pkey on projects (cost=0.00..8.51 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = public.projects.id)
Filter: (NOT pending_delete)
-> Seq Scan on projects (cost=0.00..53569.06 rows=255372 width=4) (actual time=0.010..546.966 rows=212677 loops=1)
Filter: (visibility_level = ANY ('{20,10}'::integer[]))
Rows Removed by Filter: 764356
Total runtime: 4915.084 ms
- Investigate why this query take so much time to finish?
- Running query manually gives results in seconds and I can say for sure that this is SPAM!