RUN AS-IF-FOSS Fix group search users scope times out
What does this MR do?
Issue #31092 (closed)
Currently the user search for very large groups will time out on GitLab.com. This MR changes how the group search users scope works, using a similar method as the Group Members search which does not time out on GitLab.com. I went through a few different options of queries used and database indexes before settling on the current solution. I'll list the current solution first but wanted to preserve what was attempted so additional solutions will be included at the bottom of the description.
How to test
The User search only uses Basic Search so there is no need to enable or disable Advanced Search to test this. The UI + Performance Bar were used to determine the queries generated by the searches.
After a lot of discussion, it seems best to not test this with the root/admin account since that significantly changed the queries used.
- Navigate to the Search UI
- Select any group from the drop down
- Run a User search
Example url: http://localhost:3000/search?utf8=%E2%9C%93&snippets=false&scope=users&repository_ref=&search=test&group_id=70
Before Query & Plan
Expand for SQL
SELECT
"users"."id" AS t0_r0,
"users"."email" AS t0_r1,
"users"."encrypted_password" AS t0_r2,
"users"."reset_password_token" AS t0_r3,
"users"."reset_password_sent_at" AS t0_r4,
"users"."remember_created_at" AS t0_r5,
"users"."sign_in_count" AS t0_r6,
"users"."current_sign_in_at" AS t0_r7,
"users"."last_sign_in_at" AS t0_r8,
"users"."current_sign_in_ip" AS t0_r9,
"users"."last_sign_in_ip" AS t0_r10,
"users"."created_at" AS t0_r11,
"users"."updated_at" AS t0_r12,
"users"."name" AS t0_r13,
"users"."admin" AS t0_r14,
"users"."projects_limit" AS t0_r15,
"users"."skype" AS t0_r16,
"users"."linkedin" AS t0_r17,
"users"."twitter" AS t0_r18,
"users"."failed_attempts" AS t0_r19,
"users"."locked_at" AS t0_r20,
"users"."username" AS t0_r21,
"users"."can_create_group" AS t0_r22,
"users"."can_create_team" AS t0_r23,
"users"."state" AS t0_r24,
"users"."color_scheme_id" AS t0_r25,
"users"."password_expires_at" AS t0_r26,
"users"."created_by_id" AS t0_r27,
"users"."last_credential_check_at" AS t0_r28,
"users"."avatar" AS t0_r29,
"users"."confirmation_token" AS t0_r30,
"users"."confirmed_at" AS t0_r31,
"users"."confirmation_sent_at" AS t0_r32,
"users"."unconfirmed_email" AS t0_r33,
"users"."hide_no_ssh_key" AS t0_r34,
"users"."website_url" AS t0_r35,
"users"."admin_email_unsubscribed_at" AS t0_r36,
"users"."notification_email" AS t0_r37,
"users"."hide_no_password" AS t0_r38,
"users"."password_automatically_set" AS t0_r39,
"users"."location" AS t0_r40,
"users"."encrypted_otp_secret" AS t0_r41,
"users"."encrypted_otp_secret_iv" AS t0_r42,
"users"."encrypted_otp_secret_salt" AS t0_r43,
"users"."otp_required_for_login" AS t0_r44,
"users"."otp_backup_codes" AS t0_r45,
"users"."public_email" AS t0_r46,
"users"."dashboard" AS t0_r47,
"users"."project_view" AS t0_r48,
"users"."consumed_timestep" AS t0_r49,
"users"."layout" AS t0_r50,
"users"."hide_project_limit" AS t0_r51,
"users"."note" AS t0_r52,
"users"."unlock_token" AS t0_r53,
"users"."otp_grace_period_started_at" AS t0_r54,
"users"."external" AS t0_r55,
"users"."incoming_email_token" AS t0_r56,
"users"."organization" AS t0_r57,
"users"."auditor" AS t0_r58,
"users"."require_two_factor_authentication_from_group" AS t0_r59,
"users"."two_factor_grace_period" AS t0_r60,
"users"."last_activity_on" AS t0_r61,
"users"."notified_of_own_activity" AS t0_r62,
"users"."preferred_language" AS t0_r63,
"users"."email_opted_in" AS t0_r64,
"users"."email_opted_in_ip" AS t0_r65,
"users"."email_opted_in_source_id" AS t0_r66,
"users"."email_opted_in_at" AS t0_r67,
"users"."theme_id" AS t0_r68,
"users"."accepted_term_id" AS t0_r69,
"users"."feed_token" AS t0_r70,
"users"."private_profile" AS t0_r71,
"users"."roadmap_layout" AS t0_r72,
"users"."include_private_contributions" AS t0_r73,
"users"."commit_email" AS t0_r74,
"users"."group_view" AS t0_r75,
"users"."managing_group_id" AS t0_r76,
"users"."first_name" AS t0_r77,
"users"."last_name" AS t0_r78,
"users"."static_object_token" AS t0_r79,
"users"."role" AS t0_r80,
"users"."user_type" AS t0_r81,
"user_statuses"."user_id" AS t1_r0,
"user_statuses"."cached_markdown_version" AS t1_r1,
"user_statuses"."emoji" AS t1_r2,
"user_statuses"."message" AS t1_r3,
"user_statuses"."message_html" AS t1_r4
FROM
"users"
LEFT OUTER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id"
WHERE (("users"."name" ILIKE '%test%'
OR "users"."username" ILIKE '%test%')
OR "users"."email" = 'test')
AND "users"."id" IN (
SELECT
"users"."id"
FROM ((
SELECT
"users".*
FROM
"users"
WHERE
"users"."id" IN (
SELECT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")),
"base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM ((
SELECT
"namespaces".*
FROM
"base_and_ancestors" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"base_and_descendants" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')) namespaces
WHERE
"namespaces"."type" = 'Group')))
UNION (
SELECT
"users".*
FROM
"users"
INNER JOIN "members" ON "members"."source_type" = 'Project'
AND "members"."requested_at" IS NULL
AND "members"."user_id" = "users"."id"
AND "members"."type" = 'ProjectMember'
INNER JOIN "projects" ON "projects"."id" = "members"."source_id"
INNER JOIN "namespaces" ON "namespaces"."type" = 'Group'
AND "namespaces"."id" = "projects"."namespace_id"
AND "namespaces"."type" = 'Group'
WHERE
"namespaces"."id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces"))) users)
AND "users"."id" IN (
SELECT
members.user_id
FROM
"namespaces"
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"namespaces"."type" = 'Group'
ORDER BY
"namespaces"."id" DESC)
ORDER BY
CASE WHEN users.name = 'test' THEN
0
WHEN users.username = 'test' THEN
1
WHEN users.email = 'test' THEN
2
ELSE
3
END,
"users"."name" ASC
LIMIT 20 OFFSET 0Time: 12.815 min
- planning: 15.040 ms
- execution: 12.815 min
- I/O read: 11.936 min
- I/O write: 2.574 ms
Shared buffers:
- hits: 16374884 (~124.90 GiB) from the buffer pool
- reads: 760832 (~5.80 GiB) from the OS file cache, including disk I/O
- dirtied: 36057 (~281.70 MiB)
- writes: 1 (~8.00 KiB)
Temp buffers:
- reads: 26177 (~204.50 MiB)
- writes: 16692 (~130.40 MiB)After Query & Plan
Attempt 7
- Use GroupsFinder,
@group.self_and_hierarchy, and GroupMember to get group members where the GroupsFinder and @group heirarchy intersect - go back to using the super method
- No new indexes
- Tested with non-admin user
Note that the queries differ depending on whether you are searching a top level group or sub group so I've provided both Explain plans below. The performance is much worse for subgroups but still both are overall better than the original time of 12 minutes.
Explain plan (top level group)
Expand for SQL (when group is a top level group)
SELECT
“users”.“id” AS t0_r0,
“users”.“email” AS t0_r1,
“users”.“encrypted_password” AS t0_r2,
“users”.“reset_password_token” AS t0_r3,
“users”.“reset_password_sent_at” AS t0_r4,
“users”.“remember_created_at” AS t0_r5,
“users”.“sign_in_count” AS t0_r6,
“users”.“current_sign_in_at” AS t0_r7,
“users”.“last_sign_in_at” AS t0_r8,
“users”.“current_sign_in_ip” AS t0_r9,
“users”.“last_sign_in_ip” AS t0_r10,
“users”.“created_at” AS t0_r11,
“users”.“updated_at” AS t0_r12,
“users”.“name” AS t0_r13,
“users”.“admin” AS t0_r14,
“users”.“projects_limit” AS t0_r15,
“users”.“skype” AS t0_r16,
“users”.“linkedin” AS t0_r17,
“users”.“twitter” AS t0_r18,
“users”.“failed_attempts” AS t0_r19,
“users”.“locked_at” AS t0_r20,
“users”.“username” AS t0_r21,
“users”.“can_create_group” AS t0_r22,
“users”.“can_create_team” AS t0_r23,
“users”.“state” AS t0_r24,
“users”.“color_scheme_id” AS t0_r25,
“users”.“password_expires_at” AS t0_r26,
“users”.“created_by_id” AS t0_r27,
“users”.“last_credential_check_at” AS t0_r28,
“users”.“avatar” AS t0_r29,
“users”.“confirmation_token” AS t0_r30,
“users”.“confirmed_at” AS t0_r31,
“users”.“confirmation_sent_at” AS t0_r32,
“users”.“unconfirmed_email” AS t0_r33,
“users”.“hide_no_ssh_key” AS t0_r34,
“users”.“website_url” AS t0_r35,
“users”.“admin_email_unsubscribed_at” AS t0_r36,
“users”.“notification_email” AS t0_r37,
“users”.“hide_no_password” AS t0_r38,
“users”.“password_automatically_set” AS t0_r39,
“users”.“location” AS t0_r40,
“users”.“encrypted_otp_secret” AS t0_r41,
“users”.“encrypted_otp_secret_iv” AS t0_r42,
“users”.“encrypted_otp_secret_salt” AS t0_r43,
“users”.“otp_required_for_login” AS t0_r44,
“users”.“otp_backup_codes” AS t0_r45,
“users”.“public_email” AS t0_r46,
“users”.“dashboard” AS t0_r47,
“users”.“project_view” AS t0_r48,
“users”.“consumed_timestep” AS t0_r49,
“users”.“layout” AS t0_r50,
“users”.“hide_project_limit” AS t0_r51,
“users”.“note” AS t0_r52,
“users”.“unlock_token” AS t0_r53,
“users”.“otp_grace_period_started_at” AS t0_r54,
“users”.“external” AS t0_r55,
“users”.“incoming_email_token” AS t0_r56,
“users”.“organization” AS t0_r57,
“users”.“auditor” AS t0_r58,
“users”.“require_two_factor_authentication_from_group” AS t0_r59,
“users”.“two_factor_grace_period” AS t0_r60,
“users”.“last_activity_on” AS t0_r61,
“users”.“notified_of_own_activity” AS t0_r62,
“users”.“preferred_language” AS t0_r63,
“users”.“email_opted_in” AS t0_r64,
“users”.“email_opted_in_ip” AS t0_r65,
“users”.“email_opted_in_source_id” AS t0_r66,
“users”.“email_opted_in_at” AS t0_r67,
“users”.“theme_id” AS t0_r68,
“users”.“accepted_term_id” AS t0_r69,
“users”.“feed_token” AS t0_r70,
“users”.“private_profile” AS t0_r71,
“users”.“roadmap_layout” AS t0_r72,
“users”.“include_private_contributions” AS t0_r73,
“users”.“commit_email” AS t0_r74,
“users”.“group_view” AS t0_r75,
“users”.“managing_group_id” AS t0_r76,
“users”.“first_name” AS t0_r77,
“users”.“last_name” AS t0_r78,
“users”.“static_object_token” AS t0_r79,
“users”.“role” AS t0_r80,
“users”.“user_type” AS t0_r81,
“user_statuses”.“user_id” AS t1_r0,
“user_statuses”.“cached_markdown_version” AS t1_r1,
“user_statuses”.“emoji” AS t1_r2,
“user_statuses”.“message” AS t1_r3,
“user_statuses”.“message_html” AS t1_r4
FROM
“users”
LEFT OUTER JOIN “user_statuses” ON “user_statuses”.“user_id” = “users”.“id”
WHERE ((“users”.“name” ILIKE ‘%test%’
OR “users”.“username” ILIKE ‘%test%’)
OR “users”.“email” = ‘test’)
AND “users”.“external” != TRUE
AND “users”.“id” IN (
SELECT
“members”.“user_id”
FROM
“members”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “members”.“source_id” IN ( WITH RECURSIVE “base_and_ancestors” AS (
(
SELECT
“namespaces”. *
FROM
“namespaces”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“id” = 9970)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_ancestors”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“id” = “base_and_ancestors”.“parent_id”)),
“base_and_descendants” AS (
(
SELECT
“namespaces”. *
FROM
“namespaces”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“id” = 9970)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_descendants”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“parent_id” = “base_and_descendants”.“id”))
SELECT
“namespaces”.“id”
FROM ((
SELECT
“namespaces”. *
FROM
“base_and_ancestors” AS “namespaces”
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“base_and_descendants” AS “namespaces”
WHERE
“namespaces”.“type” = ‘Group’)) namespaces
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“id” IN (
SELECT
“namespaces”.“id”
FROM (( WITH RECURSIVE “base_and_ancestors” AS (
(
SELECT
“namespaces”. *
FROM ((
SELECT
“namespaces”. *
FROM
“namespaces”
INNER JOIN “members” ON “namespaces”.“id” = “members”.“source_id”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “namespaces”.“type” = ‘Group’
AND “members”.“user_id” = 47
AND “members”.“requested_at” IS NULL)
UNION (
SELECT
namespaces.*
FROM
“projects”
INNER JOIN “project_authorizations” ON “projects”.“id” = “project_authorizations”.“project_id”
INNER JOIN “namespaces” ON “namespaces”.“id” = “projects”.“namespace_id”
WHERE
“project_authorizations”.“user_id” = 47)) namespaces
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_ancestors”
WHERE
“namespaces”.“type” = ‘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”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “namespaces”.“type” = ‘Group’
AND “members”.“user_id” = 47
AND “members”.“requested_at” IS NULL)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_descendants”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“parent_id” = “base_and_descendants”.“id”))
SELECT
“namespaces”. *
FROM ((
SELECT
“namespaces”. *
FROM
“base_and_ancestors” AS “namespaces”
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“base_and_descendants” AS “namespaces”
WHERE
“namespaces”.“type” = ‘Group’)) namespaces
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“visibility_level” IN (10, 20))) namespaces
WHERE
“namespaces”.“type” = ‘Group’)))
ORDER BY
CASE WHEN users.name = ‘test’ THEN
0
WHEN users.username = ‘test’ THEN
1
WHEN users.email = ‘test’ THEN
2
ELSE
3
END,
“users”.“name” ASC
LIMIT 20 OFFSET 0;Run 1
Time: 7.008 s
- planning: 11.771 ms
- execution: 6.997 s
- I/O read: 5.164 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 38636 (~301.80 MiB) from the buffer pool
- reads: 210278 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 5834 (~45.60 MiB)
- writes: 0Run 2
Time: 1.682 s
- planning: 11.518 ms
- execution: 1.670 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 248763 (~1.90 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Expand for SQL (when group is a sub group)
SELECT
"users"."id" AS t0_r0,
"users"."email" AS t0_r1,
"users"."encrypted_password" AS t0_r2,
"users"."reset_password_token" AS t0_r3,
"users"."reset_password_sent_at" AS t0_r4,
"users"."remember_created_at" AS t0_r5,
"users"."sign_in_count" AS t0_r6,
"users"."current_sign_in_at" AS t0_r7,
"users"."last_sign_in_at" AS t0_r8,
"users"."current_sign_in_ip" AS t0_r9,
"users"."last_sign_in_ip" AS t0_r10,
"users"."created_at" AS t0_r11,
"users"."updated_at" AS t0_r12,
"users"."name" AS t0_r13,
"users"."admin" AS t0_r14,
"users"."projects_limit" AS t0_r15,
"users"."skype" AS t0_r16,
"users"."linkedin" AS t0_r17,
"users"."twitter" AS t0_r18,
"users"."failed_attempts" AS t0_r19,
"users"."locked_at" AS t0_r20,
"users"."username" AS t0_r21,
"users"."can_create_group" AS t0_r22,
"users"."can_create_team" AS t0_r23,
"users"."state" AS t0_r24,
"users"."color_scheme_id" AS t0_r25,
"users"."password_expires_at" AS t0_r26,
"users"."created_by_id" AS t0_r27,
"users"."last_credential_check_at" AS t0_r28,
"users"."avatar" AS t0_r29,
"users"."confirmation_token" AS t0_r30,
"users"."confirmed_at" AS t0_r31,
"users"."confirmation_sent_at" AS t0_r32,
"users"."unconfirmed_email" AS t0_r33,
"users"."hide_no_ssh_key" AS t0_r34,
"users"."website_url" AS t0_r35,
"users"."admin_email_unsubscribed_at" AS t0_r36,
"users"."notification_email" AS t0_r37,
"users"."hide_no_password" AS t0_r38,
"users"."password_automatically_set" AS t0_r39,
"users"."location" AS t0_r40,
"users"."encrypted_otp_secret" AS t0_r41,
"users"."encrypted_otp_secret_iv" AS t0_r42,
"users"."encrypted_otp_secret_salt" AS t0_r43,
"users"."otp_required_for_login" AS t0_r44,
"users"."otp_backup_codes" AS t0_r45,
"users"."public_email" AS t0_r46,
"users"."dashboard" AS t0_r47,
"users"."project_view" AS t0_r48,
"users"."consumed_timestep" AS t0_r49,
"users"."layout" AS t0_r50,
"users"."hide_project_limit" AS t0_r51,
"users"."note" AS t0_r52,
"users"."unlock_token" AS t0_r53,
"users"."otp_grace_period_started_at" AS t0_r54,
"users"."external" AS t0_r55,
"users"."incoming_email_token" AS t0_r56,
"users"."organization" AS t0_r57,
"users"."auditor" AS t0_r58,
"users"."require_two_factor_authentication_from_group" AS t0_r59,
"users"."two_factor_grace_period" AS t0_r60,
"users"."last_activity_on" AS t0_r61,
"users"."notified_of_own_activity" AS t0_r62,
"users"."preferred_language" AS t0_r63,
"users"."email_opted_in" AS t0_r64,
"users"."email_opted_in_ip" AS t0_r65,
"users"."email_opted_in_source_id" AS t0_r66,
"users"."email_opted_in_at" AS t0_r67,
"users"."theme_id" AS t0_r68,
"users"."accepted_term_id" AS t0_r69,
"users"."feed_token" AS t0_r70,
"users"."private_profile" AS t0_r71,
"users"."roadmap_layout" AS t0_r72,
"users"."include_private_contributions" AS t0_r73,
"users"."commit_email" AS t0_r74,
"users"."group_view" AS t0_r75,
"users"."managing_group_id" AS t0_r76,
"users"."first_name" AS t0_r77,
"users"."last_name" AS t0_r78,
"users"."static_object_token" AS t0_r79,
"users"."role" AS t0_r80,
"users"."user_type" AS t0_r81,
"user_statuses"."user_id" AS t1_r0,
"user_statuses"."cached_markdown_version" AS t1_r1,
"user_statuses"."emoji" AS t1_r2,
"user_statuses"."message" AS t1_r3,
"user_statuses"."message_html" AS t1_r4
FROM
"users"
LEFT OUTER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id"
WHERE (("users"."name" ILIKE '%test%'
OR "users"."username" ILIKE '%test%')
OR "users"."email" = 'test')
AND "users"."external" != TRUE
AND "users"."id" IN (
SELECT
"members"."user_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")),
"base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM ((
SELECT
"namespaces".*
FROM
"base_and_ancestors" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"base_and_descendants" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')) namespaces
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM (( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM ((
SELECT
"namespaces".*
FROM
"namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 47
AND "members"."requested_at" IS NULL)
UNION (
SELECT
namespaces.*
FROM
"projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
"project_authorizations"."user_id" = 47)) namespaces
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = '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"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 47
AND "members"."requested_at" IS NULL)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces".*
FROM ((
SELECT
"namespaces".*
FROM
"base_and_ancestors" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"base_and_descendants" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')) namespaces
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."visibility_level" IN (10, 20))) namespaces
WHERE
"namespaces"."type" = 'Group'))
AND "members"."invite_token" IS NULL)
ORDER BY
CASE WHEN users.name = 'test' THEN
0
WHEN users.username = 'test' THEN
1
WHEN users.email = 'test' THEN
2
ELSE
3
END,
"users"."name" ASC
LIMIT 20 OFFSET 0;Run 1
Time: 1.587 min
- planning: 14.265 ms
- execution: 1.587 min
- I/O read: 1.511 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 43909 (~343.00 MiB) from the buffer pool
- reads: 210327 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 6923 (~54.10 MiB)
- writes: 0Run 2
Time: 1.960 s
- planning: 13.651 ms
- execution: 1.947 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 254072 (~1.90 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Previous Solutions
expand for history of changes and explain plans
Attempt 1
This one was MUCH faster but lacked the ability to search within subgroups (resulted in failed specs). It did not make sense to me to not be able to search users that only belong to a sub group, so I continued to work on the solution.
Time: 105.586 ms
- planning: 4.727 ms
- execution: 100.859 ms
- I/O read: 14.395 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 6425 (~50.20 MiB) from the buffer pool
- reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O
- dirtied: 1 (~8.00 KiB)
- writes: 0SQL
SELECT
"users"."id" AS t0_r0,
"users"."email" AS t0_r1,
"users"."encrypted_password" AS t0_r2,
"users"."reset_password_token" AS t0_r3,
"users"."reset_password_sent_at" AS t0_r4,
"users"."remember_created_at" AS t0_r5,
"users"."sign_in_count" AS t0_r6,
"users"."current_sign_in_at" AS t0_r7,
"users"."last_sign_in_at" AS t0_r8,
"users"."current_sign_in_ip" AS t0_r9,
"users"."last_sign_in_ip" AS t0_r10,
"users"."created_at" AS t0_r11,
"users"."updated_at" AS t0_r12,
"users"."name" AS t0_r13,
"users"."admin" AS t0_r14,
"users"."projects_limit" AS t0_r15,
"users"."skype" AS t0_r16,
"users"."linkedin" AS t0_r17,
"users"."twitter" AS t0_r18,
"users"."failed_attempts" AS t0_r19,
"users"."locked_at" AS t0_r20,
"users"."username" AS t0_r21,
"users"."can_create_group" AS t0_r22,
"users"."can_create_team" AS t0_r23,
"users"."state" AS t0_r24,
"users"."color_scheme_id" AS t0_r25,
"users"."password_expires_at" AS t0_r26,
"users"."created_by_id" AS t0_r27,
"users"."last_credential_check_at" AS t0_r28,
"users"."avatar" AS t0_r29,
"users"."confirmation_token" AS t0_r30,
"users"."confirmed_at" AS t0_r31,
"users"."confirmation_sent_at" AS t0_r32,
"users"."unconfirmed_email" AS t0_r33,
"users"."hide_no_ssh_key" AS t0_r34,
"users"."website_url" AS t0_r35,
"users"."admin_email_unsubscribed_at" AS t0_r36,
"users"."notification_email" AS t0_r37,
"users"."hide_no_password" AS t0_r38,
"users"."password_automatically_set" AS t0_r39,
"users"."location" AS t0_r40,
"users"."encrypted_otp_secret" AS t0_r41,
"users"."encrypted_otp_secret_iv" AS t0_r42,
"users"."encrypted_otp_secret_salt" AS t0_r43,
"users"."otp_required_for_login" AS t0_r44,
"users"."otp_backup_codes" AS t0_r45,
"users"."public_email" AS t0_r46,
"users"."dashboard" AS t0_r47,
"users"."project_view" AS t0_r48,
"users"."consumed_timestep" AS t0_r49,
"users"."layout" AS t0_r50,
"users"."hide_project_limit" AS t0_r51,
"users"."note" AS t0_r52,
"users"."unlock_token" AS t0_r53,
"users"."otp_grace_period_started_at" AS t0_r54,
"users"."external" AS t0_r55,
"users"."incoming_email_token" AS t0_r56,
"users"."organization" AS t0_r57,
"users"."auditor" AS t0_r58,
"users"."require_two_factor_authentication_from_group" AS t0_r59,
"users"."two_factor_grace_period" AS t0_r60,
"users"."last_activity_on" AS t0_r61,
"users"."notified_of_own_activity" AS t0_r62,
"users"."preferred_language" AS t0_r63,
"users"."email_opted_in" AS t0_r64,
"users"."email_opted_in_ip" AS t0_r65,
"users"."email_opted_in_source_id" AS t0_r66,
"users"."email_opted_in_at" AS t0_r67,
"users"."theme_id" AS t0_r68,
"users"."accepted_term_id" AS t0_r69,
"users"."feed_token" AS t0_r70,
"users"."private_profile" AS t0_r71,
"users"."roadmap_layout" AS t0_r72,
"users"."include_private_contributions" AS t0_r73,
"users"."commit_email" AS t0_r74,
"users"."group_view" AS t0_r75,
"users"."managing_group_id" AS t0_r76,
"users"."first_name" AS t0_r77,
"users"."last_name" AS t0_r78,
"users"."static_object_token" AS t0_r79,
"users"."role" AS t0_r80,
"users"."user_type" AS t0_r81,
"user_statuses"."user_id" AS t1_r0,
"user_statuses"."cached_markdown_version" AS t1_r1,
"user_statuses"."emoji" AS t1_r2,
"user_statuses"."message" AS t1_r3,
"user_statuses"."message_html" AS t1_r4
FROM
"users"
LEFT OUTER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id"
WHERE (("users"."name" ILIKE '%test%'
OR "users"."username" ILIKE '%test%')
OR "users"."email" = 'test')
AND "users"."id" IN (
SELECT
"members"."user_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_id" = 9970
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL)
ORDER BY
CASE WHEN users.name = 'test' THEN
0
WHEN users.username = 'test' THEN
1
WHEN users.email = 'test' THEN
2
ELSE
3
END,
"users"."name" ASC
LIMIT 20 OFFSET 0Attempt 2
I was able to modify code to let the user search sub groups, but the performance has gotten worse. Still much better than the original!
Run 1
Time: 1.619 min
- planning: 5.423 ms
- execution: 1.619 min
- I/O read: 1.535 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 30566 (~238.80 MiB) from the buffer pool
- reads: 210269 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 5717 (~44.70 MiB)
- writes: 0
Temp buffers:
- reads: 2683 (~21.00 MiB)
- writes: 2684 (~21.00 MiB)Run 2
Time: 2.508 s
- planning: 6.501 ms
- execution: 2.502 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 240783 (~1.80 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Temp buffers:
- reads: 2683 (~21.00 MiB)
- writes: 2684 (~21.00 MiB)SQL
SELECT
"users"."id" AS t0_r0,
"users"."email" AS t0_r1,
"users"."encrypted_password" AS t0_r2,
"users"."reset_password_token" AS t0_r3,
"users"."reset_password_sent_at" AS t0_r4,
"users"."remember_created_at" AS t0_r5,
"users"."sign_in_count" AS t0_r6,
"users"."current_sign_in_at" AS t0_r7,
"users"."last_sign_in_at" AS t0_r8,
"users"."current_sign_in_ip" AS t0_r9,
"users"."last_sign_in_ip" AS t0_r10,
"users"."created_at" AS t0_r11,
"users"."updated_at" AS t0_r12,
"users"."name" AS t0_r13,
"users"."admin" AS t0_r14,
"users"."projects_limit" AS t0_r15,
"users"."skype" AS t0_r16,
"users"."linkedin" AS t0_r17,
"users"."twitter" AS t0_r18,
"users"."failed_attempts" AS t0_r19,
"users"."locked_at" AS t0_r20,
"users"."username" AS t0_r21,
"users"."can_create_group" AS t0_r22,
"users"."can_create_team" AS t0_r23,
"users"."state" AS t0_r24,
"users"."color_scheme_id" AS t0_r25,
"users"."password_expires_at" AS t0_r26,
"users"."created_by_id" AS t0_r27,
"users"."last_credential_check_at" AS t0_r28,
"users"."avatar" AS t0_r29,
"users"."confirmation_token" AS t0_r30,
"users"."confirmed_at" AS t0_r31,
"users"."confirmation_sent_at" AS t0_r32,
"users"."unconfirmed_email" AS t0_r33,
"users"."hide_no_ssh_key" AS t0_r34,
"users"."website_url" AS t0_r35,
"users"."admin_email_unsubscribed_at" AS t0_r36,
"users"."notification_email" AS t0_r37,
"users"."hide_no_password" AS t0_r38,
"users"."password_automatically_set" AS t0_r39,
"users"."location" AS t0_r40,
"users"."encrypted_otp_secret" AS t0_r41,
"users"."encrypted_otp_secret_iv" AS t0_r42,
"users"."encrypted_otp_secret_salt" AS t0_r43,
"users"."otp_required_for_login" AS t0_r44,
"users"."otp_backup_codes" AS t0_r45,
"users"."public_email" AS t0_r46,
"users"."dashboard" AS t0_r47,
"users"."project_view" AS t0_r48,
"users"."consumed_timestep" AS t0_r49,
"users"."layout" AS t0_r50,
"users"."hide_project_limit" AS t0_r51,
"users"."note" AS t0_r52,
"users"."unlock_token" AS t0_r53,
"users"."otp_grace_period_started_at" AS t0_r54,
"users"."external" AS t0_r55,
"users"."incoming_email_token" AS t0_r56,
"users"."organization" AS t0_r57,
"users"."auditor" AS t0_r58,
"users"."require_two_factor_authentication_from_group" AS t0_r59,
"users"."two_factor_grace_period" AS t0_r60,
"users"."last_activity_on" AS t0_r61,
"users"."notified_of_own_activity" AS t0_r62,
"users"."preferred_language" AS t0_r63,
"users"."email_opted_in" AS t0_r64,
"users"."email_opted_in_ip" AS t0_r65,
"users"."email_opted_in_source_id" AS t0_r66,
"users"."email_opted_in_at" AS t0_r67,
"users"."theme_id" AS t0_r68,
"users"."accepted_term_id" AS t0_r69,
"users"."feed_token" AS t0_r70,
"users"."private_profile" AS t0_r71,
"users"."roadmap_layout" AS t0_r72,
"users"."include_private_contributions" AS t0_r73,
"users"."commit_email" AS t0_r74,
"users"."group_view" AS t0_r75,
"users"."managing_group_id" AS t0_r76,
"users"."first_name" AS t0_r77,
"users"."last_name" AS t0_r78,
"users"."static_object_token" AS t0_r79,
"users"."role" AS t0_r80,
"users"."user_type" AS t0_r81,
"user_statuses"."user_id" AS t1_r0,
"user_statuses"."cached_markdown_version" AS t1_r1,
"user_statuses"."emoji" AS t1_r2,
"user_statuses"."message" AS t1_r3,
"user_statuses"."message_html" AS t1_r4
FROM
"users"
LEFT OUTER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id"
WHERE (("users"."name" ILIKE '%test%'
OR "users"."username" ILIKE '%test%')
OR "users"."email" = 'test')
AND "users"."id" IN (
SELECT
"members"."user_id"
FROM ((
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_id" = 9970
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL)
UNION (
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces")
AND "members"."user_id" NOT IN (
SELECT
"users"."id"
FROM
"users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 9970
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL))) members
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."invite_token" IS NULL
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
ORDER BY
"namespaces"."id" DESC))
ORDER BY
CASE WHEN users.name = 'test' THEN
0
WHEN users.username = 'test' THEN
1
WHEN users.email = 'test' THEN
2
ELSE
3
END,
"users"."name" ASC
LIMIT 20 OFFSET 0;Attempt 3
Tried adding a new index based on discussion in thread below Used same SQL as in Attempt 2
exec CREATE INDEX index_non_requested_invited_group_members_on_source_id_and_type ON public.members USING btree (source_id, source_type) WHERE ((requested_at IS NULL) AND (invite_token IS NULL) AND ((type)::text = 'GroupMember'::text));
Session: joe-bsq4nqo350j74np1ug9g
The query has been executed. Duration: 47.535 s (edited) Run 1
Time: 40.857 s
- planning: 5.781 ms
- execution: 40.851 s
- I/O read: 37.466 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 30324 (~236.90 MiB) from the buffer pool
- reads: 210218 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 5715 (~44.60 MiB)
- writes: 0
Temp buffers:
- reads: 2683 (~21.00 MiB)
- writes: 2684 (~21.00 MiB)Run 2
Time: 2.775 s
- planning: 6.688 ms
- execution: 2.768 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 240496 (~1.80 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Temp buffers:
- reads: 2683 (~21.00 MiB)
- writes: 2684 (~21.00 MiB)Attempt 4
Tried the removal/addition of a more global encompassing index as discussed below Used same SQL as in Attempt 2
exec drop index index_members_on_source_id_and_source_type;
Session: joe-bsq4vbo350j74np1uga0
The query has been executed. Duration: 75.000 ms (edited)
exec drop index index_non_requested_project_members_on_source_id_and_type;
Session: joe-bsq4vbo350j74np1uga0
The query has been executed. Duration: 4.295 s (edited)
exec create index index_members_test1 on public.members using btree (source_id, source_type, type, requested_at, invite_token);
Session: joe-bsq4vbo350j74np1uga0
The query has been executed. Duration: 1.180 min (edited) Run 1
Time: 1.325 min
- planning: 5.848 ms
- execution: 1.325 min
- I/O read: 1.251 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 30314 (~236.80 MiB) from the buffer pool
- reads: 210289 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 5715 (~44.60 MiB)
- writes: 0
Temp buffers:
- reads: 2683 (~21.00 MiB)
- writes: 2684 (~21.00 MiB)Run 2
Time: 3.110 s
- planning: 7.192 ms
- execution: 3.102 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 240557 (~1.80 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Temp buffers:
- reads: 2683 (~21.00 MiB)
- writes: 2684 (~21.00 MiB)Attempt 5
- Use GroupsFinder
- no new index
- added testing with a non-admin user
Run with a non-admin user
Expand for SQL
SELECT
"users"."id" AS t0_r0,
"users"."email" AS t0_r1,
"users"."encrypted_password" AS t0_r2,
"users"."reset_password_token" AS t0_r3,
"users"."reset_password_sent_at" AS t0_r4,
"users"."remember_created_at" AS t0_r5,
"users"."sign_in_count" AS t0_r6,
"users"."current_sign_in_at" AS t0_r7,
"users"."last_sign_in_at" AS t0_r8,
"users"."current_sign_in_ip" AS t0_r9,
"users"."last_sign_in_ip" AS t0_r10,
"users"."created_at" AS t0_r11,
"users"."updated_at" AS t0_r12,
"users"."name" AS t0_r13,
"users"."admin" AS t0_r14,
"users"."projects_limit" AS t0_r15,
"users"."skype" AS t0_r16,
"users"."linkedin" AS t0_r17,
"users"."twitter" AS t0_r18,
"users"."failed_attempts" AS t0_r19,
"users"."locked_at" AS t0_r20,
"users"."username" AS t0_r21,
"users"."can_create_group" AS t0_r22,
"users"."can_create_team" AS t0_r23,
"users"."state" AS t0_r24,
"users"."color_scheme_id" AS t0_r25,
"users"."password_expires_at" AS t0_r26,
"users"."created_by_id" AS t0_r27,
"users"."last_credential_check_at" AS t0_r28,
"users"."avatar" AS t0_r29,
"users"."confirmation_token" AS t0_r30,
"users"."confirmed_at" AS t0_r31,
"users"."confirmation_sent_at" AS t0_r32,
"users"."unconfirmed_email" AS t0_r33,
"users"."hide_no_ssh_key" AS t0_r34,
"users"."website_url" AS t0_r35,
"users"."admin_email_unsubscribed_at" AS t0_r36,
"users"."notification_email" AS t0_r37,
"users"."hide_no_password" AS t0_r38,
"users"."password_automatically_set" AS t0_r39,
"users"."location" AS t0_r40,
"users"."encrypted_otp_secret" AS t0_r41,
"users"."encrypted_otp_secret_iv" AS t0_r42,
"users"."encrypted_otp_secret_salt" AS t0_r43,
"users"."otp_required_for_login" AS t0_r44,
"users"."otp_backup_codes" AS t0_r45,
"users"."public_email" AS t0_r46,
"users"."dashboard" AS t0_r47,
"users"."project_view" AS t0_r48,
"users"."consumed_timestep" AS t0_r49,
"users"."layout" AS t0_r50,
"users"."hide_project_limit" AS t0_r51,
"users"."note" AS t0_r52,
"users"."unlock_token" AS t0_r53,
"users"."otp_grace_period_started_at" AS t0_r54,
"users"."external" AS t0_r55,
"users"."incoming_email_token" AS t0_r56,
"users"."organization" AS t0_r57,
"users"."auditor" AS t0_r58,
"users"."require_two_factor_authentication_from_group" AS t0_r59,
"users"."two_factor_grace_period" AS t0_r60,
"users"."last_activity_on" AS t0_r61,
"users"."notified_of_own_activity" AS t0_r62,
"users"."preferred_language" AS t0_r63,
"users"."email_opted_in" AS t0_r64,
"users"."email_opted_in_ip" AS t0_r65,
"users"."email_opted_in_source_id" AS t0_r66,
"users"."email_opted_in_at" AS t0_r67,
"users"."theme_id" AS t0_r68,
"users"."accepted_term_id" AS t0_r69,
"users"."feed_token" AS t0_r70,
"users"."private_profile" AS t0_r71,
"users"."roadmap_layout" AS t0_r72,
"users"."include_private_contributions" AS t0_r73,
"users"."commit_email" AS t0_r74,
"users"."group_view" AS t0_r75,
"users"."managing_group_id" AS t0_r76,
"users"."first_name" AS t0_r77,
"users"."last_name" AS t0_r78,
"users"."static_object_token" AS t0_r79,
"users"."role" AS t0_r80,
"users"."user_type" AS t0_r81,
"user_statuses"."user_id" AS t1_r0,
"user_statuses"."cached_markdown_version" AS t1_r1,
"user_statuses"."emoji" AS t1_r2,
"user_statuses"."message" AS t1_r3,
"user_statuses"."message_html" AS t1_r4
FROM
"users"
LEFT OUTER JOIN "user_statuses" ON "user_statuses"."user_id" = "users"."id"
WHERE (("users"."name" ILIKE '%test%'
OR "users"."username" ILIKE '%test%')
OR "users"."email" = 'test')
AND "users"."external" != TRUE
AND "users"."id" IN (
SELECT
"members"."user_id"
FROM ((
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_id" = 70
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL)
UNION (
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
"id"
FROM
"base_and_ancestors" AS "namespaces")
AND "members"."user_id" NOT IN (
SELECT
"users"."id"
FROM
"users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 70
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL))
UNION (
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = 70)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces")
AND "members"."user_id" NOT IN (
SELECT
"users"."id"
FROM
"users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 70
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL))) members
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."invite_token" IS NULL
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM (( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM ((
SELECT
"namespaces".*
FROM
"namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 47
AND "members"."requested_at" IS NULL)
UNION (
SELECT
namespaces.*
FROM
"projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE
"project_authorizations"."user_id" = 47)) namespaces
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = '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"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 47
AND "members"."requested_at" IS NULL)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces".*
FROM ((
SELECT
"namespaces".*
FROM
"base_and_ancestors" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"base_and_descendants" AS "namespaces"
WHERE
"namespaces"."type" = 'Group')) namespaces
WHERE
"namespaces"."type" = 'Group')
UNION (
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."visibility_level" IN (10, 20))) namespaces
WHERE
"namespaces"."type" = 'Group'))
ORDER BY
CASE WHEN users.name = 'test' THEN
0
WHEN users.username = 'test' THEN
1
WHEN users.email = 'test' THEN
2
ELSE
3
END,
"users"."name" ASC
LIMIT 20 OFFSET 0lRun 1
Time: 1.571 min
- planning: 19.146 ms
- execution: 1.571 min
- I/O read: 1.500 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 37589 (~293.70 MiB) from the buffer pool
- reads: 209297 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 5822 (~45.50 MiB)
- writes: 0Run 2
Time: 1.217 s
- planning: 11.786 ms
- execution: 1.205 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 246735 (~1.90 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Same as above but run with Admin account, the SQL generated changed when run with admin vs. non-admin user. I decided to not test with admin users going forward.
Expand for SQL
SELECT
"users".*
FROM
"users"
WHERE (("users"."name" ILIKE '%test%'
OR "users"."username" ILIKE '%test%')
OR "users"."email" = 'test')
AND "users"."id" IN (
SELECT
"members"."user_id"
FROM ((
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_id" = 67
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL)
UNION (
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 66)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
"id"
FROM
"base_and_ancestors" AS "namespaces")
AND "members"."user_id" NOT IN (
SELECT
"users"."id"
FROM
"users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 67
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL))
UNION (
SELECT
"members".*
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = 67)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces")
AND "members"."user_id" NOT IN (
SELECT
"users"."id"
FROM
"users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 67
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL))) members
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."invite_token" IS NULL
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'))
ORDER BY
CASE WHEN users.name = 'test' THEN
0
WHEN users.username = 'test' THEN
1
WHEN users.email = 'test' THEN
2
ELSE
3
END,
"users"."name" ASCRun 1
Time: 9.930 s
- planning: 6.239 ms
- execution: 9.924 s
- I/O read: 9.826 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 15354 (~120.00 MiB) from the buffer pool
- reads: 4851 (~37.90 MiB) from the OS file cache, including disk I/O
- dirtied: 125 (~1000.00 KiB)
- writes: 0Run 2
Time: 31.010 ms
- planning: 5.628 ms
- execution: 25.382 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 20148 (~157.40 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Attempt 6
- Use GroupMemberFinder and add search params to the finder instead of running super
- No new indexes
- Tested with non-admin user
Expand for SQL
SELECT
“users”.“id” AS t0_r0,
“users”.“email” AS t0_r1,
“users”.“encrypted_password” AS t0_r2,
“users”.“reset_password_token” AS t0_r3,
“users”.“reset_password_sent_at” AS t0_r4,
“users”.“remember_created_at” AS t0_r5,
“users”.“sign_in_count” AS t0_r6,
“users”.“current_sign_in_at” AS t0_r7,
“users”.“last_sign_in_at” AS t0_r8,
“users”.“current_sign_in_ip” AS t0_r9,
“users”.“last_sign_in_ip” AS t0_r10,
“users”.“created_at” AS t0_r11,
“users”.“updated_at” AS t0_r12,
“users”.“name” AS t0_r13,
“users”.“admin” AS t0_r14,
“users”.“projects_limit” AS t0_r15,
“users”.“skype” AS t0_r16,
“users”.“linkedin” AS t0_r17,
“users”.“twitter” AS t0_r18,
“users”.“failed_attempts” AS t0_r19,
“users”.“locked_at” AS t0_r20,
“users”.“username” AS t0_r21,
“users”.“can_create_group” AS t0_r22,
“users”.“can_create_team” AS t0_r23,
“users”.“state” AS t0_r24,
“users”.“color_scheme_id” AS t0_r25,
“users”.“password_expires_at” AS t0_r26,
“users”.“created_by_id” AS t0_r27,
“users”.“last_credential_check_at” AS t0_r28,
“users”.“avatar” AS t0_r29,
“users”.“confirmation_token” AS t0_r30,
“users”.“confirmed_at” AS t0_r31,
“users”.“confirmation_sent_at” AS t0_r32,
“users”.“unconfirmed_email” AS t0_r33,
“users”.“hide_no_ssh_key” AS t0_r34,
“users”.“website_url” AS t0_r35,
“users”.“admin_email_unsubscribed_at” AS t0_r36,
“users”.“notification_email” AS t0_r37,
“users”.“hide_no_password” AS t0_r38,
“users”.“password_automatically_set” AS t0_r39,
“users”.“location” AS t0_r40,
“users”.“encrypted_otp_secret” AS t0_r41,
“users”.“encrypted_otp_secret_iv” AS t0_r42,
“users”.“encrypted_otp_secret_salt” AS t0_r43,
“users”.“otp_required_for_login” AS t0_r44,
“users”.“otp_backup_codes” AS t0_r45,
“users”.“public_email” AS t0_r46,
“users”.“dashboard” AS t0_r47,
“users”.“project_view” AS t0_r48,
“users”.“consumed_timestep” AS t0_r49,
“users”.“layout” AS t0_r50,
“users”.“hide_project_limit” AS t0_r51,
“users”.“note” AS t0_r52,
“users”.“unlock_token” AS t0_r53,
“users”.“otp_grace_period_started_at” AS t0_r54,
“users”.“external” AS t0_r55,
“users”.“incoming_email_token” AS t0_r56,
“users”.“organization” AS t0_r57,
“users”.“auditor” AS t0_r58,
“users”.“require_two_factor_authentication_from_group” AS t0_r59,
“users”.“two_factor_grace_period” AS t0_r60,
“users”.“last_activity_on” AS t0_r61,
“users”.“notified_of_own_activity” AS t0_r62,
“users”.“preferred_language” AS t0_r63,
“users”.“email_opted_in” AS t0_r64,
“users”.“email_opted_in_ip” AS t0_r65,
“users”.“email_opted_in_source_id” AS t0_r66,
“users”.“email_opted_in_at” AS t0_r67,
“users”.“theme_id” AS t0_r68,
“users”.“accepted_term_id” AS t0_r69,
“users”.“feed_token” AS t0_r70,
“users”.“private_profile” AS t0_r71,
“users”.“roadmap_layout” AS t0_r72,
“users”.“include_private_contributions” AS t0_r73,
“users”.“commit_email” AS t0_r74,
“users”.“group_view” AS t0_r75,
“users”.“managing_group_id” AS t0_r76,
“users”.“first_name” AS t0_r77,
“users”.“last_name” AS t0_r78,
“users”.“static_object_token” AS t0_r79,
“users”.“role” AS t0_r80,
“users”.“user_type” AS t0_r81,
“user_statuses”.“user_id” AS t1_r0,
“user_statuses”.“cached_markdown_version” AS t1_r1,
“user_statuses”.“emoji” AS t1_r2,
“user_statuses”.“message” AS t1_r3,
“user_statuses”.“message_html” AS t1_r4
FROM
“users”
LEFT OUTER JOIN “user_statuses” ON “user_statuses”.“user_id” = “users”.“id”
WHERE
“users”.“id” IN (
SELECT
“members”.“user_id”
FROM ((
SELECT
“members”. *
FROM
“members”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_id” = 9970
AND “members”.“source_type” = ‘Namespace’
AND “members”.“requested_at” IS NULL)
UNION (
SELECT
“members”. *
FROM
“members”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “members”.“requested_at” IS NULL
AND “members”.“source_id” IN ( WITH RECURSIVE “base_and_descendants” AS (
(
SELECT
“namespaces”. *
FROM
“namespaces”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“parent_id” = 9970)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_descendants”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“parent_id” = “base_and_descendants”.“id”))
SELECT
“id”
FROM
“base_and_descendants” AS “namespaces”)
AND “members”.“user_id” NOT IN (
SELECT
“users”.“id”
FROM
“users”
INNER JOIN “members” ON “users”.“id” = “members”.“user_id”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “members”.“source_id” = 9970
AND “members”.“source_type” = ‘Namespace’
AND “members”.“requested_at” IS NULL))) members
INNER JOIN “users” ON “users”.“id” = “members”.“user_id”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND ((“users”.“name” ILIKE ‘%test%’
OR “users”.“username” ILIKE ‘%test%’)
OR “users”.“email” = ‘test’)
AND “members”.“invite_token” IS NULL
AND “members”.“source_id” IN (
SELECT
“namespaces”.“id”
FROM (( WITH RECURSIVE “base_and_ancestors” AS (
(
SELECT
“namespaces”. *
FROM ((
SELECT
“namespaces”. *
FROM
“namespaces”
INNER JOIN “members” ON “namespaces”.“id” = “members”.“source_id”
WHERE
“members”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “namespaces”.“type” = ‘Group’
AND “members”.“user_id” = 47
AND “members”.“requested_at” IS NULL)
UNION (
SELECT
namespaces.*
FROM
“projects”
INNER JOIN “project_authorizations” ON “projects”.“id” = “project_authorizations”.“project_id”
INNER JOIN “namespaces” ON “namespaces”.“id” = “projects”.“namespace_id”
WHERE
“project_authorizations”.“user_id” = 47)) namespaces
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_ancestors”
WHERE
“namespaces”.“type” = ‘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”.“type” = ‘GroupMember’
AND “members”.“source_type” = ‘Namespace’
AND “namespaces”.“type” = ‘Group’
AND “members”.“user_id” = 47
AND “members”.“requested_at” IS NULL)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”,
“base_and_descendants”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“parent_id” = “base_and_descendants”.“id”))
SELECT
“namespaces”. *
FROM ((
SELECT
“namespaces”. *
FROM
“base_and_ancestors” AS “namespaces”
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“base_and_descendants” AS “namespaces”
WHERE
“namespaces”.“type” = ‘Group’)) namespaces
WHERE
“namespaces”.“type” = ‘Group’)
UNION (
SELECT
“namespaces”. *
FROM
“namespaces”
WHERE
“namespaces”.“type” = ‘Group’
AND “namespaces”.“visibility_level” IN (10, 20))) namespaces
WHERE
“namespaces”.“type” = ‘Group’)
ORDER BY
CASE WHEN users.name = ‘test’ THEN
0
WHEN users.username = ‘test’ THEN
1
WHEN users.email = ‘test’ THEN
2
ELSE
3
END,
“users”.“name” ASC)
LIMIT 20 OFFSET 0;Run 1
Time: 55.759 s
- planning: 13.617 ms
- execution: 55.746 s
- I/O read: 51.557 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 44998 (~351.50 MiB) from the buffer pool
- reads: 210366 (~1.60 GiB) from the OS file cache, including disk I/O
- dirtied: 5833 (~45.60 MiB)
- writes: 0Run 2
Time: 1.916 s
- planning: 10.935 ms
- execution: 1.905 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 255213 (~1.90 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Screenshots
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry - [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Edited by Terri Chu