Add index to find stuck merge requests.

What does this MR do?

It adds an index to speed up a query for the stuck merge requests worker.

In staging, this yields a nice and fast plan now after adding the index:

gitlabhq_production=# explain analyze SELECT  id, merge_jid FROM "merge_requests" WHERE ("merge_requests"."state" IN ('locked')) AND ("merge_requests"."merge_jid" IS NOT NULL)  ORDER BY "merge_requests"."id" ASC LIMIT 100;

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=491703.90..491704.15 rows=100 width=29) (actual time=5242.025..5242.025 rows=0 loops=1)
   ->  Sort  (cost=491703.90..491704.28 rows=153 width=29) (actual time=5242.024..5242.024 rows=0 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on merge_requests  (cost=0.00..491698.35 rows=153 width=29) (actual time=5241.997..5241.997 rows=0 loops=1)
               Filter: ((merge_jid IS NOT NULL) AND ((state)::text = 'locked'::text))
               Rows Removed by Filter: 4223289
 Planning time: 2.103 ms
 Execution time: 5242.054 ms
(9 rows)

gitlabhq_production=# create index concurrently abrandl_tmp1 ON merge_requests (id, merge_jid) WHERE merge_jid IS NOT NULL AND state='locked';
CREATE INDEX
gitlabhq_production=# explain analyze SELECT  id, merge_jid FROM "merge_requests" WHERE ("merge_requests"."state" IN ('locked')) AND ("merge_requests"."merge_jid" IS NOT NULL)  ORDER BY "merge_requests"."id" ASC LIMIT 100;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.12..4.10 rows=100 width=29) (actual time=0.003..0.003 rows=0 loops=1)
   ->  Index Only Scan using abrandl_tmp1 on merge_requests  (cost=0.12..6.13 rows=151 width=29) (actual time=0.002..0.002 rows=0 loops=1)
         Heap Fetches: 0
 Planning time: 0.549 ms
 Execution time: 0.043 ms
(5 rows)

Migration:

abrandl-gl:gitlab/ (ab-45608-stuck-mr-query) $ spring rake db:migrate                                                                                                                                                                                                                                             [17:28:07]
Running via Spring preloader in process 16711
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: already initialized constant PAGES_ACCESS_LEVEL_SCHEMA_VERSION
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: previous definition of PAGES_ACCESS_LEVEL_SCHEMA_VERSION was here
== 20181101144347 AddIndexForStuckMrQuery: migrating ==========================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_requests, [:id, :merge_jid], {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently})
   -> 0.0106s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- add_index(:merge_requests, [:id, :merge_jid], {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently})
   -> 0.0081s
-- execute("RESET ALL")
   -> 0.0007s
== 20181101144347 AddIndexForStuckMrQuery: migrated (0.0202s) =================

s%                                                                                                                                                                                                                                                                                                                           abrandl-gl:gitlab/ (ab-45608-stuck-mr-query) $ spring rake db:rollback                                                                                                                                                                                                                                            [17:28:13]
Running via Spring preloader in process 16777
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: already initialized constant PAGES_ACCESS_LEVEL_SCHEMA_VERSION
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: previous definition of PAGES_ACCESS_LEVEL_SCHEMA_VERSION was here
== 20181101144347 AddIndexForStuckMrQuery: reverting ==========================
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0006s
-- index_exists?(:merge_requests, [:id, :merge_jid], {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently})
   -> 0.0111s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:merge_requests, {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently, :column=>[:id, :merge_jid]})
   -> 0.0109s
-- execute("RESET ALL")
   -> 0.0005s
== 20181101144347 AddIndexForStuckMrQuery: reverted (0.0240s) =================

What are the relevant issue numbers?

See https://gitlab.com/gitlab-org/gitlab-ce/issues/45608.

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Documentation created/updated
• Tests added for this feature/bug
• Conforms to the code review guidelines
• Conforms to the merge request performance guidelines
• Conforms to the style guides
• Conforms to the database guides
• Link to e2e tests MR added if this MR has Requires e2e tests label. See the Test Planning Process.