Skip to content

Optimize followed users queries

Adam Hegyi requested to merge 346435-optimize-followed-users-queries into master

What does this MR do and why?

This MR optimizes the followed users tabs using the in-operator optimization library.

The change requires an async index because the events table is too big. The change is behind a feature flag: optimized_followed_users_queries

Performance comparison

Note: The measured timings include 160-180ms network latency.

User Filter Optimized Old % diff
euko all 750ms 550ms -36.36%
euko push 170ms 25230ms 99.33%
euko merged 180ms 1600ms 88.75%
euko issue 170ms 8780ms 98.06%
euko comments 170ms 66200ms 99.74%
euko team 170ms 1000ms 83.00%
euko wiki 170ms 270ms 37.04%
euko designs 180ms 230ms 21.74%
euko epic 170ms 880ms 80.68%
m_gill all 370ms 370ms 0.00%
m_gill push 190ms 135650ms 99.86%
m_gill merged 190ms 1390ms 86.33%
m_gill issue 200ms 400ms 50.00%
m_gill comments 180ms 2150ms 91.63%
m_gill team 170ms 230ms 26.09%
m_gill wiki 200ms 190ms -5.26%
m_gill designs 200ms 220ms 9.09%
m_gill epic 190ms 190ms 0.00%
mvanremmerden all 340ms 540ms 37.04%
mvanremmerden push 200ms 370ms 45.95%
mvanremmerden merged 190ms 170ms -11.76%
mvanremmerden issue 190ms 190ms 0.00%
mvanremmerden comments 190ms 290ms 34.48%
mvanremmerden team 190ms 170ms -11.76%
mvanremmerden wiki 180ms 170ms -5.88%
mvanremmerden designs 180ms 180ms 0.00%
mvanremmerden epic 190ms 190ms 0.00%

Database

Example database query:

Up

== 20220409160628 AddAsyncIndexForEventsFollowedUsers: migrating ==============
-- index_exists?(:events, [:author_id, :target_type, :action, :id], {:name=>"index_events_for_followed_users", :algorithm=>:concurrently})
   -> 0.0046s
-- add_index_options(:events, [:author_id, :target_type, :action, :id], {:name=>"index_events_for_followed_users", :algorithm=>:concurrently})
   -> 0.0001s
== 20220409160628 AddAsyncIndexForEventsFollowedUsers: migrated (0.0187s) =====

Down

== 20220409160628 AddAsyncIndexForEventsFollowedUsers: reverting ==============
== 20220409160628 AddAsyncIndexForEventsFollowedUsers: reverted (0.0076s) =====

Related to #346435 (closed)

Edited by Adam Hegyi

Merge request reports