Skip to content

Rework how recent push events are retrieved

yorickpeterse-staging requested to merge user-recent-push into master

This changes the way recent push events are retrieved for the "Create merge request" widget displayed at the top of some pages. See the commit message(s) for more details.

Queries

The following query is used to retrieve a push event by its ID while making sure the event is not returned if a merge request exists for it:

SELECT events.*
FROM events
INNER JOIN push_event_payloads ON push_event_payloads.event_id = events.id
WHERE events.action IN (5)
AND NOT EXISTS (
    SELECT 1
    FROM merge_requests
    WHERE merge_requests.deleted_at IS NULL
    AND merge_requests.target_project_id = events.project_id
    AND merge_requests.source_branch = push_event_payloads.ref
)
AND push_event_payloads.action IN (2, 0)
AND push_event_payloads.ref_type = 0
AND events.id = 23706830
LIMIT 1;

The first time this query runs it produces the following plan:

                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.69..203.74 rows=1 width=1875) (actual time=68.108..68.108 rows=0 loops=1)
   ->  Nested Loop Anti Join  (cost=1.69..203.74 rows=1 width=1875) (actual time=68.106..68.106 rows=0 loops=1)
         Join Filter: (merge_requests.target_project_id = events.project_id)
         ->  Nested Loop  (cost=1.13..9.19 rows=1 width=1887) (actual time=67.998..68.001 rows=1 loops=1)
               ->  Index Scan using events_pkey on events  (cost=0.57..4.59 rows=1 width=1875) (actual time=67.975..67.975 rows=1 loops=1)
                     Index Cond: (id = 23706830)
                     Filter: (action = 5)
               ->  Index Scan using index_push_event_payloads_on_event_id on push_event_payloads  (cost=0.56..4.59 rows=1 width=16) (actual time=0.019..0.020 rows=1 loops=1)
                     Index Cond: (event_id = 23706830)
                     Filter: ((action = ANY ('{2,0}'::integer[])) AND (ref_type = 0))
         ->  Index Scan using index_merge_requests_on_source_branch on merge_requests  (cost=0.56..97.26 rows=47 width=24) (actual time=0.096..0.096 rows=1 loops=1)
               Index Cond: ((source_branch)::text = push_event_payloads.ref)
               Filter: (deleted_at IS NULL)
 Planning time: 2.595 ms
 Execution time: 68.213 ms

And the second time:

                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.69..203.74 rows=1 width=1875) (actual time=0.053..0.053 rows=0 loops=1)
   ->  Nested Loop Anti Join  (cost=1.69..203.74 rows=1 width=1875) (actual time=0.053..0.053 rows=0 loops=1)
         Join Filter: (merge_requests.target_project_id = events.project_id)
         ->  Nested Loop  (cost=1.13..9.19 rows=1 width=1887) (actual time=0.031..0.033 rows=1 loops=1)
               ->  Index Scan using events_pkey on events  (cost=0.57..4.59 rows=1 width=1875) (actual time=0.018..0.019 rows=1 loops=1)
                     Index Cond: (id = 23706830)
                     Filter: (action = 5)
               ->  Index Scan using index_push_event_payloads_on_event_id on push_event_payloads  (cost=0.56..4.59 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=1)
                     Index Cond: (event_id = 23706830)
                     Filter: ((action = ANY ('{2,0}'::integer[])) AND (ref_type = 0))
         ->  Index Scan using index_merge_requests_on_source_branch on merge_requests  (cost=0.56..97.26 rows=47 width=24) (actual time=0.016..0.016 rows=1 loops=1)
               Index Cond: ((source_branch)::text = push_event_payloads.ref)
               Filter: (deleted_at IS NULL)
 Planning time: 1.013 ms
 Execution time: 0.122 ms

Getting rid of WHERE deleted_at IS NULL (which is unrelated to this MR) would produce the following plan:

                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.69..203.74 rows=1 width=1879) (actual time=0.065..0.065 rows=0 loops=1)
   ->  Nested Loop Anti Join  (cost=1.69..203.74 rows=1 width=1879) (actual time=0.064..0.064 rows=0 loops=1)
         Join Filter: (merge_requests.target_project_id = events.project_id)
         ->  Nested Loop  (cost=1.13..9.19 rows=1 width=1891) (actual time=0.032..0.034 rows=1 loops=1)
               ->  Index Scan using events_pkey on events  (cost=0.57..4.59 rows=1 width=1879) (actual time=0.018..0.019 rows=1 loops=1)
                     Index Cond: (id = 23706830)
                     Filter: (action = 5)
               ->  Index Scan using index_push_event_payloads_on_event_id on push_event_payloads  (cost=0.56..4.59 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=1)
                     Index Cond: (event_id = 23706830)
                     Filter: ((action = ANY ('{2,0}'::integer[])) AND (ref_type = 0))
         ->  Index Scan using index_merge_requests_on_source_branch on merge_requests  (cost=0.56..97.26 rows=47 width=24) (actual time=0.015..0.015 rows=1 loops=1)
               Index Cond: ((source_branch)::text = push_event_payloads.ref)
 Planning time: 1.524 ms
 Execution time: 0.106 ms

The above query is only executed when:

  1. You pushed in the past 2 hours
  2. No MR exists for the event (if it does the query will stop running on future requests)

Database Checklist

When adding or modifying queries to improve performance:

  • Included the raw SQL queries of the relevant queries
  • Included the output of EXPLAIN ANALYZE and execution timings of the relevant queries
  • Added tests for the relevant changes

General Checklist

Edited by yorickpeterse-staging

Merge request reports