Add environment_id to the [project_id, ref] index on deployments

What does this MR do and why?

Projects::BlobController#show renders this:

It runs the following query to find out which environment the particular commit is deployed to:

SELECT "environments".*
FROM "environments"
WHERE "environments"."project_id" = $1
  AND ("environments"."state" IN ($2))
  AND "environments"."id" IN (
    SELECT "deployments"."environment_id"
    FROM "deployments"
    WHERE "deployments"."project_id" = $3 
      AND (ref = $4)
    GROUP BY "deployments"."environment_id"
  )
ORDER BY (
  SELECT MAX("deployments"."id")
  FROM "deployments"
  WHERE "deployments"."environment_id" = "environments"."id"
) DESC NULLS LAST

On a project with many deployments, this query can take 35 seconds to run: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/6885/commands/24330.

The slowest part of this query is:

                     ->  Index Scan using index_deployments_on_project_id_and_ref on public.deployments  (cost=0.57..5544.56 rows=3836 width=4) (actual time=7.002..35026.091 rows=221606 loops=1)
                           Index Cond: ((deployments.project_id = 15426071) AND ((deployments.ref)::text = 'main'::text))
                           Buffers: shared hit=49 read=72478 dirtied=1747
                           I/O Timings: read=34516.969 write=0.000

We already have an index on index_deployments_on_project_id_and_ref, but we need environment_id as the result of this query, and the index doesn't have this field. So Postgres needs to fetch it from the heap for every row.

After adding enivornment_id to the index this node becomes Index Only Scan https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/6885/commands/24333 and runs much faster:

                     ->  Index Only Scan using index_deployments_on_project_id_and_ref_and_environment_id on public.deployments  (cost=0.57..224.98 rows=3839 width=4) (actual time=0.423..258.499 rows=221606 loops=1)
                           Index Cond: ((deployments.project_id = 15426071) AND (deployments.ref = 'main'::text))
                           Heap Fetches: 6239
                           Buffers: shared hit=2874 read=3316 dirtied=1267
                           I/O Timings: read=181.297 write=0.000

Migration log

vlad @ gdk1 ➜  gitlab git:(342715-db-timeout-when-loading-environment-in-projects-blobcontroller-show) ✗  ./bin/rails db:rollback STEP=2                  
== 20211011093938 RemoveProjectIdRefIndexFromDeployments: reverting ===========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:deployments, [:project_id, :ref], {:name=>"index_deployments_on_project_id_and_ref", :using=>"btree", :algorithm=>:concurrently})
   -> 0.0133s
-- execute("SET statement_timeout TO 0")
   -> 0.0031s
-- add_index(:deployments, [:project_id, :ref], {:name=>"index_deployments_on_project_id_and_ref", :using=>"btree", :algorithm=>:concurrently})
   -> 0.0217s
-- execute("RESET statement_timeout")
   -> 0.0014s
== 20211011093938 RemoveProjectIdRefIndexFromDeployments: reverted (0.0438s) ==

== 20211008162508 AddIndexDeploymentsOnProjectIdAndRefAndEnvironmentId: reverting 
-- transaction_open?()
   -> 0.0000s
-- indexes(:deployments)
   -> 0.0099s
-- remove_index(:deployments, {:algorithm=>:concurrently, :name=>"index_deployments_on_project_id_and_ref_and_environment_id"})
   -> 0.0031s
== 20211008162508 AddIndexDeploymentsOnProjectIdAndRefAndEnvironmentId: reverted (0.0155s) 

vlad @ gdk1 ➜  gitlab git:(342715-db-timeout-when-loading-environment-in-projects-blobcontroller-show) ✗  ./bin/rails db:migrate        
== 20211008162508 AddIndexDeploymentsOnProjectIdAndRefAndEnvironmentId: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:deployments, [:project_id, :ref, :environment_id], {:name=>"index_deployments_on_project_id_and_ref_and_environment_id", :using=>"btree", :algorithm=>:concurrently})
   -> 0.0145s
-- execute("SET statement_timeout TO 0")
   -> 0.0011s
-- add_index(:deployments, [:project_id, :ref, :environment_id], {:name=>"index_deployments_on_project_id_and_ref_and_environment_id", :using=>"btree", :algorithm=>:concurrently})
   -> 0.0065s
-- execute("RESET statement_timeout")
   -> 0.0014s
== 20211008162508 AddIndexDeploymentsOnProjectIdAndRefAndEnvironmentId: migrated (0.0274s) 

== 20211011093938 RemoveProjectIdRefIndexFromDeployments: migrating ===========
-- transaction_open?()
   -> 0.0000s
-- indexes(:deployments)
   -> 0.0136s
-- remove_index(:deployments, {:algorithm=>:concurrently, :name=>"index_deployments_on_project_id_and_ref"})
   -> 0.0043s
== 20211011093938 RemoveProjectIdRefIndexFromDeployments: migrated (0.0205s) ==

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 #342715 (closed)