PERF: Move `where` clause up to speed up CalculateAvgTime daily job (#7462)

PERF: Move where clause up to speed up CalculateAvgTime daily job (#7462)

Cuts down affected posts earlier in the query, so the generated plan deals with less rows, and runs faster.

Post.calculate_avg_time() taking up a long time - support - Discourse Meta

diff --git a/app/models/post.rb b/app/models/post.rb
index 9f06916..5ab7320 100644
--- a/app/models/post.rb
+++ b/app/models/post.rb
@@ -688,6 +688,7 @@ class Post < ActiveRecord::Base
                         ON p2.post_number = post_timings.post_number
                           AND p2.topic_id = post_timings.topic_id
                           AND p2.user_id <> post_timings.user_id
+                      /*where2*/
                       GROUP BY post_timings.topic_id, post_timings.post_number) AS x
                 /*where*/")
 
@@ -696,7 +697,7 @@ class Post < ActiveRecord::Base
                   AND (posts.avg_time <> (x.gmean / 1000)::int OR posts.avg_time IS NULL)")
 
       if min_topic_age
-        builder.where("posts.topic_id IN (SELECT id FROM topics where bumped_at > :bumped_at)",
+        builder.where2("p2.topic_id IN (SELECT id FROM topics where bumped_at > :bumped_at)",
                      bumped_at: min_topic_age)
       end

GitHub sha: 1fdeec56

1 Like