Skip to content

Add index for issues on relative position, project, and state for manual sorting

Brett Walker requested to merge bw-add-index-for-relative-position into master

What does this MR do?

When querying for issues sorted by relative_position (https://gitlab.com/gitlab-org/gitlab-ce/blob/master/app/models/issue.rb#L61 and https://gitlab.com/gitlab-org/gitlab-ce/blob/master/app/models/issue.rb#L137) the query times out when there are thousands of issues.

From an EXPLAIN on production:

/chatops run explain SELECT  "issues".* FROM "issues" WHERE "issues"."project_id" = 13083 AND ("issues"."state" IN ('opened')) ORDER BY "issues"."relative_position" ASC LIMIT 20 OFFSET 0

Limit  (cost=0.44..3494.26 rows=20 width=767) (actual time=7828.123..7861.635 rows=20 loops=1)
  Buffers: shared hit=2053167
  ->  Index Scan using index_issues_on_relative_position on issues  (cost=0.44..2657578.28 rows=15213 width=767) (actual time=7828.122..7861.621 rows=20 loops=1)
        Filter: ((project_id = 13083) AND ((state)::text = 'opened'::text))
        Rows Removed by Filter: 2055358
        Buffers: shared hit=2053167
Planning time: 5.385 ms
Execution time: 7861.680 ms

A similar query sorted by created_at gives

/chatops run explain SELECT  "issues".* FROM "issues" WHERE "issues"."project_id" = 13083 AND ("issues"."state" IN ('opened')) ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 20 OFFSET 0

Limit  (cost=0.56..29.66 rows=20 width=767) (actual time=0.129..0.672 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan Backward using index_issues_on_project_id_and_created_at_and_id_and_state on issues  (cost=0.56..22133.28 rows=15213 width=767) (actual time=0.129..0.668 rows=20 loops=1)
        Index Cond: ((project_id = 13083) AND ((state)::text = 'opened'::text))
        Buffers: shared hit=24
Planning time: 6.125 ms
Execution time: 0.713 ms

While there is an index on relative_position, there is not one that include the project_id and state

I've also added id to the index, as I've changed the scope from

  scope :order_relative_position_asc, -> { reorder(::Gitlab::Database.nulls_last_order('relative_position', 'ASC')) }

to

  scope :order_relative_position_asc, -> { reorder(::Gitlab::Database.nulls_last_order('relative_position', 'ASC'), 'id DESC') }

as similar scopes use, and it also guarantees a consistent order.

Related Issue: https://gitlab.com/gitlab-org/gitlab-ce/issues/62178

Does this MR meet the acceptance criteria?

Conformity

Performance and testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • 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 Brett Walker

Merge request reports