我们最近删除了所有的提及，从那以后 CPU 负载下降到 15-20%，所以效果相当好，但因此上面的查询将不会返回结果。（或者由于我们删除了它，只会返回少量结果）。
1 个赞
上面的查询在任何情况下都应该返回结果，它以 EXPLAIN VERBOSE 开头，因此它会返回一个执行计划。当您有时间时，能否请您复制并粘贴我消息中的确切查询，运行它并分享结果？
Limit (cost=123.37..22204.48 rows=1000 width=40)
Output: users.id, (array_agg(ARRAY[uccm.id, c_msg.id])), uccm.user_id
-> GroupAggregate (cost=123.37..339399.59 rows=15365 width=40)
Output: users.id, array_agg(ARRAY[uccm.id, c_msg.id]), uccm.user_id
Group Key: users.id, uccm.user_id
-> Nested Loop (cost=123.37..336077.65 rows=417317 width=24)
Output: users.id, uccm.user_id, uccm.id, c_msg.id
Inner Unique: true
-> Merge Join (cost=123.23..271629.01 rows=417317 width=28)
Output: users.id, group_users.group_id, uccm.id, uccm.user_id, c_msg.id
Merge Cond: (user_options.user_id = group_users.user_id)
-> Nested Loop Left Join (cost=121.72..221430.16 rows=132835 width=28)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, c_msg.id
Filter: (((uccm.user_id = c_mentions.user_id) AND (uccm.following IS TRUE) AND ((cc.chatable_type)::text = 'Category'::text)) OR ((cc.chatable_type)::text = 'DirectMessage'::text))
-> Nested Loop (cost=121.32..149930.76 rows=132967 width=42)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.following, cc.chatable_type, c_msg.id
Join Filter: ((c_msg.user_id <> users.id) AND (cc.id = c_msg.chat_channel_id))
-> Nested Loop (cost=120.91..57868.92 rows=21066 width=54)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following, cc.id, cc.chatable_type
Inner Unique: true
-> Nested Loop (cost=120.63..51231.73 rows=21066 width=33)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following
Inner Unique: true
-> Merge Join (cost=120.21..36835.88 rows=21096 width=29)
Output: users.id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following
Inner Unique: true
Merge Cond: (uccm.user_id = users.id)
-> Index Scan using user_chat_channel_unique_memberships on public.user_chat_channel_memberships uccm (cost=0.41..12177.25 rows=21618 width=25)
Output: uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.following, uccm.muted, uccm.desktop_notification_level, uccm.mobile_notification_level, uccm.created_at, uccm.updated_at, uccm.last_unread_mention_when_emailed_id, uccm.join_mode, uccm.last_viewed_at
-> Index Scan using users_pkey on public.users (cost=0.42..23823.53 rows=237544 width=4)
Output: users.id
Filter: (users.last_seen_at < '2023-11-27 08:00:00'::timestamp without time zone)
-> Index Scan using index_user_options_on_user_id on public.user_options (cost=0.42..0.68 rows=1 width=4)
Output: user_options.user_id
Index Cond: (user_options.user_id = users.id)
Filter: (user_options.chat_enabled AND (user_options.chat_email_frequency = 1))
-> Index Scan using chat_channels_pkey on public.chat_channels cc (cost=0.28..0.32 rows=1 width=21)
Output: cc.id, cc.chatable_id, cc.deleted_at, cc.deleted_by_id, cc.featured_in_category_id, cc.delete_after_seconds, cc.chatable_type, cc.created_at, cc.updated_at, cc.name, cc.description, cc.status, cc.user_count, cc.last_message_sent_at, cc.auto_join_users, cc.user_count_stale, cc.type, cc.slug, cc.allow_channel_wide_mentions, cc.messages_count, cc.threading_enabled, cc.last_message_id
Index Cond: (cc.id = uccm.chat_channel_id)
-> Index Scan using index_chat_messages_on_chat_channel_id_and_created_at on public.chat_messages c_msg (cost=0.41..4.19 rows=12 width=16)
Output: c_msg.id, c_msg.chat_channel_id, c_msg.user_id
Index Cond: ((c_msg.chat_channel_id = uccm.chat_channel_id) AND (c_msg.created_at > '2023-11-20 08:00:00'::timestamp without time zone))
Filter: ((c_msg.deleted_at IS NULL) AND ((uccm.last_read_message_id IS NULL) OR (c_msg.id > uccm.last_read_message_id)) AND ((uccm.last_unread_mention_when_emailed_id IS NULL) OR (c_msg.id > uccm.last_unread_mention_when_emailed_id)))
-> Index Only Scan using chat_mentions_index on public.chat_mentions c_mentions (cost=0.40..0.52 rows=1 width=8)
Output: c_mentions.chat_message_id, c_mentions.user_id, c_mentions.notification_id
Index Cond: (c_mentions.chat_message_id = c_msg.id)
-> Index Only Scan using index_group_users_on_user_id_and_group_id on public.group_users (cost=0.42..44019.59 rows=760157 width=8)
Output: group_users.user_id, group_users.group_id
-> Index Only Scan using groups_pkey on public.groups (cost=0.14..0.16 rows=1 width=4)
Output: groups.id
Index Cond: (groups.id = group_users.group_id)
(51 行)
2 个赞
@sok777 感谢您分享查询计划！非常有帮助。
您的实例包含大量用户 以及 大量 user_chat_channel_memberships 记录（正如您在此处 here 分享的那样）。
在您分享测试计划之前，我曾尝试在用户量大的站点上重现问题，但查询运行良好。现在，我看到那些站点没有大量的 user_chat_channel_memberships，这很可能是您的站点特有的问题，并且是导致您遇到如此严重问题的根源。
据我所知，您现在没有问题，因为您在某个时候删除了所有提及，但我预计即使提及量很少，此查询将来也会再次出现问题。因此，无论如何我都会对其进行优化。
我目前正在着手修复导致数据库创建大量 chat_mention 记录的根本问题。在此期间，请务必在“watched words”中保留 @all 和 @here。我会在处理完这个问题后在此发布更新，然后我会确保优化此查询。
6 个赞
根本问题已通过以下方式解决：
今后不应再出现此类性能问题，也无需将大规模提及保留在监视词中。
关于这一点：
似乎没有人抱怨此查询，所以我没有对其进行优化。如有问题，请随时报告。
5 个赞