Async namespace_id based index for issue lists
What does this MR do and why?
This creates the indexes asynchronously for GitLab.com. This index will be
used to replace existing project_id based indexes.
This index improves the performance of our group-level work items lists and also prepares us when we switch the project-level list to use namespace_id.
Local testing of bundle exec rails gitlab:db:execute_async_index_operations:all
gitlabhq_development=# \d index_issues_on_namespace_id_created_at_id_state_id
Index "public.index_issues_on_namespace_id_created_at_id_state_id"
Column | Type | Key? | Definition
--------------+-----------------------------+------+--------------
namespace_id | bigint | yes | namespace_id
created_at | timestamp without time zone | yes | created_at
id | bigint | yes | id
state_id | smallint | yes | state_id
btree, for table "public.issues"
gitlabhq_development=# \d index_issues_on_work_item_type_id_namespace_id_created_at_state
Index "public.index_issues_on_work_item_type_id_namespace_id_created_at_state"
Column | Type | Key? | Definition
-------------------+-----------------------------+------+-------------------
work_item_type_id | bigint | yes | work_item_type_id
namespace_id | bigint | yes | namespace_id
created_at | timestamp without time zone | yes | created_at
state_id | smallint | yes | state_id
btree, for table "public.issues"
gitlabhq_development=# \d index_issues_on_namespace_id_relative_position_id_state_id
Index "public.index_issues_on_namespace_id_relative_position_id_state_id"
Column | Type | Key? | Definition
-------------------+----------+------+-------------------
namespace_id | bigint | yes | namespace_id
relative_position | integer | yes | relative_position
id | bigint | yes | id
state_id | smallint | yes | state_id
btree, for table "public.issues"
gitlabhq_development=# \d index_open_issues_on_namespace_id_confidential_author_id_id
Index "public.index_open_issues_on_namespace_id_confidential_author_id_id"
Column | Type | Key? | Definition
--------------+---------+------+--------------
namespace_id | bigint | yes | namespace_id
confidential | boolean | yes | confidential
author_id | bigint | yes | author_id
id | bigint | yes | id
btree, for table "public.issues", predicate (state_id = 1)
gitlabhq_development=# \d index_closed_incidents_on_namespace_id_closed_at
Index "public.index_closed_incidents_on_namespace_id_closed_at"
Column | Type | Key? | Definition
--------------+--------------------------+------+--------------
namespace_id | bigint | yes | namespace_id
closed_at | timestamp with time zone | yes | closed_at
btree, for table "public.issues", predicate (state_id = 2 AND work_item_type_id = 2)
References
Related to #536350 (closed), #536353 (closed)
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Edited by Heinrich Lee Yu