Add index for issues on relative position, project, and state for manual sorting
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
-
Changelog entry - no changelog needed since the feature this supports is still behind a feature flag - [-] Documentation created/updated or follow-up review issue created
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Performance and testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers
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