Async create index to support counting group members
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).
- issue to create index synchronously: #439262 (closed).
- related to #423513 (closed)
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