Skip to content

Async create index to support counting group members

Felipe Cardozo requested to merge issue_423513-add_support_index into master

What does this MR do and why?

Schedule creation of index index_members_on_source_and_type_and_id to support batch counting group members. The code to count group members will be merged after this one with !142060 (merged). For more information check !142060 (comment 1740287862).

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Query plans

The index is created to support the queries like the following:

SELECT   Row_number() OVER (ORDER BY "members"."id") + 0           AS count,
         Last_value("members"."id") OVER (ORDER BY "members"."id") AS id
FROM     "members"
WHERE    "members"."type" = 'GroupMember'
AND      "members"."source_type" = 'Namespace'
AND      "members"."invite_token" IS NULL
AND      "members"."source_id" = 9970 limit 1 offset 4999

Query plan(cold run): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/25711/commands/81136

Migration output

main: == [advisory_lock_connection] object_id: 111260, pg_backend_pid: 109893
main: == 20240124171424 CreateAsyncIndexMembersOnSourceAndTypeAndId: migrating ======
main: -- index_exists?(:members, [:source_id, :source_type, :type, :id], {:where=>"invite_token IS NULL", :name=>"index_members_on_source_and_type_and_id", :algorithm=>:concurrently})
main:    -> 0.0077s
main: -- add_index_options(:members, [:source_id, :source_type, :type, :id], {:where=>"invite_token IS NULL", :name=>"index_members_on_source_and_type_and_id", :algorithm=>:concurrently})
main:    -> 0.0002s
main: == 20240124171424 CreateAsyncIndexMembersOnSourceAndTypeAndId: migrated (0.0500s) 

main: == [advisory_lock_connection] object_id: 111260, pg_backend_pid: 109893
ci: == [advisory_lock_connection] object_id: 111540, pg_backend_pid: 109895
ci: == 20240124171424 CreateAsyncIndexMembersOnSourceAndTypeAndId: migrating ======
ci: -- index_exists?(:members, [:source_id, :source_type, :type, :id], {:where=>"invite_token IS NULL", :name=>"index_members_on_source_and_type_and_id", :algorithm=>:concurrently})
ci:    -> 0.0084s
ci: -- add_index_options(:members, [:source_id, :source_type, :type, :id], {:where=>"invite_token IS NULL", :name=>"index_members_on_source_and_type_and_id", :algorithm=>:concurrently})
ci:    -> 0.0001s
ci: == 20240124171424 CreateAsyncIndexMembersOnSourceAndTypeAndId: migrated (0.0236s
Edited by Felipe Cardozo

Merge request reports