PERF: Faster moving of read state

PERF: Faster moving of read state

This should improve the performance of moving the read state of lots of posts to a new/existing topic.

diff --git a/app/models/post_mover.rb b/app/models/post_mover.rb
index 6b779edf6b..7094f317a5 100644
--- a/app/models/post_mover.rb
+++ b/app/models/post_mover.rb
@@ -333,32 +333,37 @@ class PostMover
                  WHERE p.topic_id = tu.topic_id
                    AND p.user_id = tu.user_id
                )                                         AS posted,
-             MAX(lr.new_post_number)                     AS last_read_post_number,
-             MAX(hs.new_post_number)                     AS highest_seen_post_number,
-             MAX(le.new_post_number)                     AS last_emailed_post_number,
+             (
+               SELECT MAX(lr.new_post_number)
+               FROM moved_posts lr
+               WHERE lr.old_topic_id = tu.topic_id
+                 AND lr.old_post_number <= tu.last_read_post_number
+             )                                           AS last_read_post_number,
+             (
+               SELECT MAX(hs.new_post_number)
+               FROM moved_posts hs
+               WHERE hs.old_topic_id = tu.topic_id
+                 AND hs.old_post_number <= tu.highest_seen_post_number
+             )                                           AS highest_seen_post_number,
+             (
+               SELECT MAX(le.new_post_number)
+               FROM moved_posts le
+               WHERE le.old_topic_id = tu.topic_id
+                 AND le.old_post_number <= tu.last_emailed_post_number
+             )                                           AS last_emailed_post_number,
              GREATEST(tu.first_visited_at, t.created_at) AS first_visited_at,
              GREATEST(tu.last_visited_at, t.created_at)  AS last_visited_at,
              tu.notification_level,
              tu.notifications_changed_at,
              tu.notifications_reason_id
       FROM topic_users tu
-           JOIN topics t
-                ON (t.id = :new_topic_id)
-           LEFT OUTER JOIN moved_posts lr
-                           ON (lr.old_topic_id = tu.topic_id AND lr.old_post_number <= tu.last_read_post_number)
-           LEFT OUTER JOIN moved_posts hs
-                           ON (hs.old_topic_id = tu.topic_id AND hs.old_post_number <= tu.highest_seen_post_number)
-           LEFT OUTER JOIN moved_posts le
-                           ON (le.old_topic_id = tu.topic_id AND le.old_post_number <= tu.last_emailed_post_number)
+           JOIN topics t ON (t.id = :new_topic_id)
       WHERE tu.topic_id = :old_topic_id
         AND GREATEST(
                 tu.last_read_post_number,
                 tu.highest_seen_post_number,
                 tu.last_emailed_post_number
               ) >= (SELECT MIN(old_post_number) FROM moved_posts)
-      GROUP BY tu.topic_id, tu.user_id, tu.first_visited_at, tu.last_visited_at, t.created_at, tu.notification_level,
-               tu.notifications_changed_at,
-               tu.notifications_reason_id
       ON CONFLICT (topic_id, user_id) DO UPDATE
         SET posted                   = excluded.posted,
             last_read_post_number    = CASE

GitHub sha: f1742617

1 Like

This commit has been mentioned on Discourse Meta. There might be relevant details there: