Drop issues.issue_type related indexes (!127266) · Merge requests · GitLab.org / GitLab

Mario Celi requested to merge 416949-drop-indexes into master Jul 21, 2023

What does this MR do and why?

We are dropping the issues.issue_type column, but we are dropping the indexes separately to deploy these changes in two parts for .com also to write simpler migrations

Database review

We should not be using these indexes anymore in production in favor of the new ones that use the work_item_type_id column. Here are some Thanos queries showing when these stopped being used (index_issues_on_incident_issue_type wasn't used more than 5 weeks ago)

Migration output

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 224060, pg_backend_pid: 75371
main: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0971s
main: -- indexes(:issues)
main:    -> 0.0282s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_project_id_and_created_at_issue_type_incident"})
main:    -> 0.0034s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: migrated (0.1499s)

main: == [advisory_lock_connection] object_id: 224060, pg_backend_pid: 75371
ci: == [advisory_lock_connection] object_id: 224280, pg_backend_pid: 75373
ci: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0010s
ci: -- indexes(:issues)
ci:    -> 0.0284s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_project_id_and_created_at_issue_type_incident"})
ci:    -> 0.0035s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: migrated (0.0582s)

ci: == [advisory_lock_connection] object_id: 224280, pg_backend_pid: 75373
main: == [advisory_lock_connection] object_id: 224440, pg_backend_pid: 75376
main: == 20230721194757 DropIndexIssuesOnIncidentIssueType: migrating ===============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- indexes(:issues)
main:    -> 0.0297s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_incident_issue_type"})
main:    -> 0.0033s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230721194757 DropIndexIssuesOnIncidentIssueType: migrated (0.0483s) ======

main: == [advisory_lock_connection] object_id: 224440, pg_backend_pid: 75376
ci: == [advisory_lock_connection] object_id: 224680, pg_backend_pid: 75378
ci: == 20230721194757 DropIndexIssuesOnIncidentIssueType: migrating ===============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0011s
ci: -- indexes(:issues)
ci:    -> 0.0306s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_incident_issue_type"})
ci:    -> 0.0035s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20230721194757 DropIndexIssuesOnIncidentIssueType: migrated (0.0613s) ======

ci: == [advisory_lock_connection] object_id: 224680, pg_backend_pid: 75378
main: == [advisory_lock_connection] object_id: 224860, pg_backend_pid: 75381
main: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0015s
main: -- indexes(:issues)
main:    -> 0.0297s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_on_issues_closed_incidents_by_project_id_and_closed_at"})
main:    -> 0.0047s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: migrated (0.0540s)

main: == [advisory_lock_connection] object_id: 224860, pg_backend_pid: 75381
ci: == [advisory_lock_connection] object_id: 248740, pg_backend_pid: 75383
ci: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0009s
ci: -- indexes(:issues)
ci:    -> 0.0290s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_on_issues_closed_incidents_by_project_id_and_closed_at"})
ci:    -> 0.0040s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: migrated (0.0615s)

ci: == [advisory_lock_connection] object_id: 248740, pg_backend_pid: 75383
main: == [advisory_lock_connection] object_id: 294640, pg_backend_pid: 75386
main: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0011s
main: -- indexes(:issues)
main:    -> 0.0289s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_on_issues_health_status_asc_order"})
main:    -> 0.0025s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: migrated (0.0508s) ===

main: == [advisory_lock_connection] object_id: 294640, pg_backend_pid: 75386
ci: == [advisory_lock_connection] object_id: 328360, pg_backend_pid: 75388
ci: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0022s
ci: -- indexes(:issues)
ci:    -> 0.0949s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0024s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_on_issues_health_status_asc_order"})
ci:    -> 0.0056s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0028s
ci: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: migrated (0.1552s) ===

ci: == [advisory_lock_connection] object_id: 328360, pg_backend_pid: 75388
main: == [advisory_lock_connection] object_id: 328540, pg_backend_pid: 75391
main: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: migrating ===========
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0010s
main: -- indexes(:issues)
main:    -> 0.0303s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_on_issues_health_status_desc_order"})
main:    -> 0.0046s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: migrated (0.0521s) ==

main: == [advisory_lock_connection] object_id: 328540, pg_backend_pid: 75391
ci: == [advisory_lock_connection] object_id: 361340, pg_backend_pid: 75395
ci: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: migrating ===========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0015s
ci: -- indexes(:issues)
ci:    -> 0.0300s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_on_issues_health_status_desc_order"})
ci:    -> 0.0034s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: migrated (0.0648s) ==

ci: == [advisory_lock_connection] object_id: 361340, pg_backend_pid: 75395

DOWN

bin/rails db:rollback:main db:rollback:ci STEP=5
main: == [advisory_lock_connection] object_id: 223940, pg_backend_pid: 74366
main: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: reverting ===========
main: -- transaction_open?()
main:    -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1146s
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.0238s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(: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.0115s
main: -- execute("RESET statement_timeout")
main:    -> 0.0009s
main: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: reverted (0.1728s) ==

main: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0010s
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.0246s
main: -- add_index(: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.0053s
main: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: reverted (0.0440s) ===

main: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- index_exists?(:issues, [:project_id, :closed_at], {:where=>"issue_type = 1 AND state_id = 2", :name=>"index_on_issues_closed_incidents_by_project_id_and_closed_at", :algorithm=>:concurrently})
main:    -> 0.0262s
main: -- add_index(:issues, [:project_id, :closed_at], {:where=>"issue_type = 1 AND state_id = 2", :name=>"index_on_issues_closed_incidents_by_project_id_and_closed_at", :algorithm=>:concurrently})
main:    -> 0.0030s
main: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: reverted (0.0424s)

main: == 20230721194757 DropIndexIssuesOnIncidentIssueType: reverting ===============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0008s
main: -- index_exists?(:issues, :issue_type, {:where=>"issue_type = 1", :name=>"index_issues_on_incident_issue_type", :algorithm=>:concurrently})
main:    -> 0.0261s
main: -- add_index(:issues, :issue_type, {:where=>"issue_type = 1", :name=>"index_issues_on_incident_issue_type", :algorithm=>:concurrently})
main:    -> 0.0026s
main: == 20230721194757 DropIndexIssuesOnIncidentIssueType: reverted (0.0423s) ======

main: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0010s
main: -- index_exists?(:issues, [:project_id, :created_at], {:where=>"issue_type = 1", :name=>"index_issues_on_project_id_and_created_at_issue_type_incident", :algorithm=>:concurrently})
main:    -> 0.0307s
main: -- add_index(:issues, [:project_id, :created_at], {:where=>"issue_type = 1", :name=>"index_issues_on_project_id_and_created_at_issue_type_incident", :algorithm=>:concurrently})
main:    -> 0.0034s
main: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: reverted (0.0487s)

main: == [advisory_lock_connection] object_id: 223940, pg_backend_pid: 74366
ci: == [advisory_lock_connection] object_id: 391160, pg_backend_pid: 74630
ci: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: reverting ===========
ci: -- transaction_open?()
ci:    -> 0.0002s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0013s
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.0445s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0007s
ci: -- add_index(: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.0106s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0006s
ci: == 20230721200849 DropIndexOnIssuesHealthStatusDescOrder: reverted (0.0969s) ==

ci: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: reverting ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0024s
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.0501s
ci: -- add_index(: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.0030s
ci: == 20230721200810 DropIndexOnIssuesHealthStatusAscOrder: reverted (0.1249s) ===

ci: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0016s
ci: -- index_exists?(:issues, [:project_id, :closed_at], {:where=>"issue_type = 1 AND state_id = 2", :name=>"index_on_issues_closed_incidents_by_project_id_and_closed_at", :algorithm=>:concurrently})
ci:    -> 0.0390s
ci: -- add_index(:issues, [:project_id, :closed_at], {:where=>"issue_type = 1 AND state_id = 2", :name=>"index_on_issues_closed_incidents_by_project_id_and_closed_at", :algorithm=>:concurrently})
ci:    -> 0.0090s
ci: == 20230721200323 DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt: reverted (0.0886s)

ci: == 20230721194757 DropIndexIssuesOnIncidentIssueType: reverting ===============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0016s
ci: -- index_exists?(:issues, :issue_type, {:where=>"issue_type = 1", :name=>"index_issues_on_incident_issue_type", :algorithm=>:concurrently})
ci:    -> 0.0432s
ci: -- add_index(:issues, :issue_type, {:where=>"issue_type = 1", :name=>"index_issues_on_incident_issue_type", :algorithm=>:concurrently})
ci:    -> 0.0037s
ci: == 20230721194757 DropIndexIssuesOnIncidentIssueType: reverted (0.0912s) ======

ci: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0012s
ci: -- index_exists?(:issues, [:project_id, :created_at], {:where=>"issue_type = 1", :name=>"index_issues_on_project_id_and_created_at_issue_type_incident", :algorithm=>:concurrently})
ci:    -> 0.0547s
ci: -- add_index(:issues, [:project_id, :created_at], {:where=>"issue_type = 1", :name=>"index_issues_on_project_id_and_created_at_issue_type_incident", :algorithm=>:concurrently})
ci:    -> 0.0054s
ci: == 20230721181046 DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident: reverted (0.1058s)

ci: == [advisory_lock_connection] object_id: 391160, pg_backend_pid: 74630

Timings

Database testing job is currently having some troubles posting https://gitlab.slack.com/archives/C3NBYFJ6N/p1689973022490959 (internal only) So I'll paste the results of that job here

Database migrations (on the main database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20230721181046 - DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident	Post deploy	4.1 s	✅	-18.12 MiB
20230721194757 - DropIndexIssuesOnIncidentIssueType	Post deploy	3.8 s	✅	-39.59 MiB
20230721200323 - DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt	Post deploy	3.8 s	✅	-9.52 MiB
20230721200810 - DropIndexOnIssuesHealthStatusAscOrder	Post deploy	3.9 s	✅	-3.18 GiB
20230721200849 - DropIndexOnIssuesHealthStatusDescOrder	Post deploy	3.7 s	✅	-3.18 GiB

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	10
0.1 seconds - 1 second	5
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721181046 - DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident

Type: Post deploy
Duration: 4.1 s
Database size change: -18.12 MiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	7.3 ms	7.3 ms	7.3 ms	0	DROP INDEX CONCURRENTLY "index_issues_on_project_id_and_created_at_issue_type_incident"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721194757 - DropIndexIssuesOnIncidentIssueType

Type: Post deploy
Duration: 3.8 s
Database size change: -39.59 MiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	3.0 ms	3.0 ms	3.0 ms	0	DROP INDEX CONCURRENTLY "index_issues_on_incident_issue_type"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexIssuesOnIncidentIssueType

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721200323 - DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt

Type: Post deploy
Duration: 3.8 s
Database size change: -9.52 MiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	5.2 ms	5.2 ms	5.2 ms	0	DROP INDEX CONCURRENTLY "index_on_issues_closed_incidents_by_project_id_and_closed_at"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721200810 - DropIndexOnIssuesHealthStatusAscOrder

Type: Post deploy
Duration: 3.9 s
Database size change: -3.18 GiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	3.0 ms	3.0 ms	3.0 ms	0	DROP INDEX CONCURRENTLY "index_on_issues_health_status_asc_order"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexOnIssuesHealthStatusAscOrder

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721200849 - DropIndexOnIssuesHealthStatusDescOrder

Type: Post deploy
Duration: 3.7 s
Database size change: -3.18 GiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	2.9 ms	2.9 ms	2.9 ms	0	DROP INDEX CONCURRENTLY "index_on_issues_health_status_desc_order"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexOnIssuesHealthStatusDescOrder

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
`database-testing-2138001-10664824-main`	2023-07-21T20:29:01Z	2023-07-21T20:13:11Z	2023-07-22 08:51:44 +0000
`database-testing-2138001-10664824-ci`	2023-07-21T20:29:01Z	2023-07-21T16:46:18Z	2023-07-22 08:51:44 +0000

Job artifacts

Database migrations (on the ci database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20230721181046 - DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident	Post deploy	4.5 s	✅	-8.00 KiB
20230721194757 - DropIndexIssuesOnIncidentIssueType	Post deploy	4.3 s	✅	-8.00 KiB
20230721200323 - DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt	Post deploy	4.3 s	✅	-8.00 KiB
20230721200810 - DropIndexOnIssuesHealthStatusAscOrder	Post deploy	4.2 s	✅	-8.00 KiB
20230721200849 - DropIndexOnIssuesHealthStatusDescOrder	Post deploy	4.1 s	✅	-8.00 KiB

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	15
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721181046 - DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident

Type: Post deploy
Duration: 4.5 s
Database size change: -8.00 KiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	17.0 ms	17.0 ms	17.0 ms	0	DROP INDEX CONCURRENTLY "index_issues_on_project_id_and_created_at_issue_type_incident"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721194757 - DropIndexIssuesOnIncidentIssueType

Type: Post deploy
Duration: 4.3 s
Database size change: -8.00 KiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	8.8 ms	8.8 ms	8.8 ms	0	DROP INDEX CONCURRENTLY "index_issues_on_incident_issue_type"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexIssuesOnIncidentIssueType

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721200323 - DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt

Type: Post deploy
Duration: 4.3 s
Database size change: -8.00 KiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	6.8 ms	6.8 ms	6.8 ms	0	DROP INDEX CONCURRENTLY "index_on_issues_closed_incidents_by_project_id_and_closed_at"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721200810 - DropIndexOnIssuesHealthStatusAscOrder

Type: Post deploy
Duration: 4.2 s
Database size change: -8.00 KiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	4.7 ms	4.7 ms	4.7 ms	0	DROP INDEX CONCURRENTLY "index_on_issues_health_status_asc_order"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexOnIssuesHealthStatusAscOrder

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230721200849 - DropIndexOnIssuesHealthStatusDescOrder

Type: Post deploy
Duration: 4.1 s
Database size change: -8.00 KiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	3.1 ms	3.1 ms	3.1 ms	0	DROP INDEX CONCURRENTLY "index_on_issues_health_status_desc_order"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for DropIndexOnIssuesHealthStatusDescOrder

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
`database-testing-2138001-10664824-main`	2023-07-21T20:29:01Z	2023-07-21T20:13:11Z	2023-07-22 08:51:44 +0000
`database-testing-2138001-10664824-ci`	2023-07-21T20:29:01Z	2023-07-21T16:46:18Z	2023-07-22 08:51:44 +0000

Job artifacts

Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

MR acceptance checklist

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

I have evaluated the MR acceptance checklist for this MR.

Related to #416949 (closed)

Edited Jul 21, 2023 by Mario Celi

Drop issues.issue_type related indexes

What does this MR do and why?

Database review

Migration output

Timings

Database migrations (on the main database)

Migration: 20230721181046 - DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident

Migration: 20230721194757 - DropIndexIssuesOnIncidentIssueType

Migration: 20230721200323 - DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt

Migration: 20230721200810 - DropIndexOnIssuesHealthStatusAscOrder

Migration: 20230721200849 - DropIndexOnIssuesHealthStatusDescOrder

Database migrations (on the ci database)

Migration: 20230721181046 - DropIndexIssuesOnProjectIdAndCreatedAtIssueTypeIncident

Migration: 20230721194757 - DropIndexIssuesOnIncidentIssueType

Migration: 20230721200323 - DropIndexOnIssuesClosedIncidentsByProjectIdAndClosedAt

Migration: 20230721200810 - DropIndexOnIssuesHealthStatusAscOrder

Migration: 20230721200849 - DropIndexOnIssuesHealthStatusDescOrder

MR acceptance checklist

Merge request reports