最近、すべてのメンションを削除しました。それ以来、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 レコードが含まれています（こちらで共有されたように）。
テストプランを共有していただく前に、大量のユーザーがいるサイトで問題を再現しようとしましたが、クエリは正常に動作しました。現在、それらのサイトには大量の user_chat_channel_memberships がないことがわかっています。おそらく、これがあなたのサイトの特異な点であり、あなたのケースで深刻な問題を引き起こした原因でしょう。
理解したところによると、あなたはすでにメンションをすべて削除したため、現在は問題がないとのことですが、メンションの数が少なくても、このクエリはいつか問題を引き起こす可能性があると予想されます。そのため、いずれにしても最適化を行います。
現在、データベースに大量の chat_mention レコードが作成された根本的な原因を修正する作業に取り組んでいます。それが進行中である間、@all と @here をウォッチワードに含めておくことが重要です。その作業が完了したら、ここでアップデートを投稿し、その後、このクエリの最適化を行います。
「いいね！」 6
根本的な問題は、
で解決されました。
これにより、パフォーマンスの問題は発生しなくなり、ウォッチワードに大量メンションを含める必要もなくなりました。
これに関して：
このクエリに関する苦情はないようですので、最適化は行いませんでした。問題が発生した場合は、遠慮なく報告してください。
「いいね！」 5