PERF: Keep track of first unread PM and first unread group PM for user.

PERF: Keep track of first unread PM and first unread group PM for user.

This optimization helps to filter away topics so that the joins on related tables when querying for unread messages is not expensive.

diff --git a/app/jobs/scheduled/ensure_db_consistency.rb b/app/jobs/scheduled/ensure_db_consistency.rb
index 6c21814..c687cbe 100644
--- a/app/jobs/scheduled/ensure_db_consistency.rb
+++ b/app/jobs/scheduled/ensure_db_consistency.rb
@@ -35,6 +35,9 @@ module Jobs
       UserStat.ensure_consistency!(13.hours.ago)
       measure(UserStat)
 
+      GroupUser.ensure_consistency!(13.hours.ago)
+      measure(GroupUser)
+
       Rails.logger.debug(format_measure)
       nil
     end
diff --git a/app/models/group_user.rb b/app/models/group_user.rb
index 921641d..d83b060 100644
--- a/app/models/group_user.rb
+++ b/app/models/group_user.rb
@@ -19,6 +19,52 @@ class GroupUser < ActiveRecord::Base
     NotificationLevels.all
   end
 
+  def self.ensure_consistency!(last_seen = 1.hour.ago)
+    update_first_unread_pm(last_seen)
+  end
+
+  def self.update_first_unread_pm(last_seen, limit: 10_000)
+    DB.exec(<<~SQL, archetype: Archetype.private_message, last_seen: last_seen, limit: limit)
+    UPDATE group_users gu
+    SET first_unread_pm_at = Y.min_date
+    FROM (
+      SELECT
+        X.group_id,
+        X.user_id,
+        X.min_date
+      FROM (
+        SELECT
+          gu2.group_id,
+          gu2.user_id,
+          MIN(t.updated_at) min_date
+        FROM group_users gu2
+        INNER JOIN topic_allowed_groups tag ON tag.group_id = gu2.group_id
+        INNER JOIN topics t ON t.id = tag.topic_id
+        INNER JOIN users u ON u.id = gu2.user_id
+        LEFT JOIN topic_users tu ON t.id = tu.topic_id AND tu.user_id = gu2.user_id
+        WHERE t.deleted_at IS NULL
+        AND t.archetype = :archetype
+        AND tu.last_read_post_number < CASE
+                                       WHEN u.admin OR u.moderator
+                                       THEN t.highest_staff_post_number
+                                       ELSE t.highest_post_number
+                                       END
+        AND (COALESCE(tu.notification_level, 1) >= 2)
+        GROUP BY gu2.user_id, gu2.group_id
+      ) AS X
+      WHERE X.user_id IN (
+        SELECT id
+        FROM users
+        WHERE last_seen_at IS NOT NULL
+        AND last_seen_at > :last_seen
+        ORDER BY last_seen_at DESC
+        LIMIT :limit
+      )
+    ) Y
+    WHERE gu.user_id = Y.user_id AND gu.group_id = Y.group_id
+    SQL
+  end
+
   protected
 
   def set_notification_level
diff --git a/app/models/post_timing.rb b/app/models/post_timing.rb
index 1bf1c3f..8251d42 100644
--- a/app/models/post_timing.rb
+++ b/app/models/post_timing.rb
@@ -75,7 +75,9 @@ class PostTiming < ActiveRecord::Base
 
       topic.posts.find_by(post_number: post_number).decrement!(:reads)
 
-      if !topic.private_message?
+      if topic.private_message?
+        set_minimum_first_unread_pm!(topic: topic, user_id: user.id, date: topic.updated_at)
+      else
         set_minimum_first_unread!(user_id: user.id, date: topic.updated_at)
       end
     end
@@ -101,6 +103,27 @@ class PostTiming < ActiveRecord::Base
     end
   end
 
+  def self.set_minimum_first_unread_pm!(topic:, user_id:, date:)
+    if topic.topic_allowed_users.exists?(user_id: user_id)
+      UserStat.where("first_unread_pm_at > ? AND user_id = ?", date, user_id)
+        .update_all(first_unread_pm_at: date)
+    else
+      DB.exec(<<~SQL, date: date, user_id: user_id, topic_id: topic.id)
+      UPDATE group_users gu
+      SET first_unread_pm_at = :date
+      FROM (
+        SELECT
+          gu2.user_id,
+          gu2.group_id
+        FROM group_users gu2
+        INNER JOIN topic_allowed_groups tag ON tag.group_id = gu2.group_id AND tag.topic_id = :topic_id
+        WHERE gu2.user_id = :user_id
+      ) Y
+      WHERE gu.user_id = Y.user_id AND gu.group_id = Y.group_id
+      SQL
+    end
+  end
+
   def self.set_minimum_first_unread!(user_id:, date:)
     DB.exec(<<~SQL, date: date, user_id: user_id)
       UPDATE user_stats
diff --git a/app/models/user_stat.rb b/app/models/user_stat.rb
index 881b14a..bc2a9d2 100644
--- a/app/models/user_stat.rb
+++ b/app/models/user_stat.rb
@@ -12,10 +12,59 @@ class UserStat < ActiveRecord::Base
     update_distinct_badge_count
     update_view_counts(last_seen)
     update_first_unread(last_seen)
+    update_first_unread_pm(last_seen)
   end
 
-  def self.update_first_unread(last_seen, limit: 10_000)
-    DB.exec(<<~SQL, min_date: last_seen, limit: limit, now: 10.minutes.ago)
+  UPDATE_UNREAD_MINUTES_AGO = 10
+  UPDATE_UNREAD_USERS_LIMIT = 10_000
+
+  def self.update_first_unread_pm(last_seen, limit: UPDATE_UNREAD_USERS_LIMIT)
+    DB.exec(<<~SQL, archetype: Archetype.private_message, now: UPDATE_UNREAD_MINUTES_AGO.minutes.ago, last_seen: last_seen, limit: limit)
+    UPDATE user_stats us
+    SET first_unread_pm_at = COALESCE(Z.min_date, :now)
+    FROM (
+      SELECT
+        Y.user_id,
+        Y.min_date
+      FROM (
+        SELECT
+          u1.id user_id,
+          X.min_date
+        FROM users u1
+        LEFT JOIN (
+          SELECT
+            tau.user_id,
+            MIN(t.updated_at) min_date
+          FROM topic_allowed_users tau
+          INNER JOIN topics t ON t.id = tau.topic_id
+          INNER JOIN users u ON u.id = tau.user_id
+          LEFT JOIN topic_users tu ON t.id = tu.topic_id AND tu.user_id = tau.user_id
+          WHERE t.deleted_at IS NULL
+          AND t.archetype = :archetype
+          AND tu.last_read_post_number < CASE
+                                         WHEN u.admin OR u.moderator
+                                         THEN t.highest_staff_post_number
+                                         ELSE t.highest_post_number
+                                         END
+          AND (COALESCE(tu.notification_level, 1) >= 2)
+          GROUP BY tau.user_id
+        ) AS X ON X.user_id = u1.id
+      ) AS Y
+      WHERE Y.user_id IN (
+        SELECT id
+        FROM users
+        WHERE last_seen_at IS NOT NULL
+        AND last_seen_at > :last_seen
+        ORDER BY last_seen_at DESC
+        LIMIT :limit
+      )
+    ) AS Z
+    WHERE us.user_id = Z.user_id
+    SQL
+  end
+
+  def self.update_first_unread(last_seen, limit: UPDATE_UNREAD_USERS_LIMIT)
+    DB.exec(<<~SQL, min_date: last_seen, limit: limit, now: UPDATE_UNREAD_MINUTES_AGO.minutes.ago)
       UPDATE user_stats us
       SET first_unread_at = COALESCE(Y.min_date, :now)
       FROM (
diff --git a/db/migrate/20200902054531_add_first_unread_pm_a_to_group_user.rb b/db/migrate/20200902054531_add_first_unread_pm_a_to_group_user.rb
new file mode 100644
index 0000000..8cfb624
--- /dev/null
+++ b/db/migrate/20200902054531_add_first_unread_pm_a_to_group_user.rb
@@ -0,0 +1,16 @@
+# frozen_string_literal: true
+
+class AddFirstUnreadPmAToGroupUser < ActiveRecord::Migration[6.0]
+  def up
+    add_column :group_users, :first_unread_pm_at, :datetime, null: false, default: -> { 'CURRENT_TIMESTAMP' }
+
+    execute <<~SQL
+    UPDATE group_users gu
+    SET first_unread_pm_at = gu.created_at
+    SQL
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end
diff --git a/db/migrate/20200902082203_add_first_unread_pm_at_to_user_stats.rb b/db/migrate/20200902082203_add_first_unread_pm_at_to_user_stats.rb
new file mode 100644
index 0000000..6f4c8e8
--- /dev/null
+++ b/db/migrate/20200902082203_add_first_unread_pm_at_to_user_stats.rb
@@ -0,0 +1,18 @@
+# frozen_string_literal: true
+
+class AddFirstUnreadPmAtToUserStats < ActiveRecord::Migration[6.0]
+  def up
+    add_column :user_stats, :first_unread_pm_at, :datetime, null: false, default: -> { 'CURRENT_TIMESTAMP' }
+
+    execute <<~SQL
+    UPDATE user_stats us
+    SET first_unread_pm_at = u.created_at
+    FROM users u
+    WHERE u.id = us.user_id
+    SQL
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end
diff --git a/db/post_migrate/20200903045539_add_index_topics_on_timestamps_private.rb b/db/post_migrate/20200903045539_add_index_topics_on_timestamps_private.rb
new file mode 100644

[... diff too long, it was truncated ...]

GitHub sha: 9b75d95f

This commit appears in #10586 which was approved by eviltrout. It was merged by tgxworld.