Use efficient in operator query for fetching group & project activities
Use efficient in-operator queries to fetch projects and groups activities. Async indexes are required because the events table is too big.
A limit of @limit + @offset has been added to each of the UNION queries generated for the group activity:
SELECT "events".*
FROM ((SELECT * FROM events WHERE project_id IN () LIMIT 20) UNION (SELECT * FROM events WHERE group_id IN ()) LIMIT 20)) events
ORDER BY "events"."id" DESC
LIMIT 20 OFFSET 0For group activity with no filter, we fetch @limit + @limit events for projects and groups each, sort them in memory and return the recent @limit (=20) events. In the worst case (fetching page 10), we would be sorting around 400 events but it's good for this iteration.
We've already been using the in-operator optimization. It's just that we lacked index and LIMIT for the group activity tab without a filter. This change will make that tab work regardless of the introduced FF. We had to use the multiple IN operator optimizations for the remaining tabs and the performance won't improve without the introduced FF.
Database
Example queries with plan
FF disabled
The project activity queries remain the same with FF disabled and the queries with filter are expected to time out so I have omitted them here.
FF enabled
Migrations
Up:
== 20220422121443 AddAsyncIndexForGroupActivityEvents: migrating ==============
-- index_exists?(:events, [:group_id, :target_type, :action, :id], {:name=>"index_events_for_group_activity", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
-> 0.0081s
== 20220422121443 AddAsyncIndexForGroupActivityEvents: migrated (0.0123s) =====
== 20220425111114 AddAsyncIndexForProjectActivityEvents: migrating ============
-- index_exists?(:events, [:project_id, :target_type, :action, :id], {:name=>"index_events_for_project_activity", :algorithm=>:concurrently})
-> 0.0077s
== 20220425111114 AddAsyncIndexForProjectActivityEvents: migrated (0.0103s) ===
== 20220425111453 AddAsyncIndexToEventsOnGroupIdAndId: migrating ==============
-- index_exists?(:events, [:group_id, :id], {:name=>"index_events_on_group_id_and_id", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
-> 0.0101s
== 20220425111453 AddAsyncIndexToEventsOnGroupIdAndId: migrated (0.0124s) =====Down:
== 20220425111453 AddAsyncIndexToEventsOnGroupIdAndId: reverting ==============
== 20220425111453 AddAsyncIndexToEventsOnGroupIdAndId: reverted (0.0129s) =====
== 20220425111114 AddAsyncIndexForProjectActivityEvents: reverting ============
== 20220425111114 AddAsyncIndexForProjectActivityEvents: reverted (0.0013s) ===
== 20220422121443 AddAsyncIndexForGroupActivityEvents: reverting ==============
== 20220422121443 AddAsyncIndexForGroupActivityEvents: reverted (0.0011s) =====Related to #355831 (closed)