Skip to content

Add index for sorting issues by health status

Nicolas Dular requested to merge nd/add-health-status-order-index into master

What does this MR do and why?

This adds two indexes for sorting issues based on their health status. We need two indexes as we sort based on NULLS LAST, therefore we can't just use the reverse of a DESC or ASC-only index.

Note: The generated ASC index has no ORDER BY ASC NULLS LAST in the generated index because it's the default

By default, B-tree indexes store their entries in ascending order with nulls last ...

We need to perform an async migration as it's a high-traffic table

Related MR where we introduced ordering: !101529 (merged)

Database Info

Migration 
r db:migrate
main: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: migrating ============
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_desc_order", :algorithm=>:concurrently})
main:    -> 0.0082s
main: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_desc_order", :algorithm=>:concurrently})
main:    -> 0.0001s
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_asc_order", :algorithm=>:concurrently})
main:    -> 0.0080s
main: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_asc_order", :algorithm=>:concurrently})
main:    -> 0.0000s
main: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: migrated (0.0278s) ===

ci: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: migrating ============
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_desc_order", :algorithm=>:concurrently})
ci:    -> 0.0085s
ci: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_desc_order", :algorithm=>:concurrently})
ci:    -> 0.0001s
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_asc_order", :algorithm=>:concurrently})
ci:    -> 0.0074s
ci: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :issue_type], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"index_on_issues_health_status_asc_order", :algorithm=>:concurrently})
ci:    -> 0.0000s
ci: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: migrated (0.0201s) ===
Rollback 
r db:rollback:main db:rollback:ci
main: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: reverting ============
main: -- index_name(:issues, "index_on_issues_health_status_desc_order")
main:    -> 0.0000s
main: -- index_name(:issues, "index_on_issues_health_status_asc_order")
main:    -> 0.0000s
main: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: reverted (0.0069s) ===

ci: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: reverting ============
ci: -- index_name(:issues, "index_on_issues_health_status_desc_order")
ci:    -> 0.0000s
ci: -- index_name(:issues, "index_on_issues_health_status_asc_order")
ci:    -> 0.0000s
ci: == 20221115120602 AddIndexForIssuesHealthStatusOrdering: reverted (0.0029s) ===
Resulting index 
 \d index_on_issues_health_status_desc_order
Index "public.index_on_issues_health_status_desc_order"
    Column     |   Type   | Key? |  Definition
---------------+----------+------+---------------
 project_id    | integer  | yes  | project_id
 health_status | smallint | yes  | health_status
 id            | integer  | yes  | id
 state_id      | smallint | yes  | state_id
 issue_type    | smallint | yes  | issue_type
btree, for table "public.issues"
\d index_on_issues_health_status_asc_order
Index "public.index_on_issues_health_status_asc_order"
    Column     |   Type   | Key? |  Definition
---------------+----------+------+---------------
 project_id    | integer  | yes  | project_id
 health_status | smallint | yes  | health_status
 id            | integer  | yes  | id
 state_id      | smallint | yes  | state_id
 issue_type    | smallint | yes  | issue_type
btree, for table "public.issues"
\d issues
"index_on_issues_health_status_asc_order" btree (project_id, health_status, id DESC, state_id, issue_type)
"index_on_issues_health_status_desc_order" btree (project_id, health_status DESC NULLS LAST, id DESC, state_id, issue_type)
# structure.sql changes once migration ran

CREATE INDEX index_on_issues_health_status_asc_order ON issues USING btree (project_id, health_status, id DESC, state_id, issue_type);
CREATE INDEX index_on_issues_health_status_desc_order ON issues USING btree (project_id, health_status DESC NULLS LAST, id DESC, state_id, issue_type);

Before

After

Statistics from index creation on postgres.ai: The query has been executed. Duration: 48.423 min (timing for each separately)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Nicolas Dular

Merge request reports