• Yorick Peterse's avatar
    Added partial index for merge requests · 709dd237
    Yorick Peterse authored
    This index is added on `(target_project_id, iid)` and has a `WHERE state
    = 'opened'` condition. Using this index we can drastically improve the
    performance of the query used to count the total number of merge
    requests in a group. Without this index the query would eventually
    perform the following:
    
        ->  Index Scan using index_merge_requests_on_target_project_id_and_iid on merge_requests  (cost=0.43..4.89 rows=7 width=4) (actual time=0.058..0.353 rows=6 loops=228)
              Index Cond: (target_project_id = projects.id)
              Filter: ((state)::text = 'opened'::text)
              Rows Removed by Filter: 141
              Buffers: shared hit=34351 dirtied=1
    
    Out of the ~180 milliseconds the entire query would take, around 170
    milliseconds was spent in just this segment. With the index in place,
    the above segment is turned into the following:
    
        ->  Index Only Scan using yorick_test on merge_requests  (cost=0.42..0.55 rows=7 width=4) (actual time=0.004..0.010 rows=6 loops=228)
              Index Cond: (target_project_id = projects.id)
              Heap Fetches: 419
              Buffers: shared hit=1381
    
    The index also reduces the total query time to roughly 10 milliseconds.
    709dd237