Index merge requests with state_id (!42481) · Merge requests · GitLab.org / GitLab

What does this MR do?

Adds a missing index used on the merge_requests list page, should have a notable performance improvement. I've seen this taking at least 180ms on production so I'd expect a good speedup from this.

Related #249180 (closed)

Old query explain: https://explain.depesz.com/s/Wiho

HashAggregate  (cost=56764.73..56764.77 rows=4 width=10) (actual time=66155.076..66155.077 rows=3 loops=1)
   Group Key: merge_requests.state_id
   Buffers: shared hit=1875 read=36716 dirtied=915
   I/O Timings: read=65509.382
   ->  Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests  (cost=0.56..56546.89 rows=43568 width=2) (actual time=43.875..66059.312 rows=38188 loops=1)
         Index Cond: (merge_requests.target_project_id = 278964)
         Buffers: shared hit=1875 read=36716 dirtied=915
         I/O Timings: read=65509.382

Index creation:

exec CREATE UNIQUE INDEX index_merge_requests_on_target_project_id_and_iid_and_state_id ON public.merge_requests USING btree (target_project_id, iid, state_id)

Session: joe-bthm0qg350j13v0690l0
The query has been executed. Duration: 11.456 min

New query explain: https://explain.depesz.com/s/ZWZN

 HashAggregate  (cost=1457.16..1457.20 rows=4 width=10) (actual time=235.869..235.870 rows=3 loops=1)
   Group Key: merge_requests.state_id
   Buffers: shared hit=35351 read=1258 dirtied=251
   I/O Timings: read=194.236
   ->  Index Only Scan using index_merge_requests_on_target_project_id_and_iid_and_state_id on public.merge_requests  (cost=0.56..1282.92 rows=34848 width=2) (actual time=0.540..222.205 rows=38309 loops=1)
         Index Cond: (merge_requests.target_project_id = 278964)
         Heap Fetches: 1160
         Buffers: shared hit=35351 read=1258 dirtied=251
         I/O Timings: read=194.236

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Index merge requests with state_id