PERF: speed up suggested unread

PERF: speed up suggested unread

Previously we tried using a sub-query which has some terrible performance implications, by adding 1 extra query we can eliminate the PG issue

A join to user_stats also is prone to the same slowdown

diff --git a/lib/topic_query.rb b/lib/topic_query.rb
index 9fb9496..0a861a0 100644
--- a/lib/topic_query.rb
+++ b/lib/topic_query.rb
@@ -470,7 +470,15 @@ class TopicQuery
       .order('CASE WHEN topics.user_id = tu.user_id THEN 1 ELSE 2 END')
 
     if @user
-      result = result.where("topics.updated_at >= (SELECT first_unread_at FROM user_stats WHERE user_id = ?)", @user.id)
+
+      # micro optimisation so we don't load up all of user stats which we do not need
+      unread_at = DB.query_single(
+        "select first_unread_at from user_stats where user_id = ?",
+        @user.id).first
+
+      # perf note, in the past we tried doing this in a subquery but performance was
+      # terrible, also tried with a join and it was bad
+      result = result.where("topics.updated_at >= ?", unread_at)
     end
 
     self.class.results_filter_callbacks.each do |filter_callback|

GitHub sha: 0744d2ef

1 Like