Finding issues appears to be slow (or slower) since issues are soft deleted
@pcarranza notified me of the following query:
14894.506 ms execute <unnamed>: SELECT COUNT(*) FROM "issues" WHERE "issues"."deleted_at" IS NULL AND (issues.confidential = false OR (issues.confidential = true AND (issues.author_id = 358789 OR issues.assignee_id = 358789 OR issues.project_id IN(SELECT "projects"."id" FROM "projects" WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = 358789 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 "namespaces"."type" IN ('Group') AND "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "members"."user_id" = 358789
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "members"."type" IN ('ProjectMember') AND "members"."source_type" = 'Project' AND "members"."user_id" = 358789
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" 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" = 358789)) ORDER BY "projects"."id" DESC)))) AND "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = 358789 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 "namespaces"."type" IN ('Group') AND "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "members"."user_id" = 358789
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "members"."type" IN ('ProjectMember') AND "members"."source_type" = 'Project' AND "members"."user_id" = 358789
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" 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" = 358789))
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10)))) AND ("issues"."title" ILIKE '%\插\件%' OR "issues"."description" ILIKE '%\插\件%')This produces the following query plan:
Aggregate (cost=207441.16..207441.17 rows=1 width=0) (actual time=16802.948..16802.949 rows=1 loops=1)
-> Hash Semi Join (cost=94143.76..207390.50 rows=20264 width=0) (actual time=2782.806..16792.727 rows=14536 loops=1)
Hash Cond: (issues.project_id = public.projects.id)
-> Seq Scan on issues (cost=418.88..111585.34 rows=20264 width=4) (actual time=0.400..13998.574 rows=21506 loops=1)
Filter: ((deleted_at IS NULL) AND (((title)::text ~~* '%\\%'::text) OR (description ~~* '%\\%'::text)) AND ((NOT confidential) OR (confidential AND ((author_id
= 358789) OR (assignee_id = 358789) OR (hashed SubPlan 1)))))
Rows Removed by Filter: 1129861
SubPlan 1
-> Sort (cost=418.82..418.85 rows=11 width=4) (actual time=0.155..0.155 rows=2 loops=1)
Sort Key: public.projects.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=350.26..418.63 rows=11 width=4) (actual time=0.128..0.135 rows=2 loops=1)
-> HashAggregate (cost=350.26..350.37 rows=11 width=4) (actual time=0.122..0.122 rows=2 loops=1)
-> Append (cost=0.00..350.23 rows=11 width=4) (actual time=0.064..0.103 rows=2 loops=1)
-> Nested Loop (cost=0.00..83.28 rows=3 width=4) (actual time=0.025..0.025 rows=0 loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces (cost=0.00..8.74 rows=2 width=4) (actual time=0.013..0.014 rows
=1 loops=1)
Index Cond: (owner_id = 358789)
Filter: (type IS NULL)
-> Index Scan using index_projects_on_namespace_id on projects (cost=0.00..37.17 rows=10 width=8) (actual time=0.008..0.008 ro
ws=0 loops=1)
Index Cond: (namespace_id = public.namespaces.id)
-> Nested Loop (cost=0.00..34.60 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=1)
-> Nested Loop (cost=0.00..32.13 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.00..23.79 rows=1 width=4) (actual time=0.015..0.015 rows
=0 loops=1)
Index Cond: (user_id = 358789)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
Rows Removed by Filter: 2
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..8.33 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.37 rows=10 width=8) (never executed)
Index Cond: (namespace_id = public.namespaces.id)
-> Nested Loop (cost=0.00..62.06 rows=6 width=4) (actual time=0.020..0.034 rows=2 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.00..23.79 rows=6 width=4) (actual time=0.005..0.008 rows=2 loo
ps=1)
Index Cond: (user_id = 358789)
Filter: (((type)::text = 'ProjectMember'::text) AND ((source_type)::text = 'Project'::text))
-> Index Only Scan using projects_pkey on projects (cost=0.00..6.37 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=2)
Index Cond: (id = public.members.source_id)
Heap Fetches: 2
-> Nested Loop (cost=23.80..170.18 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=1)
-> Nested Loop (cost=23.80..166.83 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=1)
-> Hash Join (cost=23.80..162.90 rows=1 width=12) (actual time=0.020..0.020 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..115.88 rows=6188 width=8) (actual time=0.005..0.005 rows=1 loops=1)
-> Hash (cost=23.79..23.79 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Index Scan using index_members_on_user_id on members (cost=0.00..23.79 rows=1 width=4) (actual time=0.005
..0.005 rows=0 loops=1)
Index Cond: (user_id = 358789)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
Rows Removed by Filter: 2
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..3.93 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.group_id)
Filter: ((type)::text = 'Group'::text)
-> Index Only Scan using projects_pkey on projects (cost=0.00..3.34 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.project_id)
Heap Fetches: 0
-> Index Only Scan using projects_pkey on projects (cost=0.00..6.19 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=2)
Index Cond: (id = public.projects.id)
Heap Fetches: 2
-> Hash (cost=87363.14..87363.14 rows=387739 width=8) (actual time=2747.108..2747.108 rows=177454 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 3474kB
-> Hash Join (cost=43760.68..87363.14 rows=387739 width=8) (actual time=959.530..2615.249 rows=177454 loops=1)
Hash Cond: (public.projects.id = public.projects.id)
-> Seq Scan on projects (cost=0.00..35039.78 rows=775478 width=4) (actual time=0.014..567.146 rows=775714 loops=1)
-> Hash (cost=41539.12..41539.12 rows=177725 width=4) (actual time=959.333..959.333 rows=177454 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 6239kB
-> HashAggregate (cost=37984.62..39761.87 rows=177725 width=4) (actual time=659.266..828.195 rows=177454 loops=1)
-> Append (cost=350.26..37540.31 rows=177725 width=4) (actual time=0.309..495.398 rows=177454 loops=1)
-> Nested Loop (cost=350.26..418.63 rows=11 width=4) (actual time=0.307..0.314 rows=2 loops=1)
-> HashAggregate (cost=350.26..350.37 rows=11 width=4) (actual time=0.297..0.299 rows=2 loops=1)
-> Append (cost=0.00..350.23 rows=11 width=4) (actual time=0.203..0.291 rows=2 loops=1)
-> Nested Loop (cost=0.00..83.28 rows=3 width=4) (actual time=0.080..0.080 rows=0 loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces (cost=0.00..8.74 rows=2 width=4) (actual time=0
.046..0.047 rows=1 loops=1)
Index Cond: (owner_id = 358789)
Filter: (type IS NULL)
-> Index Scan using index_projects_on_namespace_id on projects (cost=0.00..37.17 rows=10 width=8) (actual time
=0.029..0.029 rows=0 loops=1)
Index Cond: (namespace_id = public.namespaces.id)
-> Nested Loop (cost=0.00..34.60 rows=1 width=4) (actual time=0.057..0.057 rows=0 loops=1)
-> Nested Loop (cost=0.00..32.13 rows=1 width=8) (actual time=0.056..0.056 rows=0 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.00..23.79 rows=1 width=4) (actual time=0
.055..0.055 rows=0 loops=1)
Index Cond: (user_id = 358789)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
Rows Removed by Filter: 2
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..8.33 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.37 rows=10 width=8) (never execut
ed)
Index Cond: (namespace_id = public.namespaces.id)
-> Nested Loop (cost=0.00..62.06 rows=6 width=4) (actual time=0.062..0.109 rows=2 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.00..23.79 rows=6 width=4) (actual time=0.004..
0.007 rows=2 loops=1)
Index Cond: (user_id = 358789)
Filter: (((type)::text = 'ProjectMember'::text) AND ((source_type)::text = 'Project'::text))
-> Index Only Scan using projects_pkey on projects (cost=0.00..6.37 rows=1 width=4) (actual time=0.040..0.047
rows=1 loops=2)
Index Cond: (id = public.members.source_id)
Heap Fetches: 2
-> Nested Loop (cost=23.80..170.18 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=1)
-> Nested Loop (cost=23.80..166.83 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=1)
-> Hash Join (cost=23.80..162.90 rows=1 width=12) (actual time=0.036..0.036 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..115.88 rows=6188 width=8) (actual time=0.019..0.019
rows=1 loops=1)
-> Hash (cost=23.79..23.79 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Index Scan using index_members_on_user_id on members (cost=0.00..23.79 rows=1 width=4) (a
ctual time=0.004..0.004 rows=0 loops=1)
Index Cond: (user_id = 358789)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::te
xt))
Rows Removed by Filter: 2
-> Index Scan using namespaces_pkey on namespaces (cost=0.00..3.93 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.group_id)
Filter: ((type)::text = 'Group'::text)
-> Index Only Scan using projects_pkey on projects (cost=0.00..3.34 rows=1 width=4) (never executed)
Index Cond: (id = public.project_group_links.project_id)
Heap Fetches: 0
-> Index Only Scan using projects_pkey on projects (cost=0.00..6.19 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=2)
Index Cond: (id = public.projects.id)
Heap Fetches: 2
-> Bitmap Heap Scan on projects (cost=5838.00..35344.43 rows=177714 width=4) (actual time=30.707..301.592 rows=177452 loops=1)
Recheck Cond: (visibility_level = ANY ('{20,10}'::integer[]))
-> Bitmap Index Scan on index_projects_on_visibility_level (cost=0.00..5793.57 rows=177714 width=0) (actual time=25.464..25.464
rows=178456 loops=1)
Index Cond: (visibility_level = ANY ('{20,10}'::integer[]))
Total runtime: 16806.720 ms
(118 rows)The plan is visualized here: http://explain.depesz.com/s/BEu
The columns in question appear to be indexed so my initial thought is that we'll have to use a composite index here, though I've not tried anything out just yet.
@zj: Since you wrote this feel free to take a stab at this if you'd like, otherwise I'll pick it up somewhere in the coming weeks.