Post.calculate_avg_time() taking up a long time

support
15

LOL, sorry, I forgot the query :smile:

Before

SELECT *
FROM posts ,

  (SELECT post_timings.topic_id,
          post_timings.post_number,
          round(exp(avg(ln(msecs)))) AS gmean
   FROM post_timings
   INNER JOIN posts AS p2 ON p2.post_number = post_timings.post_number
   AND p2.topic_id = post_timings.topic_id
   AND p2.user_id <> post_timings.user_id
   GROUP BY post_timings.topic_id,
            post_timings.post_number) AS x
WHERE x.topic_id = posts.topic_id
  AND x.post_number = posts.post_number
  AND (posts.avg_time <> (x.gmean / 1000)::int
       OR posts.avg_time IS NULL)
  AND posts.topic_id IN
    (SELECT id
     FROM topics
     WHERE bumped_at > CURRENT_DATE - 2);

After

SELECT *
FROM posts ,
  (SELECT post_timings.topic_id,
          post_timings.post_number,
          round(exp(avg(ln(msecs)))) AS gmean
   FROM post_timings
   INNER JOIN posts AS p2 ON p2.post_number = post_timings.post_number
   AND p2.topic_id = post_timings.topic_id
   AND p2.user_id <> post_timings.user_id
   WHERE p2.topic_id IN
       (SELECT id
        FROM topics
        WHERE bumped_at > CURRENT_DATE - 2)
   GROUP BY post_timings.topic_id,
            post_timings.post_number) AS x
WHERE x.topic_id = posts.topic_id
  AND x.post_number = posts.post_number
  AND (posts.avg_time <> (x.gmean / 1000)::int
       OR posts.avg_time IS NULL);

I have changed from UPDATE to SELECT so I could play around with it.

1 Like