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