PERF: Faster TL3 promotion replies needed calculation (#10416)

PERF: Faster TL3 promotion replies needed calculation (#10416)

Removing the LIMIT makes PostgreSQL use index_posts_on_user_id_and_created_at which is much faster overall.

Before: 22 seconds After: 100 ms

diff --git a/app/models/trust_level3_requirements.rb b/app/models/trust_level3_requirements.rb
index 3e35497..61970b2 100644
--- a/app/models/trust_level3_requirements.rb
+++ b/app/models/trust_level3_requirements.rb
@@ -143,7 +143,7 @@ class TrustLevel3Requirements
   end
 
   def num_topics_replied_to
-    @user.user_stat.calc_topic_reply_count!(min_topics_replied_to + 1, time_period.days.ago)
+    @user.user_stat.calc_topic_reply_count!(time_period.days.ago)
   end
 
   def min_topics_replied_to
diff --git a/app/models/user_stat.rb b/app/models/user_stat.rb
index 1eb168b..881b14a 100644
--- a/app/models/user_stat.rb
+++ b/app/models/user_stat.rb
@@ -154,11 +154,9 @@ class UserStat < ActiveRecord::Base
   end
 
   # topic_reply_count is a count of posts in other users' topics
-  def calc_topic_reply_count!(max, start_time = nil)
+  def calc_topic_reply_count!(start_time = nil)
     sql = <<~SQL
-    SELECT COUNT(*) count
-    FROM (
-      SELECT DISTINCT posts.topic_id
+      SELECT COUNT(DISTINCT posts.topic_id) AS count
       FROM posts
       INNER JOIN topics ON topics.id = posts.topic_id
       WHERE posts.user_id = ?
@@ -166,13 +164,11 @@ class UserStat < ActiveRecord::Base
       AND posts.deleted_at IS NULL AND topics.deleted_at IS NULL
       AND topics.archetype <> 'private_message'
       #{start_time.nil? ? '' : 'AND posts.created_at > ?'}
-      LIMIT ?
-    ) as user_topic_replies
     SQL
     if start_time.nil?
-      DB.query_single(sql, self.user_id, max).first
+      DB.query_single(sql, self.user_id).first
     else
-      DB.query_single(sql, self.user_id, start_time, max).first
+      DB.query_single(sql, self.user_id, start_time).first
     end
   end
 
diff --git a/lib/promotion.rb b/lib/promotion.rb
index c39d13f..d768d22 100644
--- a/lib/promotion.rb
+++ b/lib/promotion.rb
@@ -91,7 +91,7 @@ class Promotion
     return false if stat.days_visited < SiteSetting.tl2_requires_days_visited
     return false if stat.likes_received < SiteSetting.tl2_requires_likes_received
     return false if stat.likes_given < SiteSetting.tl2_requires_likes_given
-    return false if stat.calc_topic_reply_count!(SiteSetting.tl2_requires_topic_reply_count) < SiteSetting.tl2_requires_topic_reply_count
+    return false if stat.calc_topic_reply_count! < SiteSetting.tl2_requires_topic_reply_count
 
     true
   end

GitHub sha: 28669dfe

1 Like

This commit appears in #10416 which was approved by danielwaterworth. It was merged by Falco.

:heart: :heart: :heart:

1 Like