# private-message-topic-tracking-state.json 加载缓慢

**URL:** https://meta.discourse.org/t/slow-loading-on-private-message-topic-tracking-state-json/202482
**Category:** Bug
**Created:** [2021年九月2日 19:16 UTC](https://meta.discourse.org/t/slow-loading-on-private-message-topic-tracking-state-json/202482 "2021-09-02T19:16:29Z")
**Posts on this page:** 1
**Showing post:** 19

<div class="post-metadata">

### Author: ![forkythetoy](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/forkythetoy/32/199298_2.png) [@forkythetoy](https://meta.discourse.org/u/forkythetoy)
#### Post date: [2021年九月8日 19:13 UTC](https://meta.discourse.org/t/slow-loading-on-private-message-topic-tracking-state-json/202482/19 "2021-09-08T19:13:42Z")

</div>

我们最近再次更新了以获取此功能 [https://github.com/discourse/discourse/pull/13603（即当前版本为](https://github.com/discourse/discourse/pull/13603%EF%BC%88%E5%8D%B3%E5%BD%93%E5%89%8D%E7%89%88%E6%9C%AC%E4%B8%BA) 2.8.0beta6）。

看起来“获取用户所有私信”的功能，最终会指向 [FEATURE: New and Unread messages for user personal messages. by tgxworld · Pull Request #13603 · discourse/discourse · GitHub](https://github.com/discourse/discourse/pull/13603/files#diff-8d278f1d9309d1b62925bfaa176df938e313d788b2c07a4bbd0856769ce45842R5%EF%BC%8C%E5%AF%BC%E8%87%B4%E7%94%9F%E6%88%90%E5%A6%82%E4%B8%8B%E6%9F%A5%E8%AF%A2%EF%BC%9A)

```plaintext
SELECT "topics"."id", "topics"."title", "topics"."last_posted_at", "topics"."created_at", "topics"."updated_at", "topics"."views", "topics"."posts_count", "topics"."user_id", "topics"."last_post_user_id", "topics"."reply_count", "topics"."featured_user1_id", "topics"."featured_user2_id", "topics"."featured_user3_id", "topics"."deleted_at", "topics"."highest_post_number", "topics"."like_count", "topics"."incoming_link_count", "topics"."category_id", "topics"."visible", "topics"."moderator_posts_count", "topics"."closed", "topics"."archived", "topics"."bumped_at", "topics"."has_summary", "topics"."archetype", "topics"."featured_user4_id", "topics"."notify_moderators_count", "topics"."spam_count", "topics"."pinned_at", "topics"."score", "topics"."percent_rank", "topics"."subtype", "topics"."slug", "topics"."deleted_by_id", "topics"."participant_count", "topics"."word_count", "topics"."excerpt", "topics"."pinned_globally", "topics"."pinned_until", "topics"."fancy_title", "topics"."highest_staff_post_number", "topics"."featured_link", "topics"."reviewable_score", "topics"."image_upload_id", "topics"."slow_mode_seconds" FROM "topics" LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = 1234) LEFT JOIN group_archived_messages gm ON gm.topic_id = topics.id
LEFT JOIN user_archived_messages um
ON um.user_id = 1234
AND um.topic_id = topics.id WHERE "topics"."deleted_at" IS NULL AND (topics.id IN (
SELECT topic_id
FROM topic_allowed_users
WHERE user_id = 1234
 UNION ALL
SELECT topic_id FROM topic_allowed_groups
 WHERE group_id IN (
SELECT group_id FROM group_users WHERE user_id = 1234
  )
)) AND "topics"."archetype" = 'private_message' AND "topics"."visible" = TRUE AND (um.user_id IS NULL AND gm.topic_id IS NULL) ORDER BY topics.bumped_at DESC LIMIT 30;

```

该查询基本导致我们的 RDS 服务器挂起。我们团队的一位成员建议，在连接之前先利用现有索引过滤数据。优化后的查询运行时间小于 100 毫秒。

```plaintext
SELECT "topics_filter"."id", "topics_filter"."title", "topics_filter"."last_posted_at", "topics_filter"."created_at", "topics_filter"."updated_at",
"topics_filter"."views", "topics_filter"."posts_count", "topics_filter"."user_id", "topics_filter"."last_post_user_id", "topics_filter"."reply_count", "topics_filter"."featured_user1_id",
"topics_filter"."featured_user2_id", "topics_filter"."featured_user3_id", "topics_filter"."deleted_at", "topics_filter"."highest_post_number", "topics_filter"."like_count",
"topics_filter"."incoming_link_count", "topics_filter"."category_id", "topics_filter"."visible", "topics_filter"."moderator_posts_count", "topics_filter"."closed",
"topics_filter"."archived", "topics_filter"."bumped_at", "topics_filter"."has_summary", "topics_filter"."archetype", "topics_filter"."featured_user4_id", "topics_filter"."notify_moderators_count",
"topics_filter"."spam_count", "topics_filter"."pinned_at", "topics_filter"."score", "topics_filter"."percent_rank", "topics_filter"."subtype", "topics_filter"."slug",
"topics_filter"."deleted_by_id", "topics_filter"."participant_count", "topics_filter"."word_count", "topics_filter"."excerpt", "topics_filter"."pinned_globally",
"topics_filter"."pinned_until", "topics_filter"."fancy_title", "topics_filter"."highest_staff_post_number", "topics_filter"."featured_link", "topics_filter"."reviewable_score",
"topics_filter"."image_upload_id", "topics_filter"."slow_mode_seconds"
FROM (select * from "topics"
      where "topics"."deleted_at" IS NULL
      and (topics.id IN ( SELECT topic_id FROM topic_allowed_users WHERE user_id = 1234
                          UNION ALL
                          SELECT topic_id FROM topic_allowed_groups WHERE group_id IN ( SELECT group_id FROM group_users WHERE user_id = 1234 )
                         ))
      AND "topics"."archetype" = 'private_message'
      AND "topics"."visible" = TRUE
      ) as topics_filter
LEFT OUTER JOIN (select topic_id from topic_users where user_id = 1234) AS tu ON (topics_filter.id = tu.topic_id )
LEFT JOIN group_archived_messages gm ON gm.topic_id = topics_filter.id
LEFT JOIN ( select user_id, topic_id from user_archived_messages where user_id = 1234) um
ON um.topic_id = topics_filter.id
WHERE (um.user_id IS NULL AND gm.topic_id IS NULL) ORDER BY topics_filter.bumped_at DESC LIMIT 30;

```

供参考：topics 表超过 1000 万行，topic\_users 表超过 5000 万行。

是否有可能重构该查询以某种方式解决这些问题？

编辑：

澄清一下，当我们检查运行时间超过 2 分钟甚至 5 分钟的长查询时，出现的只有这些查询。在应用最新更改后，我们的 Postgres CPU 持续处于 99% 的使用率。当我们终止这些查询时，CPU 使用率会恢复正常。

---

_[View the full topic](https://meta.discourse.org/t/slow-loading-on-private-message-topic-tracking-state-json/202482)._