Skip to content

Part 1: Usage data related scopes use the work_item_types table

Mario Celi requested to merge 410225-usage-data-scopes into master

What does this MR do and why?

First MR as result of splitting the larger !120520 (closed)

The index in this MR takes over 40 minutes to be created in .com, so it was already scheduled to be created over the weekend in !121092 (merged)

Database review

Query plans

I have posted the query plan links in each line of the diff so it's clear where the query is coming from. The link itself already contains a formatted version of the query.

Migration output

UP

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 275740, pg_backend_pid: 70813
main: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.2462s
main: -- index_exists?(:issues, [:work_item_type_id, :project_id, :created_at, :state_id], {:name=>"index_issues_on_work_item_type_id_project_id_created_at_state", :algorithm=>:concurrently})
main:    -> 0.0314s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:issues, [:work_item_type_id, :project_id, :created_at, :state_id], {:name=>"index_issues_on_work_item_type_id_project_id_created_at_state", :algorithm=>:concurrently})
main:    -> 0.0124s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: migrated (0.3179s) ===

main: == [advisory_lock_connection] object_id: 275740, pg_backend_pid: 70813
ci: == [advisory_lock_connection] object_id: 276040, pg_backend_pid: 70815
ci: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0009s
ci: -- index_exists?(:issues, [:work_item_type_id, :project_id, :created_at, :state_id], {:name=>"index_issues_on_work_item_type_id_project_id_created_at_state", :algorithm=>:concurrently})
ci:    -> 0.0483s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0006s
ci: -- add_index(:issues, [:work_item_type_id, :project_id, :created_at, :state_id], {:name=>"index_issues_on_work_item_type_id_project_id_created_at_state", :algorithm=>:concurrently})
ci:    -> 0.0128s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: migrated (0.0947s) ===

ci: == [advisory_lock_connection] object_id: 276040, pg_backend_pid: 70815

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 275720, pg_backend_pid: 7326
main: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: migrating ==================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1902s
main: -- indexes(:issues)
main:    -> 0.0326s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_work_item_type_id"})
main:    -> 0.0074s
main: -- execute("RESET statement_timeout")
main:    -> 0.0011s
main: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: migrated (0.2586s) =========

main: == [advisory_lock_connection] object_id: 275720, pg_backend_pid: 7326
ci: == [advisory_lock_connection] object_id: 275940, pg_backend_pid: 7328
ci: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: migrating ==================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0010s
ci: -- indexes(:issues)
ci:    -> 0.0435s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_work_item_type_id"})
ci:    -> 0.0083s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0008s
ci: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: migrated (0.0844s) =========

ci: == [advisory_lock_connection] object_id: 275940, pg_backend_pid: 7328

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 227980, pg_backend_pid: 11875
main: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: migrating
main: -- transaction_open?()
main:    -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1301s
main: -- index_exists?(:alert_management_alerts, [:created_at, :project_id], {:where=>"issue_id IS NOT NULL", :name=>"idx_alert_management_alerts_on_created_at_project_id_with_issue", :algorithm=>:concurrently})
main:    -> 0.0077s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- add_index(:alert_management_alerts, [:created_at, :project_id], {:where=>"issue_id IS NOT NULL", :name=>"idx_alert_management_alerts_on_created_at_project_id_with_issue", :algorithm=>:concurrently})
main:    -> 0.0072s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: migrated (0.1734s)

main: == [advisory_lock_connection] object_id: 227980, pg_backend_pid: 11875
ci: == [advisory_lock_connection] object_id: 228200, pg_backend_pid: 11877
ci: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0012s
ci: -- index_exists?(:alert_management_alerts, [:created_at, :project_id], {:where=>"issue_id IS NOT NULL", :name=>"idx_alert_management_alerts_on_created_at_project_id_with_issue", :algorithm=>:concurrently})
ci:    -> 0.0079s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- add_index(:alert_management_alerts, [:created_at, :project_id], {:where=>"issue_id IS NOT NULL", :name=>"idx_alert_management_alerts_on_created_at_project_id_with_issue", :algorithm=>:concurrently})
ci:    -> 0.0076s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0006s
ci: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: migrated (0.0439s)

ci: == [advisory_lock_connection] object_id: 228200, pg_backend_pid: 11877

DOWN

bin/rails db:rollback:main db:rollback:ci
main: == [advisory_lock_connection] object_id: 275540, pg_backend_pid: 73710
main: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.2921s
main: -- indexes(:issues)
main:    -> 0.0293s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_work_item_type_id_project_id_created_at_state"})
main:    -> 0.0039s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: reverted (0.3528s) ===

main: == [advisory_lock_connection] object_id: 275540, pg_backend_pid: 73710
ci: == [advisory_lock_connection] object_id: 311780, pg_backend_pid: 73962
ci: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: reverting ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0018s
ci: -- indexes(:issues)
ci:    -> 0.0649s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0008s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_work_item_type_id_project_id_created_at_state"})
ci:    -> 0.0058s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0011s
ci: == 20230516192543 AddIssuesWorkItemTypeIdProjectIdIndex: reverted (0.1252s) ===

ci: == [advisory_lock_connection] object_id: 311780, pg_backend_pid: 73962

bin/rails db:rollback:main db:rollback:ci
main: == [advisory_lock_connection] object_id: 275520, pg_backend_pid: 7962
main: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: reverting ==================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1194s
main: -- index_exists?(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
main:    -> 0.0286s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
main:    -> 0.0111s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: reverted (0.1867s) =========

main: == [advisory_lock_connection] object_id: 275520, pg_backend_pid: 7962
ci: == [advisory_lock_connection] object_id: 311500, pg_backend_pid: 8208
ci: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: reverting ==================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0014s
ci: -- index_exists?(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
ci:    -> 0.0597s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0007s
ci: -- add_index(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
ci:    -> 0.0083s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0008s
ci: == 20230531165731 DropIndexIssuesOnWorkItemTypeId: reverted (0.1051s) =========

ci: == [advisory_lock_connection] object_id: 311500, pg_backend_pid: 8208

bin/rails db:rollback:main db:rollback:ci
main: == [advisory_lock_connection] object_id: 227740, pg_backend_pid: 12325
main: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: reverting
main: -- transaction_open?()
main:    -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1188s
main: -- indexes(:alert_management_alerts)
main:    -> 0.0096s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:alert_management_alerts, {:algorithm=>:concurrently, :name=>"idx_alert_management_alerts_on_created_at_project_id_with_issue"})
main:    -> 0.0029s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: reverted (0.1554s)

main: == [advisory_lock_connection] object_id: 227740, pg_backend_pid: 12325
ci: == [advisory_lock_connection] object_id: 241180, pg_backend_pid: 12576
ci: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0011s
ci: -- indexes(:alert_management_alerts)
ci:    -> 0.0105s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0010s
ci: -- remove_index(:alert_management_alerts, {:algorithm=>:concurrently, :name=>"idx_alert_management_alerts_on_created_at_project_id_with_issue"})
ci:    -> 0.0062s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0008s
ci: == 20230606193037 CreateIndexAlertManagementAlertsOnCreatedAtProjectIdWithIssue: reverted (0.0461s)

ci: == [advisory_lock_connection] object_id: 241180, pg_backend_pid: 12576

MR acceptance checklist

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

Related to #410225 (closed)

Edited by Mario Celi

Merge request reports