Skip to content

Add a new index "index_issues_on_project_id_and_state_id_and_created_at_and_id"

euko requested to merge 337400-create-new-index-on-issues-1 into master

What does this MR do?

Related to #337400 (closed).

This MR adds a new index on issues to potentially replace an existing index.

Which existing index is problematic?

The following existing index on issues has state_id column after id.

"idx_issues_on_project_id_and_created_at_and_id_and_state_id" btree (project_id, created_at, id, state_id)

Our domain logic requires querying issues by state_id most of the time (e.g., users usually check for open issues then apply filters like dates) and we would expect having state_id come before created_at would result in a more efficient index traversal.

Query plan comparisons

project issues list query (plain / no keyset pagination):

project issues list query (keyset pagination without UNION optimization):

project issues list query (keyset pagination with UNION optimization):

group-level issues query (plain / no keyset pagination):

The query continues to use index_issue_on_project_id_state_id_and_blocking_issues_count in both cases.

group-level issues query (keyset pagination without UNION optimization):

The query continues to use index_issue_on_project_id_state_id_and_blocking_issues_count in both cases.

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • 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 euko

Merge request reports