PERF: Improve post_timing performance when moving posts

PERF: Improve post_timing performance when moving posts

Scanning for all possible invalid post_timing records in the destination topics can be a very expensive operation. The main aim is to avoid the data clashing with soon-to-be-moved posts, so we can reduce the scope of the query by targeting only rows which would actually cause a clash. post_timings has an index on (topic_id, post_number), so this is very fast.

On an example site, this reduced the query from ~6s to <10ms

diff --git a/app/models/post_mover.rb b/app/models/post_mover.rb
index c1710bb..fcbfdb8 100644
--- a/app/models/post_mover.rb
+++ b/app/models/post_mover.rb
@@ -79,7 +79,6 @@ class PostMover
     moving_all_posts = original_topic_posts_count == posts.length
 
     create_temp_table
-    delete_invalid_post_timings
     move_each_post
     create_moderator_post_in_original_topic
     update_statistics
@@ -146,6 +145,7 @@ class PostMover
     update_quotes
     move_first_post_replies
     delete_post_replies
+    delete_invalid_post_timings
     copy_first_post_timings
     move_post_timings
     copy_topic_users
@@ -318,16 +318,12 @@ class PostMover
   end
 
   def delete_invalid_post_timings
-    DB.exec(<<~SQL, topid_id: destination_topic.id)
+    DB.exec <<~SQL
       DELETE
       FROM post_timings pt
-      WHERE pt.topic_id = :topid_id
-        AND NOT EXISTS(
-          SELECT 1
-          FROM posts p
-          WHERE p.topic_id = pt.topic_id
-            AND p.post_number = pt.post_number
-        )
+      USING moved_posts mp
+      WHERE pt.topic_id = mp.new_topic_id
+        AND pt.post_number = mp.new_post_number
     SQL
   end
 

GitHub sha: 3d049245af80e27b437dff5060a1f58dde20ff5d

This commit appears in #13687 which was approved by ZogStriP and gschlager. It was merged by davidtaylorhq.