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

Merge request reports

Loading