Optimize followed users queries
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:
- old (times out on PRD randomly): https://explain.depesz.com/s/uJmK
- new: https://explain.depesz.com/s/f1Or
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