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.
Edited by Andreas Brandl