Overly complex query in CI could be simplified and/or use indexes more
This SQL is using temporary files to do multiple pass hash joins. It isn't actually taking exceedingly long to run but could probably be much faster if it were optimized to use indexes more. I note several of the nodes in the plan have "filters" that remove a large number of rows. Also several parts seem redundant, one seems to never produce rows in the examples I tested and another seems to scan the same relation with the same conditions twice.
SELECT COUNT(*)
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND (
namespaces.id IN (
WITH RECURSIVE "base_and_ancestors" AS (
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND (
namespaces.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" = 2080944
AND "members"."type" IN ('GroupMember')
AND "members"."requested_at" IS NULL
UNION
SELECT "projects"."namespace_id"
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
WHERE "project_authorizations"."user_id" = 2080944
)
)
UNION
SELECT "namespaces".*
FROM "namespaces"
,"base_and_ancestors"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = "base_and_ancestors"."parent_id"
)
,"base_and_descendants" AS (
SELECT "namespaces".*
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" = 2080944
AND "members"."type" IN ('GroupMember')
AND "members"."requested_at" IS NULL
UNION
SELECT "namespaces".*
FROM "namespaces"
,"base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" = "base_and_descendants"."id"
)
SELECT "id"
FROM (
SELECT "namespaces".*
FROM "base_and_ancestors" AS "namespaces"
WHERE "namespaces"."type" IN ('Group')
UNION
SELECT "namespaces".*
FROM "base_and_descendants" AS "namespaces"
WHERE "namespaces"."type" IN ('Group')
) namespaces
WHERE "namespaces"."type" IN ('Group')
UNION
SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namesgitlabhq_production(# paces"."type" IN ('Group')
AND "namespaces"."visibility_level" IN (
10
,20
)
)
)
AND "namespaces"."parent_id" IS NULLproduces this explain analyze verbose plan:
Aggregate (cost=95807.83..95807.84 rows=1 width=8) (actual time=1280.085..1280.085 rows=1 loops=1)
Output: count(*)
-> Hash Join (cost=58468.30..95250.38 rows=222979 width=0) (actual time=633.711..1253.523 rows=107199 loops=1)
Hash Cond: (namespaces.id = namespaces_1.id)
-> Index Scan using index_namespaces_on_type on public.namespaces (cost=0.43..29392.64 rows=445958 width=4) (actual time=0.036..392.016 rows=382513 loops=1)
Output: namespaces.id
Index Cond: ((namespaces.type)::text = 'Group'::text)
Filter: (namespaces.parent_id IS NULL)
Rows Removed by Filter: 58964
-> Hash (cost=51959.03..51959.03 rows=396707 width=4) (actual time=631.352..631.352 rows=110812 loops=1)
Output: namespaces_1.id
Buckets: 524288 Batches: 2 Memory Usage: 6043kB
-> HashAggregate (cost=44024.89..47991.96 rows=396707 width=4) (actual time=523.473..586.524 rows=110812 loops=1)
Output: namespaces_1.id
Group Key: namespaces_1.id
CTE base_and_ancestors
-> Recursive Union (cost=383.88..7009.17 rows=338 width=183) (actual time=0.030..0.030 rows=0 loops=1)
-> Nested Loop (cost=383.88..738.41 rows=18 width=183) (actual time=0.028..0.028 rows=0 loops=1)
Output: namespaces_5.id, namespaces_5.name, namespaces_5.path, namespaces_5.owner_id, namespaces_5.created_at, namespaces_5.updated_at, namespaces_5.type, namespaces_5.description, namespaces_5.avatar, namespaces_5.membership_lock, namespaces_5.share_with_group_lock, namespaces_5.visibility_level, namespaces_5.request_access_enabled, namespaces_5.ldap_sync_status, namespaces_5.ldap_sync_error, namespaces_5.ldap_sync_last_update_at, namespaces_5.ldap_sync_last_successful_update_at, namespaces_5.ldap_sync_last_sync_at, namespaces_5.lfs_enabled, namespaces_5.description_html, namespaces_5.parent_id, namespaces_5.shared_runners_minutes_limit, namespaces_5.repository_size_limit, namespaces_5.require_two_factor_authentication, namespaces_5.two_factor_grace_period, namespaces_5.cached_markdown_version, namespaces_5.plan_id, namespaces_5.project_creation_level
-> HashAggregate (cost=383.45..384.47 rows=102 width=4) (actual time=0.027..0.027 rows=0 loops=1)
Output: namespaces_6.id
Group Key: namespaces_6.id
-> Append (cost=0.86..383.20 rows=102 width=4) (actual time=0.026..0.026 rows=0 loops=1)
-> Nested Loop (cost=0.86..23.60 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Output: namespaces_6.id
-> Index Scan using index_members_on_user_id on public.members (cost=0.43..20.14 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)
Output: members.id, members.access_level, members.source_id, members.source_type, members.user_id, members.notification_level, members.type, members.created_at, members.updated_at, members.created_by_id, members.invite_email, members.invite_token, members.invite_accepted_at, members.requested_at, members.expires_at, members.ldap, members.override
Index Cond: (members.user_id = 2080944)
Filter: ((members.requested_at IS NULL) AND ((members.source_type)::text = 'Namespace'::text) AND ((members.type)::text = 'GroupMember'::text))
-> Index Scan using namespaces_pkey on public.namespaces namespaces_6 (cost=0.43..3.45 rows=1 width=4) (never executed)
Output: namespaces_6.id
Index Cond: (namespaces_6.id = members.source_id)
Filter: ((namespaces_6.type)::text = 'Group'::text)
-> Nested Loop (cost=0.99..358.58 rows=101 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Output: projects.namespace_id
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.56..9.12 rows=101 width=4) (actual time=0.013..0.013 rows=0 loops=1)
Output: project_authorizations.user_id, project_authorizations.project_id, project_authorizations.access_level
Index Cond: (project_authorizations.user_id = 2080944)
Heap Fetches: 0
-> Index Scan using projects_pkey on public.projects (cost=0.43..3.45 rows=1 width=8) (never executed)
Output: projects.namespace_id, projects.id
Index Cond: (projects.id = project_authorizations.project_id)
-> Index Scan using namespaces_pkey on public.namespaces namespaces_5 (cost=0.43..3.45 rows=1 width=183) (never executed)
Output: namespaces_5.id, namespaces_5.name, namespaces_5.path, namespaces_5.owner_id, namespaces_5.created_at, namespaces_5.updated_at, namespaces_5.type, namespaces_5.description, namespaces_5.avatar, namespaces_5.membership_lock, namespaces_5.share_with_group_lock, namespaces_5.visibility_level, namespaces_5.request_access_enabled, namespaces_5.ldap_sync_status, namespaces_5.ldap_sync_error, namespaces_5.ldap_sync_last_update_at, namespaces_5.ldap_sync_last_successful_update_at, namespaces_5.ldap_sync_last_sync_at, namespaces_5.lfs_enabled, namespaces_5.description_html, namespaces_5.parent_id, namespaces_5.shared_runners_minutes_limit, namespaces_5.repository_size_limit, namespaces_5.require_two_factor_authentication, namespaces_5.two_factor_grace_period, namespaces_5.cached_markdown_version, namespaces_5.plan_id, namespaces_5.project_creation_level
Index Cond: (namespaces_5.id = namespaces_6.id)
Filter: ((namespaces_5.type)::text = 'Group'::text)
-> Nested Loop (cost=0.43..626.40 rows=32 width=183) (actual time=0.002..0.002 rows=0 loops=1)
Output: namespaces_7.id, namespaces_7.name, namespaces_7.path, namespaces_7.owner_id, namespaces_7.created_at, namespaces_7.updated_at, namespaces_7.type, namespaces_7.description, namespaces_7.avatar, namespaces_7.membership_lock, namespaces_7.share_with_group_lock, namespaces_7.visibility_level, namespaces_7.request_access_enabled, namespaces_7.ldap_sync_status, namespaces_7.ldap_sync_error, namespaces_7.ldap_sync_last_update_at, namespaces_7.ldap_sync_last_successful_update_at, namespaces_7.ldap_sync_last_sync_at, namespaces_7.lfs_enabled, namespaces_7.description_html, namespaces_7.parent_id, namespaces_7.shared_runners_minutes_limit, namespaces_7.repository_size_limit, namespaces_7.require_two_factor_authentication, namespaces_7.two_factor_grace_period, namespaces_7.cached_markdown_version, namespaces_7.plan_id, namespaces_7.project_creation_level
-> WorkTable Scan on base_and_ancestors (cost=0.00..3.60 rows=180 width=4) (actual time=0.001..0.001 rows=0 loops=1)
Output: base_and_ancestors.id, base_and_ancestors.name, base_and_ancestors.path, base_and_ancestors.owner_id, base_and_ancestors.created_at, base_and_ancestors.updated_at, base_and_ancestors.type, base_and_ancestors.description, base_and_ancestors.avatar, base_and_ancestors.membership_lock, base_and_ancestors.share_with_group_lock, base_and_ancestors.visibility_level, base_and_ancestors.request_access_enabled, base_and_ancestors.ldap_sync_status, base_and_ancestors.ldap_sync_error, base_and_ancestors.ldap_sync_last_update_at, base_and_ancestors.ldap_sync_last_successful_update_at, base_and_ancestors.ldap_sync_last_sync_at, base_and_ancestors.lfs_enabled, base_and_ancestors.description_html, base_and_ancestors.parent_id, base_and_ancestors.shared_runners_minutes_limit, base_and_ancestors.repository_size_limit, base_and_ancestors.require_two_factor_authentication, base_and_ancestors.two_factor_grace_period, base_and_ancestors.cached_markdown_version, base_and_ancestors.plan_id, base_and_ancestors.project_creation_level
-> Index Scan using namespaces_pkey on public.namespaces namespaces_7 (cost=0.43..3.45 rows=1 width=183) (never executed)
Output: namespaces_7.id, namespaces_7.name, namespaces_7.path, namespaces_7.owner_id, namespaces_7.created_at, namespaces_7.updated_at, namespaces_7.type, namespaces_7.description, namespaces_7.avatar, namespaces_7.membership_lock, namespaces_7.share_with_group_lock, namespaces_7.visibility_level, namespaces_7.request_access_enabled, namespaces_7.ldap_sync_status, namespaces_7.ldap_sync_error, namespaces_7.ldap_sync_last_update_at, namespaces_7.ldap_sync_last_successful_update_at, namespaces_7.ldap_sync_last_sync_at, namespaces_7.lfs_enabled, namespaces_7.description_html, namespaces_7.parent_id, namespaces_7.shared_runners_minutes_limit, namespaces_7.repository_size_limit, namespaces_7.require_two_factor_authentication, namespaces_7.two_factor_grace_period, namespaces_7.cached_markdown_version, namespaces_7.plan_id, namespaces_7.project_creation_level
Index Cond: (namespaces_7.id = base_and_ancestors.parent_id)
Filter: ((namespaces_7.type)::text = 'Group'::text)
CTE base_and_descendants
-> Recursive Union (cost=0.86..1511.41 rows=151 width=183) (actual time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.86..23.60 rows=1 width=183) (actual time=0.002..0.002 rows=0 loops=1)
Output: namespaces_8.id, namespaces_8.name, namespaces_8.path, namespaces_8.owner_id, namespaces_8.created_at, namespaces_8.updated_at, namespaces_8.type, namespaces_8.description, namespaces_8.avatar, namespaces_8.membership_lock, namespaces_8.share_with_group_lock, namespaces_8.visibility_level, namespaces_8.request_access_enabled, namespaces_8.ldap_sync_status, namespaces_8.ldap_sync_error, namespaces_8.ldap_sync_last_update_at, namespaces_8.ldap_sync_last_successful_update_at, namespaces_8.ldap_sync_last_sync_at, namespaces_8.lfs_enabled, namespaces_8.description_html, namespaces_8.parent_id, namespaces_8.shared_runners_minutes_limit, namespaces_8.repository_size_limit, namespaces_8.require_two_factor_authentication, namespaces_8.two_factor_grace_period, namespaces_8.cached_markdown_version, namespaces_8.plan_id, namespaces_8.project_creation_level
-> Index Scan using index_members_on_user_id on public.members members_1 (cost=0.43..20.14 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)
Output: members_1.id, members_1.access_level, members_1.source_id, members_1.source_type, members_1.user_id, members_1.notification_level, members_1.type, members_1.created_at, members_1.updated_at, members_1.created_by_id, members_1.invite_email, members_1.invite_token, members_1.invite_accepted_at, members_1.requested_at, members_1.expires_at, members_1.ldap, members_1.override
Index Cond: (members_1.user_id = 2080944)
Filter: ((members_1.requested_at IS NULL) AND ((members_1.source_type)::text = 'Namespace'::text) AND ((members_1.type)::text = 'GroupMember'::text))
-> Index Scan using namespaces_pkey on public.namespaces namespaces_8 (cost=0.43..3.45 rows=1 width=183) (never executed)
Output: namespaces_8.id, namespaces_8.name, namespaces_8.path, namespaces_8.owner_id, namespaces_8.created_at, namespaces_8.updated_at, namespaces_8.type, namespaces_8.description, namespaces_8.avatar, namespaces_8.membership_lock, namespaces_8.share_with_group_lock, namespaces_8.visibility_level, namespaces_8.request_access_enabled, namespaces_8.ldap_sync_status, namespaces_8.ldap_sync_error, namespaces_8.ldap_sync_last_update_at, namespaces_8.ldap_sync_last_successful_update_at, namespaces_8.ldap_sync_last_sync_at, namespaces_8.lfs_enabled, namespaces_8.description_html, namespaces_8.parent_id, namespaces_8.shared_runners_minutes_limit, namespaces_8.repository_size_limit, namespaces_8.require_two_factor_authentication, namespaces_8.two_factor_grace_period, namespaces_8.cached_markdown_version, namespaces_8.plan_id, namespaces_8.project_creation_level
Index Cond: (namespaces_8.id = members_1.source_id)
Filter: ((namespaces_8.type)::text = 'Group'::text)
-> Nested Loop (cost=0.43..148.48 rows=15 width=183) (actual time=0.002..0.002 rows=0 loops=1)
Output: namespaces_9.id, namespaces_9.name, namespaces_9.path, namespaces_9.owner_id, namespaces_9.created_at, namespaces_9.updated_at, namespaces_9.type, namespaces_9.description, namespaces_9.avatar, namespaces_9.membership_lock, namespaces_9.share_with_group_lock, namespaces_9.visibility_level, namespaces_9.request_access_enabled, namespaces_9.ldap_sync_status, namespaces_9.ldap_sync_error, namespaces_9.ldap_sync_last_update_at, namespaces_9.ldap_sync_last_successful_update_at, namespaces_9.ldap_sync_last_sync_at, namespaces_9.lfs_enabled, namespaces_9.description_html, namespaces_9.parent_id, namespaces_9.shared_runners_minutes_limit, namespaces_9.repository_size_limit, namespaces_9.require_two_factor_authentication, namespaces_9.two_factor_grace_period, namespaces_9.cached_markdown_version, namespaces_9.plan_id, namespaces_9.project_creation_level
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1)
Output: base_and_descendants.id, base_and_descendants.name, base_and_descendants.path, base_and_descendants.owner_id, base_and_descendants.created_at, base_and_descendants.updated_at, base_and_descendants.type, base_and_descendants.description, base_and_descendants.avatar, base_and_descendants.membership_lock, base_and_descendants.share_with_group_lock, base_and_descendants.visibility_level, base_and_descendants.request_access_enabled, base_and_descendants.ldap_sync_status, base_and_descendants.ldap_sync_error, base_and_descendants.ldap_sync_last_update_at, base_and_descendants.ldap_sync_last_successful_update_at, base_and_descendants.ldap_sync_last_sync_at, base_and_descendants.lfs_enabled, base_and_descendants.description_html, base_and_descendants.parent_id, base_and_descendants.shared_runners_minutes_limit, base_and_descendants.repository_size_limit, base_and_descendants.require_two_factor_authentication, base_and_descendants.two_factor_grace_period, base_and_descendants.cached_markdown_version, base_and_descendants.plan_id, base_and_descendants.project_creation_level
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_9 (cost=0.43..14.81 rows=2 width=183) (never executed)
Output: namespaces_9.id, namespaces_9.name, namespaces_9.path, namespaces_9.owner_id, namespaces_9.created_at, namespaces_9.updated_at, namespaces_9.type, namespaces_9.description, namespaces_9.avatar, namespaces_9.membership_lock, namespaces_9.share_with_group_lock, namespaces_9.visibility_level, namespaces_9.request_access_enabled, namespaces_9.ldap_sync_status, namespaces_9.ldap_sync_error, namespaces_9.ldap_sync_last_update_at, namespaces_9.ldap_sync_last_successful_update_at, namespaces_9.ldap_sync_last_sync_at, namespaces_9.lfs_enabled, namespaces_9.description_html, namespaces_9.parent_id, namespaces_9.shared_runners_minutes_limit, namespaces_9.repository_size_limit, namespaces_9.require_two_factor_authentication, namespaces_9.two_factor_grace_period, namespaces_9.cached_markdown_version, namespaces_9.plan_id, namespaces_9.project_creation_level
Index Cond: (namespaces_9.parent_id = base_and_descendants.id)
Filter: ((namespaces_9.type)::text = 'Group'::text)
-> Append (cost=11.24..34512.54 rows=396707 width=4) (actual time=0.081..445.831 rows=110812 loops=1)
-> Subquery Scan on namespaces_1 (cost=11.24..11.30 rows=3 width=4) (actual time=0.039..0.039 rows=0 loops=1)
Output: namespaces_1.id
-> HashAggregate (cost=11.24..11.27 rows=3 width=2765) (actual time=0.039..0.039 rows=0 loops=1)
Output: namespaces_2.id, namespaces_2.name, namespaces_2.path, namespaces_2.owner_id, namespaces_2.created_at, namespaces_2.updated_at, namespaces_2.type, namespaces_2.description, namespaces_2.avatar, namespaces_2.membership_lock, namespaces_2.share_with_group_lock, namespaces_2.visibility_level, namespaces_2.request_access_enabled, namespaces_2.ldap_sync_status, namespaces_2.ldap_sync_error, namespaces_2.ldap_sync_last_update_at, namespaces_2.ldap_sync_last_successful_update_at, namespaces_2.ldap_sync_last_sync_at, namespaces_2.lfs_enabled, namespaces_2.description_html, namespaces_2.parent_id, namespaces_2.shared_runners_minutes_limit, namespaces_2.repository_size_limit, namespaces_2.require_two_factor_authentication, namespaces_2.two_factor_grace_period, namespaces_2.cached_markdown_version, namespaces_2.plan_id, namespaces_2.project_creation_level
Group Key: namespaces_2.id, namespaces_2.name, namespaces_2.path, namespaces_2.owner_id, namespaces_2.created_at, namespaces_2.updated_at, namespaces_2.type, namespaces_2.description, namespaces_2.avatar, namespaces_2.membership_lock, namespaces_2.share_with_group_lock, namespaces_2.visibility_level, namespaces_2.request_access_enabled, namespaces_2.ldap_sync_status, namespaces_2.ldap_sync_error, namespaces_2.ldap_sync_last_update_at, namespaces_2.ldap_sync_last_successful_update_at, namespaces_2.ldap_sync_last_sync_at, namespaces_2.lfs_enabled, namespaces_2.description_html, namespaces_2.parent_id, namespaces_2.shared_runners_minutes_limit, namespaces_2.repository_size_limit, namespaces_2.require_two_factor_authentication, namespaces_2.two_factor_grace_period, namespaces_2.cached_markdown_version, namespaces_2.plan_id, namespaces_2.project_creation_level
-> Append (cost=0.00..11.03 rows=3 width=2765) (actual time=0.038..0.038 rows=0 loops=1)
-> CTE Scan on base_and_ancestors namespaces_2 (cost=0.00..7.60 rows=2 width=2765) (actual time=0.032..0.032 rows=0 loops=1)
Output: namespaces_2.id, namespaces_2.name, namespaces_2.path, namespaces_2.owner_id, namespaces_2.created_at, namespaces_2.updated_at, namespaces_2.type, namespaces_2.description, namespaces_2.avatar, namespaces_2.membership_lock, namespaces_2.share_with_group_lock, namespaces_2.visibility_level, namespaces_2.request_access_enabled, namespaces_2.ldap_sync_status, namespaces_2.ldap_sync_error, namespaces_2.ldap_sync_last_update_at, namespaces_2.ldap_sync_last_successful_update_at, namespaces_2.ldap_sync_last_sync_at, namespaces_2.lfs_enabled, namespaces_2.description_html, namespaces_2.parent_id, namespaces_2.shared_runners_minutes_limit, namespaces_2.repository_size_limit, namespaces_2.require_two_factor_authentication, namespaces_2.two_factor_grace_period, namespaces_2.cached_markdown_version, namespaces_2.plan_id, namespaces_2.project_creation_level
Filter: ((namespaces_2.type)::text = 'Group'::text)
-> CTE Scan on base_and_descendants namespaces_3 (cost=0.00..3.40 rows=1 width=2765) (actual time=0.005..0.005 rows=0 loops=1)
Output: namespaces_3.id, namespaces_3.name, namespaces_3.path, namespaces_3.owner_id, namespaces_3.created_at, namespaces_3.updated_at, namespaces_3.type, namespaces_3.description, namespaces_3.avatar, namespaces_3.membership_lock, namespaces_3.share_with_group_lock, namespaces_3.visibility_level, namespaces_3.request_access_enabled, namespaces_3.ldap_sync_status, namespaces_3.ldap_sync_error, namespaces_3.ldap_sync_last_update_at, namespaces_3.ldap_sync_last_successful_update_at, namespaces_3.ldap_sync_last_sync_at, namespaces_3.lfs_enabled, namespaces_3.description_html, namespaces_3.parent_id, namespaces_3.shared_runners_minutes_limit, namespaces_3.repository_size_limit, namespaces_3.require_two_factor_authentication, namespaces_3.two_factor_grace_period, namespaces_3.cached_markdown_version, namespaces_3.plan_id, namespaces_3.project_creation_level
Filter: ((namespaces_3.type)::text = 'Group'::text)
-> Index Scan using index_namespaces_on_type on public.namespaces namespaces_4 (cost=0.43..30534.17 rows=396704 width=4) (actual time=0.041..390.887 rows=110812 loops=1)
Output: namespaces_4.id
Index Cond: ((namespaces_4.type)::text = 'Group'::text)
Filter: (namespaces_4.visibility_level = ANY ('{10,20}'::integer[]))
Rows Removed by Filter: 330665
Planning time: 4.574 ms
Execution time: 1282.898 ms
(94 rows)Edited by 🤖 GitLab Bot 🤖