Adding partial index on project settings
What does this MR do and why?
Adding a partial index index_project_settings_on_legacy_os_license_project_id
on project_settings
table on columns legacy_open_source_license_available
and project_id
for running data migrations efficiently for issues in epic https://gitlab.com/groups/gitlab-org/-/epics/7467. This index was suggested while running migration for !101503 (merged).
Migration output
up
bin/rails db:migrate:up:main VERSION=20221110190340
main: == 20221110190340 AddPartialLegacyOpenSourceLicenseAvailableProjectIdIndex: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0035s
main: -- index_exists?(:project_settings, [:legacy_open_source_license_available, :project_id], {:where=>"legacy_open_source_license_available = TRUE", :name=>"index_project_settings_on_legacy_os_license_project_id", :algorithm=>:concurrently})
main: -> 0.0221s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0013s
main: -- add_index(:project_settings, [:legacy_open_source_license_available, :project_id], {:where=>"legacy_open_source_license_available = TRUE", :name=>"index_project_settings_on_legacy_os_license_project_id", :algorithm=>:concurrently})
main: -> 0.0074s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20221110190340 AddPartialLegacyOpenSourceLicenseAvailableProjectIdIndex: migrated (0.0462s)
down
bin/rails db:migrate:down:main VERSION=20221110190340
main: == 20221110190340 AddPartialLegacyOpenSourceLicenseAvailableProjectIdIndex: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0017s
main: -- indexes(:project_settings)
main: -> 0.0022s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- remove_index(:project_settings, {:algorithm=>:concurrently, :name=>"index_project_settings_on_legacy_os_license_project_id"})
main: -> 0.0023s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: == 20221110190340 AddPartialLegacyOpenSourceLicenseAvailableProjectIdIndex: reverted (0.0122s)
Query plan
Sample query
UPDATE
"project_settings"
SET
"legacy_open_source_license_available" = FALSE,
"updated_at" = '2022-10-26 11:43:42.056119'
WHERE
"project_settings"."project_id" IN (
SELECT
"project_settings"."project_id"
FROM
"project_settings"
INNER JOIN project_statistics ON project_statistics.project_id = project_settings.project_id
WHERE
"project_settings"."project_id" BETWEEN 200000
AND 250000
AND "project_settings"."legacy_open_source_license_available" = TRUE
AND "project_settings"."project_id" >= 200000
AND "project_settings"."project_id" < 250000
AND (
project_statistics.repository_size < 5242880
)
);
Before adding index
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13359/commands/46943
After adding index
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13359/commands/46938
Index on postgres.ai
exec CREATE INDEX index_project_settings_on_legacy_os_license_project_id ON project_settings (project_id) WHERE legacy_open_source_license_available = true; drop index index_project_settings_on_legacy_open_source_license_available;
The query has been executed. Duration: 9.251 s
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 #376271
Edited by Hitesh Raghuvanshi