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