Skip to content

Allow sorting of the Incident SLA column

Tristan Read requested to merge tr-sort-sla-column into master

What does this MR do?

This MR makes the incident SLA column sortable.

issue: #270390 (closed)

related: #241663 (closed)

database performance

New scopes: Issue.order_sla_due_at_asc / Issue.order_sla_due_at_desc

On 50,000 issues in local GDK (limited to 100 results) - not tested on database-lab as this is a new table added in 13.5 that has limited data so far:

SELECT "issues"."id" AS t0_r0, "issues"."title" AS t0_r1, "issues"."author_id" AS t0_r2, "issues"."project_id" AS t0_r3, "issues"."created_at" AS t0_r4, "issues"."updated_at" AS t0_r5, "issues"."description" AS t0_r6, "issues"."milestone_id" AS t0_r7, "issues"."iid" AS t0_r8, "issues"."updated_by_id" AS t0_r9, "issues"."weight" AS t0_r10, "issues"."confidential" AS t0_r11, "issues"."due_date" AS t0_r12, "issues"."moved_to_id" AS t0_r13, "issues"."lock_version" AS t0_r14, "issues"."title_html" AS t0_r15, "issues"."description_html" AS t0_r16, "issues"."time_estimate" AS t0_r17, "issues"."relative_position" AS t0_r18, "issues"."service_desk_reply_to" AS t0_r19, "issues"."cached_markdown_version" AS t0_r20, "issues"."last_edited_at" AS t0_r21, "issues"."last_edited_by_id" AS t0_r22, "issues"."discussion_locked" AS t0_r23, "issues"."closed_at" AS t0_r24, "issues"."closed_by_id" AS t0_r25, "issues"."state_id" AS t0_r26, "issues"."duplicated_to_id" AS t0_r27, "issues"."promoted_to_epic_id" AS t0_r28, "issues"."health_status" AS t0_r29, "issues"."external_key" AS t0_r30, "issues"."sprint_id" AS t0_r31, "issues"."issue_type" AS t0_r32, "issues"."blocking_issues_count" AS t0_r33, "issuable_slas"."id" AS t1_r0, "issuable_slas"."issue_id" AS t1_r1, "issuable_slas"."due_at" AS t1_r2 FROM "issues" LEFT OUTER JOIN "issuable_slas" ON "issuable_slas"."issue_id" = "issues"."id" WHERE "issues"."project_id" = 1 AND "issues"."issue_type" = 1 ORDER BY issuable_slas.due_at ASC NULLS LAST, "issues"."id" DESC LIMIT 100

which when you clean it up is essentially:

SELECT
    "issues".id,
    "issuable_slas".*
FROM
    "issues"
    LEFT OUTER JOIN "issuable_slas" ON "issuable_slas"."issue_id" = "issues"."id"
WHERE
    "issues"."project_id" = 1
    AND "issues"."issue_type" = 1
ORDER BY
    issuable_slas.due_at ASC NULLS LAST,
    "issues"."id" ASC NULLS FIRST
LIMIT 100

Explain: https://explain.depesz.com/s/Rzax

Because Issuable Sla is an optional relation, and we want to return Incidents regardless, we have to use a LEFT JOIN. Unfortunately this can be slow on large data sets, but because the way we are using this is via GraphQL, and we [limit to 100 records], I think we the performance impact is acceptable.

Note, we had the same issue when sorting by Severities and we accepted the performance hit due to the limiting of records being returned. (!42800 (comment 417063999)).

Setup: Creating many issuable SLAs locally This will create 50,000 new issues and related Issuable SLAs, which will either be 1 hour from now or 1 hour in the past. 
project = Project.first
author = project.users.first

50_000.times do |i|
  issue = Issue.create!(project: project, author: author, title: "Incident #{i}")
  IssuableSla.create!(issue: issue, due_at: [-1, 1].sample.hours.from_now)
end

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability 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 Krasimir Angelov

Merge request reports