Add index to improve performance of project deployments endpoint

What does this MR do?

Adds an index to improve the performance of the project deployments endpoint.

Resolves #325627 (closed)

Query plans

explain analyze 
SELECT 
  "deployments".* 
FROM 
  "deployments" 
  INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id" 
WHERE 
  "deployments"."project_id" = 8 
  AND "deployments"."updated_at" <= '2021-03-26 05:40:10' 
  AND "environments"."name" = 'production' 
ORDER BY 
  "deployments"."iid" DESC 
LIMIT 
  100 OFFSET 0;

Before (warm cache)

https://explain.depesz.com/s/8Hgm

                                                                                          QUERY PLAN                                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=144.45..144.65 rows=82 width=148) (actual time=0.431..0.431 rows=0 loops=1)
   ->  Sort  (cost=144.45..144.65 rows=82 width=148) (actual time=0.430..0.431 rows=0 loops=1)
         Sort Key: deployments.iid DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=0.43..141.84 rows=82 width=148) (actual time=0.426..0.426 rows=0 loops=1)
               ->  Index Scan using index_environments_on_name_varchar_pattern_ops on environments  (cost=0.14..2.16 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
                     Index Cond: ((name)::text = 'test5'::text)
               ->  Index Scan using index_deployments_on_environment_id_and_iid_and_project_id on deployments  (cost=0.29..138.80 rows=88 width=148) (actual time=0.420..0.420 rows=0 loops=1)
                     Index Cond: ((environment_id = environments.id) AND (project_id = 30))
                     Filter: (updated_at <= '2021-03-29 05:40:10'::timestamp without time zone)
                     Rows Removed by Filter: 2000
 Planning Time: 0.429 ms
 Execution Time: 0.471 ms
(13 rows)

After (warm cache):

https://explain.depesz.com/s/mtQ1

                                                                                          QUERY PLAN                                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=32.24..32.37 rows=53 width=148) (actual time=0.022..0.023 rows=0 loops=1)
   ->  Sort  (cost=32.24..32.37 rows=53 width=148) (actual time=0.021..0.022 rows=0 loops=1)
         Sort Key: deployments.iid DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=0.43..30.72 rows=53 width=148) (actual time=0.017..0.017 rows=0 loops=1)
               ->  Index Scan using index_environments_on_name_varchar_pattern_ops on environments  (cost=0.14..2.16 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
                     Index Cond: ((name)::text = 'test5'::text)
               ->  Index Scan using index_deployments_on_project_and_environment_and_updated_at on deployments  (cost=0.29..28.00 rows=56 width=148) (actual time=0.008..0.008 rows=0 loops=1)
                     Index Cond: ((project_id = 30) AND (environment_id = environments.id) AND (updated_at <= '2021-03-29 05:40:10'::timestamp without time zone))
 Planning Time: 0.459 ms
 Execution Time: 0.056 ms
(11 rows)

Database migrations

Up:

== 20210326035553 AddIndexForProjectDeploymentsWithEnvironmentIdAndUpdatedAt: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:deployments, [:project_id, :environment_id, :updated_at], {:name=>"index_deployments_on_project_and_environment_and_updated_at", :algorithm=>:concurrently})
   -> 0.0076s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:deployments, [:project_id, :environment_id, :updated_at], {:name=>"index_deployments_on_project_and_environment_and_updated_at", :algorithm=>:concurrently})
   -> 0.0095s
-- execute("RESET ALL")
   -> 0.0006s
== 20210326035553 AddIndexForProjectDeploymentsWithEnvironmentIdAndUpdatedAt: migrated (0.0192s) 

Down:

== 20210326035553 AddIndexForProjectDeploymentsWithEnvironmentIdAndUpdatedAt: reverting 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:deployments, [:project_id, :environment_id, :updated_at], {:name=>"index_deployments_on_project_and_environment_and_updated_at", :algorithm=>:concurrently})
   -> 0.0065s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:deployments, {:name=>"index_deployments_on_project_and_environment_and_updated_at", :algorithm=>:concurrently, :column=>[:project_id, :environment_id, :updated_at]})
   -> 0.0081s
-- execute("RESET ALL")
   -> 0.0005s
== 20210326035553 AddIndexForProjectDeploymentsWithEnvironmentIdAndUpdatedAt: reverted (0.0164s) 

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • [-] I have not included a changelog entry because _____.
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.